InnoDB 表存在于 MySQL 中，但在将数据库复制到新服务器后表示它们不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6128286/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
InnoDB tables exist in MySQL but says they do not exist after copying database to new server
提问by mrgordon
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.
我使用 mysqldump 导出我的数据库，然后将其导入到我另一台服务器上的 MySQL 中。如果我执行“显示表格”，我现在可以看到我的所有表格，但我实际上无法从中选择或描述任何表格。
ERROR 1146 (42S02): Table 'mydatabase.user' doesn't exist
错误 1146 (42S02)：表 'mydatabase.user' 不存在
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.
我所有的表都是 InnoDB。我看到人们在使用 old_passwords 时遇到了一个问题，因此我在 my.cnf 中将其明确设置为 0，并确保 mysql 表中的所有密码都是 41 位十六进制数字，因为它们应该用于新密码。
回答by RolandoMySQLDBA
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.
原因“显示表”；有效是因为 mysqld 将只扫描 .frm 文件的数据库目录。只要它们存在，它就会看到一个表定义。
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 并出现此错误消息，我会立即做的第一件事是运行此命令：（顺便说一句，这是 MySQL 5.1.45，但无论如何都可以在 MySQL 5.x 中使用）
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:
如果您将数据导入的服务器说 InnoDB 已禁用，那么您就遇到了大问题。这是你应该做的：
1) Drop all the Data from the New Import DB Server
1) 从新的导入数据库服务器中删除所有数据
3) run SHOW ENGINES; and make sure InnoDB is fully operational !!!
3）运行显示引擎；并确保 InnoDB 完全运行！！！
4) Reload the mysqldump into the new import server
4）将mysqldump重新加载到新的导入服务器中
Give it a Try !!!
试一试 ！！！
回答by jenkin90
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.
当我从 Windows 服务器更改为 Linux 服务器时，我遇到了这个问题。表是文件，windows 文件不区分大小写，但 linux 文件区分大小写。
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.
在我的应用程序中，在 sql 查询中，有时我使用大写的表名，有时使用小写，因此，有时我得到与您相同的结果。
回答by Marek
I my case it was SQLCA.DBParmparameter.
我的情况是SQLCA.DBParm参数。
I used SQLCA.DBParm = "Databse = "sle_database.text""but it must be
我用过，SQLCA.DBParm = "Databse = "sle_database.text""但必须是
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) ' - "'"
