Ran across this fairly unhelpful error in MySQL today:
InnoDB: Error: in ALTER TABLE `myschema`.`mytablename` has or is referenced in foreign key constraints which are not compatible with the new table definition. [Warning]: Invalid (old?) table or database name '#sql-a5c_8' InnoDB: Error: table `myschema`.`mytablename` does not exist in the InnoDB internal data dictionary though MySQL is trying to drop it. Have you copied the .frm file of the table to the MySQL database directory from another database?
I was attempting to drop a column from a table without first dropping the foreign key (inside a Yii migration run from the command line). The above errors are what show in the MySQL logs, here is what is presented by yiic migrate on the command line:
drop column owe_template_id from table {{tablename}} ...Exception: CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1025 Error on rename of '.\myschema\#sql-a5c_8' to '.\myschema\mytablename' (errno: 150).
Needless to say, these really didn’t help much in resolving the problem. In order to fix it, I first needed to drop the foreign key, then drop the column.
make sure you DROP FOREIGN KEY before you DROP COLUMN!
MySQL Docs pertaining to ALTER TABLE: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
related StackOverflow question: http://stackoverflow.com/questions/7108444/attempted-to-drop-columns-containing-foreign-keys-and-received-error-message