Mysql2::Error: Cannot drop index

[kassi]

Following the installation instructions in a Ruby 3, Rails 6 application, I get the following MySQL error:

== 20210618092705 ActsAsTaggableOnMigration: migrating ========================
-- create_table(:tags, {:options=>"ENGINE=InnoDB", :id=>:integer})
   -> 0.0256s
-- create_table(:taggings, {:options=>"ENGINE=InnoDB", :id=>:integer})
   -> 0.0129s
-- add_index(:taggings, :tag_id)
   -> 0.0146s
-- add_index(:taggings, [:taggable_id, :taggable_type, :context], {:name=>"taggings_taggable_context_idx"})
   -> 0.0079s
== 20210618092705 ActsAsTaggableOnMigration: migrated (0.0620s) ===============

== 20210618092706 AddMissingUniqueIndices: migrating ==========================
-- add_index(:tags, :name, {:unique=>true})
   -> 0.0112s
-- index_exists?(:taggings, :tag_id, {:name=>"index_taggings_on_tag_id"})
   -> 0.0018s
-- remove_index(:taggings, :tag_id, {:name=>"index_taggings_on_tag_id"})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Cannot drop index 'index_taggings_on_tag_id': needed in a foreign key constraint
/app/db/migrate/20210618092706_add_missing_unique_indices.acts_as_taggable_on_engine.rb:11:in `up'
/app/bin/rails:5:in `<top (required)>'
/app/bin/spring:15:in `<top (required)>'
/app/bin/rails:2:in `load'
/app/bin/rails:2:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Cannot drop index 'index_taggings_on_tag_id': needed in a foreign key constraint
/app/db/migrate/20210618092706_add_missing_unique_indices.acts_as_taggable_on_engine.rb:11:in `up'
/app/bin/rails:5:in `<top (required)>'
/app/bin/spring:15:in `<top (required)>'
/app/bin/rails:2:in `load'
/app/bin/rails:2:in `<main>'

Caused by:
Mysql2::Error: Cannot drop index 'index_taggings_on_tag_id': needed in a foreign key constraint
/app/db/migrate/20210618092706_add_missing_unique_indices.acts_as_taggable_on_engine.rb:11:in `up'
/app/bin/rails:5:in `<top (required)>'
/app/bin/spring:15:in `<top (required)>'
/app/bin/rails:2:in `load'
/app/bin/rails:2:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Only the first migration is up. Looks like the auto generated migrations don't play well together.

[yusupss]

Got the same issue here, any solution for this?

[carloseam94]

Can confirm this error in acts-as-taggable-on 7.0, ruby 2.7.1 and rails 6.1.4

[davidlbean]

Found in 9.0.1, rails 7.0.4, ruby 3.1.1p18

[recipedude]

same here, workaround?

bin/rails db:rollback

Commented out line in the 2nd migration as so:

#remove_index ActsAsTaggableOn.taggings_table, :tag_id if index_exists?(ActsAsTaggableOn.taggings_table, :tag_id)

bin/rails db:migration

This allowed the db:migrate to complete. Not sure on implications.

[barnaclebarnes]

Replace up with this and it works.

  def self.up
    add_index ActsAsTaggableOn.tags_table, :name, unique: true

    remove_foreign_key ActsAsTaggableOn.taggings_table, :tags if foreign_key_exists?(ActsAsTaggableOn.taggings_table, :tags)
    remove_index ActsAsTaggableOn.taggings_table, :tag_id if index_exists?(ActsAsTaggableOn.taggings_table, :tag_id)
    remove_index ActsAsTaggableOn.taggings_table, name: 'taggings_taggable_context_idx'
    add_index ActsAsTaggableOn.taggings_table,
              %i[tag_id taggable_id taggable_type context tagger_id tagger_type],
              unique: true, name: 'taggings_idx'
    add_foreign_key ActsAsTaggableOn.taggings_table, ActsAsTaggableOn.tags_table, column: :tag_id
  end