Swap commit_user_mentions.note_id for self-managed instances

What does this MR do and why?

This MR swaps thecommit_user_mentions.note_id and commit_user_mentions.note_id_convert_to_bigint columns for self-managed instances. This was already done for GitLab.com with !117343 (merged).

The process is outlined here.

The timeline for GitLab.com was the following:

• 15.9 !111261 (merged)
• 15.11 !115277 (merged)
• 16.1 !123108 (merged)

Since we decided to commit schema changes to structure.sql for the above MR's, we now need to consider the state of the schema from which the self-managed instance was created.

We have the following cases:

• Instances created from 15.8 and prior – swap the columns
• Instances created from 15.9, 15.10 – swap the columns
• Instances created from 15.11 – do not swap as the columns are already swapped
• Instances created from 16.1 – do not swap as the columns are already swapped, and the cleanup is done (no more temp column and trigger)

Related to #417402 (closed)

How to set up and validate locally

I have done the following manual testing:

1. Create a new instance from each of 15.8.0, 15.9.0, 15.11, and 16.1.0
2. Upgrade all instances to 16.1.2
3. Execute the migrations from this MR
4. Verify migrations execute successfully and commit_user_mentions table has the expected schema.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

assigned to @l.rosa

mentioned in issue #417402 (closed)

@l.rosa - please see the following guidance and update this merge request.

	1 Error
	Please add typebug typefeature, or typemaintenance label to this merge request.

added 1 commit

• 0540ff60 - Convert commit_user_mentions.note_id to bigint for self-managed

Compare with previous version

added typemaintenance label

changed milestone to %16.3

added database databasereview pending labels

	2 Warnings
	New migrations added but db/structure.sql wasn't updated Usually, when adding new migrations, db/structure.sql should be updated too (unless the migration isn't changing the DB schema and isn't the most recent one).
	Please add a merge request subtype to this merge request.

Reviewer roulette

Changes that require review have been detected!

Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

Category	Reviewer	Maintainer
database	Mohamed Hamda (@mhamda) (UTC+2, 5 hours ahead of @l.rosa)	Adam Hegyi (@ahegyi) (UTC+2, 5 hours ahead of @l.rosa)
~"migration"	No reviewer available	No maintainer available

To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

changed the description

added groupdatabase label

added devopsdata stores sectioncore platform labels

added priority1 label

added severity3 label

marked this merge request as ready

Database migrations (on the main database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20230811145848 - EnsureCommitUserMentionsNoteIdBigintBackfillIsFinishedForSelfManaged	Post deploy	1.7 s		+0.00 B
20230811150636 - SwapCommitUserMentionsNoteIdToBigintForSelfManaged	Post deploy	1.7 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	4
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230811145848 - EnsureCommitUserMentionsNoteIdBigintBackfillIsFinishedForSelfManaged

• Type: Post deploy
• Duration: 1.7 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for EnsureCommitUserMentionsNoteIdBigintBackfillIsFinishedForSelfManaged

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230811150636 - SwapCommitUserMentionsNoteIdToBigintForSelfManaged

• Type: Post deploy
• Duration: 1.7 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for SwapCommitUserMentionsNoteIdToBigintForSelfManaged

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20230728171609 - AddCiJobAnnotationsPlanLimits	Regular	2.5 s		+0.00 B
20230701053315 - EnsureAgainBackfillForCiPipelineVariablesPipelineIdIsFinished	Post deploy	2.1 s		+0.00 B
20230712052619 - DropIndexDeploymentsOnProjectIdAndStatus	Post deploy	3.0 s		+0.00 B
20230712054057 - DropIndexDeploymentsOnProjectIdSha	Post deploy	2.9 s		+0.00 B
20230712055956 - DropIndexDeploymentsOnEnvironmentIdAndIidAndProjectId	Post deploy	2.8 s		+0.00 B
20230726024322 - AddNotValidForeignKeyForCiPipelineVariablesPipelineId	Post deploy	3.6 s		+8.00 KiB [note]
20230727102936 - DropPreparedAtIndex	Post deploy	3.6 s		+8.00 KiB [note]
20230727103144 - AddPreparedAtCreatedAtIndex	Post deploy	3.4 s		+0.00 B
20230727132342 - PrepareIndexOnVulnerabilityOccurrencesUuidAsync	Post deploy	2.4 s		+0.00 B
20230728122928 - PrepareIndexOnVulnerabilityOccurrencesUuidIncludingVulnerabilityIdAsync	Post deploy	1.9 s		+0.00 B
20230804121704 - RemoveNamespacesUserDetailsEnterpriseGroupIdFk	Post deploy	2.3 s		+0.00 B
20230804121705 - RemoveNamespacesUserDetailsProvisionedByGroupIdFk	Post deploy	2.2 s		+0.00 B
20230809090349 - EnsureIdUniquenessForPCiBuildsV2	Post deploy	2.1 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2206529-10877814-main	2023-08-11T16:30:02Z	2023-08-11T12:16:15Z	2023-08-12 04:36:10 +0000
database-testing-2206529-10877814-ci	2023-08-11T16:30:03Z	2023-08-11T12:45:16Z	2023-08-12 04:36:10 +0000

Job artifacts

Database migrations (on the ci database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20230811145848 - EnsureCommitUserMentionsNoteIdBigintBackfillIsFinishedForSelfManaged	Post deploy	2.6 s		+0.00 B
20230811150636 - SwapCommitUserMentionsNoteIdToBigintForSelfManaged	Post deploy	2.6 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	4
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230811145848 - EnsureCommitUserMentionsNoteIdBigintBackfillIsFinishedForSelfManaged

• Type: Post deploy
• Duration: 2.6 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for EnsureCommitUserMentionsNoteIdBigintBackfillIsFinishedForSelfManaged

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230811150636 - SwapCommitUserMentionsNoteIdToBigintForSelfManaged

• Type: Post deploy
• Duration: 2.6 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for SwapCommitUserMentionsNoteIdToBigintForSelfManaged

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20230728171609 - AddCiJobAnnotationsPlanLimits	Regular	3.4 s		+0.00 B
20230701053315 - EnsureAgainBackfillForCiPipelineVariablesPipelineIdIsFinished	Post deploy	2.9 s		+0.00 B
20230712052619 - DropIndexDeploymentsOnProjectIdAndStatus	Post deploy	5.6 s		+0.00 B
20230712054057 - DropIndexDeploymentsOnProjectIdSha	Post deploy	3.8 s		+0.00 B
20230712055956 - DropIndexDeploymentsOnEnvironmentIdAndIidAndProjectId	Post deploy	3.7 s		+0.00 B
20230726024322 - AddNotValidForeignKeyForCiPipelineVariablesPipelineId	Post deploy	5.0 s		+8.00 KiB [note]
20230727102936 - DropPreparedAtIndex	Post deploy	4.5 s		+8.00 KiB [note]
20230727103144 - AddPreparedAtCreatedAtIndex	Post deploy	4.3 s		+0.00 B
20230727132342 - PrepareIndexOnVulnerabilityOccurrencesUuidAsync	Post deploy	3.2 s		+0.00 B
20230728122928 - PrepareIndexOnVulnerabilityOccurrencesUuidIncludingVulnerabilityIdAsync	Post deploy	2.8 s		+0.00 B
20230804121704 - RemoveNamespacesUserDetailsEnterpriseGroupIdFk	Post deploy	3.2 s		+0.00 B
20230804121705 - RemoveNamespacesUserDetailsProvisionedByGroupIdFk	Post deploy	3.2 s		+0.00 B
20230809090349 - EnsureIdUniquenessForPCiBuildsV2	Post deploy	3.5 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2206529-10877814-main	2023-08-11T16:30:02Z	2023-08-11T12:16:15Z	2023-08-12 04:36:10 +0000
database-testing-2206529-10877814-ci	2023-08-11T16:30:03Z	2023-08-11T12:45:16Z	2023-08-12 04:36:10 +0000

Job artifacts

---

Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

added database-testing-automation label

Allure report

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 0986396e

expand test summary

+-----------------------------------------------------------------------+
|                            suites summary                             |
+------------------+--------+--------+---------+-------+-------+--------+
|                  | passed | failed | skipped | flaky | total | result |
+------------------+--------+--------+---------+-------+-------+--------+
| Govern           | 36     | 0      | 0       | 0     | 36    | ✅     |
| Create           | 46     | 0      | 1       | 2     | 47    | ❗     |
| Plan             | 51     | 0      | 0       | 0     | 51    | ✅     |
| Data Stores      | 22     | 0      | 0       | 0     | 22    | ✅     |
| Manage           | 13     | 0      | 1       | 1     | 14    | ❗     |
| Framework sanity | 0      | 0      | 1       | 0     | 1     | ➖     |
| Package          | 0      | 0      | 1       | 0     | 1     | ➖     |
| Monitor          | 4      | 0      | 0       | 0     | 4     | ✅     |
| Verify           | 8      | 0      | 0       | 0     | 8     | ✅     |
+------------------+--------+--------+---------+-------+-------+--------+
| Total            | 180    | 0      | 4       | 3     | 184   | ❗     |
+------------------+--------+--------+---------+-------+-------+--------+

@mhamda can I have your initial database review on this one? Can you assign to @dfrazao-gitlab once you're done?

requested review from @mhamda

changed the description

Setting label(s) Category:Database based on groupdatabase.

added Category:Database label

approved this merge request

@mhamda, thanks for approving this merge request.

This is the first time the merge request is approved. To ensure full test coverage, a new pipeline will be started shortly.

For more info, please refer to the following links:

• rspec predictive jobs
• jest predictive jobs
• merging a merge request.

added pipeline:mr-approved label

added databasereviewed label and removed databasereview pending label

requested review from @dfrazao-gitlab and removed review request for @mhamda

added 504 commits

• 0540ff60...ccdd0f49 - 503 commits from branch master
• 0986396e - Convert commit_user_mentions.note_id to bigint for self-managed

Compare with previous version

approved this merge request

added databaseapproved label and removed databasereviewed label

resolved all threads

enabled an automatic merge when the pipeline for f72727f8 succeeds

MWPS set

merged

Hello @l.rosa

The database team is looking for ways to improve the database review process and we would love your help!

If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:

@gitlab-org/database-team

And someone will be by shortly!

Thanks for your help!

This message was generated automatically. You're welcome to improve it.

mentioned in commit 48bf9365

added workflowstaging-canary label

added workflowcanary label and removed workflowstaging-canary label

added workflowstaging label and removed workflowcanary label

added workflowproduction label and removed workflowstaging label

added workflowpost-deploy-db-staging label and removed workflowproduction label

added workflowpost-deploy-db-production label and removed workflowpost-deploy-db-staging label

added releasedcandidate label

added releasedpublished label and removed releasedcandidate label

mentioned in merge request kubitus-project/kubitus-installer!2364 (merged)

added groupdatabase frameworks label and removed groupdatabase [DEPRECATED] label