Clients database with #mysql50# prefix

Created:

2016-11-16 13:22:06 UTC

Modified:

2017-08-08 13:39:43 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Clients database with #mysql50# prefix

Applicable to:

  • Plesk 10.4 for Linux
  • Plesk 11.0 for Linux
  • Plesk 11.5 for Linux

Symptoms

Several of the clients database names were prepended with the #mysql50# prefix. The database names were formatted as: #mysql50#dbname instead of being named just dbname .

Cause

MySQL introduced encoding for table names that have non-ASCII characters. After a live upgrade from MySQL 5.0 to 5.1 or higher, the server recognizes names that have non-ASCII characters and adds a #mysql50# prefix to them.

This happens because the data directory structure has changed on MySQL 5.1 and you need to upgrade the directory structure for the affected database(s).

Resolution

  1. Back up all existing databases using the following commands

    One by one database backup:

    # mkdir /tmp/mysql_backup/
    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin psa -Ne"show databases"|grep -v information_schema|while read db;do MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin $db > /tmp/mysql_backup/$db.sql;done

    OR dump databases in one file:

    # mkdir /tmp/mysql_backup/
    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin --all-databases > /tmp/mysql_backup/all.`date +%F_%H.%M`.sql
  2. Upgrade the data directory structure:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow`  mysql -uadmin  psa
    mysql > ALTER DATABASE `#mysql50#dbname` UPGRADE DATA DIRECTORY NAME;

    Note the backticks (`), that is NOT a single quote.

    If you get the following error:

    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.

    run mysql_upgrade :

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

    This will update the necessary database entries to the latest version of MySQL.

Have more questions? Submit a request
Please sign in to leave a comment.