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

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

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 | |

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.