Skip to content

Fix migration for removing orphaned issues.moved_to_id values in MySQL and PostgreSQL

Stan Hu requested to merge sh-fix-mysql-migration-10-3 into master

The PostgreSQL implementation was broken in that it wiped out all moved_to_id entries. For PostgreSQL, notice the subtle differences:

Incorrect:

gitlabhq_test=# SELECT id, moved_to_id 
FROM   issues 
WHERE  NOT EXISTS (SELECT moved_to_id 
                   FROM   issues 
                   WHERE  issues.id = issues.moved_to_id) 
       AND moved_to_id IS NOT NULL; 
 id | moved_to_id
----+-------------
  2 |           1
  3 |           2
  1 |      100000
(3 rows)

Incorrect:

gitlabhq_test=# SELECT id, moved_to_id 
FROM   issues A 
WHERE  NOT EXISTS (SELECT moved_to_id 
                   FROM   issues B 
                   WHERE  A.id = B.moved_to_id) 
       AND ( moved_to_id IS NOT NULL );                                                                              
 id | moved_to_id
----+-------------
  3 |           2
(1 row)

Correct:

gitlabhq_test=# SELECT id, moved_to_id 
FROM   issues A 
WHERE  NOT EXISTS (SELECT moved_to_id 
                   FROM   issues B 
                   WHERE  A.moved_to_id = B.id) 
       AND ( moved_to_id IS NOT NULL );                                                                            
 id | moved_to_id
----+-------------
  1 |      100000
(1 row)

For 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 (closed)

Edited by Stan Hu

Merge request reports

Loading