This is an automated email from the ASF dual-hosted git repository. ocket8888 pushed a commit to branch 5.1.x in repository https://gitbox.apache.org/repos/asf/trafficcontrol.git
commit 363b9824c4167c223473e8ea465a63db17514988 Author: Hank Beatty <[email protected]> AuthorDate: Thu Feb 11 18:40:28 2021 -0500 Update to DB migration server_id_primary_key.sql (#5509) I was unable to run the dbadmin upgrade because this update was trying to add 2 primary keys (one for each schema). I encountered this while trying to upgrade from 4.1.1 to 5.0.0. Co-authored-by: Hank Beatty <[email protected]> (cherry picked from commit 46b4fd19b449823a7c76dd4bf2fa7b743a2cbde2) --- CHANGELOG.md | 1 + .../app/db/migrations/2020082700000000_server_id_primary_key.sql | 8 ++++---- 2 files changed, 5 insertions(+), 4 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 6524f04..9d6b40e 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -44,6 +44,7 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/). - [#5339](https://github.com/apache/trafficcontrol/issues/5339) - Ensure Changelog entries for SSL key changes - [#5461](https://github.com/apache/trafficcontrol/issues/5461) - Fixed steering endpoint to be ordered consistently - [#5395](https://github.com/apache/trafficcontrol/issues/5395) - Added validation to prevent changing the Type any Cache Group that is in use by a Topology +- Fixed an issue with 2020082700000000_server_id_primary_key.sql trying to create multiple primary keys when there are multiple schemas. ### Changed - Refactored the Traffic Ops Go client internals so that all public methods have a consistent behavior/implementation diff --git a/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql b/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql index 3e4ed77..7581a11 100644 --- a/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql +++ b/traffic_ops/app/db/migrations/2020082700000000_server_id_primary_key.sql @@ -18,9 +18,9 @@ DECLARE r record; BEGIN FOR r IN (SELECT indexname FROM pg_indexes WHERE tablename = 'server' AND indexname LIKE '%primary%') LOOP - EXECUTE 'ALTER TABLE server DROP CONSTRAINT '|| quote_ident(r.indexname) || ';'; - EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id);'; + EXECUTE 'ALTER TABLE server DROP CONSTRAINT IF EXISTS '|| quote_ident(r.indexname) || ';'; END LOOP; + EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id);'; END $$ LANGUAGE plpgsql; -- +goose StatementEnd @@ -33,9 +33,9 @@ DECLARE r record; BEGIN FOR r IN (SELECT indexname FROM pg_indexes WHERE tablename = 'server' AND indexname LIKE '%primary%') LOOP - EXECUTE 'ALTER TABLE server DROP CONSTRAINT '|| quote_ident(r.indexname) || ';'; - EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id, cachegroup, type, status, profile);'; + EXECUTE 'ALTER TABLE server DROP CONSTRAINT IF EXISTS '|| quote_ident(r.indexname) || ';'; END LOOP; + EXECUTE 'ALTER TABLE ONLY server ADD CONSTRAINT '|| quote_ident(r.indexname) || ' PRIMARY KEY (id, cachegroup, type, status, profile);'; END $$ LANGUAGE plpgsql; -- +goose StatementEnd
