This is an automated email from the ASF dual-hosted git repository.
mitchell852 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/trafficcontrol.git
The following commit(s) were added to refs/heads/master by this push:
new 1183254 `db/admin migrate`: Migrate first migration explicitly if DB
version's migration is squashed (#6096)
1183254 is described below
commit 1183254381164bcd6534db7c266ca8cd65bf4b6d
Author: Zach Hoffman <[email protected]>
AuthorDate: Mon Aug 16 11:23:24 2021 -0600
`db/admin migrate`: Migrate first migration explicitly if DB version's
migration is squashed (#6096)
* Only fetch the migration version once
* If the migration version is the version of the last squashed migration,
temporarily set the migration version to 0
* Remove duplicate word
* Move Apache License 2.0 to the top
* migration version -> migration timestamp
* Migrate the first expected migration instead of temporarily forcing the
version to 0
* Add a script to squash migrations that existed before the most recent ATC
release
* Make squashing script use Migrate-style migrations
---
docs/source/development/traffic_ops.rst | 2 +-
traffic_ops/app/db/SQUASH.md | 51 ++++++++++++++++++++++++++++
traffic_ops/app/db/admin.go | 39 +++++++++++----------
traffic_ops/app/db/squash_migrations.sh | 60 +++++++++++++++++++++++++++++++++
traffic_ops/build/traffic_ops.spec | 2 ++
5 files changed, 136 insertions(+), 18 deletions(-)
diff --git a/docs/source/development/traffic_ops.rst
b/docs/source/development/traffic_ops.rst
index a276f8c..51174f8 100644
--- a/docs/source/development/traffic_ops.rst
+++ b/docs/source/development/traffic_ops.rst
@@ -259,7 +259,7 @@ If you encounter an error running a migration, you will see
a message like
[root@trafficops app]# db/admin -env production migrate
Error running migrate up: migration failed: syntax error at or near
"This_is_a_syntax_error" (column 1) in line 18: /*
-That means that the migration version in the ``version`` column of the
``schema_migrations`` table has been updated to the version of the migration
that failed, but the ``dirty`` column is also set, and if you try to run
another migration (either up or down), you will see
+That means that the migration timestamp in the ``version`` column of the
``schema_migrations`` table has been updated to the version of the migration
that failed, but the ``dirty`` column is also set, and if you try to run
another migration (either up or down), you will see
.. code-block:: bash
:caption: db/admin error migrating when the database version is dirty
diff --git a/traffic_ops/app/db/SQUASH.md b/traffic_ops/app/db/SQUASH.md
new file mode 100644
index 0000000..b1801f1
--- /dev/null
+++ b/traffic_ops/app/db/SQUASH.md
@@ -0,0 +1,51 @@
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# Squashing database migrations
+
+For convenience,
[`squash_migrations.sh`](https://github.com/apache/trafficcontrol/blob/master/traffic_ops/app/db/squash_migrations.sh)
script squashes the migrations, but whoever PRs the result is responsible for
verifying that the migrations are squashed and `LastSquashedMigrationVersion`
in
[`db/admin.go`](https://github.com/apache/trafficcontrol/blob/master/traffic_ops/app/db/admin.go)
is updated correctly, regardless of the result of having run the script.
+
+--------
+
+Each major release of Apache Traffic Control combines database migrations from
previous ATC releases into
[`create_tables.sql`](https://github.com/apache/trafficcontrol/blob/master/traffic_ops/app/db/create_tables.sql).
+
+For example, suppose the latest version of Apache Traffic Control is 147.5.8
and contains these migrations:
+* `1_my-migration.up.sql`
+* `1_my-migration.down.sql`
+* `3_another-migration.up.sql`
+* `3_another-migration.down.sql`
+
+And suppose the ATC
[`master`](https://github.com/apache/trafficcontrol/commits/master) branch
contains these migrations:
+* `1_my-migration.up.sql`
+* `1_my-migration.down.sql`
+* `3_another-migration.up.sql`
+* `3_another-migration.down.sql`
+* `4_migration-name.up.sql`
+* `4_migration-name.down.sql`
+* `9_add-column-to-table.up.sql`
+* `9_add-column-to-table.down.sql`
+
+1. In order to prepare database migrations for the next major release, in this
case, ATC 148.0.0, migrations `1` and `3` should be collapsed into
`create_tables.sql` and migrations `4` and `9` should remain in
[`traffic_ops/app/db/migrations/`](https://github.com/apache/trafficcontrol/tree/master/traffic_ops/app/db/migrations/).
+
+2. * Note that `3` is the migration timestamp of the last up/down migration
set. Find the definition for `LastSquashedMigrationTimestamp` in
[`traffic_ops/app/db/admin.go`](https://github.com/apache/trafficcontrol/blob/master/traffic_ops/app/db/admin.go)
and change it to `3`.
+ * After migrations from ATC 147.5.8 have been collapsed, the first
migration version will be `4`. Find the definition for
`FirstMigrationTimestamp` in
[`traffic_ops/app/db/admin.go`](https://github.com/apache/trafficcontrol/blob/master/traffic_ops/app/db/admin.go)
and change it to `4`.
+
+Past PRs that have collapsed the DB migrations:
+- https://github.com/apache/trafficcontrol/pull/6065
+- https://github.com/apache/trafficcontrol/pull/3524
diff --git a/traffic_ops/app/db/admin.go b/traffic_ops/app/db/admin.go
index df53b38..ce2a883 100644
--- a/traffic_ops/app/db/admin.go
+++ b/traffic_ops/app/db/admin.go
@@ -94,8 +94,8 @@ const (
CmdUp = "up"
CmdDown = "down"
CmdRedo = "redo"
- // Deprecated: Migrate only tracks migration version and dirty status,
not a status for each migration.
- // Use CmdDBVersion to check the migration version and dirty status.
+ // Deprecated: Migrate only tracks migration timestamp and dirty
status, not a status for each migration.
+ // Use CmdDBVersion to check the migration timestamp and dirty status.
CmdStatus = "status"
CmdDBVersion = "dbversion"
CmdSeed = "seed"
@@ -116,12 +116,17 @@ const (
TrafficVaultMigrationsSource = "file:" + TrafficVaultDir + "migrations"
TrafficVaultDir = dbDir + "trafficvault/"
TrafficVaultSchemaPath = TrafficVaultDir + "create_tables.sql"
+
+ LastSquashedMigrationTimestamp uint = 2021012200000000 //
2021012200000000_max_request_header_bytes_default_zero.sql
+ FirstMigrationTimestamp uint = 2021012700000000 //
2021012700000000_update_interfaces_multiple_routers.up.sql
)
var (
// globals that are passed in via CLI flags and used in commands
- Environment string
- TrafficVault bool
+ Environment string
+ TrafficVault bool
+ DBVersion uint
+ DBVersionDirty bool
// globals that are parsed out of DBConfigFile and used in commands
DBDriver string
@@ -314,7 +319,8 @@ func upgrade() {
}
func maybeMigrateFromGoose() bool {
- _, _, versionErr := Migrate.Version()
+ var versionErr error
+ DBVersion, DBVersionDirty, versionErr = Migrate.Version()
if versionErr == nil {
return false
}
@@ -329,15 +335,18 @@ func maybeMigrateFromGoose() bool {
func runMigrations() {
migratedFromGoose := initMigrate()
- upErr := Migrate.Up()
- if upErr == migrate.ErrNoChange {
+ if !TrafficVault && DBVersion == LastSquashedMigrationTimestamp &&
!DBVersionDirty {
+ if migrateErr := Migrate.Migrate(FirstMigrationTimestamp);
migrateErr != nil {
+ die(fmt.Sprintf("Error migrating from DB version %d to
%d: %s", LastSquashedMigrationTimestamp, FirstMigrationTimestamp,
migrateErr.Error()))
+ }
+ }
+ if upErr := Migrate.Up(); upErr == migrate.ErrNoChange {
if !migratedFromGoose {
println(upErr.Error())
}
} else if upErr != nil {
die("Error running migrate up: " + upErr.Error())
}
-
}
func runUp() {
@@ -361,19 +370,15 @@ func redo() {
}
}
-// Deprecated: Migrate does not track migration status of past migrations. Use
dbversion() to check the migration version and dirty status.
+// Deprecated: Migrate does not track migration status of past migrations. Use
dbversion() to check the migration timestamp and dirty status.
func status() {
dbVersion()
}
func dbVersion() {
initMigrate()
- version, dirty, err := Migrate.Version()
- if err != nil {
- die("Error running migrate version: " + err.Error())
- }
- fmt.Printf("dbversion %d", version)
- if dirty {
+ fmt.Printf("dbversion %d", DBVersion)
+ if DBVersionDirty {
fmt.Printf(" (dirty)")
}
println()
@@ -496,7 +501,7 @@ create_migration NAME
- Creates a pair of timestamped up/down migrations titled NAME.
create_user - Execute 'create_user' the user for the current environment
(traffic_ops).
-dbversion - Prints the current migration version
+dbversion - Prints the current migration timestamp
drop_user - Execute 'drop_user' the user for the current environment
(traffic_ops).
patch - Execute sql from db/patches.sql for loading post-migration data
@@ -507,7 +512,7 @@ reset - Execute db 'dropdb', 'createdb', load_schema,
migrate on the
seed - Execute sql from db/seeds.sql for loading static data (NOTE: not
supported with --trafficvault option).
show_users - Execute sql to show all of the user for the current environment.
-status - Prints the current migration version (Deprecated, status is now
an
+status - Prints the current migration timestamp (Deprecated, status is
now an
alias for dbversion and will be removed in a future Traffic
Control release).
upgrade - Execute migrate, seed, and patches on the database for the
current
diff --git a/traffic_ops/app/db/squash_migrations.sh
b/traffic_ops/app/db/squash_migrations.sh
new file mode 100755
index 0000000..2fbc274
--- /dev/null
+++ b/traffic_ops/app/db/squash_migrations.sh
@@ -0,0 +1,60 @@
+#!/usr/bin/env bash
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+trap 'echo "Error on line ${LINENO} of ${0}" >/dev/stderr; exit 1' ERR
+set -o errexit -o nounset -o pipefail
+cd "$(dirname "$0")"
+
+apache_remote="$(git remote -v | grep 'apache/trafficcontrol.*(fetch)$' | cut
-f1 | head -n1)"
+git fetch --tags --force "$apache_remote"
+last_release="$(git tag --list --sort=v:refname | grep -E
'^RELEASE-[0-9]+[.][0-9]+[.][0-9]+$' | tail -n1)"
+migrations_to_squash="$(git ls-tree -r "$last_release" -- migrations | grep -o
'migrations/[0-9].*')"
+
+cp create_tables.sql to_squash.sql
+echo "$migrations_to_squash" | grep '\.up\.sql$' | xargs cat >>to_squash.sql
+last_squashed_migration="$(<<<"$migrations_to_squash" tail -n1)"
+last_squashed_migration_timestamp="$(<<<"$last_squashed_migration" sed -E
's|migrations/([0-9]+).*|\1|')"
+first_migration="$(ls migrations/*.sql | grep -A1
"/${last_squashed_migration_timestamp}_" | tail -n1)"
+first_migration_timestamp="$(<<<"$first_migration" sed -E
's|migrations/([0-9]+).*|\1|')"
+sed -i '/^--/,$d' create_tables.sql # keeps the Apache License 2.0 header
+sed -Ei "s|(LastSquashedMigrationTimestamp\s+uint\s+=
).*|\1${last_squashed_migration_timestamp} // ${last_squashed_migration}|"
admin.go
+sed -Ei "s|(FirstMigrationTimestamp\s+uint\s+=
).*|\1${first_migration_timestamp} // ${first_migration}|" admin.go
+
+dump_db_with_migrations() {
+ trap 'echo "Error on line ${LINENO} of dump_db_with_migrations"
>/dev/stderr; exit 1' ERR
+ set -o errexit -o nounset
+ {
+ docker-entrypoint.sh postgres &
+ sleep 10
+ psql -f to_squash.sql
+ } >/dev/stderr
+ pg_dump
+}
+docker run --rm -iw/db \
+ -v "$(pwd):/db" \
+ -e PGUSER=traffic_ops \
+ -e PGPASSWORD=twelve \
+ -e POSTGRES_USER=traffic_ops \
+ -e POSTGRES_PASSWORD=twelve \
+ postgres:13-alpine bash -c "$(type dump_db_with_migrations | tail -n+2);
dump_db_with_migrations" >>create_tables.sql
+rm to_squash.sql
+
+git add create_tables.sql
+git commit -m "Redump create_tables.sql with migrations through timestamp
${last_squashed_migration_timestamp}"
+
+echo "$migrations_to_squash" | xargs git rm
+git commit -m "Remove migrations that existed at ${last_release}"
+
+git add -p admin.go
+git commit -m 'Update LastSquashedMigrationTimestamp and
FirstMigrationTimestamp'
+
+echo 'Migrations squashed successfully!'
diff --git a/traffic_ops/build/traffic_ops.spec
b/traffic_ops/build/traffic_ops.spec
index 2f75a25..fc427ba 100644
--- a/traffic_ops/build/traffic_ops.spec
+++ b/traffic_ops/build/traffic_ops.spec
@@ -236,6 +236,8 @@ fi
%config(noreplace) %attr(750,%{TRAFFIC_OPS_USER},%{TRAFFIC_OPS_GROUP})
/opt/traffic_ops/app/db/dbconf.yml
%config(noreplace)/var/www/files/osversions.json
%attr(755, %{TRAFFIC_OPS_USER},%{TRAFFIC_OPS_GROUP}) %{PACKAGEDIR}/app/db/admin
+%exclude %{PACKAGEDIR}/app/db/SQUASH.md
+%exclude %{PACKAGEDIR}/app/db/squash_migrations.sh
%attr(755, %{TRAFFIC_OPS_USER},%{TRAFFIC_OPS_GROUP})
%{PACKAGEDIR}/install/bin/convert_profile/convert_profile
%attr(755, %{TRAFFIC_OPS_USER},%{TRAFFIC_OPS_GROUP})
%{PACKAGEDIR}/app/db/reencrypt/reencrypt
%attr(755, %{TRAFFIC_OPS_USER},%{TRAFFIC_OPS_GROUP})
%{PACKAGEDIR}/app/db/traffic_vault_migrate/traffic_vault_migrate
