mysql - "#1067 - Invalid default value for 'post_date'" when trying to reset AI after backup

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?

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?

• mysql
• sql
• phpmyadmin

Share Improve this question edited Dec 20, 2024 at 12:24 fuxia♦ 107k3838 gold badges255255 silver badges459459 bronze badges asked Apr 18, 2017 at 14:10 SinisterBeardSinisterBeard 1,21733 gold badges1414 silver badges3333 bronze badges 1

• 1 Sounds like your tables were set to MyISAM instead of InnoDB – BenB Commented Apr 29, 2018 at 1:49

Add a comment  | 

8 Answers 8

Sorted by: Reset to default 18

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

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

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

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

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

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)

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.

this is the correct answer and solution to the question: https://gist.github.com/kaorukobo/718f4eb684ac01e1b65fc4cb69587fae

in short: you can not alter the table, because mysql detects an error default value for the field post_date (and others as well). so, just adapt the query to solve this problem, after that, you can alter the table as you wish.