错误吗?# 1146 -表的xxx。xxxxx”根本不存在

时间:2022-04-07 08:24:41

I am using windows XP. I am creating a table in phpMyAdmin using its built-in create table feature, my database name is ddd.

我使用的是windows XP。我正在phpMyAdmin中创建一个表,使用它的内置create table特性,我的数据库名是ddd。

It generates the following code:

它生成以下代码:

CREATE TABLE  `ddd`.`mwrevision` (

`asd` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`sddd` INT NOT NULL
) ENGINE = INNODB;

and the following error shows up:

然后出现以下错误:

MySQL said:     
#1146 - Table 'ddd.mwrevision' doesn't exist 

What might be the problem?

有什么问题吗?

18 个解决方案

#1


26  

I also had same problem in past. All had happend after moving database files to new location and after updating mysql server. All tables with InnoDB engine disappeared from my database. I was trying to recreate them, but mysql told me 1146: Table 'xxx' doesn't exist all the time until I had recreated my database and restarted mysql service.

我过去也有同样的问题。在将数据库文件移动到新的位置和更新mysql服务器之后,所有的事情都发生了。所有带有InnoDB引擎的表都从我的数据库中消失了。我试图重新创建它们,但是mysql告诉我1146:表“xxx”并不存在,直到我重新创建了数据库并重新启动mysql服务。

I think there's a need to read about InnoDB table binaries.

我认为有必要阅读InnoDB表二进制文件。

#2


11  

I had the same problem and can't get a good tip for this over the web, so I shared this for you and for all who needs.

我有同样的问题,在网上找不到一个好的提示,所以我分享给你和所有需要的人。

In my situation I copy a database (all files: frm, myd) to the data folder in MySQL data folder (using Wamp at home). All thing was OK until I want to create a table and have the error #1146 Table '...' doesn't exist!.

在我的情况下,我将数据库(所有文件:frm, myd)复制到MySQL数据文件夹中的数据文件夹(在家里使用Wamp)。所有的东西都可以,直到我想要创建一个表并有错误#1146表'…“不存在!

I use Wamp 2.1 with MySQL version 5.5.16.

我使用的是Wamp 2.1和MySQL版本5.5.16。

My solution:

我的解决方案:

  1. Export the database to file;

    将数据库导出到文件;

  2. verify if exported file is really OK!!;

    验证导出的文件是否真的OK!!

  3. drop the database where I have issues;

    删除我有问题的数据库;

  4. create a new database with the same name that the last;

    创建一个同名的新数据库;

  5. import the file to the database.

    将文件导入数据库。

FOR ME IS PROBLEM SOLVED. Now I can create tables again without errors.

对我来说,问题解决了。现在我可以在没有错误的情况下再次创建表。

#3


4  

In my case I ran this command even if the table wasn't visible in PhpMyAdmin :

在我的例子中,即使表格在PhpMyAdmin中不可见,我仍然运行这个命令:

DROP TABLE mytable

删除表mytable

then

然后

CREATE TABLE....

创建表....

Worked for me !

为我工作!

#4


4  

Restarting MySQL works fine for me.

重新启动MySQL对我来说没问题。

#5


3  

Check filenames.
You might need to create a new database in phpmyadmin that matches the database you're trying to import.

检查文件名。您可能需要在phpmyadmin中创建一个与要导入的数据库相匹配的新数据库。

#6


3  

I had the same problem. I tried to create a table in mysql and got the same error. I restarted mysql server and ran the command and was able to create/migrate table after restating.

我遇到了同样的问题。我尝试在mysql中创建一个表并得到相同的错误。我重新启动mysql服务器并运行该命令,并能够在restating后创建/迁移表。

#7


1  

Today i was facing same problem. I was in very difficult situation but what id did i create a table with diffrent name e.g (modulemaster was not creating then i create modulemaster1) and after creating table i just do the rename table.

今天我遇到了同样的问题。我当时处境非常困难,但我用不同的名字创建了一个表格。g(模块化没有创建,然后我创建了modulemaster1),在创建表之后,我只做重命名表。

#8


1  

I encountered the same problem today. I was trying to create a table users, and was prompted that ERROR 1146 (42S02): Table users doesn't exist, which did not make any sense, because I was just trying to create the table!!

我今天遇到了同样的问题。我试图创建一个表用户,并导致了错误1146 (42S02):表用户不存在,这没有任何意义,因为我只是在尝试创建表!!

I then tried to drop the table by typing DROP TABLE users, knowing it would fail because it did not exist, and I got an error, saying Unknown table users. After getting this error, I tried to create the table again, and magically, it successfully created the table!

然后,我尝试通过键入drop table users来删除表,知道它会失败,因为它不存在,并且我有一个错误,说不知道的表用户。在得到这个错误之后,我尝试再次创建表,并神奇地创建了表!

My intuition is that I probably created this table before and it was not completely cleared somehow. By explicitly saying DROP TABLE I managed to reset the internal state somehow? But that is just my guess.

我的直觉是,我可能在之前创建了这个表,但它并没有完全被清除。通过显式地说DROP表,我设法重新设置了内部状态?但这只是我的猜测。

In short, try DROP whatever table you are creating, and CREATE it again.

简而言之,尝试删除您正在创建的任何表,并再次创建它。

#9


1  

As pprakash mentions above, copying the table.frm files AND the ibdata1 file was what worked for me. (I would have just commented on that comment but this SO requirement for 50 points means I have to provide a solution, even if it's just a rehash of an existing one ... weird.)

正如pprakash所提到的,复制表。frm文件和ibdata1文件对我有效。(我本想就这条评论发表评论,但这要求50分意味着我必须提供一个解决方案,即使它只是一个现有的解决方案……)奇怪的)。

In short:

简而言之:

  1. Shut your DB explorer client emphasized text(eg Workbench).
  2. 关闭DB explorer客户端强调的文本(如工作台)。
  3. Stop the MySQL service (Windows host).
  4. 停止MySQL服务(Windows主机)。
  5. Make a safe copy of virtually everything!
  6. 把几乎所有的东西都拷贝下来!
  7. Save a copy of the table file(s) (eg mytable.frm) to the schema data folder (eg MySQL Server/data/{yourschema}).
  8. 将表文件(如mytable.frm)的副本保存到模式数据文件夹(如MySQL服务器/数据/{yourschema})。
  9. Save a copy of the ibdata1 file to the data folder (ie, MySQL Server/data).
  10. 将ibdata1文件的副本保存到数据文件夹(即MySQL服务器/数据)。
  11. Restart the MySQL service.
  12. 重新启动MySQL服务。
  13. Check that the tables are now accessible, queryable, etc in your DB explorer client.
  14. 检查在您的DB explorer客户机中,这些表现在是可访问的、可查询的等等。

After that, all was well. (Don't forget to backup if you have success!)

在那之后,一切都好了。(如果你成功了,别忘了备份!)

#10


0  

Column names must be unique in the table. You cannot have two columns named asd in the same table.

列名必须在表中是唯一的。您不能在同一个表中有两个名为asd的列。

#11


0  

run from CMD & %path%=set to mysql/bin

从CMD & %path%=设置到mysql/bin。

mysql_upgrade -u user -ppassword

mysql_upgrade - u用户-ppassword

#12


0  

Recently I had same problem, but on Linux Server. Database was crashed, and I recovered it from backup, based on simply copying /var/lib/mysql/* (analog mysql DATA folder in wamp). After recovery I had to create new table and got mysql error #1146. I tried to restart mysql, and it said it could not start. I checked mysql logs, and found that mysql simply had no access rigths to its DB files. I checked owner info of /var/lib/mysql/*, and got 'myuser:myuser' (myuser is me). But it should be 'mysql:adm' (so is own developer machine), so I changed owner to 'mysql:adm'. And after this mysql started normally, and I could create tables, or do any other operations.

最近我遇到了同样的问题,但是在Linux服务器上。数据库崩溃了,我从备份中恢复了它,基于简单的复制/var/lib/mysql/*(模拟mysql数据文件夹在wamp)。恢复后,我必须创建新表,并得到mysql错误#1146。我试着重启mysql,但它说无法启动。我检查了mysql日志,发现mysql根本无法访问它的DB文件。我查看了/var/lib/mysql/*的所有者信息,并得到了“myuser:myuser”(myuser是me)。但它应该是“mysql:adm”(所以是自己的开发人员机器),所以我将所有者改为“mysql:adm”。在mysql正常启动之后,我可以创建表,或者做其他操作。

So after moving database files or restoring from backups check access rigths for mysql.

因此,在移动数据库文件或从备份中恢复之后,检查mysql的访问精确度。

Hope this helps...

希望这有助于……

#13


0  

The reason I was facing this was because I had two "models.py" files which contained slightly different fields. I resolved it by:

我面对这一切的原因是我有两个“模型”。包含稍微不同字段的文件。我解决了:

  1. deleting one of the models.py files
  2. 删除其中一个模型。py文件
  3. correcting references to the deleted file
  4. 纠正对已删除文件的引用。
  5. then running manage.py syncdb
  6. 然后运行管理。py syncdb

#14


0  

I got this issue after copying mytable.idb table file from another location. To fix this problem I did the following:

我是在复制了mytable之后才得到这个问题的。来自另一个位置的idb表文件。为了解决这个问题,我做了如下工作:

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

Copy mytable.idb

复制mytable.idb

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

Restart MySql

重新启动MySql

#15


0  

I had the same issue. It happened after windows start up error, it seems some files got corrupted due to this. I did import the DB again from the saved script and it works fine.

我有同样的问题。它发生在windows启动错误之后,似乎有一些文件被损坏了。我从保存的脚本中再次导入了DB,它运行良好。

#16


0  

I had this problem because of a trigger not working..Worked after I deleted the trigger.

我遇到了这个问题,因为一个触发器不能工作。我删除了触发器后工作。

#17


0  

In my case, MySQL's parameter; lower_case_table_names was configured = 0.

在我的例子中,MySQL的参数;_case_table_name被配置为= 0。

It causes queries related with using upper cases will not work.

它会导致与使用大写字母有关的查询不起作用。

#18


0  

For me it was a table name upper/lower case issue. I had to make sure that table case name matched in a delete query, table notifications was not the same as Notifications. I fixed it by matching table name case with query and what MySQLWorkbench reported.

对我来说,它是一个表名,上面/小写的问题。我必须确保在删除查询中匹配的表用例名,表通知与通知不一样。我通过匹配表名case和查询以及MySQLWorkbench所报告的内容修复了它。

What is wierd is that this error showed up in a worked sql statement. Don't know what caused this case sensitivity. Perhaps an auto AWS RDS update.

wierd是在一个工作的sql语句中出现的这个错误。不知道是什么导致了这种病例的敏感性。也许是自动AWS RDS更新。

#1


26  

I also had same problem in past. All had happend after moving database files to new location and after updating mysql server. All tables with InnoDB engine disappeared from my database. I was trying to recreate them, but mysql told me 1146: Table 'xxx' doesn't exist all the time until I had recreated my database and restarted mysql service.

我过去也有同样的问题。在将数据库文件移动到新的位置和更新mysql服务器之后,所有的事情都发生了。所有带有InnoDB引擎的表都从我的数据库中消失了。我试图重新创建它们,但是mysql告诉我1146:表“xxx”并不存在,直到我重新创建了数据库并重新启动mysql服务。

I think there's a need to read about InnoDB table binaries.

我认为有必要阅读InnoDB表二进制文件。

#2


11  

I had the same problem and can't get a good tip for this over the web, so I shared this for you and for all who needs.

我有同样的问题,在网上找不到一个好的提示,所以我分享给你和所有需要的人。

In my situation I copy a database (all files: frm, myd) to the data folder in MySQL data folder (using Wamp at home). All thing was OK until I want to create a table and have the error #1146 Table '...' doesn't exist!.

在我的情况下,我将数据库(所有文件:frm, myd)复制到MySQL数据文件夹中的数据文件夹(在家里使用Wamp)。所有的东西都可以,直到我想要创建一个表并有错误#1146表'…“不存在!

I use Wamp 2.1 with MySQL version 5.5.16.

我使用的是Wamp 2.1和MySQL版本5.5.16。

My solution:

我的解决方案:

  1. Export the database to file;

    将数据库导出到文件;

  2. verify if exported file is really OK!!;

    验证导出的文件是否真的OK!!

  3. drop the database where I have issues;

    删除我有问题的数据库;

  4. create a new database with the same name that the last;

    创建一个同名的新数据库;

  5. import the file to the database.

    将文件导入数据库。

FOR ME IS PROBLEM SOLVED. Now I can create tables again without errors.

对我来说,问题解决了。现在我可以在没有错误的情况下再次创建表。

#3


4  

In my case I ran this command even if the table wasn't visible in PhpMyAdmin :

在我的例子中,即使表格在PhpMyAdmin中不可见,我仍然运行这个命令:

DROP TABLE mytable

删除表mytable

then

然后

CREATE TABLE....

创建表....

Worked for me !

为我工作!

#4


4  

Restarting MySQL works fine for me.

重新启动MySQL对我来说没问题。

#5


3  

Check filenames.
You might need to create a new database in phpmyadmin that matches the database you're trying to import.

检查文件名。您可能需要在phpmyadmin中创建一个与要导入的数据库相匹配的新数据库。

#6


3  

I had the same problem. I tried to create a table in mysql and got the same error. I restarted mysql server and ran the command and was able to create/migrate table after restating.

我遇到了同样的问题。我尝试在mysql中创建一个表并得到相同的错误。我重新启动mysql服务器并运行该命令,并能够在restating后创建/迁移表。

#7


1  

Today i was facing same problem. I was in very difficult situation but what id did i create a table with diffrent name e.g (modulemaster was not creating then i create modulemaster1) and after creating table i just do the rename table.

今天我遇到了同样的问题。我当时处境非常困难,但我用不同的名字创建了一个表格。g(模块化没有创建,然后我创建了modulemaster1),在创建表之后,我只做重命名表。

#8


1  

I encountered the same problem today. I was trying to create a table users, and was prompted that ERROR 1146 (42S02): Table users doesn't exist, which did not make any sense, because I was just trying to create the table!!

我今天遇到了同样的问题。我试图创建一个表用户,并导致了错误1146 (42S02):表用户不存在,这没有任何意义,因为我只是在尝试创建表!!

I then tried to drop the table by typing DROP TABLE users, knowing it would fail because it did not exist, and I got an error, saying Unknown table users. After getting this error, I tried to create the table again, and magically, it successfully created the table!

然后,我尝试通过键入drop table users来删除表,知道它会失败,因为它不存在,并且我有一个错误,说不知道的表用户。在得到这个错误之后,我尝试再次创建表,并神奇地创建了表!

My intuition is that I probably created this table before and it was not completely cleared somehow. By explicitly saying DROP TABLE I managed to reset the internal state somehow? But that is just my guess.

我的直觉是,我可能在之前创建了这个表,但它并没有完全被清除。通过显式地说DROP表,我设法重新设置了内部状态?但这只是我的猜测。

In short, try DROP whatever table you are creating, and CREATE it again.

简而言之,尝试删除您正在创建的任何表,并再次创建它。

#9


1  

As pprakash mentions above, copying the table.frm files AND the ibdata1 file was what worked for me. (I would have just commented on that comment but this SO requirement for 50 points means I have to provide a solution, even if it's just a rehash of an existing one ... weird.)

正如pprakash所提到的,复制表。frm文件和ibdata1文件对我有效。(我本想就这条评论发表评论,但这要求50分意味着我必须提供一个解决方案,即使它只是一个现有的解决方案……)奇怪的)。

In short:

简而言之:

  1. Shut your DB explorer client emphasized text(eg Workbench).
  2. 关闭DB explorer客户端强调的文本(如工作台)。
  3. Stop the MySQL service (Windows host).
  4. 停止MySQL服务(Windows主机)。
  5. Make a safe copy of virtually everything!
  6. 把几乎所有的东西都拷贝下来!
  7. Save a copy of the table file(s) (eg mytable.frm) to the schema data folder (eg MySQL Server/data/{yourschema}).
  8. 将表文件(如mytable.frm)的副本保存到模式数据文件夹(如MySQL服务器/数据/{yourschema})。
  9. Save a copy of the ibdata1 file to the data folder (ie, MySQL Server/data).
  10. 将ibdata1文件的副本保存到数据文件夹(即MySQL服务器/数据)。
  11. Restart the MySQL service.
  12. 重新启动MySQL服务。
  13. Check that the tables are now accessible, queryable, etc in your DB explorer client.
  14. 检查在您的DB explorer客户机中,这些表现在是可访问的、可查询的等等。

After that, all was well. (Don't forget to backup if you have success!)

在那之后,一切都好了。(如果你成功了,别忘了备份!)

#10


0  

Column names must be unique in the table. You cannot have two columns named asd in the same table.

列名必须在表中是唯一的。您不能在同一个表中有两个名为asd的列。

#11


0  

run from CMD & %path%=set to mysql/bin

从CMD & %path%=设置到mysql/bin。

mysql_upgrade -u user -ppassword

mysql_upgrade - u用户-ppassword

#12


0  

Recently I had same problem, but on Linux Server. Database was crashed, and I recovered it from backup, based on simply copying /var/lib/mysql/* (analog mysql DATA folder in wamp). After recovery I had to create new table and got mysql error #1146. I tried to restart mysql, and it said it could not start. I checked mysql logs, and found that mysql simply had no access rigths to its DB files. I checked owner info of /var/lib/mysql/*, and got 'myuser:myuser' (myuser is me). But it should be 'mysql:adm' (so is own developer machine), so I changed owner to 'mysql:adm'. And after this mysql started normally, and I could create tables, or do any other operations.

最近我遇到了同样的问题,但是在Linux服务器上。数据库崩溃了,我从备份中恢复了它,基于简单的复制/var/lib/mysql/*(模拟mysql数据文件夹在wamp)。恢复后,我必须创建新表,并得到mysql错误#1146。我试着重启mysql,但它说无法启动。我检查了mysql日志,发现mysql根本无法访问它的DB文件。我查看了/var/lib/mysql/*的所有者信息,并得到了“myuser:myuser”(myuser是me)。但它应该是“mysql:adm”(所以是自己的开发人员机器),所以我将所有者改为“mysql:adm”。在mysql正常启动之后,我可以创建表,或者做其他操作。

So after moving database files or restoring from backups check access rigths for mysql.

因此,在移动数据库文件或从备份中恢复之后,检查mysql的访问精确度。

Hope this helps...

希望这有助于……

#13


0  

The reason I was facing this was because I had two "models.py" files which contained slightly different fields. I resolved it by:

我面对这一切的原因是我有两个“模型”。包含稍微不同字段的文件。我解决了:

  1. deleting one of the models.py files
  2. 删除其中一个模型。py文件
  3. correcting references to the deleted file
  4. 纠正对已删除文件的引用。
  5. then running manage.py syncdb
  6. 然后运行管理。py syncdb

#14


0  

I got this issue after copying mytable.idb table file from another location. To fix this problem I did the following:

我是在复制了mytable之后才得到这个问题的。来自另一个位置的idb表文件。为了解决这个问题,我做了如下工作:

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

Copy mytable.idb

复制mytable.idb

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

Restart MySql

重新启动MySql

#15


0  

I had the same issue. It happened after windows start up error, it seems some files got corrupted due to this. I did import the DB again from the saved script and it works fine.

我有同样的问题。它发生在windows启动错误之后,似乎有一些文件被损坏了。我从保存的脚本中再次导入了DB,它运行良好。

#16


0  

I had this problem because of a trigger not working..Worked after I deleted the trigger.

我遇到了这个问题,因为一个触发器不能工作。我删除了触发器后工作。

#17


0  

In my case, MySQL's parameter; lower_case_table_names was configured = 0.

在我的例子中,MySQL的参数;_case_table_name被配置为= 0。

It causes queries related with using upper cases will not work.

它会导致与使用大写字母有关的查询不起作用。

#18


0  

For me it was a table name upper/lower case issue. I had to make sure that table case name matched in a delete query, table notifications was not the same as Notifications. I fixed it by matching table name case with query and what MySQLWorkbench reported.

对我来说,它是一个表名,上面/小写的问题。我必须确保在删除查询中匹配的表用例名,表通知与通知不一样。我通过匹配表名case和查询以及MySQLWorkbench所报告的内容修复了它。

What is wierd is that this error showed up in a worked sql statement. Don't know what caused this case sensitivity. Perhaps an auto AWS RDS update.

wierd是在一个工作的sql语句中出现的这个错误。不知道是什么导致了这种病例的敏感性。也许是自动AWS RDS更新。