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.

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

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, Nov 22 '15 at 22:32
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](https://www.percona.com/blog/2011/05/13/connecting-orphaned-ibd-files/) 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.) — Tgr, Nov 22 '15 at 22:52
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! — dedrumhead, Nov 23 '15 at 15:43
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. — Tgr, Nov 23 '15 at 19:27
Removing ibdata1 destroys all data. Even if you have .ibd files, it makes the tables inaccessible. — Rick James, Nov 19 '16 at 21:13
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. — Bruce Raynor, Mar 18 '17 at 12:15

Nesar · Answer 1 · 2018-01-15T17:21:23.107

217

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)
Look for [mysqld]
Just below [mysqld] insert innodb_force_recovery = 1
Start MySQL Service
Stop MySQL Service
Remove the line from my.ini (innodb_force_recovery = 1)
Start MySQL Service

Worked perfect in my case.

I hope this will solve your problem.

edited Jan 15 '18 at 17:21

answered Oct 14 '16 at 08:57

Nesar

5,599
2
22
21

12

This is fantastic! Worked like a charm, with no need to delete everything and reinstall. – Luke Shaheen Oct 18 '16 at 12:50
1

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: https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html – Michael Olenick Jan 30 '17 at 11:46
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 Feb 27 '17 at 14:42
7

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 – user299791 Feb 27 '17 at 15:19
2

*Stack Overflow*, saving developer's arses at every bloody answer. Spot on! – brasofilo May 02 '17 at 22:02
10

In case you cannot find `my.cnf` on MAMP (on Mac), this is useful: http://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` – kakoma May 17 '17 at 02:57
Worked in Mac Sierra with MAMP – Benjamin Intal Jul 07 '17 at 16:44
On MAMP you can find your mysql config file by going to file > edit template > mysql > *version* – Max Mumford Jul 17 '17 at 15:17
7

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) – eplazai Sep 09 '17 at 22:31
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). – Matthias Kleine Oct 12 '17 at 19:12
1

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." – Robert Sinclair Oct 23 '17 at 15:17
1

In case someone using xampp on mac with default settings, the path is `/Applications/XAMPP/xamppfiles/etc/my.cnf` – jeffsama Jan 23 '18 at 04:35
Worked on Windows with WAMP. – reformed May 10 '18 at 15:53
6

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. – InanisAtheos Jul 07 '18 at 21:04
worked on Windows 10 with MYSQL 5.6. my.ini location C:\ProgramData\MySQL\MySQL Server 5.6 – Unknown Oct 26 '18 at 05:02
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! – Wade Apr 11 '19 at 12:55
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? – Flash Thunder Jul 30 '19 at 11:25
1

This worked so well for me but after restarting my mac. – zakaria mouqcit Dec 30 '19 at 15:26
seems my database is completely hosed. this did not work for me. – Blaine Lafreniere Nov 22 '20 at 06:19

score 8 · Answer 2 · answered Jan 25 '17 at 17:37

8

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

answered Jan 25 '17 at 17:37

Djanym

332
1
4
13

This answer helped me! Thanks. – mior farhan Oct 02 '18 at 09:43
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. – dartacus Jan 27 '20 at 09:19

score 7 · Answer 3 · answered Mar 23 '16 at 05:55

7

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

Took the backup of \xampp\mysql\data
Removed all the files and folders from data folder except mysql
Quit and started the XAMPP again.
Move the databases from data folder one by one.

answered Mar 23 '16 at 05:55

San

666
7
27

1

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. – CodingYourLife May 20 '16 at 22:29
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 – zeta Jul 09 '20 at 05:42

score 3 · Answer 4 · answered Mar 19 '16 at 08:16

3

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.

answered Mar 19 '16 at 08:16

Tarun Gupta

6,305
2
42
39

score 3 · Answer 5 · edited Jul 26 '17 at 13:35

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.

edited Jul 26 '17 at 13:35

Blazemonger

90,923
26
142
180

answered Apr 03 '17 at 22:58

Leandro Castro

518
8
14

If you're using MAMP PRO this file can be easily edited in the File Menu > Edit Template > MySQL(my.cnf). – Robbie White Feb 17 '18 at 14:47

score 2 · Answer 6 · answered May 08 '17 at 09:19

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.

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.

score 0 · Answer 7 · answered Feb 13 '16 at 10:06

Tgr's answer looks appropriate. 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

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.

score 0 · Answer 8 · answered Nov 19 '16 at 21:16

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

score 0 · Answer 9 · answered Mar 29 '18 at 14:43

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

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.

Then go to /Library/Application Support/appsolute/MAMP PRO/db/mysql and remove the folder named after the problem causing 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.

score 0 · Answer 10 · answered May 20 '18 at 05:16

0

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.

answered May 20 '18 at 05:16

morteza khadem

346
3
8

score 0 · Answer 11 · answered Sep 21 '20 at 15:30

if someone has problems with any of their corrupt tables, this may be a possible solution to their problem, in my case I had the backup of my database so just delete the tables that are the files .ibd then restart MAMP PRO and mysql started correctly. A database backup must be taken into account. hope this helps someone and doesn't waste a lot of time. regards

score 0 · Answer 12 · answered Aug 23 '22 at 23:19

after 4 hours i got solution without loos any data, please use below to resolve your issue

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 centos /etc/my.cnf.d/server.cnf) Look for [mysqld]

Just below [mysqld] insert innodb_force_recovery = 1

Start MySQL Service

Stop MySQL Service

Remove the line from my.ini (innodb_force_recovery = 1)

Start MySQL Service

Worked perfect in my case.

I hope this will solve your problem.

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Aug 27 '22 at 13:04

InnoDB: Attempted to open a previously opened tablespace

12 Answers12

Linked