From 23b58e54680e5a73a2e81960114504e6a8a161cf Mon Sep 17 00:00:00 2001 From: Greg Stark Date: Tue, 19 Sep 2017 22:47:04 +0100 Subject: Remove an unneeded index on ci_builds left in some databases Migration 20170919211300_remove_temporary_ci_builds_index.rb created a temporary partial index and tried to drop it at the end of the migration. In some circumstances apparently it failed to drop the index and it ended up in our schema.rb. This accidentally failed to fail due to a bug in the regular expression for partial indexes which caused the index creation in schema.rb to be ignored. Now that that's fixed we could be resurrecting this zombie index from the past in some but not all databases. Add a migration to drop this index if it's present to reconcile this discrepancy. --- ...70919211300_remove_temporary_ci_builds_index.rb | 27 ++++++++++++++++++++++ db/schema.rb | 1 - 2 files changed, 27 insertions(+), 1 deletion(-) create mode 100644 db/migrate/20170919211300_remove_temporary_ci_builds_index.rb (limited to 'db') diff --git a/db/migrate/20170919211300_remove_temporary_ci_builds_index.rb b/db/migrate/20170919211300_remove_temporary_ci_builds_index.rb new file mode 100644 index 00000000000..b2009b282e9 --- /dev/null +++ b/db/migrate/20170919211300_remove_temporary_ci_builds_index.rb @@ -0,0 +1,27 @@ +# See http://doc.gitlab.com/ce/development/migration_style_guide.html +# for more information on how to write migrations for GitLab. + +class RemoveTemporaryCiBuildsIndex < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + # Set this constant to true if this migration requires downtime. + DOWNTIME = false + + # To use create/remove index concurrently + disable_ddl_transaction! + + def up + return unless index_exists?(:ci_builds, :id, name: 'index_for_ci_builds_retried_migration') + remove_concurrent_index(:ci_builds, :id, name: "index_for_ci_builds_retried_migration") + end + + def down + # this was a temporary index for a migration that was never + # present previously so this probably shouldn't be here but it's + # easier to test the drop if we have a way to create it. + add_concurrent_index("ci_builds", ["id"], + name: "index_for_ci_builds_retried_migration", + where: "(retried IS NULL)", + using: :btree) + end +end diff --git a/db/schema.rb b/db/schema.rb index 80ef91ec95d..330336e8e61 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -256,7 +256,6 @@ ActiveRecord::Schema.define(version: 20170921115009) do add_index "ci_builds", ["commit_id", "status", "type"], name: "index_ci_builds_on_commit_id_and_status_and_type", using: :btree add_index "ci_builds", ["commit_id", "type", "name", "ref"], name: "index_ci_builds_on_commit_id_and_type_and_name_and_ref", using: :btree add_index "ci_builds", ["commit_id", "type", "ref"], name: "index_ci_builds_on_commit_id_and_type_and_ref", using: :btree - add_index "ci_builds", ["id"], name: "index_for_ci_builds_retried_migration", where: "(retried IS NULL)", using: :btree add_index "ci_builds", ["project_id"], name: "index_ci_builds_on_project_id", using: :btree add_index "ci_builds", ["protected"], name: "index_ci_builds_on_protected", using: :btree add_index "ci_builds", ["runner_id"], name: "index_ci_builds_on_runner_id", using: :btree -- cgit v1.2.1 From abcfdd5c17020abbcb3f39eaa976cb49a0917a2a Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Thu, 28 Sep 2017 12:09:08 +0200 Subject: Add index for merge_requests.merge_commit_sha This is a composite index on (target_project_id, merge_commit_sha, id) that allows queries such as the following to use a full backwards index scan: SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."deleted_at" IS NULL AND "merge_requests"."target_project_id" = 13083 AND "merge_requests"."merge_commit_sha" = 'e80a893ff0ea8466099f6478183631af55933db2' ORDER BY "merge_requests"."id" DESC LIMIT 1; Fixes https://gitlab.com/gitlab-org/gitlab-ce/issues/38507 --- ...ite_index_on_merge_requests_merge_commit_sha.rb | 33 ++++++++++++++++++++++ db/schema.rb | 3 +- 2 files changed, 35 insertions(+), 1 deletion(-) create mode 100644 db/migrate/20170928100231_add_composite_index_on_merge_requests_merge_commit_sha.rb (limited to 'db') diff --git a/db/migrate/20170928100231_add_composite_index_on_merge_requests_merge_commit_sha.rb b/db/migrate/20170928100231_add_composite_index_on_merge_requests_merge_commit_sha.rb new file mode 100644 index 00000000000..9f02daf04c1 --- /dev/null +++ b/db/migrate/20170928100231_add_composite_index_on_merge_requests_merge_commit_sha.rb @@ -0,0 +1,33 @@ +# See http://doc.gitlab.com/ce/development/migration_style_guide.html +# for more information on how to write migrations for GitLab. + +class AddCompositeIndexOnMergeRequestsMergeCommitSha < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + # Set this constant to true if this migration requires downtime. + DOWNTIME = false + + # The default index name is too long for PostgreSQL and would thus be + # truncated. + INDEX_NAME = 'index_merge_requests_on_tp_id_and_merge_commit_sha_and_id' + + COLUMNS = [:target_project_id, :merge_commit_sha, :id] + + disable_ddl_transaction! + + def up + return if index_is_present? + + add_concurrent_index(:merge_requests, COLUMNS, name: INDEX_NAME) + end + + def down + return unless index_is_present? + + remove_concurrent_index(:merge_requests, COLUMNS, name: INDEX_NAME) + end + + def index_is_present? + index_exists?(:merge_requests, COLUMNS, name: INDEX_NAME) + end +end diff --git a/db/schema.rb b/db/schema.rb index 330336e8e61..e6200cfccd6 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -11,7 +11,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 20170921115009) do +ActiveRecord::Schema.define(version: 20170928100231) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -895,6 +895,7 @@ ActiveRecord::Schema.define(version: 20170921115009) do add_index "merge_requests", ["source_project_id", "source_branch"], name: "index_merge_requests_on_source_project_id_and_source_branch", using: :btree add_index "merge_requests", ["target_branch"], name: "index_merge_requests_on_target_branch", using: :btree add_index "merge_requests", ["target_project_id", "iid"], name: "index_merge_requests_on_target_project_id_and_iid", unique: true, using: :btree + add_index "merge_requests", ["target_project_id", "merge_commit_sha", "id"], name: "index_merge_requests_on_tp_id_and_merge_commit_sha_and_id", using: :btree add_index "merge_requests", ["title"], name: "index_merge_requests_on_title", using: :btree add_index "merge_requests", ["title"], name: "index_merge_requests_on_title_trigram", using: :gin, opclasses: {"title"=>"gin_trgm_ops"} -- cgit v1.2.1 From 38607b48b689b39721ff0cf7355ba2a176f4bf5e Mon Sep 17 00:00:00 2001 From: Gabriel Mazetto Date: Wed, 6 Sep 2017 04:24:31 +0200 Subject: [Backported from EE] Readonly flag for Projects This is used in EE for the storage migration, and we want to use this in CE as well to be able to migrate projects to hashed_storage. --- .../20160713200638_add_repository_read_only_to_projects.rb | 9 +++++++++ db/schema.rb | 1 + 2 files changed, 10 insertions(+) create mode 100644 db/migrate/20160713200638_add_repository_read_only_to_projects.rb (limited to 'db') diff --git a/db/migrate/20160713200638_add_repository_read_only_to_projects.rb b/db/migrate/20160713200638_add_repository_read_only_to_projects.rb new file mode 100644 index 00000000000..8ee8b55f210 --- /dev/null +++ b/db/migrate/20160713200638_add_repository_read_only_to_projects.rb @@ -0,0 +1,9 @@ +class AddRepositoryReadOnlyToProjects < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + def change + add_column :projects, :repository_read_only, :boolean + end +end diff --git a/db/schema.rb b/db/schema.rb index 330336e8e61..e2acca25c5e 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -1215,6 +1215,7 @@ ActiveRecord::Schema.define(version: 20170921115009) do t.datetime "last_repository_updated_at" t.integer "storage_version", limit: 2 t.boolean "resolve_outdated_diff_discussions" + t.boolean "repository_read_only" end add_index "projects", ["ci_id"], name: "index_projects_on_ci_id", using: :btree -- cgit v1.2.1 From e9eae3eb0dd25e4a34c9a4b6bcc7de312dde4489 Mon Sep 17 00:00:00 2001 From: Markus Koller Date: Thu, 28 Sep 2017 16:49:42 +0000 Subject: Support custom attributes on users --- .../20170720122741_create_user_custom_attributes.rb | 17 +++++++++++++++++ db/schema.rb | 12 ++++++++++++ 2 files changed, 29 insertions(+) create mode 100644 db/migrate/20170720122741_create_user_custom_attributes.rb (limited to 'db') diff --git a/db/migrate/20170720122741_create_user_custom_attributes.rb b/db/migrate/20170720122741_create_user_custom_attributes.rb new file mode 100644 index 00000000000..b1c0bebc633 --- /dev/null +++ b/db/migrate/20170720122741_create_user_custom_attributes.rb @@ -0,0 +1,17 @@ +class CreateUserCustomAttributes < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + def change + create_table :user_custom_attributes do |t| + t.timestamps_with_timezone null: false + t.references :user, null: false, foreign_key: { on_delete: :cascade } + t.string :key, null: false + t.string :value, null: false + + t.index [:user_id, :key], unique: true + t.index [:key, :value] + end + end +end diff --git a/db/schema.rb b/db/schema.rb index 330336e8e61..63c8b8b3f49 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -1534,6 +1534,17 @@ ActiveRecord::Schema.define(version: 20170921115009) do add_index "user_agent_details", ["subject_id", "subject_type"], name: "index_user_agent_details_on_subject_id_and_subject_type", using: :btree + create_table "user_custom_attributes", force: :cascade do |t| + t.datetime "created_at", null: false + t.datetime "updated_at", null: false + t.integer "user_id", null: false + t.string "key", null: false + t.string "value", null: false + end + + add_index "user_custom_attributes", ["key", "value"], name: "index_user_custom_attributes_on_key_and_value", using: :btree + add_index "user_custom_attributes", ["user_id", "key"], name: "index_user_custom_attributes_on_user_id_and_key", unique: true, using: :btree + create_table "user_synced_attributes_metadata", force: :cascade do |t| t.boolean "name_synced", default: false t.boolean "email_synced", default: false @@ -1760,6 +1771,7 @@ ActiveRecord::Schema.define(version: 20170921115009) do add_foreign_key "todos", "projects", name: "fk_45054f9c45", on_delete: :cascade add_foreign_key "trending_projects", "projects", on_delete: :cascade add_foreign_key "u2f_registrations", "users" + add_foreign_key "user_custom_attributes", "users", on_delete: :cascade add_foreign_key "user_synced_attributes_metadata", "users", on_delete: :cascade add_foreign_key "users_star_projects", "projects", name: "fk_22cd27ddfc", on_delete: :cascade add_foreign_key "web_hook_logs", "web_hooks", on_delete: :cascade -- cgit v1.2.1 From 472be7fe611b726c065cc384259b75bb894ce19b Mon Sep 17 00:00:00 2001 From: Sean McGivern Date: Tue, 26 Sep 2017 15:26:26 +0100 Subject: Reschedule merge request diff background migration The first attempt didn't migrate all rows on GitLab.com, due to a couple of issues: 1. Some rows in merge_request_diffs had truly huge numbers of commits and diffs serialised - one in particular had 26,000 commits! 2. The jobs were sometimes on Sidekiq hosts with frequent OOM errors, leading to the job being lost. The previous commit adds more logging, and a more robust insertion method. This commit reschedules the jobs, with a generous pause between each. --- ...edule_merge_request_diff_migrations_take_two.rb | 32 ++++++++++++++++++++++ 1 file changed, 32 insertions(+) create mode 100644 db/post_migrate/20170926150348_schedule_merge_request_diff_migrations_take_two.rb (limited to 'db') diff --git a/db/post_migrate/20170926150348_schedule_merge_request_diff_migrations_take_two.rb b/db/post_migrate/20170926150348_schedule_merge_request_diff_migrations_take_two.rb new file mode 100644 index 00000000000..5732cb85ea5 --- /dev/null +++ b/db/post_migrate/20170926150348_schedule_merge_request_diff_migrations_take_two.rb @@ -0,0 +1,32 @@ +class ScheduleMergeRequestDiffMigrationsTakeTwo < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + BATCH_SIZE = 500 + MIGRATION = 'DeserializeMergeRequestDiffsAndCommits' + DELAY_INTERVAL = 10.minutes + + disable_ddl_transaction! + + class MergeRequestDiff < ActiveRecord::Base + self.table_name = 'merge_request_diffs' + + include ::EachBatch + + default_scope { where('st_commits IS NOT NULL OR st_diffs IS NOT NULL') } + end + + # By this point, we assume ScheduleMergeRequestDiffMigrations - the first + # version of this - has already run. On GitLab.com, we have ~220k un-migrated + # rows, but these rows will, in general, take a long time. + # + # With a gap of 10 minutes per batch, and 500 rows per batch, these migrations + # are scheduled over 220_000 / 500 / 6 ~= 74 hours, which is a little over + # three days. + def up + queue_background_migration_jobs_by_range_at_intervals(MergeRequestDiff, MIGRATION, DELAY_INTERVAL, batch_size: BATCH_SIZE) + end + + def down + end +end -- cgit v1.2.1 From 8bac6e41ef1568b5b46a92a2279b4fb488ae73bc Mon Sep 17 00:00:00 2001 From: "Lin Jen-Shin (godfat)" Date: Fri, 29 Sep 2017 11:18:58 +0000 Subject: Fix notes type created from import --- ...927112318_update_legacy_diff_notes_type_for_import.rb | 16 ++++++++++++++++ .../20170927112319_update_notes_type_for_import.rb | 16 ++++++++++++++++ 2 files changed, 32 insertions(+) create mode 100644 db/post_migrate/20170927112318_update_legacy_diff_notes_type_for_import.rb create mode 100644 db/post_migrate/20170927112319_update_notes_type_for_import.rb (limited to 'db') diff --git a/db/post_migrate/20170927112318_update_legacy_diff_notes_type_for_import.rb b/db/post_migrate/20170927112318_update_legacy_diff_notes_type_for_import.rb new file mode 100644 index 00000000000..a238216253b --- /dev/null +++ b/db/post_migrate/20170927112318_update_legacy_diff_notes_type_for_import.rb @@ -0,0 +1,16 @@ +class UpdateLegacyDiffNotesTypeForImport < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + disable_ddl_transaction! + + def up + update_column_in_batches(:notes, :type, 'LegacyDiffNote') do |table, query| + query.where(table[:type].eq('Github::Import::LegacyDiffNote')) + end + end + + def down + end +end diff --git a/db/post_migrate/20170927112319_update_notes_type_for_import.rb b/db/post_migrate/20170927112319_update_notes_type_for_import.rb new file mode 100644 index 00000000000..1e70acd9868 --- /dev/null +++ b/db/post_migrate/20170927112319_update_notes_type_for_import.rb @@ -0,0 +1,16 @@ +class UpdateNotesTypeForImport < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + disable_ddl_transaction! + + def up + update_column_in_batches(:notes, :type, 'Note') do |table, query| + query.where(table[:type].eq('Github::Import::Note')) + end + end + + def down + end +end -- cgit v1.2.1