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

Question

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.

Midi

Are you sure that the restore made without errors? Try to make the restore with a single transaction, the transaction will stop on errors. – Meade

It was working for a while after I copied it and then it just started saying the table didn't exist. But I can't create the table because it does exist... The problem is there were a few changes to the data that happened with this copy after the import, so I need to be able to access those at least. The .frm file for this table is exactly the same as a working copy of the database. – Midi

It seems you have problems with a table structure. Can't help with you that. Try the fresh restore with log enabled and post here any error from log. – Meade

Answer 1

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

Lelia

Answer 2

I had this problem when I changed from a windows server to a Linux server. Tables are files, and windows files are case insesitive, but linux files are case sensitive.

In my aplication, in the sql queries, some times I used uppercase tablenames and other times lowercase, so, sometimes I obtained the same result as you.

Becky

Answer 3

I my case it was SQLCA.DBParm parameter.

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

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

Explain : you are going to combine three strings :

a) Database='               - "Database='"

b) (name of the database)   - +sle_database.text+

c) ' - "'"

Confabulation