2

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?

  • 1
    Sounds like your tables were set to MyISAM instead of InnoDB – BenB Apr 29 '18 at 1:49
2

I eventually solved this by deleting the faulty database, backing up again from the working database but exporting structure and data separately.

| improve this answer | |
2

You must to add this code at the top of your SQL

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
| improve this answer | |
2

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

| improve this answer | |
  • 1
    The above added an incorrect AUTO_CREATE_USER. The correct code was in the linked URL (stackoverflow.com/a/37696251/504910) set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; – popealope Jul 7 at 6:43
  • The code I wrote is without NO_ZERO_IN_DATE,NO_ZERO_DATE. The original sql_mode variable in my system was with NO_AUTO_CREATE_USER so I left it that way. – Carlos Faria Jul 10 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.