InnoDB: Attempted to open a previously opened tablespace

Question

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.

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.

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

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'

None of these have worked.

Answer 1

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

Worked perfect in my case.

I hope this will solve your problem.