MySQL Upgrade To 5.1: Database Name Prefix #mysql50#

Oh Dear monitors your entire site, not just the homepage. We crawl and search for broken pages and mixed content, send alerts when your site is down and notify you on expiring SSL certificates.

Start your free 10 day trial! »

Profile image of Mattias Geniar

Mattias Geniar, August 07, 2010

Follow me on Twitter as @mattiasgeniar

If you’ve upgraded from a MySQL version prior to 5.1, to a MySQL version of 5.1 or later, you might find some of your databases have their names altered to “#mysql50#dbname", where it used to be just “dbname".

This is because in MySQL 5.1 the data directory structure changed.

You can either alter your queries, so you retrieve from the database “#mysql50#dbname”, or you can be sane and do the following query to upgrade the data directory structure. Note; this assumes you have shell access to the server with enough privileges! If you don’t, please contact your server administrator.

mysql > ALTER DATABASE `#mysql50#dbname` UPGRADE DATA DIRECTORY NAME;

Note the backticks (`), that’s not a single quote.

You might run into the following error when you first do so:

ERROR 1558 (HY000): Column count of mysql.proc is wrong. Expected 20, found 16. 
Created with MySQL 50077, now running 50148. Please use mysql_upgrade to fix this error.

To fix this, run the following on the server’s command line.

# mysql_upgrade -h localhost -u root -p

For Plesk environments, you can use

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

This will update the necessary database entries to the latest version of MySQL. Chances are, this will even have fixed the ‘#mysql50#’ database prefix for you. If it hasn’t, return to the first step here.

More information can be found at the bottom of MySQL’s Documentation on the ALTER DATABASE command.



Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.