Why Am I Getting 'ERROR 1067 (42000): Invalid Default Value for 'created_at'' When Adding a Column in MySQL?

"ERROR 1067 (42000): Invalid Default Value for 'created_at'" Explained

When attempting to modify a table by adding a new column, you may encounter an error stating: "ERROR 1067 (42000): Invalid default value for 'created_at'." This error seems counterintuitive since you are not altering the 'created_at' column itself.

The Root Cause

The issue stems from the MySQL configuration settings known as SQL modes. Specifically, the "NO_ZERO_IN_DATE" and "NO_ZERO_DATE" SQL modes. These modes restrict default date and timestamp values to include both a date and time component, disallowing values with a time component of '00:00:00'.

When you create a new table, it inherits the SQL modes active at the time of creation. In the case where these SQL modes are enabled, it impacts the default values for timestamp columns like 'created_at'. Adding a new column to a table with these SQL modes activated attempts to override the existing default value for 'created_at', which is not allowed.

Resolution

To resolve this issue, you can identify the active SQL modes by running the command:

show variables like 'sql_mode' ; 

If the "NO_ZERO_IN_DATE" and "NO_ZERO_DATE" SQL modes are present, you can disable them by setting the SQL mode to remove these restrictions. You can do this globally as the root user with the command:

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

After making the necessary changes to the SQL mode, you should be able to add the new column without encountering the error.

The above is the detailed content of Why Am I Getting 'ERROR 1067 (42000): Invalid Default Value for 'created_at'' When Adding a Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!