I discovered that this was because it had foreign-key constraints. The awful error message is itself listed as a bug on mysql... but the real problem is that it doesn't want to drop an index I've asked it - because there's a foreign-key constraint on the column referenced by that index*.

Fair enough.

Unfortunately, the "remove_foreign_key" code we have assumes the foreign-key has been named in a certain way (table_name_column_name) but in this case it wasn't. So Trying a drop index on that caused it to give an equally unhelpful message along the same lines...

This foreign key has been hanging around since the dawn-o-time, and it has one of those automagically-generated constraint-names built by the db itself... something like: my_table_name_ibfk_16.

Now that'd be fine to drop if we just had one client. with one database... you can easily put the following into a migration:

Unfortunately for us, we have 150 clients - each with their own db... and it looks like that constraint-name differs depending on which order the foreign-keys got created. ie sometimes it's my_table_name_ibfk_16 and sometimes it's my_table_name_ibfk_3 - and if you drop it by number - you could be killing the WRONG foreign-key constraint... which would just be embarrassing.

The way out of this quandary is to query the information_schema table to find the foreign-key's actual constraint-name. and here it is for your amusement. (note: put this into initializers eg by saving it as config/initializers/migrations.rb

class ActiveRecord::Migration
# grab the db-name out of the connection and persist it
# it's not going to change over the course of a single migration
def self.fetch_database_name
@@database_name ||= connection.database_name
end
# Use this if the foreign-key was created without an explicit name -
# and has one of the automatically-generated constraint-names.
#
# This method queries the information-schema table to fetch out the key
# name before continuing to drop the foreign-key
#
# Use this in your migrations with:
# remove_legacy_foreign_key :table_name, :field_name
# eg:
# remove_legacy_foreign_key :widgets, :wodget_id
def self.remove_legacy_foreign_key(table, column_name)
# first pull the foreign-key name from the information schema
result = execute "select constraint_name from information_schema.key_column_usage as ke where ke.table_schema = '#{fetch_database_name}' and ke.table_name = '#{table}' and ke.column_name = '#{column_name}';"
name = result.fetch_hash['constraint_name']
raise "Got no foreign key by that name" unless name.present?
execute "ALTER TABLE `#{table}` DROP FOREIGN KEY `#{name}`"
end
end

[*] Note: mysql also gives a similar error if you're dropping a foreign-key constraint that doesn't exist at all by the name you give it: