catalogue
Background problem analysis and solution
Background
Import the test environment data into the local database and report incorrect datetime value: '0000-00-00 00:00:00' , and the error details are as follows
[DTF] Data Transfer started
[DTF] 0> Getting tables
[DTF] 1> xx: Getting table structure
[DTF] 1> xxx: Fetching records
[DTF] 1> xx: Drop table
[DTF] 1> xx: Create table
[DTF] 1> xx: Transferring records
[ERR] 1> INSERT INTO xxx VALUES (xxxx
[ERR] 1> 1292 – Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘create_ time’ at row 373
[DTF] Process terminated
Problem analysis
Why is there 0000-00-00 00:00:00 ?
The official document states that MySQL allows’ 0000-00-00 ‘to be saved as a “pseudo date” (if no is used)_ ZERO_ Date (SQL mode). In some cases, this is more convenient than using null values (and takes less space for data and indexes).
Solution
The solution is to set sql_ Mode , cancel no_ ZERO_ DATE。 Let’s first look at SQL in the local environment_ Mode setting. Use show variables like '% SQL'_ mode%';, the results are as follows.
Value：ONLY_ FULL_ GROUP_ BY,STRICT_ TRANS_ TABLES,NO_ ZERO_ IN_ DATE,NO_ ZERO_ DATE,ERROR_ FOR_ DIVISION_ BY_ ZERO,NO_ AUTO_ CREATE_ USER,NO_ ENGINE_ SUBSTITUTION
Then look at the test environment
it is easy to find that there is no No_ ZERO_ Date . Now let's modify the my.cnf file of MySQL and modify sql_ Mode (if not, add it under [mysqld]) and remove No_ ZERO_ DATE。 The code is shown below.
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Then import again, and you can succeed. The results are as follows
