werefuckedup.md

While working on PD-5217 we noticed some tables which should not exist and decided to drop them instead of adding timestamps to them.

We added corresponding migrations with drop_table and merged them into integration. Deploy passed as expected on CA staging, but errored on US (EY) staging.

Unfortunately, it was unclear from deploy logs what exactly happened:

+ 3m 31s       StandardError: An error has occurred, this and all later migrations canceled:
+ 3m 31s
+ 3m 31s       PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ 3m 31s       :             SELECT tablename
+ 3m 31s                   FROM pg_tables
+ 3m 31s                   WHERE schemaname = ANY (current_schemas(false))

We downloaded DB dump from staging and successfully applied migrations locally. Further investigation led us to foreigner gem and its drop_table method:

https://github.com/matthuhiggins/foreigner/blob/7d700a54bc726c221e4d037c111dae80ceeda7e6/lib/foreigner/connection_adapters/sql2003.rb#L10-L14

As migrations failed on drop_table method, we added force: true to all drop_table instructions expecting that this will drop stuck tables. That didn't help, so we started inspecting full deploy process: ssh to the box and tail -f log messages. That finally led us to the root cause:

Migrating to DropOpenIdAuthenticationAssociationsTable (20150805170138)
   (0.3ms)  BEGIN
   (1.7ms)  ALTER TABLE "activity_logs" DISABLE TRIGGER ALL;ALTER TABLE "applications" DISABLE TRIGGER ALL;ALTER TABLE "attachments_backups" DISABLE TRIGGER ALL;ALTER TABLE "audit_types" DISABLE TRIGGER ALL;ALTER TABLE "control_test_plans" DISABLE TRIGGER ALL;ALTER TABLE "backups" DISABLE TRIGGER ALL;ALTER TABLE "ckeditor_assets" DISABLE TRIGGER ALL;ALTER TABLE "comments" DISABLE TRIGGER ALL;ALTER TABLE "control_terms" DISABLE TRIGGER ALL;ALTER TABLE "control_tests" DISABLE TRIGGER ALL;ALTER TABLE "control_test_terms" DISABLE TRIGGER ALL;ALTER TABLE "controls" DISABLE TRIGGER ALL;ALTER TABLE "coso_principles" DISABLE TRIGGER ALL;ALTER TABLE "delayed_jobs" DISABLE TRIGGER ALL;ALTER TABLE "devices" DISABLE TRIGGER ALL;ALTER TABLE "editable_attachment_slugs" DISABLE TRIGGER ALL;ALTER TABLE "finding_terms" DISABLE TRIGGER ALL;ALTER TABLE "findings" DISABLE TRIGGER ALL;ALTER TABLE "narrative_terms" DISABLE TRIGGER ALL;ALTER TABLE "objective_terms" DISABLE TRIGGER ALL;ALTER TABLE "objectives" DISABLE TRIGGER ALL;ALTER TABLE "open_id_authentication_associations" DISABLE TRIGGER ALL;ALTER TABLE "entity_types" DISABLE TRIGGER ALL;ALTER TABLE "entity_responsibilities" DISABLE TRIGGER ALL;ALTER TABLE "mitigations" DISABLE TRIGGER ALL;ALTER TABLE "open_id_authentication_nonces" DISABLE TRIGGER ALL;ALTER TABLE "profiles" DISABLE TRIGGER ALL;ALTER TABLE "project_files" DISABLE TRIGGER ALL;ALTER TABLE "project_roles" DISABLE TRIGGER ALL;ALTER TABLE "push_notifications" DISABLE TRIGGER ALL;ALTER TABLE "push_notifications_settings" DISABLE TRIGGER ALL;ALTER TABLE "request_items" DISABLE TRIGGER ALL;ALTER TABLE "result_control_test_mappings" DISABLE TRIGGER ALL;ALTER TABLE "result_interpretation_mappings" DISABLE TRIGGER ALL;ALTER TABLE "risk_audit_mappings" DISABLE TRIGGER ALL;ALTER TABLE "risk_terms" DISABLE TRIGGER ALL;ALTER TABLE "risk_control_matrices" DISABLE TRIGGER ALL;ALTER TABLE "roles" DISABLE TRIGGER ALL;ALTER TABLE "roles_users" DISABLE TRIGGER ALL;ALTER TABLE "rpush_apps" DISABLE TRIGGER ALL;ALTER TABLE "rpush_feedback" DISABLE TRIGGER ALL;ALTER TABLE "rpush_notifications" DISABLE TRIGGER ALL;ALTER TABLE "saved_reports" DISABLE TRIGGER ALL;ALTER TABLE "schema_migrations" DISABLE TRIGGER ALL;ALTER TABLE "software_registrations" DISABLE TRIGGER ALL;ALTER TABLE "subscriptions" DISABLE TRIGGER ALL;ALTER TABLE "taggings" DISABLE TRIGGER ALL;ALTER TABLE "tags" DISABLE TRIGGER ALL;ALTER TABLE "testing_rounds" DISABLE TRIGGER ALL;ALTER TABLE "time_entries" DISABLE TRIGGER ALL;ALTER TABLE "sessions" DISABLE TRIGGER ALL;ALTER TABLE "todos" DISABLE TRIGGER ALL;ALTER TABLE "versioned_documents" DISABLE TRIGGER ALL;ALTER TABLE "versions" DISABLE TRIGGER ALL;ALTER TABLE "walkthrough_terms" DISABLE TRIGGER ALL;ALTER TABLE "narratives" DISABLE TRIGGER ALL;ALTER TABLE "action_comments" DISABLE TRIGGER ALL;ALTER TABLE "async_requests" DISABLE TRIGGER ALL;ALTER TABLE "risks" DISABLE TRIGGER ALL;ALTER TABLE "accounts_users" DISABLE TRIGGER ALL;ALTER TABLE "accounts" DISABLE TRIGGER ALL;ALTER TABLE "process_walkthroughs" DISABLE TRIGGER ALL;ALTER TABLE "control_assertions" DISABLE TRIGGER ALL;ALTER TABLE "auditable_entities" DISABLE TRIGGER ALL;ALTER TABLE "attached_documents" DISABLE TRIGGER ALL;ALTER TABLE "audits" DISABLE TRIGGER ALL;ALTER TABLE "finding_actions" DISABLE TRIGGER ALL;ALTER TABLE "test_plans" DISABLE TRIGGER ALL;ALTER TABLE "users" DISABLE TRIGGER ALL;ALTER TABLE "walkthroughs" DISABLE TRIGGER ALL
PG::InsufficientPrivilege: ERROR:  permission denied: "RI_ConstraintTrigger_a_12674268" is a system trigger

That was actually a permission error:

PG::InsufficientPrivilege: ERROR:  permission denied: "RI_ConstraintTrigger_a_12674268" is a system trigger

It appears that on EY Rails connects to the database as non-superuser (in terms of Postgresql) which is not allowed to disable triggers. Locally and on CA servers Rails connects as superuser, so migrations were applied successfully. We decided to have another look at foreigner drop_table method:

if options[:force]
  disable_referential_integrity { super }
else
  super
end

When force is set to true it first disables referential integrity checks which produces all these DISABLE TRIGGER queries: https://github.com/rails/rails/blob/260da06e6b63f4644fe67e67fbd486585f9d2724/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L9-L28.

If we set force to false or omit it (as Iwan did here: https://github.com/acl-services/workpapers/commit/709e86f1ff558536223fb53da77fe886167cd5f3), then migrations simply run DROP TABLE query:

Migrating to DropOpenIdAuthenticationAssociationsTable (20150805170138)
   (0.3ms)  BEGIN
   (8.5ms)  DROP TABLE "open_id_authentication_associations"
  SQL (0.6ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20150805170138"]]
   (3.5ms)  COMMIT
Migrating to DropOpenIdAuthenticationNoncesTable (20150806083012)
   (0.4ms)  BEGIN
   (2.4ms)  DROP TABLE "open_id_authentication_nonces"
  SQL (0.4ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20150806083012"]]
   (1.3ms)  COMMIT
Migrating to DropVersionsTable (20150806083430)
   (0.3ms)  BEGIN
   (5.2ms)  DROP TABLE "versions"
  SQL (0.5ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20150806083430"]]
   (4.7ms)  COMMIT
Migrating to DropCkeditorAssetsTable (20150806085149)
   (0.4ms)  BEGIN
   (3.1ms)  DROP TABLE "ckeditor_assets"
  SQL (0.3ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20150806085149"]]
   (2.1ms)  COMMIT
Migrating to DropAttachmentsBackupsTable (20150806092922)
   (0.3ms)  BEGIN
   (3.9ms)  DROP TABLE "attachments_backups"
  SQL (0.3ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20150806092922"]]
   (4.0ms)  COMMIT
Migrating to DropVersionedDocumentsTable (20150806094436)
   (0.3ms)  BEGIN
   (2.9ms)  DROP TABLE "versioned_documents"
  SQL (0.5ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20150806094436"]]
   (1.7ms)  COMMIT
  ActiveRecord::SchemaMigration Load (0.9ms)  SELECT "schema_migrations".* FROM "schema_migrations"

Lessons Learned

EY environment differs from dev/CA environments: DB user is not superuser and can't disable triggers
drop_table should be used carefully: ensure table doesn't have any referencing foreign keys and drop it without force: true
deploy logs may be useless: always start with connection to the box and looking to the logs directly

Next Steps

destroy remaining tables and unblock deployment
stabilize integration branch and make sure it can be merged to preprod on Monday
before next deployment apply these migrations in cloned production environment
investigate why DB schema differs across environments

vast/werefuckedup.md

Lessons Learned

Next Steps