When adding foreign key on geo_event_log.hashed_storage_attachments_event_id first remove missing rows
When we tried to deploy the changes from https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/7990 to production, we ran into an issue: https://gitlab.com/gitlab-org/gitlab-ee/issues/8301
10.218.3.2 PG::ForeignKeyViolation: ERROR: insert or update on table "geo_event_log" violates foreign key constraint "fk_304067fc30"
10.218.3.2 DETAIL: Key (hashed_storage_attachments_event_id)=(210) is not present in table "geo_hashed_storage_attachments_events".
10.218.3.2 /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:204:in `block in add_concurrent_foreign_key'
10.218.3.2 /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:254:in `disable_statement_timeout'
10.218.3.2 /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:203:in `add_concurrent_foreign_key'
10.218.3.2 /opt/gitlab/embedded/service/gitlab-rails/ee/db/migrate/20181017131623_add_missing_geo_even_log_indexes.rb:41:in `block in up'
10.218.3.2 /opt/gitlab/embedded/service/gitlab-rails/ee/db/migrate/20181017131623_add_missing_geo_even_log_indexes.rb:40:in `each'
10.218.3.2 /opt/gitlab/embedded/service/gitlab-rails/ee/db/migrate/20181017131623_add_missing_geo_even_log_indexes.rb:40:in `up'
Cause
There were Geo events for which the row in
geo_hashed_storage_attachments_events
was deleted. Probably because
the related project was deleted, and thus the event was deleted by a
FK constraint https://gitlab.com/gitlab-org/gitlab-ee/blob/a5f8eab907471b0d7d737728a300576c53424cae/db/schema.rb#L3290.
But the FK constraint between geo_hashed_storage_attachments_events
and geo_event_log
was missing. So the row in geo_event_log
wasn't
deleted.
I've found 5 rows, which were pointing to a non-existing geo_hashed_storage_attachments_events
row:
[ gprd ] production> Geo::EventLog.where.not(hashed_storage_attachments_event_id: nil).joins('LEFT OUTER JOIN geo_hashed_storage_attachments_events ON geo_hashed_storage_attachments_events.id = hashed_storage_attachments_event_id') .where('geo_hashed_storage_attachments_events.id IS NULL').pluck(:id)
=> [80109616, 80118503, 80142602, 80148223, 80151857]
I've deleted those by hand, but this should have been done by the migration itself.
Proposed fix
Remove orphaned rows, before adding the FK constraint.