CentOS 6.6 and MySQL 5.6

By default, CentOS 6.6 will come with MySQL 5.1 packages. After MySQL upgrade to version 5.6, some old good working SQL inserts start to cause errors. The error message was “data too long” which was a bit strange.

First, let’s look isolated SQL insert with error output:

mysql> insert into my_table(mt_employee, mt_ip, mt_agent)
values (123, '10.1.36.19', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.89 Safari/537.36')

1406: Data too long for column 'mt_agent' at row 1

Table structure was intact before and after MySQL upgrade.

CREATE TABLE `my_table` (
  `mt_id` int(11) NOT NULL auto_increment,
  `mt_employee` varchar(5) character set latin2 collate latin2_bin NOT NULL,
  `mt_ip` varchar(20) collate latin2_croatian_ci NOT NULL,
  `mt_agent` varchar(50) collate latin2_croatian_ci NOT NULL,
  `mt_now` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY (`mt_id`)
) ENGINE=InnoDB

Yes, value of “mt_agent” is longer than varchar length but expected behaviour was to truncate surplus and execute insert. That was how MySQL 5.1 manage similar SQL inserts without throwing an error. Change in version 5.6 is STRICT_TRANS_TABLES set as default SQL mode and that causes error instead of implicit data truncate. In MySQL documentation all SQL modes are named and described:

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

With the following SQL select it’s possible to check SQL_MODE system variable of the MySQL server:

mysql> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

The solution for CentOS 6.6 is to disable STRICT_TRANS_TABLES in the /etc/my.cnf configuration file. Here is how “sql_mode” settings looks in CentOS:

[mysqld]
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

So, just delete STRICT_TRANS_TABLES save my.cnf and restart MySQL server. This will force MySQL 5.6 to behave like it was in version 5.1 and “data too long” errors will be gone.

[mysqld]
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION

At the end, I would consider to disable this default SQL strict mode and instead try to fix application. I would consider disabling default SQL strict mode and instead try to fix application. This kind of approach will have greater benefit in application stability and resolving some implicit actions of MySQL server executed in the background.