WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 2 years ago

#41785 closed defect (bug) (duplicate)

I don't think the default date "0000-00-00 00:00:00" is good idea in MySQL 5.7+

Reported by: kmvan Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.8.1
Component: Database Keywords:
Focuses: Cc:
PR Number:

Description

In MySQL 5.7+, the default date "1970-01-01 00:00:00" is better than "0000-00-00 00:00:00".
It will show a invalid date when date is "0000-00-00 00:00:00" in MySQL 5.7+

Shows: Invalid default value for 'post_date'.

Change History (4)

#1 @Collizo4sky
2 years ago

+1 for this.

I use this for my plugin DB schema 1000-01-01 00:00:00

Last edited 2 years ago by Collizo4sky (previous) (diff)

#2 @dd32
2 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Even in MySQL 5.7 if the SQL Mode is set correctly, it's supported.

Although we wish a better default was chosen, lots of plugins expect this date format and there's not a lot we can do.
For consistency, this was discussed previously in #8857 and where it should be brought up.

#3 @Collizo4sky
2 years ago

  • Resolution duplicate deleted
  • Status changed from closed to reopened

@dd32 the date format is correct. The issue is the default value of 0000-00-00 00:00:00. Please read through https://stackoverflow.com/questions/23153822/mysql-datetime-default-current-timestamp-error

Setting it to 1000-01-01 00:00:00 or epoch time 1970-01-01 00:00:00 will fix this. And it's backward compatible with older MySQL versions. So nothing will break.

#4 @dd32
2 years ago

  • Resolution set to duplicate
  • Status changed from reopened to closed

@Collizo4sky I'm aware of the issue, and it's covered well in #8857

Unfortunately many plugins expect the exact string "0000-00-00 00:00:00" Which is where the backwards compatibility issues come in, we can't simply change it easily.

Further discussion should occur on #8857 in order to reduce as much duplication as possible.

Invalid default value for 'post_date'.

If the correct sql modes are set (as they are during the WordPress install) then that warning shouldn't be hit, if you're getting that error from within WordPress (rather than through a sql import or other sql application) then extra details would be appreciated on how you're hitting it.

Note: See TracTickets for help on using tickets.