How to Fix MySQL Error: Invalid Default Value for 'create_date' Timestamp Field

Table of Contents#

1. Understanding the Error

   • What Triggers the Error?
   • Common Scenarios

2. Root Causes of the Error

   • Strict SQL Modes (NO_ZERO_DATE, NO_ZERO_IN_DATE)
   • Invalid Default Values (e.g., '0000-00-00 00:00:00')
   • Conflicts with NOT NULL Constraints

3. Step-by-Step Solutions

   • Solution 1: Use CURRENT_TIMESTAMP as the Default (Recommended)
   • Solution 2: Modify SQL Mode to Disable Strict Checks (Not Recommended)
   • Solution 3: Allow NULL Values (If Applicable)

4. Preventing the Error in New Tables

5. Troubleshooting Persistent Issues

6. Conclusion

7. References

Understanding the Error#

What Triggers the Error?#

The error "Invalid default value for 'create_date'" occurs when MySQL rejects the default value assigned to a TIMESTAMP column (e.g., create_date). MySQL enforces strict rules for timestamp defaults to ensure data integrity, and if your default value violates these rules, the database throws this error.

Common Scenarios#

You’ll encounter this error in situations like:

• Creating a new table with create_date TIMESTAMP DEFAULT '0000-00-00 00:00:00'.
• Altering an existing table to add a TIMESTAMP column with an invalid default.
• Upgrading MySQL (e.g., from 5.6 to 5.7+) where strict SQL modes are enabled by default.

Root Causes of the Error#

To fix the error, we first need to understand why MySQL rejects the default value. Here are the most common culprits:

1. Strict SQL Modes: NO_ZERO_DATE and NO_ZERO_IN_DATE#

MySQL’s SQL modes are a set of rules that define how the database handles invalid or non-standard data. Two modes in particular often cause this error:

• NO_ZERO_DATE: Rejects '0000-00-00' as a valid date.
• NO_ZERO_IN_DATE: Rejects dates with a month or day of 00 (e.g., '2023-00-15' or '2023-02-00').

By default, MySQL 5.7+ enables strict SQL modes (via the STRICT_TRANS_TABLES mode, which includes NO_ZERO_DATE and NO_ZERO_IN_DATE). If your create_date column uses '0000-00-00 00:00:00' as a default, these modes will block it.

2. Invalid Default Values for TIMESTAMP#

TIMESTAMP columns have specific requirements for default values:

• Prior to MySQL 5.6.5, TIMESTAMP columns required a default value (unless using NULL with NULL allowed).
• Even in newer versions, using '0000-00-00 00:00:00' as a default is invalid under strict modes (due to NO_ZERO_DATE).
• If you set DEFAULT NULL but the column is declared NOT NULL, MySQL will reject it (since NULL isn’t allowed).

3. Conflicts with NOT NULL Constraints#

If your create_date column is defined as NOT NULL but has no valid default (e.g., DEFAULT '0000-00-00 00:00:00' or no default at all), MySQL will throw the error. NOT NULL columns must have a default value that MySQL accepts.

Step-by-Step Solutions#

Let’s resolve the error with three actionable solutions, starting with the most recommended approach.

Solution 1: Use CURRENT_TIMESTAMP as the Default (Recommended)#

The best practice for a create_date column is to set its default to CURRENT_TIMESTAMP, which automatically populates the column with the current date and time when a new record is inserted. This avoids invalid zero dates and aligns with MySQL’s timestamp handling.

Step 1: Check the Current Column Definition#

First, verify the existing definition of create_date using DESCRIBE or SHOW CREATE TABLE:

-- Replace 'your_table' with the name of your table
DESCRIBE your_table;
 
-- Or for more details:
SHOW CREATE TABLE your_table;

Look for the create_date row. You might see something like:

create_date | TIMESTAMP | NOT NULL | DEFAULT '0000-00-00 00:00:00'

Step 2: Alter the Table to Use CURRENT_TIMESTAMP#

Use ALTER TABLE to update the default value to CURRENT_TIMESTAMP:

ALTER TABLE your_table
MODIFY COLUMN create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

What this does:

• MODIFY COLUMN: Updates the definition of create_date.
• TIMESTAMP NOT NULL: Ensures the column cannot be NULL.
• DEFAULT CURRENT_TIMESTAMP: Sets the default to the current date/time when a new row is inserted (without requiring manual input).

Solution 2: Modify SQL Mode to Disable Strict Checks (Not Recommended)#

If you must use '0000-00-00 00:00:00' (e.g., for legacy compatibility), you can disable NO_ZERO_DATE and NO_ZERO_IN_DATE in MySQL’s SQL mode. Note: This is not recommended, as strict modes protect against invalid data. Use this only as a last resort.

Step 1: Check the Current SQL Mode#

Run this query to see your active SQL modes:

-- Check global SQL mode (applies to all new connections)
SELECT @@GLOBAL.sql_mode;
 
-- Check session SQL mode (applies to your current connection)
SELECT @@SESSION.sql_mode;

You’ll see a comma-separated list of modes (e.g., STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,...).

Step 2: Temporarily Disable Strict Modes (Session-Level)#

To disable modes for your current session (resets when you disconnect):

-- Remove NO_ZERO_DATE and NO_ZERO_IN_DATE from the session SQL mode
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_DATE', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_IN_DATE', '');

Step 3: Permanently Disable Strict Modes (Global-Level)#

To make the change permanent, edit MySQL’s configuration file (my.cnf on Linux/macOS or my.ini on Windows):

1. Locate the config file:

   • Linux/macOS: Typically /etc/mysql/my.cnf or /etc/my.cnf.
   • Windows: Usually C:\ProgramData\MySQL\MySQL Server X.X\my.ini.

2. Add/modify the [mysqld] section to exclude NO_ZERO_DATE and NO_ZERO_IN_DATE:

   [mysqld]
   sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

   Note: Remove NO_ZERO_DATE and NO_ZERO_IN_DATE from the existing sql_mode line.

3. Restart MySQL for changes to take effect:

   # Linux/macOS
   sudo systemctl restart mysql
    
   # Windows (via Command Prompt)
   net stop mysql
   net start mysql

Solution 3: Allow NULL Values (If Applicable)#

If your application allows create_date to be NULL (e.g., for records where the creation date is unknown), you can modify the column to allow NULL and set DEFAULT NULL:

ALTER TABLE your_table
MODIFY COLUMN create_date TIMESTAMP NULL DEFAULT NULL;

When to use this: Only if NULL is semantically valid for your use case. Most applications require a create_date, so this is less common than Solution 1.

Preventing the Error in New Tables#

To avoid the error when creating new tables, define create_date with a valid default from the start. Here’s a best-practice CREATE TABLE example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    -- Use CURRENT_TIMESTAMP for create_date
    create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL DEFAULT NULL
);

Why this works:

• TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ensures create_date is auto-populated with the insertion time.
• No invalid zero dates, so strict modes won’t block it.

Troubleshooting Persistent Issues#

If the error persists after applying the solutions above, check these common pitfalls:

1. Other Columns May Be Causing the Error#

The error message may mention create_date, but other TIMESTAMP columns in the table could also have invalid defaults. Run SHOW CREATE TABLE your_table to audit all columns.

2. MySQL Version Compatibility#

• MySQL 5.7+: Strict modes are enabled by default. Use CURRENT_TIMESTAMP for defaults.
• MySQL 5.6 or earlier: NO_ZERO_DATE may be disabled by default, but TIMESTAMP columns have stricter default requirements (e.g., only one TIMESTAMP column could have DEFAULT CURRENT_TIMESTAMP).

3. ALTER TABLE Failed to Execute#

If ALTER TABLE returns an error, ensure you have write permissions on the table. Check the MySQL error log (e.g., /var/log/mysql/error.log on Linux) for details.

4. Config File Changes Not Applied#

If you modified my.cnf/my.ini but the SQL mode didn’t update, ensure:

• The config file was saved correctly.
• MySQL was restarted (required for global config changes).

Conclusion#

The "Invalid default value for 'create_date'" error is almost always caused by invalid zero-date defaults, strict SQL modes, or NOT NULL conflicts. The best solution is to use CURRENT_TIMESTAMP as the default for create_date, ensuring compatibility with strict modes and proper timestamp handling.

By following the steps in this guide, you’ll resolve the error and adopt industry best practices for timestamp columns in MySQL.

References#

• MySQL Documentation: TIMESTAMP Default Values
• MySQL Documentation: SQL Modes
• MySQL Documentation: NO_ZERO_DATE Mode
• MySQL Documentation: ALTER TABLE Syntax