CI migrations fail due to duplicate CI projects

Excerpt from Slack:

Kamil Trzciński [14:53]
@yorickpeterse: this is bad, this is gitlab.com database?

Yorick Peterse [14:53]
This is my local copy of gitlab.com of October

[14:54]
Hm, I have 8 projects which appear to be duplicate locally

Kamil Trzciński [14:55]
added a Plain Text snippet
```
# gitlabhq_production=# select gitlab_id, count(*) as cnt from ci_projects group by gitlab_id having COUNT(*)>1;
 gitlab_id | cnt
-----------+-----
     10306 |   2
Add Comment Click to expand inline 38 lines
Kamil Trzciński [14:56]
then, which is good? :simple_smile:(edited)

Yorick Peterse [14:57]
Did we already run these migrations on production?

[14:57]
 ```SELECT projects.id AS project_id, concat(namespaces.path, '/', projects.path) AS full_path, count(projects.id)
FROM ci_projects
LEFT JOIN projects   ON projects.id = ci_projects.gitlab_id
LEFT JOIN namespaces ON namespaces.id = projects.namespace_id
GROUP BY projects.id, full_path
HAVING count(projects.id) > 1;
```

[14:57]
Dumps them with their path and all that

Kamil Trzciński [14:57]
@yorickpeterse: not yet

Yorick Peterse [14:57]
Ah

Kamil Trzciński [14:58]
@yorickpeterse: what is this query for?

Yorick Peterse [14:58]
Spits out the projects with their namespace/project path that have double CI projects

[14:58]
at least if I wrote it correctly, lemme check

Kamil Trzciński [14:59]
it will not tell us which ci_project we should use - maybe we should select the latest created?

Yorick Peterse [14:59]
What if that one is not correct?

[15:00]
How can you even get multiple CI projects?

Kamil Trzciński [15:00]
We don’t know

[15:00]
concurrently run ensure_ci_project without the uniqueness on gitlab_id

Yorick Peterse [15:00]
Ah, we validate uniqueness in Rails?(edited)

[15:01]
(that would explain since it's terrible at handling race conditions without a proper DB index)

[15:01]
 ```gitlabhq_production=# select id, name, created_at, updated_at from ci_projects where gitlab_id = 533963;
  id   | name |         created_at         |         updated_at
-------+------+----------------------------+----------------------------
 11575 |      | 2015-10-16 18:40:54.436914 | 2015-10-16 18:40:54.436914
 11576 |      | 2015-10-16 18:40:54.534179 | 2015-10-16 18:40:54.534179
(2 rows)
```

[15:01]
Hm, seems to be the case

Kamil Trzciński [15:01]
We had: validates_uniqueness_of :gitlab_id, but not unique in database(edited)

Yorick Peterse [15:02]
There should at least be an index on the DB, `validates_uniqueness_of` doesn't take care of race conditions

Kamil Trzciński [15:04]
Yeap, but we don’t use ci_projects anymore

[15:04]
We need to fix it somehow

[15:04]
Any ideas?

Yorick Peterse [15:05]
Can we perhaps base it on the last build run for a ci_project ?

Kamil Trzciński [15:06]
builds are assigned to project already

Yorick Peterse [15:06]
Yes, so we could pick the project that doesn't have builds

[15:06]
and if neither have any pick the newest one

[15:07]
unless the builds are assigned randomly between the two

[15:07]
I'll make an issue

Kamil Trzciński [15:08]
@yorickpeterse: builds are assigned to project, not ci_projects

Yorick Peterse [15:08]
oh, hm

Kamil Trzciński [15:09]
to ci_projects are assigned: triggers, variables, services and events

@ayufan What would be the worst scenario if we happen to simply pick the newest ci_project row? Would this mean users could potentially lose CI related data?

@yorickpeterse

Yes, user could potentially lose Secure Variables.

On gitlab.com we have 32 projects that are broken: 2 to 6 ci_projects assigned.

Basically there's no good solution. I would simply fix that by adding LIMIT 1 and ORDER id DESC.

In case a project has multiple ci_project rows, how does the application determine what ci_project to assign secret variables to?

@yorickpeterse I think that this is purely random. It depends on what PostgreSQL returns.

@ayufan In that case we'd probably need a migration that does the following before migrating the ci_projects table:

Get all projects that are duplicate
For every project, move all data to a single ci_projects row (variables, builds, whatever)
Nuke the ci_projects row that's now without data
Proceed with the other migrations, which should now run fine as any doubles have been removed

@yorickpeterse

It doesn't make sense.

The builds are already assigned to GitLab project.
The variables, webhooks, triggers and services will auto-heal: we set ci_variables.gl_project_id to ci_projects.gitlab_id
The only lost data are things stored in ci_projects table: e-mail notification, build timeout, git fetch or clone, coverage regex. If we order by updated_at we should get the latest

@ayufan Ah ok, yes in that case I think we can just go with the newest row in ci_projects.

mentioned in issue #3977 (closed)

mentioned in merge request !2133 (merged)

mentioned in commit 110a5f9c

Added ~19173 label

It looks like fix has been already merged, I'm closing this issue.

Status changed to closed

mentioned in commit tnir/gitlab-ce@110a5f9c

Mentioned in commit pfjason/gitlab-ce@110a5f9c

mentioned in commit test-nick/gitlab-ce@df955beb

CI migrations fail due to duplicate CI projects

Designs

Child items ...

Activity

Admin message

Admin message

CI migrations fail due to duplicate CI projects

Activity