Remove orphaned merge request approvers from the database

My first crack at a migration to remove bad data and finally close https://gitlab.com/gitlab-org/gitlab-ee/issues/1

@ayufan noted that this query may lock the approvers table for too long. He suggests we loop through and do sets of 100 until it's all done. However, I can't make this work in a sane way - mostly due to my own lack of experience here. Rails migration gurus, please help me out and suggest how we can do this.

cc/ @rspeicher @yorickpeterse @DouweM

Milestone changed to %8.10

100 seems to low. I would go with 1k or 10k. Deletion should quite fast.

@dblessing

until exec_delete("DELETE FROM approvers WHERE id IN (SELECT approvers.id FROM approvers LEFT JOIN users ON users.id=approvers.user_id WHERE users.id IS NULL LIMIT 1000)") == 0
end

Milestone changed to %8.11

Reassigned to @dblessing

Added 1 commit:

• 7d98c195 - Remove orphaned merge request approvers from the database

@yorickpeterse Does this look OK?

Reassigned to @DouweM

Unmarked this merge request as a Work In Progress

Added 3011 commits:

• 36a00a14...cae1f705 - 3011 commits from branch gitlab-org:master

Stupid MySQL adapter

@dblessing You need an extra sub-query for this to work in MySQL. So something like this if I'm not mistaken:

DELETE FROM approvers WHERE id IN (SELECT id FROM (SELECT approvers.id ...) AS sub_query)

Other than that I think this should be fine.

@yorickpeterse I don't follow, sorry. Please give an example of the sub-query.

Added 1 commit:

• 326ae551 - Remove orphaned merge request approvers from the database

Reassigned to @dblessing

@dblessing For the sub-query, in the past we have used queries such as:

DELETE FROM notification_settings
WHERE user_id = #{uid}
...
AND id != (
  SELECT id FROM (
    SELECT min(id) AS id
    FROM notification_settings
    WHERE user_id = #{uid}
    AND source_type = #{stype}
    AND source_id = #{sid}
  ) min_ids
)

Note the sub-select (SELECT id FROM ( ... ) min_ids) in the AND id != ( ... ) chunk.

Milestone changed to %8.12

@dblessing do we still need this? If so and you'd like someone else to pick this up, let me know!

Added 1 commit:

• 5d64b590 - Fix conflicts

Compare with previous version

Added 6036 commits:

• 326ae551...e09ca533 - 6036 commits from branch gitlab-org:master

Compare with previous version

Mentioned in commit 3597ed8d

closed