Problems with MySQL upgrading from 5.1.63 to 5.6.20

To improve the efficiency of MySQL master-slave replication, upgrade MySQL from 5.1.63 to 5.6.20, the upgrade process is very smooth, but there are problems during the migration of the data,

The first data migration is directly using the method of data file migration: Stop the original 5.1.63 database service, copy the physical files of the database directly to the data directory of the 5.6.20 database, start the 5.6.20 database service, start very normal, but when opening the front-end application, a lot of data can not be rendered properly, into the database See a lot of data missing, should be high version of the low version of the data file identification problems, so from the low version to the high version of the data migration can not use the method of directly copying data files. Copy files directly when the same large version of the case.

The second migration, because the first migration failed, so the second time had to dump the data directly, using the tool Mysqldump-uroot-proot database >./data.isql the entire library to be migrated data back up, Generate Data.isql, this process depends on the size of the database, time is different, and so on after the data dump, using the command Mysql-uroot-proot database <./data.isql, import the backup file into the 5.6.20 databases. All normal, everyone front-end application, data display is also very normal, in the preparation of the time, the problem occurs, in the front-end application can not add data, the hint to add failure.

The debug information in the process of adding data to the front-end application is printed, and it is found that the value of a column (View the field's properties is allowed null null value exists) does not cause an error,  Why did you not have this problem before in 5.1.63, viewing the configuration file for the 5.6.20 database found a problem, the configuration file for that version of the database was added by default Sql_mode=no_engine_substitution,strict_trans_tables This configuration, where the parameter strict_trans_table, indicates that the SQL statement is to be strictly checked, is not allowed to have a null value exists, you must assign a value to the field, and the lower version of 5.1.63 The default is no parameter in the database, that is, do not strictly check your SQL syntax.

The solution to the problem is simply to remove the strict_trans_tables parameter, or comment out the sql_mode of the entire parameter, and restart the database.

This database version upgrade and data migration is also twists, at least calculate the migration success.

This article is from the "Dances With Wolves" blog, please be sure to keep this source http://renwd.blog.51cto.com/858281/1552485

Problems with MySQL upgrading from 5.1.63 to 5.6.20