Upgrading from MySQL 3.23 to 5.0
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.
