Web lists-archives.com

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace




Hello Neil,

On 1/22/2016 3:23 PM, Neil Tompkins wrote:
2016-01-22 19:43:48 2556 [ERROR] InnoDB: Attempted to open a previously
opened tablespace. Previous tablespace mysql/innodb_table_stats uses space
ID: 1 at filepath: .\mysql\innodb_table_stats.ibd. Cannot open tablespace
sportstrader/event which uses space ID: 1 at filepath:
.\sportstrader\event.ibd
InnoDB: Error: could not open single-table tablespace file
.\sportstrader\event.ibd

This is where you need to focus. Something has modified the tablespace ID in the event.ibd file (the table `sportstrader`.`events`) so that it has the same ID as one of the system tables (the one used to store persistent stats for the InnoDB storage engine).

Each tablespace must have its own unique ID value.

This could have been anything from a bad sector on disk, a stray write by some other program, an intentional file-system-level change by some nefarious person, a bad memory cell that held the ID value of the `event` table's first page that caused a wrong value to be written to disk when the table closed, a bad backup/restore cycle ... the list can go on practically forever. The problem is, the ID value in the .ibd file now conflicts with that of another tablespace file.

The resolution is to delete that table then restore it from a known good copy (preferably one from a backup). If necessary, use the content of the Binary log to recover changes made to the table since that backup was made.

If you don't have a good copy (or any Binary log content) then try this:

1) Make a copy of event.ibd then remove it from that folder
2) Restart mysqld, it will complain about the missing file into the Error log. This is expected.
3) Use the techniques here to DROP that table.
http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
4) Create an empty copy of that table (correct schema, no data)
5) Use ALTER TABLE...DISCARD TABLESPACE then ALTER TABLE...IMPORT TABLESPACE to replace the empty .ibd file you have now with the one you backed up earlier. (demonstrated at the bottom of that link I just provided for "orphaned" tablespaces)

Then, when you can, schedule a full hardware check to look for potentially failing memory or disk media. That's the most common cause for a fault like this.

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql