Reputation: 1151
I'm freaking out. I work at a company that (incredibly stupidly) doesn't back up their MySQL database. I had to change one of the columns in a table "items" in order to raise the max character count for an item's description. I used the following command:
ALTER TABLE items CHANGE description description varchar(5000) NOT NULL;
But, after I entered this command, I got this error:
Error on rename of './company/#sql-b30_400ad' to './company/items' (errno: 150 - Foreign key constraint is incorrectly formed)
Now, the table is completely gone. Is there a way to access the "#sql-b30_400ad" table to recover it? I know there was a key removed from this table with the following method:
SET foreign_key_checks = 0;
ALTER TABLE items DROP KEY foreign_key_name;
SET foreign_key_checks = 1;
Is there any way to undo what happened, or to recover the temporary items table that was created for the ALTER command?
EDIT: The table uses the InnoDB engine
EDIT AGAIN: So I found out that since we use Amazon Web Service for our MySQL Server, AWS automatically backs it up daily, the last backup being conveniently 15 minutes before my error. My pants are brown and yellow now, but all is good and I extend my thanks to everyone who answered!
Upvotes: 2
Views: 582
Reputation: 2257
Check out unDROP tool for InnoDB. It allows to read records directly from InnoDB tablespace.
#sql-b30_400ad
is a file with the new structure. So, split it with stream_parser and then extract records with c_parser. See https://twindb.com/undrop-tool-for-innodb/ , your case fits pretty well into the description.
Upvotes: 0
Reputation: 1
check this http://www.thegeekstuff.com/2014/04/recover-innodb-mysql/
Is it production DB or Development DB, I would not worry if it is development,
Upvotes: 0
Reputation: 61
You can read documentation about the command :
check
repair
and the tool
mysqlcheck
myisamchk
...
My advice : But first of all if data are very important, for safety reason you may have to stop any read/write process on the disk, and may be stop the disk.
In future : Always make your own backup before any modification, even for these repair/check commands
mysqldump
mysqlhotcopy
Upvotes: 2