CODEWITHSUNDEEP
phpmysqlsqldatabaseinnodb
terpak
terpak

Reputation: 1151

MySQL ALTER TABLE deleted table

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

Answers (3)

akuzminsky
akuzminsky

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

user3894153
user3894153

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

BeepMaster
BeepMaster

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

Related Questions