InnoDB tables exist in MySQL but say they do not exist after copying the database to the new server

Question

I used mysqldump to export my database and then imported it into MySQL on another server. Now I can see all my tables if I do "show tables", but I cannot select or describe them.

ERROR 1146 (42S02): Table "mydatabase.user" does not exist

All my tables are InnoDB. I saw one problem in people where they used old_passwords, so I explicitly set it to 0 in my.cnf, and I made sure that all passwords in the mysql table have 41 hexadecimal digits, just like for new passwords.

Answer 1

Reason for "show tables"; works because mysqld will check the database directory for .frm files only. As long as they exist, he sees the definition of the table.

If you imported data into MySQL and this error message occurred, the first thing I did immediately was run this command: (BTW This is MySQL 5.1.45, but it works in MySQL 5.x anyway)

mysql> show engines; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 8 rows in set (0.00 sec) 

If the server you imported the data to says InnoDB is down, you have a big problem. Here is what you should do:

1) Drop all data from the new import server server

2) Clean InnoDB Setup

3) run SHOW ENGINES; and make sure InnoDB is fully functional.

4) Reload mysqldump to the new import server

Give it a try !!!

Answer 2

I had this problem when I switched from a Windows server to a Linux server. Tables are files, and window files are case insensitive, but linux files are case sensitive.

In my application, in sql queries, several times I used uppercase tags in upper case and other lowercase letters, so sometimes I got the same result as you.

Answer 3

In my case, it was an SQLCA.DBParm parameter.

I used SQLCA.DBParm = "Databse = "sle_database.text"" , but it should be

 SQLCA.DBParm = "Database='" +sle_database.text+ "'" 

Explain that you are going to combine three lines:

 a) Database=' - "Database='" b) (name of the database) - +sle_database.text+ c) ' - "'"