I just upgraded mysql from 5.6 to 5.7 .A query used to work but after upgrading it don't seem to work anymore..The below error when i run the query on the mysql 5.7

DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'last_touch_by' at row 1

Can anybody help.. Thanks

  • 1
    this might be due to the variable innodb_strict_mode which is on in MySQL 5.7 and in MySQL 5.6 it might have been off, the quick fix it to turn this off using set global innodb_strict_mode=0 but you should make sure that you adhere the maximum limits set by MySQL – Nawaz Sohail Mar 6 '18 at 17:49
  • @Nawaz Sohail thanks for you reply...also after upgrading ..my show databases query is very slow and also have some slowness on my phpmyadmin when pulling databases and tables..any help please?.. – Byg Eynst Mar 6 '18 at 18:42
  • SHOW CREATE TABLE and show us the value you are trying to store. – Rick James Mar 8 '18 at 20:00

Presumably the problem is that your sql_mode variable now has STRICT in it, which means that inserts/updates with varchar values that are too long for the defined length of the columns now lead to errors, whereas previously the values were truncated without any errors.

To go back to the old behaviour, you can edit your /etc/my.cnf file, look for the sql-mode variable, and remove the STRICT keyword from it. Then either restart mysqld or just log in and set sql_mode dynamically:

SET GLOBAL sql_mode='the same value as in the .cnf file here';
  • @@dbdemon thanks for you reply...also after upgrading from mysql 5.6 to 5.7..my show databases query is slow and also experienced some slowness on my phpmyadmin when pulling databases and tables..any help please? – Byg Eynst Mar 6 '18 at 19:06
  • @BygEynst - that sounds like a different issue. You should create a new question for that, with as much detail as possible. – dbdemon Mar 6 '18 at 21:14
  • Yes @dbdemon...the solution you gave was right. i already figured that out yesterday and it worked...as for the new question, i already created another question with subject 'SHOW DATABASES query slower after mysql upgrade"..Please take a look at it and see how you can help – Byg Eynst Mar 6 '18 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.