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.
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.