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.

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):

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.

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.