MySQL [ERROR] Invalid (old?) table or database name

Posted on April 25th, 2009 in ,, by Brandon

After upgrading to MySQL 5, one of my databases appeared empty even though the MyISAM tables existed in /var/lib/mysql/database-name. I found this error in the mysql log file in /var/log/mysqld.log:

090425 23:10:52 [ERROR] Invalid (old?) table or database name 'database-name'

Turns out that MySQL 5 doesn’t like database names that contain a hyphen. In the mysql directory, I see that it created a second directory for the database, but with the hyphen replaced with ‘@002d’.

drwx------  2 mysql mysql     4096 Apr 25 23:10 database-name

drwx------  2 mysql mysql     4096 Apr 24 00:30 database@002dname

To resolve that I decided to replace the hyphen with an underscore. I stopped mysql, renamed /var/lib/mysql/database-name to /var/lib/mysql/database_name, and then ran this SQL to update the permissions:

UPDATE mysql.db SET Db = 'database_name' WHERE Db = 'database\-name';

FLUSH PRIVILEGES;

I could then see the tables using mysql from the command line. From there, I just updated the setting for the application to use the new name, and everything worked out find.