InnoDB:尝试打开先前打开的表空间

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.

我试着做的第一件事就是按照日志里的建议去做。

The were no permission problems
这不是许可问题
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.
不清楚我是否需要该表，也不清楚是否要删除phpmyadmin/pma__tracking或archive.ibd。当我处理掉档案的时候。ibd，错误刚刚传递到另一个。ibd文件。
'innodb_force_recovery=1' was added to my.cnf and this cause a bunch of errors.
“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

web上的第一个建议是删除下面的文件。

\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.

这些方法都没有奏效。

10 个解决方案

#1

131

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

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

Open my.ini (my.cnf on linux-based systems and Mac)
打开我的。ini(基于linux的系统和Mac上的my.cnf)
Look for [mysqld]
寻找(mysqld)
Just below [mysqld] insert innodb_force_recovery = 1
在[sqmyld]下面插入innodb_force_recovery = 1
Start MySQL Service
启动MySQL服务
Stop MySQL Service
停止MySQL服务
Remove the line from my.ini (innodb_force_recovery = 1)
把线从我的。ini(innodb_force_recovery = 1)
Start MySQL Service
启动MySQL服务

Worked perfect in my case.

我的工作做得很好。

I hope this will solve your problem.

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

#2

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

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

Took the backup of \xampp\mysql\data

备份\xampp\mysql\数据
Removed all the files and folders from data folder except mysql

从数据文件夹中删除除mysql外的所有文件和文件夹
Quit and started the XAMPP again.

退出并再次启动XAMPP。
Move the databases from data folder one by one.

将数据库逐个地从数据文件夹中移动。

#3

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文件夹。只有这样，它才能发挥作用。

#4

Solution is for MAC 10.11.3 El Captian

解决方案是MAC 10.11.3 El Captian

Go to /Applications/XAMPP/xamppfiles/var/mysql/
去/应用程序/ XAMPP / xamppfiles / var / mysql /
Delete all random files (except the actual database folders)
删除所有随机文件(实际数据库文件夹除外)
Restart Apache and MySQL.
重新启动Apache和MySQL。

This worked for me.

这为我工作。

#5

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

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

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

和/应用程序/ XAMPP / xamppfiles / var / mysql / ib_logfile1 /应用程序/ XAMPP / xamppfiles / var / mysql / ib_logfile1.bkp

#6

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…”。因此，仔细看看这个错误日志，它显示了文件位于“/库/应用程序支持/appsolute/MAMP PRO/db”。由于我在不再使用的数据库上遇到了问题，所以我尝试删除相应的文件夹，结果是成功的。下一步是删除作者已经提到的文件。

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

But as mentioned deleting the databasefolder worked out great.

但是，正如前面提到的，删除databasefolder效果很好。

#7

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。现在，你必须以某种方式重新编号其中的一个。

#8

"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.
MySQL有两个副本，都在尝试运行。
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.
Windows未能在MySQL的一个文件上发布“open”。我以前见过这个问题。通过重启窗户治愈了它;后来通过升级Windows。

#9

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__tracking so the table is the one with the pma_ extension.

当InnoDB崩溃时，在错误消息中的spot会引起麻烦。这里是phpmyadmin/pma__tracking，因此该表具有pma_扩展名。

Then go to /Library/Application Support/appsolute/MAMP PRO/db/mysql and 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文件夹放回其所属位置，并再次启动服务器。一切都会好起来的。

#10

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- use -innodb-engine-without out-file-ibdata1.html)之后，我也遇到了同样的问题。启动mysql文件。然后在数据库中创建归档表。

#1

131