Fix migration for removing orphaned issues.moved_to_id values in MySQL

According to https://dev.mysql.com/doc/refman/5.7/en/update.html,
"You cannot update a table and select from the same table in a subquery."
Attempting to do so results in the error:

```
Mysql2::Error: Table 'issues' is specified twice, both as a target for 'UPDATE' and as a separate source for data
```

Instead, we can use a LEFT JOIN on the same table to make MySQL do the right
thing.

Closes #41498

3 files changed, 53 insertions, 2 deletions

diff --git a/changelogs/unreleased/sh-fix-mysql-migration-10-3.yml b/changelogs/unreleased/sh-fix-mysql-migration-10-3.yml
new file mode 100644
index 00000000000..ac150d2f8ab
--- /dev/null
+++ b/changelogs/unreleased/sh-fix-mysql-migration-10-3.yml
@@ -0,0 +1,5 @@
+---
+title: Fix migration for removing orphaned issues.moved_to_id values in MySQL
+merge_request:
+author:
+type: fixed
diff --git a/db/migrate/20171106151218_issues_moved_to_id_foreign_key.rb b/db/migrate/20171106151218_issues_moved_to_id_foreign_key.rb
index 8d2ceb8cc18..18c2b294c1e 100644
--- a/db/migrate/20171106151218_issues_moved_to_id_foreign_key.rb
+++ b/db/migrate/20171106151218_issues_moved_to_id_foreign_key.rb
@@ -21,8 +21,10 @@ class IssuesMovedToIdForeignKey < ActiveRecord::Migration
   end
 
   def up
-    Issue.with_orphaned_moved_to_issues.each_batch(of: 100) do |batch|
-      batch.update_all(moved_to_id: nil)
+    if Gitlab::Database.postgresql?
+      postgresql_remove_orphaned_moved_to_ids
+    else
+      mysql_remove_orphaned_moved_to_ids
     end
 
     add_concurrent_foreign_key(
@@ -41,4 +43,23 @@ class IssuesMovedToIdForeignKey < ActiveRecord::Migration
     remove_foreign_key_without_error(:issues, column: :moved_to_id)
     remove_concurrent_index(:issues, :moved_to_id)
   end
+
+  private
+
+  def postgresql_remove_orphaned_moved_to_ids
+    Issue.with_orphaned_moved_to_issues.each_batch(of: 100) do |batch|
+      batch.update_all(moved_to_id: nil)
+    end
+  end
+
+  # MySQL doesn't allow modification of the same table in a subquery. See
+  # https://dev.mysql.com/doc/refman/5.7/en/update.html for more details.
+  def mysql_remove_orphaned_moved_to_ids
+    execute <<~SQL
+      UPDATE issues AS a
+      LEFT JOIN issues AS b ON a.moved_to_id = b.id
+      SET a.moved_to_id = NULL
+      WHERE a.moved_to_id IS NOT NULL AND b.id IS NULL;
+    SQL
+  end
 end
diff --git a/spec/migrations/issues_moved_to_id_foreign_key_spec.rb b/spec/migrations/issues_moved_to_id_foreign_key_spec.rb
new file mode 100644
index 00000000000..009207ef214
--- /dev/null
+++ b/spec/migrations/issues_moved_to_id_foreign_key_spec.rb
@@ -0,0 +1,25 @@
+require 'spec_helper'
+require Rails.root.join('db', 'migrate', '20171106151218_issues_moved_to_id_foreign_key.rb')
+
+# The schema version has to be far enough in advance to have the
+# only_mirror_protected_branches column in the projects table to create a
+# project via FactoryBot.
+describe IssuesMovedToIdForeignKey, :migration, schema: 20171109115718 do
+  let!(:issue_first) { create(:issue, moved_to_id: issue_second.id) }
+  let!(:issue_second) { create(:issue, moved_to_id: issue_third.id) }
+  let!(:issue_third) { create(:issue) }
+
+  subject { described_class.new }
+
+  it 'removes the orphaned moved_to_id' do
+    subject.down
+
+    issue_third.update_attributes(moved_to_id: 100000)
+
+    subject.up
+
+    expect(issue_first.reload.moved_to_id).to eq(issue_second.id)
+    expect(issue_second.reload.moved_to_id).to eq(issue_third.id)
+    expect(issue_third.reload.moved_to_id).to be_nil
+  end
+end