When you encounter the MySQL error "1067: Invalid default value," the immediate action you should take involves identifying the column and table causing the issue and then correcting the default value for that column. Follow these steps:
DESCRIBE yourtablename;
- For a DATE or TIMESTAMP column, ensure the default value is a valid date or '1970-01-01' if it must represent a 'zero' date. Alternatively, if your application logic allows, set it to `NULL` if the column is nullable.ALTER TABLE yourtablename MODIFY COLUMN yourcolumnname DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
- Or, for a nullable column:ALTER TABLE yourtablename MODIFY COLUMN yourcolumnname DATETIME NULL DEFAULT NULL;
SET GLOBAL sql_mode = '';
After this, you should plan to correct the default value as described above, as disabling strict mode can have broader implications.
Important: Always back up your database before making schema changes, and if possible, test these changes in a development environment first.
Let Dr. Droid create custom investigation plans for your infrastructure.
Start Free POC (15-min setup) →
