Why didn't InnoDB table recover?

Posted on 2014-01-10
Medium Priority
Last Modified: 2014-01-11
I use MySQL Workbench 6.0 for MySQL 5.6 running on my W7 64 pc.

I was altering a table when my pc crashed.  After reboot, I got an error when I tried to run a query on the table that the table didn't exist.  Looking in the system log, I found:

InnoDB: table ltbhouston/sellers contains 13 user defined columns in InnoDB, but 12 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how to resolve it
InnoDB: Cannot open table ltbhouston/sellers from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2014-01-10 21:13:10 1b34  InnoDB: table "ltbhouston"."sellers"is corrupted. Please drop the table and recreate

I see in the system log that it took steps to recover from the crash using the bin logs.

1) What could have caused the recovery to fail?
2) Is there something I can reconfigure to make recovery more bulletproof so it can recover the table to prior to the alter table operation (i.e. in the future should something like this happen again)?
3) How can I recover the table so I don't have to lose a day's work by restoring last night's backup?  I reviewed the links referenced in the system log, but didn't see how I could use the info to try and recover the table

Thanks for your help--
Question by:SAbboushi
1 Comment
LVL 23

Accepted Solution

Patrick Bogers earned 2000 total points
ID: 39773192

1) there could be queries present in memory but not yet written to the database in time the machine crashed.
2) No there is not, chrashes while running an active DB surely can corrupt open databases.
3) From the error i read "ltbhouston"."sellers" is corrupted. The name implifies that this table stores a list of seller-contacts, how much can change in one day? I tend to believe not that much but off course i cannot look into the tables.

Else you could try to run innodb on the corrupted database and try to dump the sellers table to a file, just give it a chance. How to is described here.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question