6

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.

mrgordon
  • 130
  • 1
  • 2
  • 7
  • Are you sure that the restore made without errors? Try to make the restore with a single transaction, the transaction will stop on errors. – Igor May 25 '11 at 17:20
  • 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. – mrgordon May 25 '11 at 17:29
  • 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. – Igor May 25 '11 at 17:34

3 Answers3

7

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

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
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.

jenkin90
  • 357
  • 1
  • 5
0

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) ' - "'"
NoNaMe
  • 6,020
  • 30
  • 82
  • 110
Marek
  • 11
  • 1