When we try to insert/update data that exceeds column length, the default behavior should be to truncate the value and insert/update the column; though this was there in MySQL 4 by default but strangely in MySQL 5.x this feature is disabled by default. In this article will highlight how we can resolve this problem.
To Highlight more on this check out the error message below:
mysql> create table user(id int PRIMARY KEY AUTO_INCREMENT, name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user values(“Hello World”);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1
In MySQL 5.x their are various Server SQL Modes and by default it is set to STRICT MODE. This does not allow to insert/update values exceeding column length. To overcome this their are two ways.
1) Set Value in Global Variable
2) Add Environment variable in my.cnf file
Setting Global Variable in MySQL:
You can check for mode set in your MySql by executing following query. If you are using MySQL5 it will return empty unless explicitly set.
Now to set it we will execute following query.
Adding Environment Variable in my.cnf file:
In this open my.cnf located at /etc and check for sql-mode text. Replace that text with following value.
So after adding this line your my.cnf will look like:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Finally restart the MySQL daemon to reflect the changes.