6

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
  • 1
    Sounds like your tables were set to MyISAM instead of InnoDB
    – BenB
    Apr 29, 2018 at 1:49

7 Answers 7

14

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

3
  • 2
    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, 2020 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. Jul 10, 2020 at 16:24
  • @CarlosFaria provided code does not work for the described problem. But the code provided by popealope works fine. Therefore i prefer to correct this in the post.
    – STORM
    Jun 19, 2023 at 12:00
10

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";
1
  • SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; this has solved it for me
    – tmarois
    Mar 7, 2022 at 20:26
3

You can use following command to change your default post_date:-

  ALTER TABLE `wp_posts` 
  CHANGE `post_date` `post_date`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
1
  • This should be how Wordpress creates new schemas. Aug 8, 2022 at 19:11
2

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

1

First run

ALTER TABLE `wp_posts` 
  CHANGE `post_date` `post_date`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE `post_date_gmt` `post_date_gmt`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE `post_modified` `post_modified`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE `post_modified_gmt` `post_modified_gmt`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

then after auto increment

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jul 13, 2023 at 18:27
0

Changing sql_mode to

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

works, but it only lasts until you restart the sql server.

So in case you need it again, you have to change sql_mode again.

0

The answer of Carlos Faria is perfect !

But here is a small code you can use if you are on PhpMyAdmin and doesn't want to mess with the SQL configuration.

Here I gonna change the sql_mode value but only for the session, just the time needed to alter the table :

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE',''));

ALTER TABLE wp_posts auto_increment = XXX;

(Don't forget to change the table prefix wp_ and replace XXX by the new AI value you want)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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