Upgrading MySQL 5.0 to 5.1 on a Plesk machine.

By default a CentOS 5 machine comes with MySQL 5.0
For several reasons you might want to have MySQL 5.1 on it. Even on a Plesk machine.

It is very well possible to upgrade such a CentOS5 Plesk machine with MySQL 5.1
For example see here: http://www.atomicorp.com/wiki/index.php/Mysql
If you follow the Atomic Turtle guide, you will have no problem.

But when you did not do that, you will run into several problems:

1. Some of your databases no longer work and when you look at them with phpMyAdmin, you will see a database name preceded with “#mysql50#”
To solve this issue, you can do either one of the following solutions A or B:

A: upgrade the data directory structure.
You need to login into your MySQL server as root.
So  issue following command:

mysql -u admin -p`cat /etc/psa/.psa.shadow`
mysql> ALTER DATABASE `#mysql50#databasename` UPGRADE DATA DIRECTORY NAME;

B: restore a database dump.
if you were clever and you got a mysqldump of your databases from before you upgraded, you could simply restore it:

mysql -u admin -p`cat /etc/psa/.psa.shadow` < databasename.sql

2. You might get some error message containing “Please use mysql_upgrade to fix this error”
When upgrading from MySQL 5.0 to 5.1 you MUST always run the mysql_upgrade command, like so:

mysql_upgrade -h localhost -u admin -p`cat /etc/psa/.psa.shadow`

3. When trying to add a user to a database, you get this error: error connection to the database server has failed table ‘mysql.servers’ doesn’t exist
Then just use the following command to fix the privileges:

mysql_fix_privilege_tables -u admin -p`cat /etc/psa/.psa.shadow` --verbose

It will be followed by outputting a bunch of ERROR 1060 (43S21) and alike. That’s ok. After that, your mysql table will contain 23 (MySQL 5.1) rowns instead of just 17 (MySQL 5.0)