Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm about to drop an index from a mySQL INNODB table. There are references defined already. When I try to alter it, it says:

1025 - Error on rename of 'X/#sql-2a5f_219828' to 'X' (errno: 150)

Which at first seems logical, since there are relations, it can't be renamed. But even if I disable foreign key checking:

SET foreign_key_checks = 0;

ALTER TABLE flyers DROP INDEX Index_5, ROW_FORMAT = DYNAMIC;

I get the same! I don't want to drop ALL foreign key definitions, drop that index, and define them again! Another solution?

share|improve this question
    

2 Answers 2

Create a new index before dropping the old one.

share|improve this answer
    
what has a new index has to do with the old one? –  deeped Aug 11 '11 at 22:50
    
My answer may be incorrect if it does not help for you. I recently faced a situation where I had a foreign key referring to a column in another table and wanted to change/re-create the index for that column. I was not able to get rid of the old index, but after I first created a new index (basically a duplicate of the original index) I was able to drop the old one. Maybe the error message was different though and this is not related... Sorry about the noise. –  snap Aug 11 '11 at 23:51

the problem was there was already an index on the foreign key field :) and I didnt know that foreign key is also an index. Anyway I ripped all my hair out in the end, I had to drop all indexes to get rid of it. I will never use innoDB again

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.