Thursday, January 26, 2006

Joomla still doesn't work with SQL mode enabled

In October, very shortly before MySQL 5 became a Production Release, I tested Joomla and found out that there were a lot of problems if SQL mode is enabled. I reported the problem, but the developers didn't seem very interested and argued, MySQL was still a Release Candidate.

Now, 3 months have passed by and some new maintenance releases of Joomla were released in the meantime, so I thought it would be interested if the problems have been solved.

I installed Joomla 1.0.7 (using my local MySQL 5.0.18-max-nt installation with SQL mode "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION") and the first thing I tried was to create a new user.

And it still failed. First I thought, this could be an Opera problem (as I use Opera as default browser) and tried it with Internet Explorer. It still failed. So I set the SQL mode to '' (empty) and tried it again - suddenly it worked. So the evidence is clear - Joomla still doesn't work with SQL mode enabled.

The roadmap announces for Joomla 1.1: "MySQL 5 support (to be confirmed)" - but I ask to myself, what has yet to be confirmed? MySQL is the primary and at the moment only database system that's supported, and they question if it's necessary to support the latest Production Release? Or did I get something wrong?

However, it's an important task for everyone who develops and maintains a MySQL driven application to make it compatible with enabled SQL modes. And generally it's caused by sloppy development in the past, if an application doesn't work.

Unfortunately, people might tend to blame MySQL if an application does not work correctly (simply thinking, it has worked with MySQL 4.x, but does not work with MySQL 5.0 - so it must be MySQL's fault). Also db4free.net uses SQL mode "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" and there have been some requests, why doesn't this or that application work. In most applications it's not that hard to change the SQL mode on a session basis - but it should really slowly come to an end that current versions of popular applications don't support enabled SQL mode.

3 comments:

Anonymous said...

Does Joomla create its own MySQL users? If so, NO_AUTO_CREATE_USER is a pretty big change for MySQL behavior from previous versions, and many users would find no reason to enable this sql mode. I'm not sure it's reasonable for application developers to continually be required to modify their applications to make them compatible with every combination of SQL modes selected by users.

More realistically, applications should probably set the SQL modes that suit them when they connect, though that practice does present some security concerns.

If the problem is that some columns don't have default values (I had that problem when using Wordpress with STRICT_TRANS_TABLES), then that IS a schema problem that should be corrected. The developer has been relying on MySQL laziness and there's no time better than the present to take a few minutes to bring thigns more into shape.

Markus Popp said...

The SQL modes that I use here are those that are set if you install MySQL under Windows with the installer and check the 'enable strict mode' box. So it seems to be kind of a recommandation from MySQL and so I went on using them.

Of course, applications wouldn't necessarily have to support every kind of SQL mode, but the queries should provide values for every field (as you said: not rely on MySQL's laziness), which seems to be the real cause of the problem e.g. in Joomla.

Anonymous said...

Is any solutions for Joomla 1.0.11. Does it work in this mode?