MySQL: Value Truncated When Inserting Into a Table

Again as a newbie in MySQL, I created a procedure that would insert values into a table depending on the business requirements. However, there I noticed that if a value with length created than the table column, MySQL truncates the value to fit the column.


Here’s what I did:


mysql> create table sample (sample_col varchar(3));
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //

mysql> create procedure sample_proc (in par_var varchar(2))
-> begin
-> insert into sample values (par_var);
-> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call sample_proc ('XXX');
Query OK, 1 row affected, 1 warning (0.00 sec)

Notice that MySQL issued a warning.
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'par_var' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
Checking the table, I get

mysql> select * from sample;
+------------+
| sample_col |
+------------+
| XX         |
+------------+
1 row in set (0.00 sec)
MySQL did truncate the value. I tried researching the issue but unfortunately I wasn’t able to find a document that resolves the issue (or maybe I wasn’t using the right keywords). So, being a newbie, I created a topic on the MySQL website (http://forums.mysql.com/). Peter Brawley, one of the forum members, suggested to change the sql_mode to ’strict_trans_table’:


set sql_mode='strict_trans_tables';
After which, I tried running the procedure again:


mysql> call sample_proc ('XXX');
ERROR 1406 (22001): Data too long for column 'c' at row 1
This corrected the issue that I was having. However, this need to be in effect on the entire database. Thus, I added this variable in the /etc/my.cnf file:


sql-mode = 'strict_trans_tables'
Note that the variable in the configuration file has a hyphen (-) instead of an underscore (_).