【唐诗】《送別》 王維：下馬飲君酒，問君何所之。君言不得意，歸臥南山陲。但去莫復問，白雲無盡時。

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

[ERR] 1292 – Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘created_at_gmt’ at row 1 When Copying a Table Between Databases in Navicat for MySQL (MySQL 8.0)

In Navicat for MySQL, when copying a table from one database to another, an error occurs: [ERR] 1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at_gmt' at row 1

作者：

永夜

在

, , , , , , , , , , , , ,
Views: 58

1. In Navicat for MySQL, when copying a table from one database to another, an error occurs: [ERR] 1292 – Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘created_at_gmt’ at row 1. See Figure 1.

In Navicat for MySQL, when copying a table from one database to another, an error occurs: [ERR] 1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at_gmt' at row 1
Figure 1
Plaintext

[DTF] Drop table: `attribute_names`
[DTF] Create table: `attribute_names`
[DTF] Begin transaction on target server
[DTF] Start transfer data for table: `attribute_names`
[ERR] 1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at_gmt' at row 1
[DTF] End transaction on target server
[DTF] Finished unsuccessfully

2. Execute: SELECT VERSION(), and I find that the versions of the two database software are different. They are: 5.7.19-log and 8.0.23-0ubuntu0.20.04.1. See Figure 2.

Execute: SELECT VERSION(), and find that the versions of the two database software are different. They are: 5.7.19-log and 8.0.23-0ubuntu0.20.04.1
Figure 2

3. Since the target database is deployed based on Homestead, edit Homestead.yaml and add: mysql8: false in the “features” settings. See Figure 3.

Since the target database is deployed based on Homestead, edit Homestead.yaml and add: mysql8: false in the "features" settings
Figure 3
YAML

features:
    - mysql: true
    - mariadb: false
    - postgresql: false
    - ohmyzsh: false
    - webdriver: false
    - mysql8: false

4. First destroy the virtual machine: vagrant destroy –force, then start vagrant: vagrant up. It is still MySQL 8, because the built-in software is already MySQL 8. See Figure 4.

First destroy the virtual machine: vagrant destroy --force, then start vagrant: vagrant up. It is still MySQL 8, because the built-in software is already MySQL 8
Figure 4

5. Execute: SHOW VARIABLES LIKE ‘sql_mode’; to check the SQL mode. Under 5.7 it is: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION. Under 8.0 it is: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION. See Figure 5.

Execute: SHOW VARIABLES LIKE 'sql_mode'; to check the SQL mode. Under 5.7 it is: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION. Under 8.0 it is: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Figure 5

6. Reset the SQL mode under 8.0, remove: NO_ZERO_IN_DATE,NO_ZERO_DATE, and keep the others. Then close the connection, reopen the connection, and check the SQL mode; it has taken effect. See Figure 6.

Reset the SQL mode under 8.0, remove: NO_ZERO_IN_DATE,NO_ZERO_DATE, and keep the others. Then close the connection, reopen the connection, and check the SQL mode; it has taken effect
Figure 6
SQL

set GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

7. Press Ctrl + C again, then press Ctrl + V in the target database, and no error occurs anymore. See Figure 7 and Figure 8.

Press Ctrl + C again, then press Ctrl + V in the target database
Figure 7
No error occurs anymore
Figure 8

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我  ·  GitHub  ·  邮件联系

更多文章