1

First of all sorry everybody if this question sounds too basic.

I have a Wordpress database and a table wp_comments.

In this table, I have two DATETIME fields, whose default value is 0000-00-00 00:00:00.

I need to change this value to CURRENT_TIMESTAMP, so for example I run the following query:

ALTER TABLE `wp_comments` MODIFY `comment_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

So, comment_date (NOT comment_date_gmt) is the column to modify.

Now, when I run this query I get the following error:

Error 1067: Invalid default value for comment_date_gmt

Why is this happening? What could I do?

[UPDATE]

This is the table:

CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) UNSIGNED NOT NULL,
  `comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) NOT NULL DEFAULT '',
  `comment_type` varchar(20) NOT NULL DEFAULT 'comment',
  `comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `wp_comments`
  ADD PRIMARY KEY (`comment_ID`),
  ADD KEY `comment_post_ID` (`comment_post_ID`),
  ADD KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  ADD KEY `comment_date_gmt` (`comment_date_gmt`),
  ADD KEY `comment_parent` (`comment_parent`),
  ADD KEY `comment_author_email` (`comment_author_email`(10));

MySQL version is 5.7.33

[UPDATE]

I don't know WHY but I know HOW I solved this issue:

ALTER TABLE `wp_comments` MODIFY `comment_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY `comment_date_gmt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

This way, by modifying both fields together, I get no errors.

It works, but I'm still quite curious, so if have any idea to share... :-)

3
  • Hi, and welcome to dba.se! Please include the output (as text) of the command SHOW CREATE TABLE wp_comments\G! Use the edit link under the question! Aug 29 at 10:43
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Aug 29 at 10:43
  • @Community: I need to change the default datetime column value from 0000-00-00 00:00:00 to CURRENT_TIMESTAMP: I'm NOT talking about data stored, but field structure, instead. Sorry if my question wasn't clear Aug 29 at 11:02
0

As the manual says, for the DATETIME data type

supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

Presumably your table was created while the NO_ZERO_DATE mode was not in effect, which allowed the 0000-00-00 date part, but it seems to be in effect now, thus, along with the strict mode, producing the error you see. When you modify comment_date, other column definitions are also revalidated according to the current SQL mode, and revalidation of the comment_date_gmt fails.

1
  • Yes, the tables were created some years ago, you're right. And the NO_ZERO_DATE is currently "in effect". Thank you very much Aug 29 at 12:26

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.