In Navicat for MySQL, when the table is copied from one database to another, an error is reported:[ERR]1292 – Incorrect DateTime Value:0000-00-00 00:00:00for columnCREATED_AT_GMTAT ROW 1 (in MySQL 8.0)

1. In Navicat for MySQL, when the table is copied from one database to another, an error is reported:[ERR]1292 – Incorrect DateTime Value:0000-00-00 00:00:00for columnCREATED_AT_GMTat row 1. as shown in Figure 1

Figure 1

[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(), it is found that the version of the two database software is inconsistent. They are: 5.7.19-log and 8.0.23-0ubuntu0.20.04.1. as shown in Figure 2

Figure 2

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

Figure 3

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

4. Destroy the virtual machine first: vagrant destroy –force, and then start vagrant: vagrant up. Still mysql 8 because the built-in software is already mysql 8. as shown in Figure 4

Figure 4

5. Execute: show variables likesql_mode;, view the SQL mode. 5.7 is: Strict_trans_tables, no_auto_create_user, no_engine_substitution. 8.0 The following is: ONLY_FULL_GROUP_BY, Strict_Trans_Tables, NO_ZERO_IN_DATE, NO _zero_date, error_for_division_by_zero, no_engine_substitution. as shown in Figure 5

Figure 5

6. Reset the sql mode under 8.0, delete: no_zero_in_date, no_zero_date, and keep the others. Then close the connection, then open the connection, and check the SQL mode, which has taken effect. as shown in Figure 6

Figure 6

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

7. Ctrl + C again, and then Ctrl + V in the target database, no more errors are reported. Figure 7, Figure 8

Figure 7

8