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.

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

Solution 2

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 except mysql

3. Quit and started the XAMPP again.

4. Move the databases from data folder one by one.

Solution 3

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

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

Solution 4

Solution is for 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.

This worked for me.

Solution 5

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.

View more solutions

Comments

[Tgr]

Problems with your password/connection are certainly not related to your server being unable to load the database. Do you have backups? You can probably do without archive (it holds deleted wiki pages) but chances are it is only mentioned because it is at the beginning of the alphabet and all your tables are affected.

[Tgr]

The message about permissions etc. is a generic one; the actual error message is 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 (This article has some advice on changin tablespace IDs, but it's way beyond my InnoDB skills to tell whether that's a good idea in this case or not.)

[dedrumhead]

Tgr, we backed up all of our files on box so there should be all of the changes to each file available. When the 'archive' database is taken out, yes it just stops at the next one so it is alphabetic. This leads me to believe that either ALL of my tables are corrupt (probably not) or there's something corrupt with whatever is reading them in. I'm currently looking into the link you suggested. Thanks!

[Tgr]

They aren't corrupt, really; 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.

[Rick James]

Removing ibdata1 destroys all data. Even if you have .ibd files, it makes the tables inaccessible.

[Bruce Raynor]

I had a similar problem to this using Xampp on Mac - I used Nesar's answer, however I also had to make a change permissions first on: /Applications/XAMPP/xamppfiles/var/mysql/tablefoldername for some bizarre reason this folder had mysteriously had its permissions removed from all users and caused an InnoDB error in my case which resulted in MySQL not starting.

[Nookeen]

Thsi method works with Aquia DevDesktop as well

[CodingYourLife]

That's it! You don't even have to do it one by one, just say skip existing files. After starting mysql with just the data folder present it creates important files on it's own. When not overriden it works.

[Luke Shaheen]

This is fantastic! Worked like a charm, with no need to delete everything and reinstall.

[Michael Olenick]

Worked for me too. Set it to 1, mysql started (and, apparently, repaired itself). Stopped mysql, set it to 0, restarted and all is OK. Note the docs say that when the value is >0 INSERT, UPDATE, and DELETE don't work. Docs are here: dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

[user299791]

I am trying this but no success, and every time I check the my.cnf file the line I added is deleted... don't understand why, I am accessing the file from pico after sudo, so I should have permission to write...

[user299791]

ok got it, MAMP Pro rewrites the .cnf file each time it starts, so you have to edit the template from File->Edit Template->MySQL

[brasofilo]

Stack Overflow, saving developer's arses at every bloody answer. Spot on!

[Mugoma J. Okomba]

Worked for me too

[kakoma]

In case you cannot find my.cnf on MAMP (on Mac), this is useful: stackoverflow.com/a/2869707/1131990 Basically, create a my.cnf file at /Applications/MAMP/conf/, add a [mysqld] section and to it, add innodb_force_recovery = 1

[Benjamin Intal]

Worked in Mac Sierra with MAMP

[Max Mumford]

On MAMP you can find your mysql config file by going to file > edit template > mysql > version

[Nuno Sarmento]

Thanks @Nesar you have saved my day

[eplazai]

Don't try to edit directly my.cnf by finding on Spotlight, you'll reach /tmp/my.cnf instead the real-one. You have to modify directly via MAMP Pro App (File > Edit > Template > MySQL)

[Matthias Kleine]

Works perfectly! Thank you very much. In MAMP Pro you can use the "Edit Template" feature to add those values to your configuration file (and remove it afterwards).

[Robert Sinclair]

Just a note that this solution may corrupt the data, as per the log file it specifically says "We do not continue the crash recovery, because the table may become corrupt if we cannot apply the log records in the InnoDB log to it."

[jeffsama]

In case someone using xampp on mac with default settings, the path is /Applications/XAMPP/xamppfiles/etc/my.cnf

[Robbie White]

If you're using MAMP PRO this file can be easily edited in the File Menu > Edit Template > MySQL(my.cnf).

[reformed]

Worked on Windows with WAMP.

[InanisAtheos]

I love this site. A question asked some 4 years ago helped me out now and it took 2 frickin minutes. This answer needs a damn checkmark.

[mior farhan]

This answer helped me! Thanks.

[Unknown]

worked on Windows 10 with MYSQL 5.6. my.ini location C:\ProgramData\MySQL\MySQL Server 5.6

[Wade]

Should be selected answer. After a mac update, or computer froze and forced restart (not sure which threw mysql into a fit), mysql failed to start. This solved it!

[Flash Thunder]

didn't have that problem before when watchdog worked on raspberry pi (rebooting the system), but now it happens every time!!! any idea how to avoid that?

[zakaria mouqcit]

This worked so well for me but after restarting my mac.

[dartacus]

This was what worked for me on mariadb bundled with the latest release of xampp, alongside the innodb_force_recovery workaround above. Previously only this step was necessary, now it seems I have to nix the logfiles too.

[zeta]

By moving the folders one by one, and stopping and running MySQL I could find the culprit database folder. But this didn't help me fix the issue

[Blaine Lafreniere]

seems my database is completely hosed. this did not work for me.