Why Does Adding a Column Result in an 'Invalid Default Value for 'created_at'' Error in MySQL?

Invalid Default Value for Created_At

Problem:

When attempting to alter a table by adding a new column, you encounter an error:

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

despite not modifying any timestamp columns.

Solution:

The error originates from sql_modes. To resolve it:

1. Check the SQL Modes:

show variables like 'sql_mode' ;

2. Disable Problematic Modes:

Remove the following modes from sql_mode:

NO_ZERO_IN_DATE
NO_ZERO_DATE

These modes are present by default in newer MySQL versions.

Global Setting:

For a system-wide change, execute the following as root:

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

Additional Notes:

• The new column can now be added without triggering the error.
• If you wish to use the default NOT NULL value for created_at, you can specify it explicitly:

ALTER TABLE investments ADD bank TEXT DEFAULT NOT NULL;

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