Errors when migrating from MySQL 5.6 to 5.7 and how to fix them - database dump import failed with an error or INSERT does not work. Disabling STRICT_TRANS_TABLES strict mode or using IGNORE

09.11.23 IT / Database 3680

If you unexpectedly discover that errors occurred when migrating to a new version of MySQL, then most likely you wrote the database query code in non-strict simple mode. This mode allows liberties, which, on the one hand, simplifies writing queries, but on the other hand, it generates many inaccuracies and errors. Let's look at how to avoid this or how to correct mistakes that have already been made.

MySQL Strict Mode

Starting with MySQL version 5.7, MySQL strict mode (STRICT MODE) is introduced, which is enabled by default. That is, previously it was also available, but it had to be enabled manually. This innovation causes errors with the written code - the site or web application refuses to work.

The first thing to check is whether strict mode is actually enabled in MySQL. If the STRICT_TRANS_TABLES value is present in the sql_mode variable, strict mode is enabled. We type in the console and look at the result, here is the command for this: SHOW VARIABLES LIKE 'sql_mode'.

Errors when migrating from MySQL 5.6 to 5.7

How do errors appear when migrating from MySQL 5.6 to 5.7? For example, most likely this was first noticed on local hosting. When changing the MySQL version, the code stopped working, for example:

• Import of database dump into phpMyAdmin completed with an error. It was necessary to manually edit the dump, removing lines with errors - for example, the data was too large;
• New records are not added to the database, the INSERT command does not work. This is because all fields in the table that do not have default values must be filled in. In non-strict mode there is no such requirement.

How to fix errors

How to fix errors when migrating from MySQL 5.6 to 5.7? Two scenarios can be distinguished:

• Fix the query code itself or the dump, as mentioned above;
• Turn off MySQL strict mode or fix the tables in the database - for example, set all the necessary default values (DEFAULT).

It’s better, of course, to immediately write the code in strict mode and without errors, but what exactly should be done to correct the situation that has already arisen according to the indicated scenarios? There are two possible solutions for each scenario, the first two points below relate to the first scenario, the second two respectively to the second:

• Review the table structure - set all necessary default values, check data types and length of inserted values;
• Look at all the code for working with the database and refactor it - set values for all fields without default values, check the correspondence of field types and added data, as well as their length. Or use the IGNORE command to ignore errors in a specific request;
• For local hosting or if you have access to configuration files: open the MySQL configuration file and in the [mysqld] section remove all restrictions, write sql-mode = "". Don't forget to restart the server;
• Write code at the beginning of your script that will execute the command: SET GLOBAL sql_mode = "".

So, the note can be quite useful, since quick solutions to such implicit problems will always be at hand when working with databases.