mysql yum upgrade error

[faris]

I just updated MySQL (yum upgrade mysql) on a Centos 5 system.
The old version was mysql-5.5.32-something.el5.art
and the new one is mysql-5.5.33-20.el5.art

As usual, part of the upgrade script stops MySQL, does some stuff, then starts it again.
Well, during that part of the upgrade process, this error was displayed :

Code: Select all

Failed to ALTER DATABASE `#mysql50#database-name_mysql037dd079c45f2d82a7moreredacted` UPGRADE DATA DIRECTORY NAME

Error: Can't create database 'database-name_mysql037dd079c45f2d82a7moreredacted'; database exists

[The "moreredacted" part is just some more random hex strings which I've redacted for no particular reason]

I'm after some guidance here as I'm not quite sure what's happening.

I'm guessing this might have something to do with the hyphen in the database name - isn't there some MySQL 5.5 thing where hyphens are not supported anymore or something like that? And I note it says #mysql50# at the start of the first line. So I'm further guessing that this database was created when MySQL 5.x was installed, which was happy with hyphens, and that the "mysql_upgrade" script which I believe gets run as part of the installation/upgrade script in the atomic rpms is choking on the database name or something?

The database in question still works fine.

What's the best option to resolve it? Ignore it? Dump the database, create a new one without a hyphen in the name and restore it? Or something else?

I'm also curious to know why has this error has only just cropped up now - but maybe that's a red herring -- I'm not 100% certain that I've personally upgrade MySQL on this particular system before now so maybe that's all there is to it, and it isn't a new error.

EDIT:
Yup. Looks like I was right about the cause. Just noticed this in MySQL.log:

Code: Select all

[ERROR] Invalid (old?) table or database name 'database-name_mysql037dd079c45f2d82a7moreredacted'

Thanks,

[scott]

This is an event specific to just plesk boxes that parallels asked us to add in. What it does is handle migrations from 5.0 to 5.1 or 5.5 in the background.

[faris]

Can you be more specific about that please Scott? What exactly is it supposed to do?

[scott]

Sure, you can dump the script out with rpm -q --scripts <packagename>. Normally I wouldnt do this in a package, but this is a post install function they sent to us. It would only execute on a system with plesk on it.

Code: Select all

datadir=/var/lib/mysql
socketfile="$datadir/mysql.sock"
mypidfile=/var/run/mysqld/mysqld.pid
errorlogfile=/var/log/mysqld.log

start_nonsecure() {
        /etc/init.d/mysqld stop || :

        /usr/bin/mysqld_safe   --datadir="$datadir" --socket="$socketfile" \
                --log-error="$errlogfile" --pid-file="$mypidfile" \
                --user=mysql --skip-grant-tables  >/dev/null 2>&1 &

        STARTTIMEOUT=120
        while [ $STARTTIMEOUT -gt 0 ]; do
                if /usr/bin/mysqladmin ping 2>/dev/null; then break; fi
                sleep 1
                STARTTIMEOUT=`expr $STARTTIMEOUT - 1`
        done
}
run_upgrade() {
        start_nonsecure

        # black magic 1: mysqlupgrade does not actually able to upgrade
        # InnoDB tables, as REPAIR TABLE do not work on them. 
        # So they need to be re-created.
        # OPTIMIZE TABLE does not work on them as well, but does re-creation
        # instead. But we actually want to re-create them!
       tmpfile=$(/bin/mktemp)

        no_repair="The storage engine for the table doesn't support repair"
        /usr/bin/mysql_upgrade | grep -B1 "$no_repair" | grep -v "$no_repair" | sed -e "s/^\(.*\)$/OPTIMIZE TABLE \\1;/" 2>&1 > $tmpfile

        # black magic 2: after mysql_upgrade, mysqld --skip-grant table 
        # suddenly wants to authenticate us again..

        start_nonsecure
        /usr/bin/mysql < $tmpfile
        rm -f $tmpfile
        /etc/init.d/mysqld stop || :
        /etc/init.d/mysqld start || :

}

[faris]

Thanks Scott.

So, basically this is enhancing mysql_upgrade: An optimize_table is done on any innodb tables, which doesn't actually optimize them but instead recreates them, thus doing the same job in a way.

If doing so is so important, I don't understand why mysql_upgrade doesn't do this itself, but there you go -- I'm not a MySQL expert. I'll just accept it as one of those things.


BUT given that mysql_upgrade is being run, which itself does a mysqlcheck --fix-db-names --fix-tables-names I do not understand why this particular database still has the special #mysql50# prefix and isn't getting "fixed".

This is what the docs say about 5.0 to 5.1 upgrades:

Code: Select all

Known issue: MySQL introduces encoding for table names that have non-ASCII characters (see Section 9.2.3, “Mapping of Identifiers to File Names”). After a binary 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. 

So this is where the #mysql50# in the error message comes from. OK. Fine. I understand that.

But then it goes on to say:

Code: Select all

As of MySQL 5.1.31, mysql_upgrade encodes these names by executing the following command: 
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

Prior to MySQL 5.1.31, mysql_upgrade does not execute this command, so you should execute it manually if you have database or table names that contain nonalphanumeric characters. 

To me, this implies that on running mysql_upgrade on a system with 5.1 or later installed, the database name should be encoded so that the #mysql50# is removed (not necessary) because the name has now been "encoded".

Hence my confusion.....

I love MySQL but sometimes it confuses the hell out of me. Left join this. Right join that