I’ve restored my WordPress database from an sql backup. However, in doing so, all of the tables have lost auto increment.
When I try to add it back in with this sql
ALTER TABLE `mercury_posts` CHANGE `ID` `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
I get the error #1067 - Invalid default value for 'post_date'
. How do I fix this?
5 Answers
The post_date default value is 0000-00-00 00:00:00. If you check the sql_mode variable like this:
show variables like 'sql_mode';
… it will show you the sql_mode variable, that will be sth like this: 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
You have to set up again sql_mode variable without
NO_ZERO_IN_DATE,NO_ZERO_DATE
So in the previous example you should set the sql_mode like this:
SET sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Then check the sql_mode variable again to be sure it has changed correctly:
show variables like 'sql_mode';
Then the restriction is gone ;D
Found the solution here: https://stackoverflow.com/a/37696251/504910