Lost table on failed removal of MySQL foreign key

Question

I have a table like so:

CREATE TABLE `Words` (
  `wordId` int(11) NOT NULL AUTO_INCREMENT,
     ... snip! ...
  `PartOfSpeechpartOfSpeechId` int(11) DEFAULT NULL,
  PRIMARY KEY (`wordId`) USING BTREE,
  KEY `fk_Words_PartOfSpeech` (`PartOfSpeechpartOfSpeechId`),
  CONSTRAINT `fk_Words_PartOfSpeech` FOREIGN KEY (`PartOfSpeechpartOfSpeechId`) REFERENCES `PartOfSpeech` (`partOfSpeechId`) ON DELETE SET NULL ON UPDATE NO ACTION,
) ENGINE=InnoDB AUTO_INCREMENT=7306 DEFAULT CHARSET=utf8

I need to convert the PartOfSpeechpartOfSpeechId column into an enumerated value (ie keep it as an INT column, but remove the table it references entirely).

Trying to drop the PartOfSpeech table fails as one would expect, due to the FK on Words. To fix this, I've run ALTER TABLE Words DROP FOREIGN KEY fk_Words_PartOfSpeech, using the name of the FK instead of the column as noted everywhere. I still get an error:

ERROR 1025 (HY000): Error on rename of './lexercise/#sql-1a5_263' to './lexercise/Words' (errno: 150)

On looking for the table Words, it has vanished. Gone. No idea where to. Presumably, MySQL's internal alter table code is creating a temp table and is failing to return it to its proper name/location.

What is going on here? How is it possible to lose a whole table on dropping a foreign key? My background is in Postgres, not sure how best to debug this.

Ever hear of this thing called: Transactions? If FAILS, it rolls back — Fallenreaper, Jul 25 '13 at 19:52
It was my understanding that transactions in MySQL don't work with ALTER TABLE, but upon reading up, it looks like that bug/gap was fixed several years back (in between my two bouts with MySQL) - will look into updating the migration script system to use this new info, but that doesn't help find the problem with the above. — Irongaze.com, Jul 25 '13 at 19:55

score 0 · Answer 1 · answered Jul 25 '13 at 21:29

OK, so for anyone who finds this at some point with the same issue, here's what I think was happening:

I had another foreign key on the table (not shown above) that referenced a table that had been renamed prior to trying to drop the FK above. I think that the presence of that FK was what was causing the error on my ALTER TABLE call.

MySQL basically rebuilds the table schema during ALTER TABLE calls - it does not make incremental changes. As a result, if you have other things wrong with the table schema, make sure you identify them and fix them as well if you're losing tables randomly.

Oh, and MySQL REALLY should handle this better. My god.

Lost table on failed removal of MySQL foreign key

1 Answers1