WordPress and MySQL's strict modeFri, Mar 15, 2013 in Databases
I really don’t like running my database in “I Love Garbage” mode, so I set the following SQL_MODE:
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE, NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
Guess what WordPress does with that? It doesn’t install. If you set the SQL_MODE to empty and install WordPress, then restore the SQL_MODE, WordPress will run, but if you try to create a post you’ll see an error page that says “You are not allowed to edit this post.”
This problem was reported to WordPress at least 7 years ago. Lessons learned:
- There is a huge amount of software that was built to work with MySQL 3.23′s irritating habit of inserting different data than you told it to, with nothing but a warning most people will never see.
- That software will break in unlovely ways when you try to make MySQL behave more correctly.
- Those who gripe about MySQL’s bugs (as I sometimes do) should remember that MySQL is probably better quality than most of the software that is built to use it. This is probably a universal truth – the Linux kernel is probably better quality than most software that runs in Linux, for example.
- MySQL’s bugs often get fixed faster than aforementioned software’s bugs, too.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.