SQLSTATE[42000]: 1067 Error When Setting Default datetime to ‘0000-00-00 00:00:00’ in Laravel 9 and MySQL 8.0

1. In MySQL 8.0, setting the default value of a datetime field to ‘0000-00-00 00:00:00’ throws the error: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for ‘operated_at_gmt’. See Figure 1.

2. Reference: https://www.shuijingwanwq.com/2022/09/28/6993/ 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 (in MySQL 8.0).

3. Now I need to set the SQL mode permanently. Reference: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html 5.1.11 Server SQL Modes

4. Edit C:\ProgramData\MySQL\MySQL Server 8.0\my.ini, search for: sql-mode, and remove: NO_ZERO_IN_DATE,NO_ZERO_DATE, keeping the rest.

# sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

# sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

5. After restarting the computer, re-running the corresponding SQL still throws an error. However, manually changing the datetime value of a record to ‘0000-00-00 00:00:00’ in the database succeeds. See Figure 2.

6. I copied the failing SQL and executed it manually in the database, which succeeded. See Figure 3.

7. Reference: https://stackoverflow.com/questions/18067614/how-can-i-set-the-default-value-of-a-timestamp-column-to-the-current-timestamp-w . I adjusted the migration file and gave up setting the default value to ‘0000-00-00 00:00:00’. See Figure 4.

// $table->dateTime('operated_at_gmt')->default('0000-00-00 00:00:00')->comment('操作时间')->index();
$table->dateTime('operated_at_gmt')->comment('操作时间')->index();

// $table->dateTime('operated_at_gmt')->default('0000-00-00 00:00:00')->comment('操作时间')->index();
$table->dateTime('operated_at_gmt')->comment('操作时间')->index();