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

Posted on April 25th, 2009 in General,Linux

System Administration,MySQL 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 mysql4096 Apr 25 23:10 database-name

drwx------2 mysql mysql4096 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.