MySQL ERROR 1067 (42000): Invalid default value for 'created_at' - Analysis and Solutions

Problem Background and Error Phenomenon

During MySQL database operations, when attempting to modify table structures, you may encounter the ERROR 1067 (42000): Invalid default value for 'created_at' error. This error typically occurs in operations involving timestamp fields, even when the operation itself doesn't directly modify the timestamp fields.

For example, in the following scenario:

mysql> ALTER TABLE investments ADD bank TEXT;
ERROR 1067 (42000): Invalid default value for 'created_at'

The user is merely trying to add a new TEXT type column but receives an error about an invalid default value for the created_at field. This indicates that the problem may be hidden in the existing table structure rather than the current operation itself.

Root Cause Analysis

Through in-depth analysis, the root cause of ERROR 1067 lies in MySQL's sql_mode settings. sql_mode is an important configuration parameter for MySQL servers that defines the strictness and compatibility level of SQL statements.

In newer versions of MySQL (particularly 5.7 and above), the default sql_mode includes NO_ZERO_IN_DATE and NO_ZERO_DATE modes. These modes prohibit the use of zero values like "0000-00-00" as default values for date and timestamp fields.

To check the current sql_mode settings, use the following command:

SHOW VARIABLES LIKE 'sql_mode';

Typical output might include:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

When a table contains timestamp fields (such as created_at, updated_at) and these fields' default values conflict with the current sql_mode, any table structure modification operation may trigger this error.

Solutions

Method 1: Check and Modify sql_mode

First, check the current sql_mode settings:

SHOW VARIABLES LIKE 'sql_mode';

If the output includes NO_ZERO_IN_DATE and NO_ZERO_DATE, you can temporarily remove these modes:

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

This command requires root privileges and only affects the current session. To make it permanent, you need to modify the MySQL configuration file (such as my.cnf or my.ini).

Method 2: Quick Solution for Development Environment

In development environments, you can temporarily clear sql_mode:

SET sql_mode = '';

Important Note: This method is only suitable for development and testing environments and is not recommended for production use, as it disables all SQL mode checks and may affect data integrity.

Method 3: Fix Table Structure

If the table structure itself has issues, you need to check and repair the timestamp field definitions. The referenced article shows that some tools (like HeidiSQL) may not properly handle the CURRENT_TIMESTAMP keyword.

The correct timestamp field definition should be:

created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Instead of:

created_at TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP'

Note that CURRENT_TIMESTAMP should not be enclosed in quotes, as it is a MySQL built-in function, not a string value.

Technical Details

Impact of SQL Modes

The NO_ZERO_DATE mode prohibits using "0000-00-00" as a date value, while NO_ZERO_IN_DATE mode prohibits using zero months or days in dates (like "2023-00-00"). These modes are enabled by default in MySQL 5.7 and above, aiming to improve data integrity.

When these modes are enabled, if a table contains timestamp field default values that violate these rules, any ALTER TABLE operation may fail, even if the operation itself doesn't involve timestamp fields.

Version Compatibility Considerations

Different MySQL versions handle sql_mode differently:

• MySQL 5.6 and earlier: NO_ZERO_IN_DATE and NO_ZERO_DATE not included by default
• MySQL 5.7 and above: These modes included by default
• MySQL 8.0: Further strengthens SQL mode strictness

When upgrading MySQL versions, pay special attention to how these changes might affect existing applications.

Best Practice Recommendations

Production Environment Configuration

In production environments, it's recommended to maintain appropriate SQL modes to ensure data integrity:

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

This configuration removes NO_ZERO_IN_DATE and NO_ZERO_DATE but retains other important integrity checks.

Table Design Recommendations

When designing tables containing timestamp fields:

• Use TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP for creation time
• Use TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for update time
• Avoid using string-form default values
• Ensure default values are compatible with current sql_mode

Tool Usage Considerations

When using database management tools (like HeidiSQL, phpMyAdmin, etc.), be aware that they may process SQL statements differently. It's recommended to use the SHOW CREATE TABLE command to verify the actual table structure before important operations.

Conclusion

The ERROR 1067 (42000): Invalid default value for 'created_at' error is typically caused by MySQL's sql_mode settings, particularly the NO_ZERO_IN_DATE and NO_ZERO_DATE modes. By properly configuring sql_mode, fixing issues in table structures, and following best practices, you can effectively prevent and resolve such errors. Special attention should be paid to how these configuration changes might impact your systems during database migrations and version upgrades.