WordPress and MySQL's strict mode

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.

About The Author

Baron is the founder and CEO of VividCortex. He is the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. Baron contributes to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.


Comments