Rename instead of delete, feedback:

https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/12363#note_33449374

2 files changed, 35 insertions, 45 deletions

diff --git a/db/migrate/20170622135451_remove_duplicated_variable.rb b/db/migrate/20170622135451_remove_duplicated_variable.rb
deleted file mode 100644
index bd3aa3f5323..00000000000
--- a/db/migrate/20170622135451_remove_duplicated_variable.rb
+++ /dev/null
@@ -1,45 +0,0 @@
-class RemoveDuplicatedVariable < ActiveRecord::Migration
-  include Gitlab::Database::MigrationHelpers
-
-  DOWNTIME = false
-
-  disable_ddl_transaction!
-
-  def up
-    if Gitlab::Database.postgresql?
-      execute <<~SQL
-        DELETE FROM ci_variables var USING (#{duplicated_ids}) dup
-          #{join_conditions}
-      SQL
-    else
-      execute <<~SQL
-        DELETE var FROM ci_variables var INNER JOIN (#{duplicated_ids}) dup
-          #{join_conditions}
-      SQL
-    end
-  end
-
-  def down
-    # noop
-  end
-
-  def duplicated_ids
-    <<~SQL
-      SELECT MAX(id) AS id, #{key}, project_id
-        FROM ci_variables GROUP BY #{key}, project_id
-    SQL
-  end
-
-  def join_conditions
-    <<~SQL
-      WHERE var.key = dup.key
-        AND var.project_id = dup.project_id
-        AND var.id <> dup.id
-    SQL
-  end
-
-  def key
-    # key needs to be quoted in MySQL
-    quote_column_name('key')
-  end
-end
diff --git a/db/migrate/20170622135451_rename_duplicated_variable_key.rb b/db/migrate/20170622135451_rename_duplicated_variable_key.rb
new file mode 100644
index 00000000000..b88e7d7ba81
--- /dev/null
+++ b/db/migrate/20170622135451_rename_duplicated_variable_key.rb
@@ -0,0 +1,35 @@
+class RenameDuplicatedVariableKey < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    execute(<<~SQL)
+      UPDATE ci_variables SET #{key} = CONCAT(#{key}, #{underscore}, id)
+        WHERE id IN (
+          SELECT * FROM ( -- MySQL requires an extra layer
+            SELECT dup.id FROM ci_variables dup
+              INNER JOIN (SELECT max(id) AS id, #{key}, project_id
+                            FROM ci_variables tmp
+                            GROUP BY #{key}, project_id) var
+                USING (#{key}, project_id) where dup.id <> var.id
+          ) dummy
+        )
+    SQL
+  end
+
+  def down
+    # noop
+  end
+
+  def key
+    # key needs to be quoted in MySQL
+    quote_column_name('key')
+  end
+
+  def underscore
+    quote('_')
+  end
+end