Skip to content
GitLab Next
  • Menu
Projects Groups Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in / Register
  • GitLab FOSS GitLab FOSS
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
    • Locked Files
  • Issues 0
    • Issues 0
    • List
    • Boards
    • Service Desk
    • Milestones
    • Iterations
    • Requirements
  • Merge requests 1
    • Merge requests 1
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages & Registries
    • Packages & Registries
    • Package Registry
    • Container Registry
    • Infrastructure Registry
  • Monitor
    • Monitor
    • Metrics
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • Code review
    • Insights
    • Issue
    • Repository
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Commits
  • Issue Boards
Collapse sidebar
  • GitLab.orgGitLab.org
  • GitLab FOSSGitLab FOSS
  • Merge requests
  • !16141

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

  • Review changes

  • Download
  • Email patches
  • Plain diff
Merged Stan Hu requested to merge sh-fix-mysql-migration-10-3 into master Dec 27, 2017
  • Overview 13
  • Commits 3
  • Pipelines 6
  • Changes 3

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 Dec 27, 2017 by Stan Hu
Assignee
Assign to
Reviewer
Request review from
Time tracking
Source branch: sh-fix-mysql-migration-10-3