Thursday, October 20, 2005

Web apps ready for MySQL 5?

I think there is no doubt that MySQL 5 is ready for web applications. But I'd like to put this question around and ask, whether the most popular web applications are ready for MySQL 5.

Well, this question is easy to answer, as long as MySQL 5 in not executed with any strict mode options. The behavior is similar to that of MySQL 4.x and so there should be no problems.

However, I have tested some popular web applications like Mambo/Joomla, phpBB and Typo3 with db4free.net, which uses the sql-mode:

"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION"

On Windows, this sql-mode is the default.

I'd like to start with Mambo. There is quite an old problem when multi byte character sets are used. Trying to install Mambo into a database using utf8, the creation of the tables fails here:
CREATE TABLE `mos_core_acl_aro` (
`aro_id` int(11) NOT NULL auto_increment,
`section_value` varchar(240) NOT NULL default '0',
`value` varchar(240) NOT NULL default '',
`order_value` int(11) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`hidden` int(11) NOT NULL default '0',
PRIMARY KEY (`aro_id`),
UNIQUE KEY `section_value_value_aro` (`section_value`,`value`),
UNIQUE KEY `mos_gacl_section_value_value_aro` (`section_value`,`value`),
KEY `hidden_aro` (`hidden`),
KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM

The reason is quite simple: the index length is limited to 1,000 bytes in MySQL. For single byte character sets, this is a legal statement, because the sums of the indexed column lengths are still less than 1,000 bytes. However, using utf8 or any other multi byte character set, this limit is exceeded. This problem isn't new in MySQL 5, it has also occured in MySQL 4.1.

In Joomla 1.0.3, this problem has been solved - I have been able to install Joomla 1.0.3 without any problems. But also Joomla is not free of MySQL 5 related problems. Creating a new user account, this account is created, but doesn't show up in the user management backend.
Joomla screenshot
I also have found problems trying to log in at the frontend - it simply didn't work. Searching inside the code, I found the source of the problem. They tried to insert a quoted value into a tinyint field - something that's also invalid in strict mode.

The next web app that I've tested was phpBB. It already failed during the installation at this set of queries (as you can see, I've used the German version of phpBB):
phpBB screenshot
CREATE TABLE phpbb_posts_text (
post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
bbcode_uid char(10) NOT NULL,
post_subject char(60),
post_text text,
PRIMARY KEY (post_id)
);

INSERT INTO phpbb_posts_text (post_id, post_subject, post_text) VALUES
(1, NULL, 'Das ist ein Beispiel-Beitrag. Du kannst ihn löschen, wenn Du
willst, denn so wie es aussieht, hat die Installation geklappt!

Hier noch ein paar wichtige Informationen zu phpBB

Weitere Informationen zu phpBB und seiner Benutzung und Administration
findest Du auf phpBB.de unter der URL - http://www.phpbb.de/doku/doku.php -.

Wenn Du Fragen hast oder Probleme mit einer Funktion, schau im Forum unter
der URL - http://www.phpbb.de/index.php - nach.

Da die meisten Fragen schonmal gestellt und beantwortet wurde, nutze bitte
die Suchfunktion unter der URL - http://www.phpbb.de/search.php - bevor
Du Deine Frage ins Forum schreibst.

Erweiterungen (Mods) zu phpBB findest Du unter der URL -
http://www.phpbb.de/moddb/ -.

So, und nun viel Spaß mit phpBB ;-)');

The reason for this can also be found easily. There is no default value for the column bbcode_uid, but no value has been provided for this column in the insert statement.
phpBB screenshot
Without strict mode, MySQL uses a system defined default value (in this case, an empty string), but in strict mode, a value must be provided for any field without a default value.

The third application that I've tested was Typo3. With Typo3, no errors occured during setting up the tables, so this Content Management System could possibly work with MySQL 5 without errors.

I have reported the errors that I've found to Joomla and phpBB and I hope that they solve these problems soon. There's always the danger that MySQL 5 has to take the blame for bad written queries in the web applications, because the users often don't know what the real source of the problem is. So I think that another important job to support MySQL 5 is to make developers of these web applications aware of those problems, so that they can fix them as soon as possible until they all work well with MySQL 5, also.

5 comments:

Roland Bouman said...

Hi Markus,

A while ago, I has some trouble too with MySQL 5 and older apps. I ran into some quoted default issues with XMB:
install/cinst.php: xmb_forums.theme has a wrong default '', should be 0
install/cinst.php: xmb_settings.tickerdelay has a wrong default '', should be 0

I guess we now know what to look for first when seeing this type of trouble. Good tip on the sql_mode thing!

Markus Popp said...

Hi,

that's exactly the issue I was talking about - many (not only older) applications rely on the correct type casting or that the correct default values are set.

Right now, I have a weird discussion with a phpBB developer and guess what: he's refusing (!) to add a default value to the create statement or to provide a value for the bbcode_uid column (mentioned in my article) in the insert statement just because MySQL 5 isn't GA, yet. That's so stupid - it's bad programming style anyway, to not provide values for the columns that have no default values. But he simply doesn't accept my argument that it's not even a MySQL related topic, but a topic of good programming style in general.

I hope that developers of other web applications care more about their programming style to clear these problems. I would hate that MySQL had to take the blame for their bad programming style. Users might simply think: the application worked before - with MySQL 5 it doesn't, so it has to be MySQL's fault. But that's not fair and I'll do what I can to clarify that.

Roland Bouman said...

Markus, I have no dealings with this phpBB app (lucky me, so it seems?). I got to agree it's kind of stupid to categorically refuse to add an appropriate default. I really cannot see how it could hurt the previous app versions.

On the other hand, I can appreciate an app developers argument that he/she's busy fixing something else that's more urgent, or something like that...even if the current situation is considered sloppy, bad, whatever programming practice.

Anyway, if the app's any good, it probably explicitly states it's dependencies. And; theyll be on it soon enough.

Markus Popp said...

> On the other hand, I can
> appreciate an app developers
> argument that he/she's busy
> fixing something else that's more
> urgent

That's true, but they have spent so much time with arguing that they could have solved this problem a hundred times in the meantime.

But maybe now, as MySQL 5 is a Production Release ;), they will do it ... I hope so.

Anonymous said...

Thank you very much for the link to typo3 and its problems with mysql 5 it helped me fix the problems