Fix zero-downtime upgrade from 13.8.x to 13.9.3 (!56352) · Merge requests · GitLab.org / GitLab

Douglas Barbosa Alexandre requested to merge 324160-geo-zero-downtime-upgrades-step-gitlab-rake-db-migrate-fails-in-13-9-3 into master Mar 11, 2021

What does this MR do?

Perform the cleanup on the asset_proxy_whitelist column rename on the application_settings table made in !50824 (merged) before reverting it in !55419 (merged).

This MR has been tested upgrading a Geo deployment from 13.8.5 to 13.9.3 (including these changes) following the zero downtime docs, and it fixes the error mentioned in #324160 (closed).

Migrations

== 20210301200601 RenameAssetProxyAllowlistOnApplicationSettings: migrating ===
-- column_exists?(:application_settings, :asset_proxy_whitelist)
   -> 0.1240s
-- quote_table_name(:application_settings)
   -> 0.0000s
-- execute("DROP TRIGGER IF EXISTS trigger_9a96291bb38d\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_9a96291bb38d;\n")
   -> 0.0011s
-- execute("DROP TRIGGER IF EXISTS trigger_22a39c5c25f3\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_22a39c5c25f3;\n")
   -> 0.0006s
-- execute("DROP TRIGGER IF EXISTS trigger_1572cbc9a15f\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_1572cbc9a15f;\n")
   -> 0.0005s
-- remove_column(:application_settings, :asset_proxy_whitelist)
   -> 0.0008s
-- transaction_open?()
   -> 0.0000s
-- columns(:application_settings)
   -> 0.1229s
-- column_exists?(:application_settings, :id)
   -> 0.1225s
-- column_exists?(:application_settings, :asset_proxy_whitelist)
   -> 0.1213s
-- columns(:application_settings)
   -> 0.1384s
-- add_column(:application_settings, :asset_proxy_whitelist, :text, {:limit=>nil, :precision=>nil, :scale=>nil})
   -> 0.0024s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"application_settings\"")
   -> 0.0008s
-- exec_query("SELECT \"application_settings\".\"id\" FROM \"application_settings\" ORDER BY \"application_settings\".\"id\" ASC LIMIT 1")
   -> 0.0005s
-- exec_query("SELECT \"application_settings\".\"id\" FROM \"application_settings\" WHERE \"application_settings\".\"id\" >= 1 ORDER BY \"application_settings\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0005s
-- execute("UPDATE \"application_settings\" SET \"asset_proxy_whitelist\" = \"application_settings\".\"asset_proxy_allowlist\" WHERE \"application_settings\".\"id\" >= 1")
   -> 0.0022s
-- indexes(:application_settings)
   -> 0.0075s
-- foreign_keys(:application_settings)
   -> 0.0061s
-- transaction_open?()
   -> 0.0000s
-- column_exists?(:application_settings, :asset_proxy_allowlist)
   -> 0.1233s
-- column_exists?(:application_settings, :asset_proxy_whitelist)
   -> 0.1224s
-- current_schema()
   -> 0.0003s
-- quote_table_name(:application_settings)
   -> 0.0000s
-- quote_column_name(:asset_proxy_allowlist)
   -> 0.0000s
-- quote_column_name(:asset_proxy_whitelist)
   -> 0.0000s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_e40a6f1858e6()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  IF NEW.\"asset_proxy_allowlist\" IS NULL AND NEW.\"asset_proxy_whitelist\" IS NOT NULL THEN\n    NEW.\"asset_proxy_allowlist\" = NEW.\"asset_proxy_whitelist\";\n  END IF;\n\n  IF NEW.\"asset_proxy_whitelist\" IS NULL AND NEW.\"asset_proxy_allowlist\" IS NOT NULL THEN\n    NEW.\"asset_proxy_whitelist\" = NEW.\"asset_proxy_allowlist\";\n  END IF;\n\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_e40a6f1858e6\nON \"application_settings\";\n\nCREATE TRIGGER trigger_e40a6f1858e6\nBEFORE INSERT ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_e40a6f1858e6();\n")
NOTICE:  trigger "trigger_e40a6f1858e6" for relation "application_settings" does not exist, skipping
   -> 0.0007s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_0d588df444c8()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"asset_proxy_allowlist\" := NEW.\"asset_proxy_whitelist\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_0d588df444c8\nON \"application_settings\";\n\nCREATE TRIGGER trigger_0d588df444c8\nBEFORE UPDATE OF \"asset_proxy_whitelist\" ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_0d588df444c8();\n")
NOTICE:  trigger "trigger_0d588df444c8" for relation "application_settings" does not exist, skipping
   -> 0.0007s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_b01442420ce6()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"asset_proxy_whitelist\" := NEW.\"asset_proxy_allowlist\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_b01442420ce6\nON \"application_settings\";\n\nCREATE TRIGGER trigger_b01442420ce6\nBEFORE UPDATE OF \"asset_proxy_allowlist\" ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_b01442420ce6();\n")
NOTICE:  trigger "trigger_b01442420ce6" for relation "application_settings" does not exist, skipping
   -> 0.0006s
== 20210301200601 RenameAssetProxyAllowlistOnApplicationSettings: migrated (0.9339s)

== 20210301200959 CleanUpAssetProxyAllowlistRenameOnApplicationSettings: migrating
-- column_exists?(:application_settings, :asset_proxy_allowlist)
   -> 0.1234s
-- quote_table_name(:application_settings)
   -> 0.0000s
-- execute("DROP TRIGGER IF EXISTS trigger_e40a6f1858e6\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_e40a6f1858e6;\n")
   -> 0.0004s
-- execute("DROP TRIGGER IF EXISTS trigger_0d588df444c8\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_0d588df444c8;\n")
   -> 0.0005s
-- execute("DROP TRIGGER IF EXISTS trigger_b01442420ce6\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_b01442420ce6;\n")
   -> 0.0005s
-- remove_column(:application_settings, :asset_proxy_allowlist)
   -> 0.0007s
== 20210301200959 CleanUpAssetProxyAllowlistRenameOnApplicationSettings: migrated (0.1291s)

Down

== 20210301200959 CleanUpAssetProxyAllowlistRenameOnApplicationSettings: reverting
-- transaction_open?()
   -> 0.0000s
-- columns(:application_settings)
   -> 0.1248s
-- column_exists?(:application_settings, :id)
   -> 0.1204s
-- column_exists?(:application_settings, :asset_proxy_allowlist)
   -> 0.1207s
-- columns(:application_settings)
   -> 0.1210s
-- add_column(:application_settings, :asset_proxy_allowlist, :text, {:limit=>nil, :precision=>nil, :scale=>nil})
   -> 0.0028s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"application_settings\"")
   -> 0.0016s
-- exec_query("SELECT \"application_settings\".\"id\" FROM \"application_settings\" ORDER BY \"application_settings\".\"id\" ASC LIMIT 1")
   -> 0.0006s
-- exec_query("SELECT \"application_settings\".\"id\" FROM \"application_settings\" WHERE \"application_settings\".\"id\" >= 1 ORDER BY \"application_settings\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0006s
-- execute("UPDATE \"application_settings\" SET \"asset_proxy_allowlist\" = \"application_settings\".\"asset_proxy_whitelist\" WHERE \"application_settings\".\"id\" >= 1")
   -> 0.0021s
-- indexes(:application_settings)
   -> 0.0080s
-- foreign_keys(:application_settings)
   -> 0.0067s
-- transaction_open?()
   -> 0.0000s
-- column_exists?(:application_settings, :asset_proxy_whitelist)
   -> 0.1215s
-- column_exists?(:application_settings, :asset_proxy_allowlist)
   -> 0.1219s
-- current_schema()
   -> 0.0003s
-- quote_table_name(:application_settings)
   -> 0.0000s
-- quote_column_name(:asset_proxy_allowlist)
   -> 0.0000s
-- quote_column_name(:asset_proxy_whitelist)
   -> 0.0000s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_e40a6f1858e6()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  IF NEW.\"asset_proxy_allowlist\" IS NULL AND NEW.\"asset_proxy_whitelist\" IS NOT NULL THEN\n    NEW.\"asset_proxy_allowlist\" = NEW.\"asset_proxy_whitelist\";\n  END IF;\n\n  IF NEW.\"asset_proxy_whitelist\" IS NULL AND NEW.\"asset_proxy_allowlist\" IS NOT NULL THEN\n    NEW.\"asset_proxy_whitelist\" = NEW.\"asset_proxy_allowlist\";\n  END IF;\n\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_e40a6f1858e6\nON \"application_settings\";\n\nCREATE TRIGGER trigger_e40a6f1858e6\nBEFORE INSERT ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_e40a6f1858e6();\n")
   -> 0.0014s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_0d588df444c8()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"asset_proxy_allowlist\" := NEW.\"asset_proxy_whitelist\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_0d588df444c8\nON \"application_settings\";\n\nCREATE TRIGGER trigger_0d588df444c8\nBEFORE UPDATE OF \"asset_proxy_whitelist\" ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_0d588df444c8();\n")
   -> 0.0006s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_b01442420ce6()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"asset_proxy_whitelist\" := NEW.\"asset_proxy_allowlist\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_b01442420ce6\nON \"application_settings\";\n\nCREATE TRIGGER trigger_b01442420ce6\nBEFORE UPDATE OF \"asset_proxy_allowlist\" ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_b01442420ce6();\n")
   -> 0.0007s
== 20210301200959 CleanUpAssetProxyAllowlistRenameOnApplicationSettings: reverted (0.7916s)

== 20210301200601 RenameAssetProxyAllowlistOnApplicationSettings: reverting ===
-- column_exists?(:application_settings, :asset_proxy_whitelist)
   -> 0.1339s
-- quote_table_name(:application_settings)
   -> 0.0000s
-- execute("DROP TRIGGER IF EXISTS trigger_e40a6f1858e6\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_e40a6f1858e6;\n")
   -> 0.0007s
-- execute("DROP TRIGGER IF EXISTS trigger_0d588df444c8\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_0d588df444c8;\n")
   -> 0.0010s
-- execute("DROP TRIGGER IF EXISTS trigger_b01442420ce6\nON \"application_settings\";\n\nDROP FUNCTION IF EXISTS function_for_trigger_b01442420ce6;\n")
   -> 0.0005s
-- remove_column(:application_settings, :asset_proxy_whitelist)
   -> 0.0008s
-- transaction_open?()
   -> 0.0000s
-- columns(:application_settings)
   -> 0.1266s
-- column_exists?(:application_settings, :id)
   -> 0.1228s
-- column_exists?(:application_settings, :asset_proxy_whitelist)
   -> 0.1209s
-- columns(:application_settings)
   -> 0.1207s
-- add_column(:application_settings, :asset_proxy_whitelist, :text, {:limit=>nil, :precision=>nil, :scale=>nil})
   -> 0.0022s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"application_settings\"")
   -> 0.0017s
-- exec_query("SELECT \"application_settings\".\"id\" FROM \"application_settings\" ORDER BY \"application_settings\".\"id\" ASC LIMIT 1")
   -> 0.0006s
-- exec_query("SELECT \"application_settings\".\"id\" FROM \"application_settings\" WHERE \"application_settings\".\"id\" >= 1 ORDER BY \"application_settings\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0005s
-- execute("UPDATE \"application_settings\" SET \"asset_proxy_whitelist\" = \"application_settings\".\"asset_proxy_allowlist\" WHERE \"application_settings\".\"id\" >= 1")
   -> 0.0025s
-- indexes(:application_settings)
   -> 0.0080s
-- foreign_keys(:application_settings)
   -> 0.0068s
-- transaction_open?()
   -> 0.0000s
-- column_exists?(:application_settings, :asset_proxy_allowlist)
   -> 0.1213s
-- column_exists?(:application_settings, :asset_proxy_whitelist)
   -> 0.1284s
-- current_schema()
   -> 0.0003s
-- quote_table_name(:application_settings)
   -> 0.0000s
-- quote_column_name(:asset_proxy_whitelist)
   -> 0.0000s
-- quote_column_name(:asset_proxy_allowlist)
   -> 0.0000s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_9a96291bb38d()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  IF NEW.\"asset_proxy_whitelist\" IS NULL AND NEW.\"asset_proxy_allowlist\" IS NOT NULL THEN\n    NEW.\"asset_proxy_whitelist\" = NEW.\"asset_proxy_allowlist\";\n  END IF;\n\n  IF NEW.\"asset_proxy_allowlist\" IS NULL AND NEW.\"asset_proxy_whitelist\" IS NOT NULL THEN\n    NEW.\"asset_proxy_allowlist\" = NEW.\"asset_proxy_whitelist\";\n  END IF;\n\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_9a96291bb38d\nON \"application_settings\";\n\nCREATE TRIGGER trigger_9a96291bb38d\nBEFORE INSERT ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_9a96291bb38d();\n")
   -> 0.0013s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_22a39c5c25f3()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"asset_proxy_whitelist\" := NEW.\"asset_proxy_allowlist\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_22a39c5c25f3\nON \"application_settings\";\n\nCREATE TRIGGER trigger_22a39c5c25f3\nBEFORE UPDATE OF \"asset_proxy_allowlist\" ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_22a39c5c25f3();\n")
   -> 0.0007s
-- execute("CREATE OR REPLACE FUNCTION function_for_trigger_1572cbc9a15f()\nRETURNS trigger\nLANGUAGE plpgsql\nAS $$\nBEGIN\n  NEW.\"asset_proxy_allowlist\" := NEW.\"asset_proxy_whitelist\";\n  RETURN NEW;\nEND\n$$;\n\nDROP TRIGGER IF EXISTS trigger_1572cbc9a15f\nON \"application_settings\";\n\nCREATE TRIGGER trigger_1572cbc9a15f\nBEFORE UPDATE OF \"asset_proxy_whitelist\" ON \"application_settings\"\nFOR EACH ROW EXECUTE FUNCTION function_for_trigger_1572cbc9a15f();\n")
   -> 0.0006s
== 20210301200601 RenameAssetProxyAllowlistOnApplicationSettings: reverted (0.9458s)

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

Realted issues

Related to #324160 (closed)

Edited Mar 11, 2021 by Mayra Cabrera

Fix zero-downtime upgrade from 13.8.x to 13.9.3