Saturday, January 21. 2006
Short Version: You can't.
But you are not totally hooped. Here is a quickie recipe if you didn't read the upgrading manual.
- Backup your tables! I did it by making a copy of the database directory. In Debian it's /var/lib/mysql. Also backup your servers my.cnf
- Get a list of your ISAM tables. Do this by issuing find /path/to/databases -name *.ISM >brokenTableList.txt.
- Convert your broken table list into a series of ALTER TABLE sql commands. I used awk because only one database was left un-upgraded. Basically you want to turn every directory in the find to use dirname and every line into ALTER TABLE filename-without-.ISM ENGINE=MYISAM. Save this new file to fixTables.sql
- Stop the mysql server.
- Copy the mysql database to something like mysql5, this is important because we have to reset the permissions table.
- uninstall MySQL 5
- Insall MySQL 4
- run the mysql_install_db script. This rebuilds your permissions table so you can fix your table
- Start Mysql 4. If you have problems, your my.cnf is probably the issue. check your syslog and try and fix the problem.
- Make a new mysql user for upgrading your tables: /usr/bin/mysqladmin -u root password 'foo'
- Run your upgrade script: mysql -u root -p
and enter in your password.
- stop the server.
- move the mysql table to something like mysql4. This is in case you need it back for whatever reason
- move mysql5 back to mysql
- Finish off by uninstalling mysql4, and going back to 5
Finished. Not easy, but not hard either. You can also use the mysql_convert_table_format script. In fact, that might be easier. I opted for this other route so I could see what was going on a little better. Upon retrospect I might have just used the script instead.
Next time, I am going to read the upgrade instructions, just in case. And maybe try to keep my table formats a little more current.