The Upgrade TrapUpgrading from an older database like MySQL 5.6 to version 8.0 usually feels like a performance win. However, many developers hit a wall when running migrations or importing legacy SQL dumps. Suddenly, a simple ALTER TABLE command fails with a frustrating error:
ERROR 1067 (42000): Invalid default value for 'created_at'
This error typically targets TIMESTAMP or DATETIME columns using '0000-00-00 00:00:00' as a default value. While this worked for years in older environments, modern MySQL versions are much more protective of data integrity.
The Root Cause: Strict ModeThis isn't a bug in your code. It is a result of MySQL’s shift toward stricter data validation. Starting with version 5.7, released back in 2015, MySQL enabled "Strict Mode" by default. Two specific SQL modes trigger this 1067 error:
- NO_ZERO_IN_DATE: Prevents dates where the year is valid but the month or day is zero (like 2023-00-01).- NO_ZERO_DATE: Completely bans the dummy date
0000-00-00 00:00:00.Legacy applications often used these 'zero' dates as placeholders. Modern MySQL treats them as logical impossibilities. When you try to create a table with these defaults, the engine blocks the operation to prevent corrupt or nonsensical data from entering your rows.
The Quick Fix: Session-Based (Temporary)You might need to run a one-time migration or import a 500MB SQL dump without reconfiguring the entire server. In these cases, you can temporarily relax the rules for your current database session. Run this command before your main script:
-- View your current settings
SELECT @@sql_mode;
-- Disable date-specific strictness for this session
SET session sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
By removing NO_ZERO_IN_DATE and NO_ZERO_DATE from the string, you tell MySQL to look the other way just this once. This allows the 'zero' dates to pass through without error.
The Permanent Fix: Server ConfigurationTo ensure this error doesn't return after a server reboot, you must update your global configuration file. This is usually my.cnf on Linux or my.ini on Windows.
Step 1: Find your config file- Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf- CentOS/RHEL: /etc/my.cnf- Windows (XAMPP): C:\xampp\mysql\bin\my.ini### Step 2: Modify the SQL ModeLocate the [mysqld] section. If a sql_mode line already exists, edit it. If not, add it. Ensure the value does not include the two "ZERO" modes mentioned earlier.
[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Step 3: Restart the ServiceApply your changes by restarting the MySQL daemon:
# For Linux users
sudo systemctl restart mysql
# For macOS (Homebrew) users
brew services restart mysql
The Professional Approach: Schema UpdatesDisabling strict mode is a functional workaround, but it isn't the "cleanest" solution. The best practice is to update your schema to handle empty dates properly. Modern databases should use NULL or the current time instead of a string of zeros.
Instead of using an invalid default:
-- This fails in Strict Mode
ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT '0000-00-00 00:00:00';
Try one of these modern alternatives:
-- Option A: Allow NULL (Recommended for optional dates)
ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT NULL;
-- Option B: Use the current timestamp (Best for audit trails)
ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;
VerificationConfirm the fix by logging into your MySQL terminal and checking the global status:
SELECT @@GLOBAL.sql_mode;
If the output string is missing NO_ZERO_DATE, your CREATE and ALTER commands will now execute smoothly.
Pro-Tips for SuccessLegacy data often contains "dirty" timestamps that don't conform to ISO standards. I recommend using the Timestamp Converter on ToolCraft to verify if an integer timestamp converts to a valid human date before you attempt a bulk insert. It saves hours of debugging 'Invalid value' errors.
Check your application framework too. For example, Laravel defaults to 'strict' => true in config/database.php. Even if you change your server settings, the framework might override them during the connection phase. If the error persists, ensure your application-level DB config matches your server's new flexibility.