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
Read More:
- Converting string object into datetime type in pandas
- Error converting data type nvarchar to datetime
- Word column after the text evenly distributed in the left and right two columns, rather than fill in the left column and then fill in the right column, how to do?
- Solution of error converting data type varchar to datetime in SQL Server
- In the SQL query statement, add the column that does not exist in the table and set the fixed value of the column
- Arithmetical overflow error converting expression to data type datetime
- mysql error 1033 Incorrect information in file: ‘xxx.frm’ (Fixed)
- java.sql.SQLException: Incorrect string value:
- Mybatis integrates Oracle query and reports an error in the datetime type field
- 8、Forbidden (CSRF token missing or incorrect.): /register/
- When calling time module – time / datetime in wxPython, an error is reported. Valueerror: unknown locale: zh cn
- Gradle location is incorrect in IntelliJ
- ginx-1.10.0\index/**“ failed (123: The filename, directory name, orvolume label syntax is incorrect)
- Caused by: java.io.IOException: CreateProcess error=87, The parameter is incorrect
- Resolve Vue error empty block XXXX
- Flutter solves the problem of incorrect use of parent datawidget and boundary constraint
- Mysql reports an error Operand should contain 1 column(s)
- remote: XXX Incorrect username or password (access token) fatal: Authentication failed for XXX
- sqlite3.OperationalError: no such column:
- 1067 – Invalid default value for ‘sex‘1366 – Incorrect string value: ‘\xE6\x8A\x80\xE6\x9C\xAF…‘ f