(en) MySQL 5.7 Data truncated for column ‘xxx’ at row 1

After upgrading to MySQL 5.7, unexpected problem appeared.

The col1 column is of ENUM type, and the empty string ” is not in its range.  This caused problem.

However, in MySQL5.6 there was no problems.  But it errs in 5.7.

After searching the internet, I found no help.  All were indifferent.

By carefully reading MySQL official document again, I found the true problem is by SQL mode.

If you have the same problem with me, try to do the following.

Make sure that STRICT_ALL_TABLES and STRICT_TRANS_TABLES are in the SQL_MODE environment variable.  Also, if you encounter invalid date problem, such as can not insert ‘0000-00-00’, then just put in SQL_MODE.

To make this setting on session base, do the following on every connection.

To set it globally for all sessions persistently, add the following setting in /etc/my.cnf .

After restarting mysqld, everything will be OK.

If you want to know more about SQL MODE, refer to the following page.

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

That’s all.

Leave a Reply

Your email address will not be published. Required fields are marked *