Upgrading to MySQL 5 – the hard way

Today I decided to upgrade the main database server to MySQL 5. As former upgrades went without problems I was expecting easy sailing. Oh boy, how wrong I was. The upgrade itself went as expected using the pre-compiled RPMs provided at mysql.com. However, after the upgrade I was presented with this error message:

Access denied for user ‘root’@'localhost’ (using password: YES)

As there was nothing wrong with the password I started MySQL using mysql –skip-grant-tables –user=root and had a look. Surprise, surprise. Doing this query

SELECT * FROM mysql.user WHERE User=’root’;

gave me an empty result set. However, the following query gave back one result:

SELECT * FROM mysql.user WHERE User LIKE ‘root%’;

So as it seems, with MySQL 5 the way the User field is interpreted has changed. The only way to fix this was to issue the following commands:

DELETE FROM mysql.user WHERE User LIKE ‘root%’;
INSERT INTO mysql.user VALUES (‘localhost’, ‘root’, PASSWORD(‘YourPassword’), ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0, 0, 0);
FLUSH PRIVILEGES;

Now I was able to log in again as root. However, this didn’t solve all my problems. It took me a while to figure out that I had to run mysql_fix_privilege_tables before the other accounts became usable again.
To ease my pains I found out that with MySQL 5 you can use the old 16 bit encrypted passwords and the new 41 bit encrypted passwords without specifying old-passwords in my.cnf and you can even mix their usage.

To sum it up:
1) Install the RPMs
2) Stop the MySQL server automatically started at the end of step 1)
3) Run mysqld –skip-grant-tables –user=root
4) Open a second ssh shell to the MySQL server
5) Run mysql -u root
6) Issue DELETE FROM mysql.user WHERE User LIKE ‘root%’;
7) Issue INSERT INTO mysql.user VALUES (‘localhost’, ‘root’, PASSWORD(‘YourPassword’), ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0, 0, 0);
(Don’t forget to replace YourPassword with your actual password ;))
8) Issue EXIT
9) Run mysql_fix_privilege_tables
10) Kill the MySQL server process using killall mysqld
11) Restart MySQL using /etc/init.d/mysql start (or whatever command your Linux distribution is using)
12) Try logging in with any account other than root

Incoming search terms:

• system upgrading 5 hard way