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
