[SOLVED] InnoDB tables exist in MySQL but says they do not exist after copying database to new server

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

I used mysqldump to export my database and then I imported it into MySQL on my other server. I can now see all my tables if I do "show tables" but I can't actually select from or describe any of them.

ERROR 1146 (42S02): Table 'mydatabase.user' doesn't exist

All of my tables are InnoDB. I saw one issue people had where they were using old_passwords, so I explicitly set that to 0 in my.cnf and I made sure all of the passwords in the mysql table were 41 hexadecimal digits as they should be for the new passwords.

Solution

The reason "show tables;" works is because mysqld will scan the database directory for .frm files only. As long as they exist, it sees a table definition.

If you imported the data into MySQL and this error message happens, the first thing I would immediately do is run this command: (BTW This is MySQL 5.1.45, but 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 into says InnoDB is disabled, then you have a big problem. Here is what you should do:

1) Drop all the Data from the New Import DB Server

2) Cleanup InnoDB Setup

3) run SHOW ENGINES; and make sure InnoDB is fully operational !!!

4) Reload the mysqldump into the new import server

Give it a Try !!!