.deb sql_mode default breaks wordpress and similar applications

[Kristian Nielsen]

Our .deb packages include a default my.cnf file which sets sql_mode=NO_ENGINE_SUBSTITUTION,TRADITIONAL

This is a change from the MySQL packages in stock Debian and Ubuntu, which do not set sql_mode in their default my.cnf, and so default to a less strict sql_mode.

One could argue that the stricter sql_mode is a better default. However, in practice it causes problems.

The problem is that there are other packages in Debian that depend on a MySQL (or MariaDB) server, and which do not work with this stricter sql_mode.

One such example is Wordpress. We have had several people have problems after installing MariaDB and Wordpress with apt, and then finding it does not work (I had the issue myself). The users then think that MariaDB is not compatible with Wordpress, when in reality it is just the sql_mode that is not supported by Wordpress.

So we have to change the default sql_mode to be the same as MySQL Debian packages, so that a default install of the Debian Wordpress package will work with MariaDB.

A user that understands my.cnf and sql_mode can easily change it as desired. A user that does not will be confused by Wordpress not working.

I will change the default my.cnf for the next release.

[Kristian Nielsen (knielsen)]

Fix should be in 5.1.56 and 5.2.6

[Arjen Lentz (arjen-lentz)]

Kristian, I believe your patch is wrong.

I can see the point of not putting TRADITIONAL in the defaults, but there is no case for removing NO_ENGINE_SUBSTITUTION within the scope of this bug report. So rather than removing the entire line, the TRADITION bit should be removed, keeping the sql_mode=NO_ENGINE_SUBSTITUTION

By the way, the rationale for also putting TRADITIONAL in is that this is the default on Windows installs, and it's been applauded by users then asking to also have this put in place on Linux. I do however appreciate that it can break existing apps, and so commenting it out with a hint can make the most sense. In the end WordPress should be able to work in strict mode, so that's something other devs (me included as I'm a wordpress user) can look at.

[Kristian Nielsen (knielsen)]

Debian packages do not have NO_ENGINE_SUBSTITUTION in default my.cnf.

Have you tested that adding this does not break any other Debian packages?

[Arjen Lentz (arjen-lentz)]

I didn't say Debian packages have that setting. I said that removing that setting as part of this bug report makes no sense as it has no bearing on the bug that was reported. Modifying features in a bugfix that are unrelated to the bug is confusing and is highly likely to cause problems elsewhere.

The sql_mode=NO_ENGINE_SUBSTITUTION makes sense in the context of the current defaults.
In old Debian, there was skip-innodb. Naturally, we don't have that as we want InnoDB enabled by default, and I believe the current Debian defaults enable InnoDB also.
When it's enabled and configured, the no engine substitution clause merely guarantees that if due to another misconfiguration InnoDB becomes unavailabe (not initialised during startup), and an application does CREATE TABLE ... ENGINE=InnoDB, it throws an error rather than silently creating a MyISAM table. The same applies to other engines, including PBXT, FEDERATED, SPHINX, OQGRAPH, etc.

It cannot break anything, because when a specific engine is specified in a create/alter table statement, actually delivering that engine is exactly what's desired. In fact, not delivering it with a silent fail or conversion to another engine is what can cause trouble. And this setting prevents that.

For instance, consider Federated or Sphinx not initialising during startup due to a misconfiguration - because mysqld only aborts if the default engine does not initialise (MyISAM or InnoDB), it'll merely log to errorlog/syslog and continue, but the failed engines won't be available. Then a table is created with that engine, silent converted back to MyISAM or InnoDB. If Federated/Sphinx specific options are specified it'll cause a syntax error which is confusing in the context, but otherwise it's all silent.
Similarly, an OQGRAPH table would always fail silently since it never contains any special keywords or options other than the engine type. Obviously, if you run OQGRAPH query constructs on MyISAM or InnoDB tables, you get incorrect query results.
That is all completely undesirable behaviour, an thus NO_ENGINE_SUBSTITUTION is the correct default choice.
As others have noted on the mailing list, it would make even more sense to put such settings in the build defaults.

I hope this clarifies. Please revert that part of your fix.