MySQL MariaDB 的数据库损坏：引擎中不存在表

Database corruption with MariaDB : Table doesn't exist in engine

提问by sf_tristanb

I'm in an environement setup, running OSX with MariaDB 10.0.12-MariaDB Homebrew

我在环境设置中，运行 OSX MariaDB 10.0.12-MariaDB Homebrew

I've screwed up the installation so I did completely removed MySQL and MariaDB from my setup and started again.

我搞砸了安装，所以我确实从我的设置中完全删除了 MySQL 和 MariaDB，然后重新开始。

After finishing installing MariaDB, I've reimported my databases (innoDB) via a DB Dump from the production server. It worked fine. After a reboot, the day after, I can no longer access to the databases :

安装完 MariaDB 后，我innoDB通过 DB Dump 从生产服务器重新导入了我的数据库 ( )。它工作得很好。重新启动后，第二天，我无法再访问数据库：

Table 'my.table' doesn't exist in engine

Table 'my.table' doesn't exist in engine

What's causing this and what's the solution ? I do see the structure of my database, but when I try to access it, it gives me this error message.

这是什么原因造成的，解决方法是什么？我确实看到了我的数据库的结构，但是当我尝试访问它时，它给了我这个错误消息。

I did try mysql-upgrade --forceand deleting rm ib_logfile1 ib_logfile0

我确实尝试mysql-upgrade --force删除rm ib_logfile1 ib_logfile0

The data loss is not a problem here, the problem is that I can't spend 30 minutes on re-installing each database each time I do a reboot.

数据丢失在这里不是问题，问题是我无法在每次重新启动时花 30 分钟重新安装每个数据库。

Here's some logs :

这是一些日志：

140730  9:24:13 [Note] Server socket created on IP: '127.0.0.1'.
140730  9:24:14 [Note] Event Scheduler: Loaded 0 events
140730  9:24:14 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
140730  9:24:14 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
140730  9:24:14 [Note] /usr/local/Cellar/mariadb/10.0.12/bin/mysqld: ready for connections.
Version: '10.0.12-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew
140730 16:26:28 [Warning] InnoDB: Cannot open table db/site from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

回答by akuzminsky

Something has deleted your ibdata1 file where InnoDB keeps the dictionary. Definitely it's not MySQL who does it.

有些东西删除了您的 ibdata1 文件，其中 InnoDB 保存了字典。绝对不是 MySQL 做的。

回答by lvto2000

Ok folks, I ran into this problem this weekend when my OpenStack environment crashed. Another post about that coming soon on how to recover.

好的伙计们，本周末当我的 OpenStack 环境崩溃时，我遇到了这个问题。关于如何恢复的另一篇文章即将发布。

I found a solution that worked for me with a SQL Server instance running under the Ver 15.1 Distrib 10.1.21-MariaDB with Fedora 25 Server as the host. Do not listen to all the other posts that say your database is corrupted if you completely copied your old mariadb-server's /var/lib/mysql directory and the database you are copying is not already corrupted. This process is based on a system where the OS became corrupted but its files were still accessible.

我找到了一个适用于我的解决方案，它使用在 Ver 15.1 Distrib 10.1.21-MariaDB 下运行的 SQL Server 实例，以 Fedora 25 Server 作为主机。 如果您完全复制了旧的 mariadb-server 的 /var/lib/mysql 目录并且您正在复制的数据库尚未损坏，请不要听所有其他说您的数据库已损坏的帖子。此过程基于操作系统已损坏但其文件仍可访问的系统。

Here are the steps I followed.

这是我遵循的步骤。

1. Make sure that you have completely uninstalled any current versions of SQL only on the NEW server. Also, make sure ALL mysql-server or mariadb-server processes on the NEW AND OLD servers have been halted by running:

   service mysqld stop or service mariadb stop.

2. On the NEW SQL server go into the /var/lib/mysql directory and ensure that there are no files at all in this directory. If there are files in this directory then your process for removing the database server from the new machine did not work and is possibly corrupted. Make sure it completely uninstalled from the new machine.

3. On the OLD SQL server:

   mkdir /OLDMYSQL-DIR cd /OLDMYSQL-DIR tar cvf mysql-olddirectory.tar /var/lib/mysql gzip mysql-olddirectory.tar

4. Make sure you have sshd running on both the OLD and NEW servers. Make sure there is network connectivity between the two servers.

5. On the NEW SQL server:

   mkdir /NEWMYSQL-DIR

6. On the OLD SQL server:

   cd /OLDMYSQL-DIR scp mysql-olddirectory.tar.gz @:/NEWMYSQL-DIR

7. On the NEW SQL server:

   cd /NEWMYSQL-DIR gunzip mysql-olddirectory.tar.gz OR tar zxvf mysql-olddirectory.tar.gz (if tar zxvf doesn't work) tar xvf mysql-olddirectory.tar.gz

8. You should now have a "mysql" directory file sitting in the NEWMYSQL-DIR. Resist the urge to run a "cp" command alone with no switches. It will not work. Run the following "cp" command and ensure you use the same switches I did.

   cd mysql/ cp -rfp * /var/lib/mysql/

9. Now you should have a copy of all of your old SQL server files on the NEW server with permissions in tact. On the NEW SQL server:

   cd /var/lib/mysql/

1. 确保您仅在新服务器上完全卸载了任何当前版本的 SQL。此外，通过运行以下命令确保新旧服务器上的所有 mysql-server 或 mariadb-server 进程已停止：

   服务 mysqld 停止或服务 mariadb 停止。

2. 在新的 SQL 服务器上，进入 /var/lib/mysql 目录并确保该目录中根本没有文件。如果此目录中有文件，则您从新机器上删除数据库服务器的过程不起作用，并且可能已损坏。确保它从新机器上完全卸载。

3. 在旧 SQL 服务器上：

   mkdir /OLDMYSQL-DIR cd /OLDMYSQL-DIR tar cvf mysql-olddirectory.tar /var/lib/mysql gzip mysql-olddirectory.tar

4. 确保您在旧服务器和新服务器上都运行了 sshd。确保两台服务器之间存在网络连接。

5. 在新的 SQL 服务器上：

   mkdir /NEWMYSQL-DIR

6. 在旧 SQL 服务器上：

   cd /OLDMYSQL-DIR scp mysql-olddirectory.tar.gz @:/NEWMYSQL-DIR

7. 在新的 SQL 服务器上：

   cd /NEWMYSQL-DIR gunzip mysql-olddirectory.tar.gz 或 tar zxvf mysql-olddirectory.tar.gz（如果 tar zxvf 不起作用） tar xvf mysql-olddirectory.tar.gz

8. 您现在应该有一个位于 NEWMYSQL-DIR 中的“mysql”目录文件。抵制在没有开关的情况下单独运行“cp”命令的冲动。不起作用。运行以下“cp”命令并确保您使用与我所做的相同的开关。

   cd mysql/ cp -rfp * /var/lib/mysql/

9. 现在，您应该在新服务器上拥有所有旧 SQL 服务器文件的副本，并具有完整的权限。在新的 SQL 服务器上：

   cd /var/lib/mysql/

VERY IMPORTANT STEP. DO NOT SKIP

非常重要的一步。不要跳过

> rm -rfp ib_logfile*

1. Now install mariadb-server or mysql-server on the NEW SQL server. If you already have it installed and/or running then you have not followed the directions and these steps will fail.

10. 现在在新的 SQL 服务器上安装 mariadb-server 或 mysql-server。如果您已经安装和/或运行它，那么您没有按照说明进行操作，这些步骤将失败。

FOR MARIADB-SERVER and DNF:

对于 MARIADB 服务器和 DNF：

> dnf install mariadb-server
> service mariadb restart

FOR MYSQL-SERVER and YUM:

对于 MYSQL-SERVER 和 YUM：

> yum install mysql-server
> service mysqld restart

回答by Adrian P.

You may try to:

您可以尝试：

• backup your database folder from C:\xampp\mysql\data (.frm & .ibd files only corresponding your table names)
• reinstall xampp and recopy DB folder at C:\xampp\mysql\data

• modify my.ini add

  [mysqld]
  innodb_file_per_table = on
  

• then open phpmyadmin (or any other DB viewer as Navicat or MYSQL Workbench) and run

  ALTER TABLE tbl_name IMPORT TABLESPACE 
  

  for each table

• Once you can open your tables make a full mysql dump

• delete everything and make a clean install

• 从 C:\xampp\mysql\data 备份你的数据库文件夹（.frm 和 .ibd 文件只对应你的表名）
• 重新安装 xampp 并重新复制 C:\xampp\mysql\data 中的 DB 文件夹

• 修改 my.ini 添加

  [mysqld]
  innodb_file_per_table = on
  

• 然后打开 phpmyadmin（或任何其他数据库查看器作为 Navicat 或 MYSQL Workbench）并运行

  ALTER TABLE tbl_name IMPORT TABLESPACE 
  

  每张桌子

• 一旦你可以打开你的表，做一个完整的 mysql 转储

• 删除所有内容并进行全新安装

I know, a lot of work to do that you don't need it.

我知道，很多工作要做而你不需要它。

回答by hexten

I've just had this problem with MariaDB/InnoDB and was able to fix it by

我刚刚在 MariaDB/InnoDB 上遇到了这个问题，并且能够通过

• create the required table in the correct database in another MySQL/MariaDB instance
• stop both servers
• copy .ibd, .frm files to original server
• start both servers
• drop problem table on both servers

• 在另一个 MySQL/MariaDB 实例的正确数据库中创建所需的表
• 停止两台服务器
• 将 .ibd、.frm 文件复制到原始服务器
• 启动两台服务器
• 在两台服务器上删除问题表

回答by Chad Scira

This one really sucked.

这个真烂。

I tried all of the solutions suggested here but the only thing that worked was to

我尝试了这里建议的所有解决方案，但唯一有效的是

• create a new database
• run ALTER TABLE old_db.{table_name} RENAME new_db.{table_name}on all of the functioning tables
• run DROP old_db
• create old_dbagain
• run ALTER TABLE new_db.{table_name} RENAME old_db.{table_name}on all the tables in new_db

• 创建一个新的数据库
• ALTER TABLE old_db.{table_name} RENAME new_db.{table_name}在所有功能表上运行
• 跑 DROP old_db
• old_db再创造
• ALTER TABLE new_db.{table_name} RENAME old_db.{table_name}在所有表上运行new_db

Once you have done that you can finally just create the table again that you previously had.

完成后，您最终可以再次创建以前拥有的表。

回答by Herbert Giller

This theme required awhile to find results and reasons:

这个主题需要一段时间才能找到结果和原因：

using MaiaDB 5.4. via SuSE-LINUX tumblweed

使用 MaiaDB 5.4。通过 SuSE-LINUX 风滚草

1. some files in the appointed directory havn't been necessary in any direct relation with mariadb. I.e: I placed some hints, a text-file, some bakup-copys somewhere in the same appointed directory for mysql mariadb and this caused endless error-messages and blocking the server from starting. Mariadb appears to be very sensible and hostile with the presence of other files not beeing database files(comments,backups,experimantal files etc) .

2. using libreoffice as client then there already this generated much problems with the creation and working on a database and caused some crashes.The crashes eventually produced bad tables.

3. May be because of that or may be because of the presence of not yet deleted but unusable tables !! the mysql mariadb server crashed and didn't want to do it' job not even start.

1. 指定目录中的某些文件与 mariadb 没有任何直接关系。即：我在 mysql mariadb 的同一指定目录中的某处放置了一些提示、文本文件、一些备份副本，这导致了无休止的错误消息并阻止服务器启动。Mariadb 似乎非常明智和敌意，因为存在其他不是数据库文件（评论、备份、实验文件等）的文件。

2. 使用 libreoffice 作为客户端，这已经在创建和处理数据库时产生了很多问题，并导致了一些崩溃。崩溃最终产生了坏表。

3. 可能是因为这个原因，也可能是因为存在尚未删除但无法使用的表！！mysql mariadb 服务器崩溃了，不想做它甚至没有开始的工作。

Error message always same : "Table 'some.table' doesn't exist in engine"

错误消息始终相同：“引擎中不存在表‘some.table’”

But when it started then tables appeared as normal, but it was unpossible to work on them.

但是当它开始时，表格看起来正常，但无法处理它们。

So what to do without a more precise Error Message ?? The unusable tables showed up with: "CHECK TABLE" or on the command line of the system with "mysqlcheck " So I deleted by filemanager or on the system-level as root or as allowed user all the questionable files and then problem was solved.

那么没有更精确的错误信息怎么办？？不可用的表显示为：“CHECK TABLE”或在系统的命令行上使用“mysqlcheck”所以我通过文件管理器或在系统级别以 root 或允许用户的身份删除了所有有问题的文件，然后问题就解决了。

Proposal: the Error Message could be a bit more precisely for example: "corrupted tables" (which can be found by CHECK TABLE, but only if the server is running) or by mysqlcheck even whe server is not running - but here are other disturbing files like hints/bakups a.s.o not visible.

建议：错误消息可能更精确一些，例如：“损坏的表”（可以通过 CHECK TABLE 找到，但仅当服务器正在运行时）或 mysqlcheck 即使服务器没有运行 - 但这里还有其他令人不安的提示/备份等文件也不可见。

ANYWAY it helped a lot to have backup-copies of the original database-files on a backup volume. This helped to check out and test it again and again until solution was found.

无论如何，在备份卷上拥有原始数据库文件的备份副本有很大帮助。这有助于一次又一次地检查和测试，直到找到解决方案。

Good luck all - Herbert

祝大家好运 - 赫伯特

回答by otidh

In my case, the error disappeared after I rebooted my OS and restarted MariaDB-server. Strange.

就我而言，在我重新启动操作系统并重新启动 MariaDB 服务器后，错误消失了。奇怪的。