Draft: Fix database default values where missing from MySQL migration (!59749) · Merge requests · GitLab.org / GitLab

Ben Bodenmiller requested to merge fix-db-defaults into master Apr 20, 2021

What does this MR do?

Fix database column default values for long running instances that do not properly have them set due to issues with MySQL to PostgreSQL migration. This is especially true for instances that migrated from MySQL to PostgreSQL with older versions of migration directions (before pgLoader was used e.g. https://gitlab.com/gitlab-org/gitlab/-/blob/8-0-stable-ee/doc/update/mysql_to_postgresql.md).

Defaults to fix grabbed from #285565 (comment 556646671) & #326187 (closed) (except as fixed in !59814 (merged))

No db/structure.sql changes because this MR doesn't change defaults but rather corrects them to align with existing defaults in db/structure.sql.

Testing

Import schema & migrations from database that was migrated from MySQL to PostgreSQL: #326187 (comment 555412753)

# in gdk folder
gdk stop
gdk start postgresql

# in gitlab folder
bundle exec rake db:drop
bundle exec rake db:create

# in gdk folder
gdk psql

# verify empty
\d

# set schema
\i /workspace/gitlab/structure-older-13.5.1.sql

# set migrations
\i /workspace/gitlab/migrations-older-13.5.1.sql

\q

gdk start redis # needed for a migration apparently

# in gitlab folder
# check that some migrations are done and some aren't
bundle exec rake db:migrate:status

# run all migrations except this one
bundle exec rails db:migrate VERSION=20210415144538

# in gdk folder
# verify some incorrect defaults showing
gdk psql
\d users
...

bundle exec rails db:migrate

in/rails db:migrate RAILS_ENV=test
== 20220519181644 FixApplicationSettingsColumnDefault: migrating ==============
-- change_column_default(:application_settings, :polling_interval_multiplier, 1.0)
   -> 0.1191s
== 20220519181644 FixApplicationSettingsColumnDefault: migrated (0.1192s) =====

== 20220519181739 FixBroadcastMessagesColumnDefaults: migrating ===============
-- change_column_default(:broadcast_messages, :color, nil)
   -> 0.0037s
-- change_column_default(:broadcast_messages, :font, nil)
   -> 0.0029s
== 20220519181739 FixBroadcastMessagesColumnDefaults: migrated (0.0067s) ======

== 20220519181800 FixIssuesColumnDefaults: migrating ==========================
-- change_column_default(:issues, :title, nil)
   -> 0.0045s
== 20220519181800 FixIssuesColumnDefaults: migrated (0.0045s) =================

== 20220519182033 FixKeysColumnDefaults: migrating ============================
-- change_column_default(:keys, :title, nil)
   -> 0.0039s
-- change_column_default(:keys, :type, nil)
   -> 0.0039s
-- change_column_default(:keys, :fingerprint, nil)
   -> 0.0032s
== 20220519182033 FixKeysColumnDefaults: migrated (0.0111s) ===================

== 20220519182047 FixLabelLinksColumnDefaults: migrating ======================
-- change_column_default(:label_links, :target_type, nil)
   -> 0.0028s
== 20220519182047 FixLabelLinksColumnDefaults: migrated (0.0028s) =============

== 20220519182100 FixLabelsColumnDefaults: migrating ==========================
-- change_column_default(:labels, :title, nil)
   -> 0.0029s
-- change_column_default(:labels, :color, nil)
   -> 0.0023s
== 20220519182100 FixLabelsColumnDefaults: migrated (0.0053s) =================

== 20220519182116 FixMembersColumnDefault: migrating ==========================
-- change_column_default(:members, :type, nil)
   -> 0.0030s
== 20220519182116 FixMembersColumnDefault: migrated (0.0030s) =================

== 20220519182135 FixMergeRequestDiffsColumnDefaults: migrating ===============
-- change_column_default(:merge_request_diffs, :state, nil)
   -> 0.0022s
== 20220519182135 FixMergeRequestDiffsColumnDefaults: migrated (0.0023s) ======

== 20220519182153 FixMergeRequestColumnDefault: migrating =====================
-- change_column_default(:merge_requests, :title, nil)
   -> 0.0038s
== 20220519182153 FixMergeRequestColumnDefault: migrated (0.0038s) ============

== 20220519182217 FixMilestonesColumnDefault: migrating =======================
-- change_column_default(:milestones, :state, nil)
   -> 0.0022s
== 20220519182217 FixMilestonesColumnDefault: migrated (0.0023s) ==============

== 20220519182242 FixNamespacesColumnDefaults: migrating ======================
-- change_column_default(:namespaces, :type, nil)
   -> 0.0053s
-- change_column_default(:namespaces, :avatar, nil)
   -> 0.0033s
== 20220519182242 FixNamespacesColumnDefaults: migrated (0.0087s) =============

== 20220519182258 FixNotesColumnDefaults: migrating ===========================
-- change_column_default(:notes, :noteable_type, nil)
   -> 0.0029s
-- change_column_default(:notes, :attachment, nil)
   -> 0.0025s
-- change_column_default(:notes, :line_code, nil)
   -> 0.0025s
-- change_column_default(:notes, :commit_id, nil)
   -> 0.0025s
-- change_column_default(:notes, :system, false)
   -> 0.0028s
== 20220519182258 FixNotesColumnDefaults: migrated (0.0134s) ==================

== 20220519182315 FixProjectsColumnDefaults: migrating ========================
-- change_column_default(:projects, :name, nil)
   -> 0.0049s
-- change_column_default(:projects, :path, nil)
   -> 0.0043s
-- change_column_default(:projects, :import_url, nil)
   -> 0.0040s
-- change_column_default(:projects, :archived, false)
   -> 0.0046s
== 20220519182315 FixProjectsColumnDefaults: migrated (0.0178s) ===============

== 20220519182332 FixIntegrationsColumnDefaults: migrating ====================
-- change_column_default(:integrations, :active, false)
   -> 0.0033s
== 20220519182332 FixIntegrationsColumnDefaults: migrated (0.0033s) ===========

== 20220519182349 FixSnippetsColumnDefaults: migrating ========================
-- change_column_default(:snippets, :title, nil)
   -> 0.0026s
-- change_column_default(:snippets, :file_name, nil)
   -> 0.0023s
-- change_column_default(:snippets, :type, nil)
   -> 0.0022s
== 20220519182349 FixSnippetsColumnDefaults: migrated (0.0072s) ===============

== 20220519182407 FixTaggingsColumnDefaults: migrating ========================
-- change_column_default(:taggings, :taggable_type, nil)
   -> 0.0022s
-- change_column_default(:taggings, :tagger_type, nil)
   -> 0.0019s
-- change_column_default(:taggings, :context, nil)
   -> 0.0019s
== 20220519182407 FixTaggingsColumnDefaults: migrated (0.0060s) ===============

== 20220519182423 FixTagsColumnDefault: migrating =============================
-- change_column_default(:tags, :name, nil)
   -> 0.0020s
== 20220519182423 FixTagsColumnDefault: migrated (0.0021s) ====================

== 20220519182438 FixUsersColumnDefaults: migrating ===========================
-- change_column_default(:users, :reset_password_token, nil)
   -> 0.0048s
-- change_column_default(:users, :current_sign_in_ip, nil)
   -> 0.0045s
-- change_column_default(:users, :last_sign_in_ip, nil)
   -> 0.0051s
-- change_column_default(:users, :name, nil)
   -> 0.0050s
-- change_column_default(:users, :admin, false)
   -> 0.0055s
-- change_column_default(:users, :username, nil)
   -> 0.0048s
-- change_column_default(:users, :can_create_group, true)
   -> 0.0053s
-- change_column_default(:users, :can_create_team, true)
   -> 0.0052s
-- change_column_default(:users, :state, nil)
   -> 0.0048s
-- change_column_default(:users, :avatar, nil)
   -> 0.0048s
-- change_column_default(:users, :confirmation_token, nil)
   -> 0.0046s
-- change_column_default(:users, :unconfirmed_email, nil)
   -> 0.0048s
-- change_column_default(:users, :hide_no_ssh_key, false)
   -> 0.0051s
== 20220519182438 FixUsersColumnDefaults: migrated (0.0649s) ==================

== 20220519181644 FixApplicationSettingsColumnDefault: migrating ==============
-- change_column_default(:application_settings, :polling_interval_multiplier, 1.0)
   -> 0.1012s
== 20220519181644 FixApplicationSettingsColumnDefault: migrated (0.1012s) =====

== 20220519181739 FixBroadcastMessagesColumnDefaults: migrating ===============
-- change_column_default(:broadcast_messages, :color, nil)
   -> 0.0025s
-- change_column_default(:broadcast_messages, :font, nil)
   -> 0.0021s
== 20220519181739 FixBroadcastMessagesColumnDefaults: migrated (0.0047s) ======

== 20220519181800 FixIssuesColumnDefaults: migrating ==========================
-- change_column_default(:issues, :title, nil)
   -> 0.0034s
== 20220519181800 FixIssuesColumnDefaults: migrated (0.0034s) =================

== 20220519182033 FixKeysColumnDefaults: migrating ============================
-- change_column_default(:keys, :title, nil)
   -> 0.0024s
-- change_column_default(:keys, :type, nil)
   -> 0.0023s
-- change_column_default(:keys, :fingerprint, nil)
   -> 0.0023s
== 20220519182033 FixKeysColumnDefaults: migrated (0.0071s) ===================

== 20220519182047 FixLabelLinksColumnDefaults: migrating ======================
-- change_column_default(:label_links, :target_type, nil)
   -> 0.0021s
== 20220519182047 FixLabelLinksColumnDefaults: migrated (0.0021s) =============

== 20220519182100 FixLabelsColumnDefaults: migrating ==========================
-- change_column_default(:labels, :title, nil)
   -> 0.0025s
-- change_column_default(:labels, :color, nil)
   -> 0.0022s
== 20220519182100 FixLabelsColumnDefaults: migrated (0.0047s) =================

== 20220519182116 FixMembersColumnDefault: migrating ==========================
-- change_column_default(:members, :type, nil)
   -> 0.0026s
== 20220519182116 FixMembersColumnDefault: migrated (0.0027s) =================

== 20220519182135 FixMergeRequestDiffsColumnDefaults: migrating ===============
-- change_column_default(:merge_request_diffs, :state, nil)
   -> 0.0024s
== 20220519182135 FixMergeRequestDiffsColumnDefaults: migrated (0.0024s) ======

== 20220519182153 FixMergeRequestColumnDefault: migrating =====================
-- change_column_default(:merge_requests, :title, nil)
   -> 0.0031s
== 20220519182153 FixMergeRequestColumnDefault: migrated (0.0032s) ============

== 20220519182217 FixMilestonesColumnDefault: migrating =======================
-- change_column_default(:milestones, :state, nil)
   -> 0.0023s
== 20220519182217 FixMilestonesColumnDefault: migrated (0.0024s) ==============

== 20220519182242 FixNamespacesColumnDefaults: migrating ======================
-- change_column_default(:namespaces, :type, nil)
   -> 0.0040s
-- change_column_default(:namespaces, :avatar, nil)
   -> 0.0040s
== 20220519182242 FixNamespacesColumnDefaults: migrated (0.0081s) =============

== 20220519182258 FixNotesColumnDefaults: migrating ===========================
-- change_column_default(:notes, :noteable_type, nil)
   -> 0.0029s
-- change_column_default(:notes, :attachment, nil)
   -> 0.0026s
-- change_column_default(:notes, :line_code, nil)
   -> 0.0027s
-- change_column_default(:notes, :commit_id, nil)
   -> 0.0027s
-- change_column_default(:notes, :system, false)
   -> 0.0030s
== 20220519182258 FixNotesColumnDefaults: migrated (0.0141s) ==================

== 20220519182315 FixProjectsColumnDefaults: migrating ========================
-- change_column_default(:projects, :name, nil)
   -> 0.0057s
-- change_column_default(:projects, :path, nil)
   -> 0.0053s
-- change_column_default(:projects, :import_url, nil)
   -> 0.0052s
-- change_column_default(:projects, :archived, false)
   -> 0.0053s
== 20220519182315 FixProjectsColumnDefaults: migrated (0.0217s) ===============

== 20220519182332 FixIntegrationsColumnDefaults: migrating ====================
-- change_column_default(:integrations, :active, false)
   -> 0.0033s
== 20220519182332 FixIntegrationsColumnDefaults: migrated (0.0034s) ===========

== 20220519182349 FixSnippetsColumnDefaults: migrating ========================
-- change_column_default(:snippets, :title, nil)
   -> 0.0027s
-- change_column_default(:snippets, :file_name, nil)
   -> 0.0024s
-- change_column_default(:snippets, :type, nil)
   -> 0.0023s
== 20220519182349 FixSnippetsColumnDefaults: migrated (0.0075s) ===============

== 20220519182407 FixTaggingsColumnDefaults: migrating ========================
-- change_column_default(:taggings, :taggable_type, nil)
   -> 0.0023s
-- change_column_default(:taggings, :tagger_type, nil)
   -> 0.0056s
-- change_column_default(:taggings, :context, nil)
   -> 0.0022s
== 20220519182407 FixTaggingsColumnDefaults: migrated (0.0101s) ===============

== 20220519182423 FixTagsColumnDefault: migrating =============================
-- change_column_default(:tags, :name, nil)
   -> 0.0028s
== 20220519182423 FixTagsColumnDefault: migrated (0.0029s) ====================

== 20220519182438 FixUsersColumnDefaults: migrating ===========================
-- change_column_default(:users, :reset_password_token, nil)
   -> 0.0059s
-- change_column_default(:users, :current_sign_in_ip, nil)
   -> 0.0051s
-- change_column_default(:users, :last_sign_in_ip, nil)
   -> 0.0047s
-- change_column_default(:users, :name, nil)
   -> 0.0050s
-- change_column_default(:users, :admin, false)
   -> 0.0053s
-- change_column_default(:users, :username, nil)
   -> 0.0049s
-- change_column_default(:users, :can_create_group, true)
   -> 0.0052s
-- change_column_default(:users, :can_create_team, true)
   -> 0.0049s
-- change_column_default(:users, :state, nil)
   -> 0.0045s
-- change_column_default(:users, :avatar, nil)
   -> 0.0044s
-- change_column_default(:users, :confirmation_token, nil)
   -> 0.0045s
-- change_column_default(:users, :unconfirmed_email, nil)
   -> 0.0043s
-- change_column_default(:users, :hide_no_ssh_key, false)
   -> 0.0049s
== 20220519182438 FixUsersColumnDefaults: migrated (0.0640s) ==================

Verify defaults correctly changed

# in gdk folder
gdk psql
\d users
...

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because _____.
Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited Jun 09, 2022 by Ben Bodenmiller

Draft: Fix database default values where missing from MySQL migration