MySQL InnoDB：尝试打开以前打开的表空间

InnoDB: Attempted to open a previously opened tablespace

提问by dedrumhead

I have been working on a problem for a few days now. Our local mediawiki page that sits on our box account, destroyed itself and we've been working to get it online. Using XAMPP Control Panel v3.2.1, the errors were numerous so we decided to update XAMPP (v3.2.2) and move the 'htdocs' and 'mysql/data' files over to the new data base.

几天来我一直在研究一个问题。我们的本地 mediawiki 页面位于我们的 Box 帐户上，它自行销毁，我们一直在努力使其在线。使用 XAMPP 控制面板 v3.2.1，错误很多，所以我们决定更新 XAMPP (v3.2.2) 并将“htdocs”和“mysql/data”文件移动到新数据库。

First error:

第一个错误：

    9:50:21 AM  [mysql]     Attempting to start MySQL app...
    9:50:22 AM  [mysql]     Status change detected: running
    9:50:22 AM  [mysql]     Status change detected: stopped
    9:50:22 AM  [mysql]     Error: MySQL shutdown unexpectedly.
    9:50:22 AM  [mysql]     This may be due to a blocked port, missing dependencies, 
    9:50:22 AM  [mysql]     improper privileges, a crash, or a shutdown by another method.
    9:50:22 AM  [mysql]     Press the Logs button to view error logs and check
    9:50:22 AM  [mysql]     the Windows Event Viewer for more clues
    9:50:22 AM  [mysql]     If you need more help, copy and post this
    9:50:22 AM  [mysql]     entire log window on the forums

As it says, I then went to the logs and found this:

正如它所说，然后我去了日志，发现了这个：

    2015-11-20 09:50:22 11f8 InnoDB: Warning: Using      innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
    2015-11-20  9:50:22 4600 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    2015-11-20  9:50:22 4600 [Note] InnoDB: The InnoDB memory heap is disabled
    2015-11-20  9:50:22 4600 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
    2015-11-20  9:50:22 4600 [Note] InnoDB: Memory barrier is not used
    2015-11-20  9:50:22 4600 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2015-11-20  9:50:22 4600 [Note] InnoDB: Not using CPU crc32 instructions
    2015-11-20  9:50:22 4600 [Note] InnoDB: Initializing buffer pool, size = 16.0M
    2015-11-20  9:50:22 4600 [Note] InnoDB: Completed initialization of buffer pool
    2015-11-20  9:50:22 4600 [Note] InnoDB: Highest supported file format is Barracuda.
    2015-11-20  9:50:22 4600 [Note] InnoDB: The log sequence numbers 1665234 and 1665234 in ibdata files do not match the log sequence number 50125498 in the ib_logfiles!
    2015-11-20  9:50:22 4600 [Note] InnoDB: Database was not shutdown normally!
    2015-11-20  9:50:22 4600 [Note] InnoDB: Starting crash recovery.
    2015-11-20  9:50:22 4600 [Note] InnoDB: Reading tablespace information from the .ibd files...
    2015-11-20  9:50:22 4600 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace phpmyadmin/pma__tracking uses space ID: 21 at filepath: .\phpmyadmin\pma__tracking.ibd. Cannot open tablespace wiki/archive which uses space ID: 21 at filepath: .\wiki\archive.ibd
    InnoDB: Error: could not open single-table tablespace file .\wiki\archive.ibd
    InnoDB: We do not continue the crash recovery, because the table may become
    InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
    InnoDB: To fix the problem and start mysqld:
    InnoDB: 1) If there is a permission problem in the file and mysqld cannot
    InnoDB: open the file, you should modify the permissions.
    InnoDB: 2) If the table is not needed, or you can restore it from a backup,
    InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
    InnoDB: crash recovery and ignore that table.
    InnoDB: 3) If the file system or the disk is broken, and you cannot remove
    InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
    InnoDB: and force InnoDB to continue crash recovery here.

Now this looks like a standard error that I've seen with many different suggestions throughout the web on how to fix it. I will go over them briefly.

现在这看起来像是一个标准错误，我在整个网络上看到了许多关于如何修复它的不同建议。我将简要介绍一下。

The first thing I tried was to follow the suggestions in the log.

我尝试的第一件事是遵循日志中的建议。

1. The were no permission problems
2. It is not clear if I need the table or not, OR whether to get rid of phpmyadmin/pma__tracking or archive.ibd. When I got rid of the archive.ibd, the error just past on to another .ibd file.
3. 'innodb_force_recovery=1' was added to my.cnf and this cause a bunch of errors.

1. 没有权限问题
2. 目前尚不清楚我是否需​​要该表，或者是否删除 phpmyadmin/pma__tracking 或 archive.ibd。当我摆脱了 archive.ibd 时，错误刚刚传递到另一个 .ibd 文件。
3. 'innodb_force_recovery=1' 被添加到 my.cnf 中，这会导致一系列错误。

The next thing I noticed is that when we built the new database, I got this error in my phpMyAdmin (localhost/phpMyAdmin): phpMyAdmin error

我注意到的下一件事是，当我们构建新数据库时，我的 phpMyAdmin (localhost/phpMyAdmin) 中出现此错误： phpMyAdmin 错误

I'm not sure if this is causing all of my problems or not. I found that people were saying to switch a password to =''. This error might be happening because I'm entering old data folders in a new database. I'm not sure.

我不确定这是否会导致我的所有问题。我发现人们说要将密码切换为 =''。发生此错误可能是因为我在新数据库中输入旧数据文件夹。我不知道。

The first suggestion on the web was to remove the following files from

网络上的第一个建议是从以下文件中删除以下文件

\mysql\data:
    innodb_index_stats.frm
    innodb_index_stats.ibd 
    innodb_table_stats.frm 
    innodb_table_stats.ibd 
    slave_master_info.ibd 
    slave_relay_log_info.frm 
    slave_relay_log_info.ibd 
    slave_worker_info.frm 
    slave_worker_info.ibd

The 2nd:

第二：

I've tried removing 'ibdata1'

我试过删除'ibdata1'

None of these have worked.

这些都没有奏效。

回答by Nesar

I had the same problem. I tried all the solution others has proposed, and unfortunately nothing worked.

我有同样的问题。我尝试了其他人提出的所有解决方案，但不幸的是没有任何效果。

After spending hours on searching for the solution in Google I finally found this

花了几个小时在谷歌搜索解决方案后，我终于找到了这个

1. Open my.ini (my.cnf on linux-based systems and Mac)
2. Look for [mysqld]
3. Just below [mysqld] insert innodb_force_recovery = 1
4. Start MySQL Service
5. Stop MySQL Service
6. Remove the line from my.ini (innodb_force_recovery = 1)
7. Start MySQL Service

1. 打开 my.ini（基于 linux 的系统和 Mac 上的 my.cnf）
2. 寻找 [mysqld]
3. 在 [mysqld] 下面插入innodb_force_recovery = 1
4. 启动 MySQL 服务
5. 停止 MySQL 服务
6. 从 my.ini 中删除该行（innodb_force_recovery = 1）
7. 启动 MySQL 服务

Worked perfect in my case.

在我的情况下工作完美。

I hope this will solve your problem.

我希望这能解决你的问题。

回答by San

I got the same error. These are the steps I followed.

我得到了同样的错误。这些是我遵循的步骤。

1. Took the backup of \xampp\mysql\data

2. Removed all the files and folders from data folder exceptmysql

3. Quit and started the XAMPP again.

4. Move the databases from datafolder one by one.

1. 备份了\xampp\mysql\data

2. 从数据文件夹中删除了所有文件和文件夹，除了mysql

3. 退出并再次启动 XAMPP。

4. 将数据库从data文件夹中一一移动。

回答by Djanym

try to rename /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile0to /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile0.bkp

尝试重命名/Applications/XAMPP/xamppfiles/var/mysql/ib_logfile0为/Applications/XAMPP/xamppfiles/var/mysql/ib_logfile0.bkp

and /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile1to /Applications/XAMPP/xamppfiles/var/mysql/ib_logfile1.bkp

并/Applications/XAMPP/xamppfiles/var/mysql/ib_logfile1以/Applications/XAMPP/xamppfiles/var/mysql/ib_logfile1.bkp

回答by Leandro Castro

To use the above (Nesar solution) in MAMP (version >= 4), you must first copy the my.cnf file that is inside MAMP/tmp/mysql to the MAMP/conf folder. Only then will it work.

要在 MAMP（版本 >= 4）中使用上述（Nesar 解决方案），您必须首先将 MAMP/tmp/mysql 中的 my.cnf 文件复制到 MAMP/conf 文件夹中。只有这样才能奏效。

回答by Tarun Gupta

Solution is for MAC 10.11.3 El Captian

解决方案适用于 MAC 10.11.3 El Captian

• Go to /Applications/XAMPP/xamppfiles/var/mysql/
• Delete all random files (except the actual database folders)
• Restart Apache and MySQL.

• 转到 /Applications/XAMPP/xamppfiles/var/mysql/
• 删除所有随机文件（实际数据库文件夹除外）
• 重新启动 Apache 和 MySQL。

This worked for me.

这对我有用。

回答by haeki

I had a similar problem with Mamp Pro. For me it turned out the correct .idb-files were not located at "Applications/Mamp...". So taking a closer look at the error-log it showed me the files were located at "/Library/Application Support/appsolute/MAMP PRO/db". As I had problems with a database I wasn't using anymore, I just tried to delete the corresponding folder and it worked out. The next step would have been to delete the files already mentioned by the author.

我在 Mamp Pro 上遇到了类似的问题。对我来说，原来正确的 .idb 文件不在“应用程序/Mamp ...”中。因此，仔细查看错误日志，它告诉我文件位于“/Library/Application Support/appsolute/MAMP PRO/db”。由于我不再使用的数据库出现问题，我只是尝试删除相应的文件夹并解决了问题。下一步是删除作者已经提到的文件。

• innodb_index_stats.frm
• innodb_index_stats.ibd
• innodb_table_stats.frm
• innodb_table_stats.ibd
• slave_master_info.ibd
• slave_relay_log_info.frm
• slave_relay_log_info.ibd
• slave_worker_info.frm
• slave_worker_info.ibd

• innodb_index_stats.frm
• innodb_index_stats.ibd
• innodb_table_stats.frm
• innodb_table_stats.ibd
• slave_master_info.ibd
• slave_relay_log_info.frm
• slave_relay_log_info.ibd
• slave_worker_info.frm
• slave_worker_info.ibd

But as mentioned deleting the databasefolder worked out great.

但如前所述，删除数据库文件夹效果很好。

回答by Lou

Another solution for the issue discribed above for MAMP Pro, as I found impossible to edit my.cnf properly :

上面针对 MAMP Pro 描述的问题的另一个解决方案，因为我发现无法正确编辑 my.cnf：

When InnoDB is crashing, spot in the error message the DB that is causing trouble. Here it is phpmyadmin/pma__trackingso the table is the one with the pma_ extension.

当 InnoDB 崩溃时，在错误消息中找出导致问题的 DB。这phpmyadmin/pma__tracking使该表是一个与pma_扩展。

Then go to /Library/Application Support/appsolute/MAMP PRO/db/mysqland remove the folder named after the problem causing DB.

然后转到/Library/Application Support/appsolute/MAMP PRO/db/mysql并删除以导致 DB 的问题命名的文件夹。

Restart your MAMP server. Once you restarted with success, you can stop servers again, put back the DB folder where it belongs and start the servers again. Everything should be fine again.

重新启动您的 MAMP 服务器。成功重新启动后，您可以再次停止服务器，放回它所属的 DB 文件夹并再次启动服务器。一切都应该又好了。

回答by morteza khadem

I had the same problem, after use recovery database structure ( http://zadpen.com/20-restore-lost-data-in-mysql-using-innodb-engine-without-file-ibdata1.html) I remove the archive.ibm file and start mysql. then create archive table in database.

我遇到了同样的问题，在使用恢复数据库结构（http://zadpen.com/20-restore-lost-data-in-mysql-using-innodb-engine-without-file-ibdata1.html）后，我删除了存档.ibm 文件并启动 mysql。然后在数据库中创建存档表。

回答by Nemo

Tgr's answer looks appropriate. The message about permissions etc. is a generic one; the actual error message is

Tgr 的答案看起来很合适。有关权限等的消息是通用的；实际的错误信息是

2015-11-20 9:50:22 4600 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace phpmyadmin/pma__tracking uses space ID: 21 at filepath: .\phpmyadmin\pma__tracking.ibd. Cannot open tablespace wiki/archive which uses space ID: 21 at filepath: .\wiki\archive.ibd

Your wiki database and phpmyadmin database somehow ended up with the same tablespace ID. Each would work fine if the other wasn't present; as it is now, you'll have to renumber one of them somehow.

您的 wiki 数据库和 phpmyadmin 数据库不知何故以相同的表空间 ID 结束。如果另一个不在场，每个都可以正常工作；就像现在一样，您必须以某种方式重新编号其中之一。

回答by Rick James

"Attempted to open a previously opened tablespace." -- That smells like one of these:

“试图打开以前打开的表空间。” - 闻起来像其中之一：

• There are two copies of MySQL and both are trying to run.
• Windows failed to release an 'open' on one of MySQL's files. I used to see that problem. It was cured by rebooting Windows; later by upgrading Windows.

• 有两个 MySQL 副本，并且都在尝试运行。
• Windows 未能在 MySQL 的文件之一上释放“打开”。我曾经看到过这个问题。它通过重新启动 Windows 被治愈；稍后通过升级 Windows。