Why Am I Getting MySQL ERROR 1067: Invalid Default Value for 'created_at'?

Troubleshooting MySQL ERROR 1067: Invalid Default Value for 'created_at' While Adding a New Column

Attempting to alter a table by adding a new column may result in the error "ERROR 1067 (42000): Invalid default value for 'created_at'". This error commonly occurs when altering timestamp columns; however, it can also arise when adding non-timestamp columns.

Cause:

The error is caused by MySQL's sql_modes settings. Specifically, the "NO_ZERO_IN_DATE" and "NO_ZERO_DATE" modes prevent the default timestamp values '0000-00-00' and '00:00:00' from being stored in created_at and updated_at columns.

Solution:

To resolve the error, remove the "NO_ZERO_IN_DATE" and "NO_ZERO_DATE" modes from the sql_modes setting.

Steps:

1. Verify Current sql_modes:

   show variables like 'sql_mode' ; 

2. Remove Offending Modes:

   • Temporarily disable the modes for the current session:

     SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

   • Set the global modes permanently:

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

3. Reattempt Adding Column:

   ALTER TABLE investments ADD bank TEXT;

Note:

The default sql_mode in newer versions of MySQL includes the "NO_ZERO_IN_DATE" and "NO_ZERO_DATE" modes. Therefore, it is important to regularly check the sql_modes setting and make necessary adjustments when encountering this error.

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