Thursday, January 8, 2009

Of MySQL 5.1 and Joomla part 1

Some of you might know that I run a little website on my few spare hours (actually, I have several sites, but I have one that takes up some 95% of all the time I spend on these sites). The site is called PapaBlues and if you pop by some time, and you have seen it before, then you realize that there has been a very major restructuring. The old site is all gone, and the thing is now built using the Joomla CMS as the framework, whereas the old site was using homebuild PHP, HTML and SQL in a bit of a mess, like so many other sites.

I took the new site live just before Christmas, and as MySQL 5.1 had just been declared GA, I decided to use this for my site. I have to say that I am very happy with it so far, stable and performant, and with a some new useful features, I will write more about these in a later blogpost.

If you are currently using Joomla and want to upgrade to MySQL 5.x, then I can tell you that this works fine. To an extent at least, but the limitations are by far more in Joomla than in MySQL, sadly.

What I mean when I say that I use Joomla as a framework, is that I use the Joomla basic libraries, database components etc, and some base components also, but very few of the Joomla modules, components or templates. Except the articles, all the content is provided by my own home-built components that keep track of blues bands, gigs, festivals etc, all running in my own designed template (this I am a bit proud of, as I am a very bad GUI and grphic designer, but I think the look of this turned out OK).

One thing that bugs me with Joomla is that the developers of it seems set in MySQL 4.0 days, actually, in the database mdules, there is a specific "SET SQL_MODE = 'MYSQL40'" command. Come on now, Joomly guys, MySQL 4.0 is about to become extinct! In the comment to this command in the database module, there is even a comment that says that this is to overcome the issues with "STRICT" settings. Yeah right, I want it STRICT so that I know things are rights, just as I want NO WARNINGS when I compile a piece of code. No, this isn't good enough.

In theory, Joomla is database agnostic, at least on paper. In the real world, it is firmly set not only with MySQL but also with MySQL 4.x syntax and behaviour. Try to use the Joomla installer to install a stored procedure or function. It will not work, the SQL installer doesn't understand the delimiter command. This is annoying, but frankly, the stuff still works, even with MySQL 5.1.

You have complete control of your SQL in your own modules, which makes life easier. In the database tables that I use for my modules, not the tables that Joomle use interally, I use InnoDB with aggressive foreign keys all over the place, just the way I like it. I want to be able to mock around a bit with the data, even with interactive SQL, knowing that my relations are still intact.

NULL values is another area where the Joomla database developers has gone all wrong. So very wrong. In essence, they assume that NULL is the same as 0 (for a numeric values) or the empty string for a string value. This I can to an extent understand, as an HTML form, which is the base for much of the administration of a Joomla site at least, doesn't know about NULL either. This makes things a mess when it comes to FOREIGN KEYS. A NULL in a referencing key is allowed, without a matching references key, but not so a 0 or an empty string.

But why would you want that anyway? A refernce to any value is useless, right? Well, if we are talking single column keys, yes, that is probably true, but for a multi column key, this is a very useful feature. I will explain why in a later blogpost, and how this works.

All in all then, am I happy with my Joomla and MySQL 5.1 combination? Yes, sure, it works as expected. I do have quite a few issues with Joomla, the docs are no good, there are few really good books on it (some half-good are avilable now), the database support is not as good as it should be, and the database independence features that Joomla provides aren't good enough, and aren't used much even in the basic Joomla modules. Fact is, I tried running without MYSQL40 mode, and failed. And with STRICT mode, it was even worse. I gave up trying to fix this.

But it does work, it comes for free, the framework seems reasonably stable and once you get the hang of it, it is easy to use. The issue is that it just takes too much work to get the hang of it. If you have a simple site with some article you want to publish, then Joomla is just fine. If you are picky with the database interface and has some special needs, prepare to spend some time with it.

One sign, if you ask me, that the docs are not that good, is that fact that even developing an application with Joomla, not developing Joomla itself, I constantly go back to read the source.

But Joomla is helping me, the site is finally up and running, I have developed quite a bunch of useful Joomla utilities and components (one utility is a PapaTable class, derived from the JTable class, that fixes one big NULL value issue with Joomla), and people have told me they like the site, so maybe I shouldn't complain. And instead of complaining, I plan to write a MySQL 5.0 database module for Joomla, based on the MySQL 4.0 assuming ones that already exist. And then we'll see.

And BTW, don't try running Joomla with the Joomla system tables in InnoDB, it is error prone and not even that useful.



Dado said...

If you need a framework, try ZF. The "figure out the framework quirks" applies here too, but IMHO the platform is much more powerful.

Karlsson said...

Well, you might be right, I just happened to end up with Joomla. The reason was that when I started, I was under the impression that I could use many more of the CMS features that I ended up using.
The lesson is to check if you REALLY need a CMS before you get one. And that said, I actually tend to think Joomla is OK.


Roland Bouman said...


"in the database mdules, there is a specific "SET SQL_MODE = 'MYSQL40'" command."

Really - this is not bad at all! Most PHP apps simply assume default settings and err in case your server happens to be configured differntly. You may not like the mode it uses, but at least the took it into account and took the trouble to ensure MySQL was doing what they were expecting...

Anders Karlsson said...

I beg to differ. September 3 1967 sweden changed from driving on the left to the right, but today, driving on the left in the name of backwards "compatability" is a bad idea. MySQL 4 is OLD now, get over it!

opensourcesociologist said...

I don't think anyone says Joomla! is database agnostic. In fact there is a project to work towards that goal, but it's a development project not finished.

It has already been announced that 1.6 will be php 5.2+ and Mysql 5+. But it is true that Joomla is designed to work on many hosts and meet many needs, from people on shared environments where they don't necessarily have the option to go to the latest stack to people like you who control their own.

I will say that the dev team is very, very happy that that the minimum version decisions have been made.

Karlsson said...

Oops, I'm sorry that I missed the announcement that Joomla will support MySQL 5+. And make no mistake, I actually LIKE Joomla, quite a bit, and I'm not about to change my view on that.
But fact remains that using foreign keys is difficult, NULL value implementation is lacking and use of the database partability features (for quoting of names and strings etc etc) that the database library in Joomla supports, is far from complete even in the rest of the library or in the supplied modules.
I really do think transactions are a good thing, and that they should be used, or at least properly supported.
That Joomly clearly has made an attempt to be a bit database agnostic is clear, if for no other reason so from the structure of teh database implementation. But as long as things are progressing, I'm happy, and I'll be glad to upgrade to 1.6 eventually.

masterchief said...

Interesting article Karlsson. If you are up to it, I'd love to go deeper into any recommendations you have for the API. Drop me a line at andrew DOT eddie AT

I doubt we can do much for 1.5 but 1.6 is a new animal and our options are still open there.