I’ve spent too many hours this week porting the company database from MySQL 3.23 to 5. This was probably a bit more painful than it needed to be because we’ve not upgraded MySQL versions… ever.

The biggest hurdles were manipulating the SQL dump file (we had to dump and re-import due to MyISAM changes between versions) and leaving MySQL 5.0’s server mode as STRICT (as recommended by the installer).

Manipulation of the dump file (which was 13GBs) really made it stand out how modern programmers don’t necessary think about resources (or don’t have to with memory managed languages). I guess with the ample resources of most desktops for day-to-day operations its easy to see why. However, trying to load a file of this size made every Windows text editor I tried, fall over (either loading or saving).

In the end I chopped the file into 1GB chunks and edited those with gVim. In hindsight I should have split by line number and not by filesize.

Although it was the easy way out (and we should have updated our SQL), changing the mode to mysql323 saved us a LOT of work. Few application changes were required, the main one was for a replication issue. Replication would stop on certain CREATE TABLE statements where a default value was specified on integer auto_increment columns (gotta love legacy application code!).

When running our system on my dev machine with PHP 5.1.4 and MySQL 5 it did feel ’snappy’. I think this was probably more psychological than anything else.

For several months now I’ve thought MySQL 5’s push to a production ready status was more of a political move than technically justified. First I read this and again today when it came time to compile any MySQL support into PHP 5.1.4 on my newly installed Linux box I ran into problems.

We’re in the process of making the move to MySQL 5.0 at work too; however since we’re moving from 3.23.x there’s nothing too clever going on with the SQL. I always check up on the changes made in recent point releases. MySQL 5 scares me because of some the bugs still unpatched (SELECT DISTINCT queries sometimes returned only the last row), we’re now on more point releases for 5.0 than 4.1 (21 vs 19).