Solving awful "preview button" problem

Issue description: For some reason instead of “post” button “preview” button was showed, even for admin, and after pressing it nothing was happening.

Once I faced this issue I was googling a lot and only things I found were:
– use another theme;
– update wordpress;
– disable all plugins and enable one-by-one.

Which are horrible suggestions.

There were only two good suggestions which lead me to solution, but they were not 100% solving problem.

– Warnings in SQL dump for WP website and messed up auto_increment indexes
Not main problem, just nice to clean dump up a bit and fix warnings and auto_increment indexes so they really represent amount of data in your table, not some random numbers.

– DATETIME format was wrong
It was ‘0000-00-00 00:00:00’, instead of ‘1000-01-01 00:00:00’.
MySQL Reference:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

Some guy was suggesting to modify WP code to fix it which it too radical in my opinion. So, there is another solution for that problem, which is main problem BTW.

Solution:

Be aware of certain properties of date value interpretation in MySQL:

– MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as ’10:11:12′ might look like a time value because of the “:” delimiter, but is interpreted as the year ‘2010-11-12′ if used in a date context. The value ’10:45:15’ is converted to ‘0000-00-00′ because ’45’ is not a valid month.

– The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as ‘2004-04-31’ are converted to ‘0000-00-00’ and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.7, “Server SQL Modes”, for more information.

So all you need is to set correct sql-mode in your my.cnf (dunno how it called on Win).
Instead of sql-mode = TRADITIONAL or any mode you have to mode which will tolerate bad DATETIME syntax, e.g. sql-mode = "STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
Or just do not enable NO_ZERO_DATE.

That’s it.

P.S. I had this issue on WP 3.4.x or something like that. I wasn’t 3.6 or any later version.