Bug #56441 mysql_upgrade 5.0->5.1 fails for tables with long names
Submitted: 1 Sep 2010 9:22 Modified: 13 Sep 2010 18:16
Reporter: Kristian Nielsen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0-bzr, 5.1-bzr OS:Linux
Assigned to: CPU Architecture:Any
Tags: Contribution
Triage: Triaged: D2 (Serious)

[1 Sep 2010 9:22] Kristian Nielsen
Description:
When mysql_upgrade is run to upgrade from MySQL 5.0 to MySQL 5.1, as part of
the process it renames tables like XXX <-> #mysql50#XXX.

This rename fails when table names are so long that they are close to the
maximum length:

$ /usr/local/mysql/bin/mysql_upgrade
...
Failed to RENAME TABLE `#mysql50#1ltoriaeinnovacionendesarrolloempres?912076109722007737` TO `1ltoriaeinnovacionendesarrolloempres?912076109722007737`
Error: Can't find file: './test/1ltoriaeinnovacionendesarrolloempres?912076109722007737.frm' (errno: 2)
Failed to RENAME TABLE `#mysql50#ltoriaeinnovacionendesarrolloempres?9120761097220077376` TO `ltoriaeinnovacionendesarrolloempres?9120761097220077376`
Error: Can't find file: './test/ltoriaeinnovacionendesarrolloempres?9120761097220077376.frm' (errno: 2)

How to repeat:
I first installed MySQL 5.0 from source

    bzr branch bzr+ssh://bazaar.launchpad.net/~mysql/mysql-server/mysql-5.0/
    cd mysql-5.0
    BUILD/compile-pentium64-valgrind-max
    make install
    (cd /usr/local/mysql && bin/mysql_install_db)
    (cd /usr/local/mysql && libexec/mysqld --defaults-file=/usr/local/mysql/share/mysql/my-medium.cnf)

Then I created some test tables with long names:

    /usr/local/mysql/bin/mysql -uroot test

    mysql> create table `ltoriaeinnovacionendesarrolloempres?9120761097220077376?cio_com` ( a int, b varchar(100), c varchar(600), d blob, e timestamp, primary key (a,b), unique(c), key(e)) engine=myisam;
    mysql> create table `1ltoriaeinnovacionendesarrolloempres?9120761097220077376?cio_com` ( a int, b varchar(100), c varchar(600), d blob, e timestamp, primary key (a,b), unique(c), key(e)) engine=myisam;
    mysql> insert into `1ltoriaeinnovacionendesarrolloempres?9120761097220077376?cio_com` values (1,'foo', 'asdasd', null, now()), (2, 'bar', null, '1234567890', null);
    mysql> insert into `ltoriaeinnovacionendesarrolloempres?9120761097220077376?cio_com` values (1,'foo', 'asdasd', null, now()), (2, 'bar', null, '1234567890', null);

I then shutdown the 5.0 server and deleted the installation except the data
directory /usr/local/mysql/var. I then installed MySQL 5.1 from source and ran mysql_upgrade:

    bzr branch bzr+ssh://bazaar.launchpad.net/~mysql/mysql-server/mysql-5.1/
    cd mysql-5.1
    BUILD/compile-pentium64-valgrind-max
    make install
    (cd /usr/local/mysql && libexec/mysqld --defaults-file=/usr/local/mysql/share/mysql/my-medium.cnf)
    /usr/local/mysql/bin/mysql_upgrade

Here is the output:

$ /usr/local/mysql/bin/mysql_upgrade --port=3307 --host=127.0.0.1 -uroot 
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--port=3307' '--host=127.0.0.1' 
Failed to RENAME TABLE `#mysql50#1ltoriaeinnovacionendesarrolloempres?912076109722007737` TO `1ltoriaeinnovacionendesarrolloempres?912076109722007737`
Error: Can't find file: './test/1ltoriaeinnovacionendesarrolloempres?912076109722007737.frm' (errno: 2)
Failed to RENAME TABLE `#mysql50#ltoriaeinnovacionendesarrolloempres?9120761097220077376` TO `ltoriaeinnovacionendesarrolloempres?9120761097220077376`
Error: Can't find file: './test/ltoriaeinnovacionendesarrolloempres?9120761097220077376.frm' (errno: 2)
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--port=3307' '--host=127.0.0.1' 
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.func                                         OK
mysql.help_category
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
mysql.help_relation                                OK
mysql.help_topic
error    : Table upgrade required. Please do "REPAIR TABLE `help_topic`" or dump/reload to fix it!
mysql.host                                         OK
mysql.proc
error    : Table upgrade required. Please do "REPAIR TABLE `proc`" or dump/reload to fix it!
mysql.procs_priv
error    : Table upgrade required. Please do "REPAIR TABLE `procs_priv`" or dump/reload to fix it!
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name
error    : Table upgrade required. Please do "REPAIR TABLE `time_zone_name`" or dump/reload to fix it!
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test.#mysql50#1ltoriaeinnovacionendesarrolloempres?912076109722007737
Error    : Table 'test.#mysql50#1ltoriaeinnovacionendesarrolloempres?912076109722007737' doesn't exist
status   : Operation failed
test.#mysql50#ltoriaeinnovacionendesarrolloempres?9120761097220077376
Error    : Table 'test.#mysql50#ltoriaeinnovacionendesarrolloempres?9120761097220077376' doesn't exist
status   : Operation failed

Repairing tables
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_topic                                   OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.time_zone_name                               OK
test.#mysql50#1ltoriaeinnovacionendesarrolloempres?912076109722007737
Error    : Table 'test.#mysql50#1ltoriaeinnovacionendesarrolloempres?912076109722007737' doesn't exist
status   : Operation failed
test.#mysql50#ltoriaeinnovacionendesarrolloempres?9120761097220077376
Error    : Table 'test.#mysql50#ltoriaeinnovacionendesarrolloempres?9120761097220077376' doesn't exist
status   : Operation failed
Running 'mysql_fix_privilege_tables'...
OK

Note that despite these errors, /usr/local/mysql/var/mysql_upgrade_info is
still created, preventing a subsequent mysql_upgrade from running unless --force.

Suggested fix:
Make mysql_upgrade properly handle long table names and not try to rename them to an invalid name.
[10 Sep 2010 21:47] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior:

test.ltoriaeinnovacionendesarrolloempres           OK
test.ltoriaeinnovacionendesarrolloempresltoriaeinnovacionendesarrolp OK
test.a?a                                           OK
test.ltoriaeinnovacionendesarrolloempres?9120761097220077376 OK

Please provide names of tables and databases you experience problems with.
[11 Sep 2010 8:08] Kristian Nielsen
It is strange that you cannot repeat. Did you follow "how to repeat" exactly,
including branching both 5.0 and 5.1 sources? Did you test on Linux, or
some other OS (file system semantics is involved I think)?

There are only two tables created in the "how to repeat", and they both cause the problem:

    test.ltoriaeinnovacionendesarrolloempres?9120761097220077376?cio_com
    test.1ltoriaeinnovacionendesarrolloempres?9120761097220077376?cio_com

The table names in your comment are different, eg. they are missing the trailing "?cio_com", any idea how that could have happened?
[11 Sep 2010 8:13] Kristian Nielsen
BTW, there is a fix for this bug in the Launchpad tree lp:maria/5.1,
revision revid:monty@askmonty.org-20100903162030-9a4dwmvvfy2xpoii
[13 Sep 2010 18:16] Sveta Smirnova
Thank you for the feedback.

I used table name from mysql_upgrade by mistake. Verified as described.