Analysis and Solutions for 'Invalid Default Value' Error in MySQL TIMESTAMP Fields

Problem Background and Error Phenomenon

During MySQL database table creation, developers frequently encounter issues with TIMESTAMP field default values. Specifically, when executing CREATE TABLE statements containing TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', the system returns ERROR 1067 (42000): Invalid default value for 'create_date'. This error typically occurs in MySQL 5.6 and later versions, closely related to the system's SQL mode configuration.

Root Cause: SQL_MODE and NO_ZERO_DATE

The core reason for this error lies in MySQL's SQL_MODE settings, particularly the impact of the NO_ZERO_DATE mode. When NO_ZERO_DATE is enabled, MySQL in strict mode does not allow '0000-00-00' as a valid date. Starting from MySQL 5.6, the default SQL_MODE typically includes NO_ZERO_DATE, causing traditional zero-date default values to be rejected.

In non-strict mode, the system accepts zero-date values but generates warning messages. This design change reflects MySQL's emphasis on data integrity and standard compliance, preventing potential date calculation errors and data inconsistency issues.

Solution 1: Adjust SQL_MODE Configuration

The most direct solution is to modify the current session or global SQL_MODE settings. The NO_ZERO_DATE restriction can be temporarily removed using the following SQL command:

SET SESSION sql_mode = 'ALLOW_INVALID_DATES';

More comprehensively, modify the sql_mode parameter in the MySQL configuration file:

# In my.cnf or my.ini configuration file
[mysqld]
sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After modifying the configuration, restart the MySQL service for the changes to take effect. This approach is suitable for scenarios requiring backward compatibility but requires attention to potential data validation risks.

Solution 2: Use Valid Default Values

A more recommended solution is to use valid default values that comply with the TIMESTAMP range. The valid range for TIMESTAMP type is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. The default value can be modified to:

CREATE TABLE IF NOT EXISTS `erp`.`je_menus` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `create_date` TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

Solution 3: Use NULL Values as Alternative

For certain business scenarios, using NULL values as defaults may be more appropriate:

CREATE TABLE IF NOT EXISTS `erp`.`je_menus` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `create_date` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

This approach allows the field to be automatically set to NULL when inserting records, avoiding date validation issues while maintaining data flexibility.

Practical Application Considerations

When choosing a solution, specific application requirements must be considered:

• If maintaining compatibility with legacy systems is necessary, adjusting SQL_MODE may be required
• In new system development, using valid date defaults or NULL values is recommended
• Tables with multiple TIMESTAMP fields require special attention due to MySQL's specific auto-update behavior for the first TIMESTAMP field

Version Compatibility Notes

MySQL 5.6 and later versions enforce stricter date validation, which is the main reason for the frequent occurrence of this error. Developers need to pay special attention to compatibility issues arising from version differences when migrating databases or deploying new environments. It is recommended to adopt standard-compliant date handling methods during the development phase to avoid unexpected errors in production environments.