Why Am I Getting an 'Invalid Default Value for 'created_at'' Error in MySQL?

Invalid Default Value for 'created_at' Error in MySQL

When attempting to modify a table, the following error message appears:

ERROR 1067 (42000): Invalid default value for 'created_at'

This error typically arises when altering the timestamp column, but in this instance, it occurs while adding a new column. The affected table has 'created_at' and 'updated_at' columns as the last two entries.

Resolution

The cause of this error lies in the SQL mode settings. To rectify the issue, verify the current SQL mode using the command:

show variables like 'sql_mode' ; 

If the SQL mode includes "NO_ZERO_IN_DATE,NO_ZERO_DATE," remove these settings to resolve the problem. These settings are the default in newer versions of MySQL.

As the root user, you can modify the SQL mode globally using the command:

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

The above is the detailed content of Why Am I Getting an 'Invalid Default Value for 'created_at'' Error in MySQL?. For more information, please follow other related articles on the PHP Chinese website!