diff options
author | Grzegorz Bizon <grzegorz@gitlab.com> | 2017-12-27 16:14:36 +0000 |
---|---|---|
committer | Oswaldo Ferreira <oswaldo@gitlab.com> | 2017-12-27 21:37:21 +0000 |
commit | 9580ddbc1883ab53c86e8ccd67b5d994d06400da (patch) | |
tree | 496c02159f56ec3da3addcf82dbebf45499e3048 | |
parent | 63aec1bfa582d85b6d623a2c295d05d99bd0788a (diff) | |
download | gitlab-ce-9580ddbc1883ab53c86e8ccd67b5d994d06400da.tar.gz |
Merge branch 'sh-fix-mysql-migration-10-3' into 'master'
Fix migration for removing orphaned issues.moved_to_id values in MySQL and PostgreSQL
Closes #41498
See merge request gitlab-org/gitlab-ce!16141
(cherry picked from commit ff3e9af6fdac37ae71ea526bbb9aaf4b7d2ce876)
5cacdc4e Fix migration for removing orphaned issues.moved_to_id values in MySQL
dfdf22c7 Fix PostgreSQL implementation of migration
e97671b8 Simplify implementation and allow for batch updates in MySQL
3 files changed, 44 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..d3d1d2f8256 --- /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 and PostgreSQL +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..6395462384b 100644 --- a/db/migrate/20171106151218_issues_moved_to_id_foreign_key.rb +++ b/db/migrate/20171106151218_issues_moved_to_id_foreign_key.rb @@ -15,8 +15,20 @@ class IssuesMovedToIdForeignKey < ActiveRecord::Migration self.table_name = 'issues' def self.with_orphaned_moved_to_issues - where('NOT EXISTS (SELECT true FROM issues WHERE issues.id = issues.moved_to_id)') - .where('moved_to_id IS NOT NULL') + if Gitlab::Database.postgresql? + # Be careful to use a second table here for comparison otherwise we'll null + # out all rows that don't have id == moved.to_id! + where('NOT EXISTS (SELECT true FROM issues B WHERE issues.moved_to_id = B.id)') + .where('moved_to_id IS NOT NULL') + else + # MySQL doesn't allow modification of the same table in a subquery, + # and using a temporary table isn't automatically guaranteed to work + # due to the MySQL query optimizer. See + # https://dev.mysql.com/doc/refman/5.7/en/update.html for more + # details. + joins('LEFT JOIN issues AS b ON issues.moved_to_id = b.id') + .where('issues.moved_to_id IS NOT NULL AND b.id IS NULL') + end 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..d2eef81f396 --- /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: 20171114150259 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 |