sql-mode 'TRADITIONAL' breaks installation

[tsunami7]

The installation script for WordPress relies on a bug in MySQL which allows invalid times and dates, as well as input which is not in an ENUM specification list. In MySQL the solution for this problem is to set sql-mode = 'TRADITIONAL'

But when the database server is set this way, the installation script for WordPress crashes while building the database. There are numerous dateTime fields which have a default set to '0000-00-00 00:00:00' These values are not allowed when sql-mode is set to TRADITIONAL.

WordPress should not rely on bugs in MySQL.

This bug is related to #2148 which was supposedly fixed, but apparently the solution wasn't complete.

[DD32]

See Also: #8857

The installation script for WordPress relies on a bug in MySQL which allows invalid times and dates

I'm not aware of any times when WordPress stores a non-valid date (Excluding the Zero value)

There are numerous dateTime fields which have a default set to '0000-00-00 00:00:00' These values are not allowed when sql-mode is set to TRADITIONAL.

Looking through the manual, I don't see anything defining this, It does say that DateTime's have to have valid data, however, that the Zero value ('0000-00-00 00:00:00') Is still valid - I dont see how it couldn't be valid either, It would be hideously stupid to require a date all the time, there are many use-cases(like this) where the date will not be known.

Can you supply the exact config (or something that allows for reproduction) under which WordPress/MySQL has the errors?(ie. That forces you to set traditional mode)

[ryan]

see #8857

[tsunami7]

MySQL Configuration file

[tsunami7]

See ​http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_traditional

Zero dates are not allowed when using TRADITIONAL. This mode is used to "Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column." - from the top of that document.

The reason I found out about this is, the standard configuration of MySQL allows you to insert an empty string, even when the column type is an ENUM and the field is set to NOT NULL. This caused a major problem in another database, so now I configure all installations to act according the SQL standard.

[tsunami7]

WP Schema file

[mrmist]

In a more general sense, this could be fixed by setting a session mode for the mysql connection. After all, some of the stricter modes force proper group by syntax which many of the database queries in WordPress don't use.

However, I guess this must be fairly niche, because otherwise there would be a lot more clamour over it.

[tsunami7]

Probably correct.

I doubt most MySQL admins have even run into this problem. It's a pretty obscure bug that I ran into in one of my production machines, which forced me to start setting things up this way.

However the change to the schema should be pretty trivial. Just pick a valid date as the default, and never use it again. The only problem would be if the WP code is using those default dates somewhere. Hopefully it isn't. In theory fixing this should make WP more portable to alternate DB servers.

[janeforshort]

Punting due to feature freeze. Reconsider with next release.

[Denis-de-Bernardy]

see #8857