Error "Checksum failed for migration ..." when upgrading from 3.1.0 to latest #78

mrsarm · 2021-04-21T17:41:46Z

This error was reported here, and I could confirm the error in my local environment migrating a small database, so we will keep track of the solution in this.

When you have a Postgres database created and maintained with medic-couch2pg 3.1.0, and the you try to run medic-couch2pg 3.2.0 over the same database, the process is exited with the following error:

$ node .
[2021-04-21T17:39:26.046Z DEBUG]:  { debug: true,
  v4Mode: false,
  couchdbUrl: 'localhost:5984/medic',
  postgresqlUrl: 'localhost:5432/postgres',
  docLimit: 1000,
  changesLimit: undefined,
  retryCount: 5,
  sleepMins: 120,
  couchdbUsersMetaDocLimit: 50 }
[2:39:26 PM] version of database is: 201803202020
[2:39:26 PM] migrating up to 201803202020
[2:39:26 PM] verifying checksum of migration 201606091247.do.2318-standardise-schema.sql
[2:39:26 PM] verifying checksum of migration 201606091248.do.2124-support-edit-and-delete-of-documents.sql
[2:39:26 PM] verifying checksum of migration 201607132040.do.2506-unique-id-constraint.sql
[2:39:26 PM] verifying checksum of migration 201607210832.do.2523-remove-unused-index.sql
[2:39:26 PM] verifying checksum of migration 201611271809.do.2923-couch2-support.sql
[2:39:26 PM] verifying checksum of migration 201803202020.do.3423-multiple-db-support.sql
[2021-04-21T17:39:26.081Z INFO]:  Adapter is running in NORMAL mode
[2021-04-21T17:39:26.097Z ERROR]:  An unrecoverable error occurred
[2021-04-21T17:39:26.097Z ERROR]:  { Error: MD5 checksum failed for migration [201606200952]
    at sequence.then.then.then.results (/projects/medic/medic-couch2pg/node_modules/postgrator/postgrator.js:165:21)
    at process._tickCallback (internal/process/next_tick.js:68:7) appliedMigrations: [] }
[2021-04-21T17:39:26.098Z ERROR]:  exiting

CC @nomulex @garethbowen

The text was updated successfully, but these errors were encountered:

dianabarsan · 2021-04-27T21:46:11Z

Is it possible that migrations need to be "read-only" and this commit mutates the migration that is failing? 34f1d2a

By read-only, I mean migrations should run ONCE, in a predetermined order (I assume that's what the date prefix is for).
So it's quite straight forward to store a checksum of a past run migration and check it against current pushed migrations.

dianabarsan · 2021-04-27T21:52:16Z

I guess it's easy enough to test this theory by updating another migration and see if it also fails.

mrsarm · 2021-04-28T16:25:58Z

@dianabarsan, yes the problem is the one you mention.

I think the approach I'll take to fix it is:

Rollback the change.
Apply the patch as a separated migration.
Ensure the change works in new databases and existent databases created with previous versions of medic-couch2pg.
Maybe I can add a regression test to avoid errors like this in the future, it is not easy because it won't be an integration test where we execute different versions of the CLI, maybe I can add a unit test where the md5 sum is verified for each SQL script to ensure it didn't change, but I'll need to hardcode the md5 sums in the test, and another test that ensure all the SQL scripts are covered, so if a new SQL script is added in the migration folder, the test will fail until the md5 sum of the new file is added to the list.

mrsarm · 2021-04-28T16:38:06Z

Note that if I move the change to a new SQL script and rolling back the original content, it should works for new Postgres databases and it should works for existent databases created with older versions of medic-couch2pg , but not for Postgres databases that were created with the version that introduced the bug (v3.2.0, last version), because in that case the md5 sum will be the one computed with the edited version.

A solution to the problem could be check the md5 sum stored in the database, and only if the "edited" md5 sum is found in the database, patch it, and then run the migrations.

mrsarm · 2021-04-28T16:46:26Z

Another path:

Leave the modified SQL file as it is
For a existent database that has the old md5 sum: patch it with the new md5 sum, just for this case, and then run the migrations.

The cons is that we don't have to change one more time a file that is supposedly immutable, and it is easier to implement.

dianabarsan · 2021-04-28T16:46:40Z

@mrsarm
I think the steps you listed are correct, it's the right and only way to fix this.
Have there been any postgres databases that were created with v3.2.0 ? Even if they exist, they could not have existed for more than 10 days and maybe the effort to dump and redo them with 3.2.1 would be minimal.

dianabarsan · 2021-04-28T16:48:39Z

@nomulex
Please assist us. Has medic-couch2pg@3.2.0 been deployed on any "fresh" postgres databases (not upgrades from 3.1.x)?

nomulex · 2021-04-28T18:31:58Z

I can confirm that we do not have any project running medic-couch2pg@3.2.0

mrsarm · 2021-04-28T18:45:12Z

Thanks @nomulex and @dianabarsan ,

In this case, I'll rollback the change in a patch release, and maybe leave a new section "Known issues" pointing to this issue an the following SQL script to run against the Postgres database in case somebody did start a fresh database with medic-couch2pg 3.2.0:

UPDATE xmlforms_migrations SET md5 = 'e0535c9fe3faef6e66a31691deebf1a8'
  WHERE version = '201606200952' AND md5 = '40187aa5ee95eda0e154ecefd7512cda';

mrsarm · 2021-05-05T01:43:10Z

Ready for AT, branch 78-fix-checksum-migration-issue, PR #79.

Summarizing, the PR rollbacks the last changes in the 3.2.0 migration scripts and adds a new migration script that applies the same changes in a new change-set. Despite the many SQL instructions the script has, the only real change is that a new column contact_type is created in the materialized view contactview_metadata, but the whole view, indexes and dependents views are recreated because a limitation in Postgres that does not allow to add new columns to views.

So the scenarios to tests are:

The script should run without problems in a new empty Postgres databases, and after run the CLI, Postgres must contain the data migrated, and the new column from the mentioned script.
Same test but running medic-couch2pg over an existent Postgres database that contains a dataset created with medic-couch2pg 3.1.0.

NOTE: in my local test moving data from a small local medic DB in CouchDB to Postgres using medic-couch2pg, I couldn't see any value in the new column ``contactview_metadata.contact_type`, but the field didn't exist at the source so it's OK to not see any value in Postgres, and I don't know how to populate that field to check.

ngaruko · 2021-05-06T23:14:36Z

LGTM. Thanks @mrsarm for your help in getting medic-couch2pg up and running .
Tested on one of our test instances.

Create a db in Postgres
checked v3.10 (medic-couch2pg git:(80225c3))
run node . with correct env variables
Run complete successfully

➜  medic-couch2pg git:(80225c3) node .
[2021-05-06T22:58:52.917Z DEBUG]:  { debug: true,
  v4Mode: false,
  couchdbUrl: 'gamma-b.dev.medicmobile.org/medic',
  postgresqlUrl: 'localhost:15432/mariano',
  docLimit: 1000,
  changesLimit: undefined,
  retryCount: 5,
  sleepMins: 120 }
[10:58:53 am] table couch2pg_migrations does not exist - creating it.
[10:58:53 am] version of database is: 0
[10:58:53 am] migrating up to 201803202020
[10:58:53 am] running 201606091247.do.2318-standardise-schema.sql
[10:58:53 am] running 201606091248.do.2124-support-edit-and-delete-of-documents.sql
[10:58:53 am] running 201607132040.do.2506-unique-id-constraint.sql
[10:58:53 am] running 201607210832.do.2523-remove-unused-index.sql
[10:58:53 am] running 201611271809.do.2923-couch2-support.sql
[10:58:53 am] running 201803202020.do.3423-multiple-db-support.sql
[2021-05-06T22:58:53.197Z INFO]:  Adapter is running in NORMAL mode
[2021-05-06T22:58:53.354Z INFO]:  Beginning couch2pg and xmlforms run at Fri May 07 2021 10:58:53 GMT+1200 (New Zealand Standard Time)
[2021-05-06T22:58:53.356Z DEBUG]:  Performing an import batch of up to 10000 changes
[2021-05-06T22:58:53.378Z DEBUG]:  Downloading CouchDB changes feed from 0
[2021-05-06T22:59:09.257Z INFO]:  There are 200 changes to process
[2021-05-06T22:59:09.259Z DEBUG]:  There are 18 deletions and 182 new / changed documents
[2021-05-06T22:59:14.221Z DEBUG]:  Pulled 182 results from couchdb
[2021-05-06T22:59:14.221Z DEBUG]:  Clearing any existing documents from postgresql
[2021-05-06T22:59:14.238Z DEBUG]:  Inserting 182 results into postgresql
[2021-05-06T22:59:14.734Z DEBUG]:  Batch completed with 200 changes
[2021-05-06T22:59:14.734Z DEBUG]:  Performing an import batch of up to 10000 changes
[2021-05-06T22:59:14.736Z DEBUG]:  Downloading CouchDB changes feed from 467-g1AAAAJjeJyd0M0KwjAMAODiBL36BPPmbXTttq4gujfRNusYY-rJgyd9E30TfRN9k9mfHTwMoSOQQEI-SFqE0LwOSrSA0xnqUhYxYRHWEbd6NBFIhl3XNXUgVgfdmBGmMBAxtPCHkUud5baXsJUk8JLnla9UGGnXS6GVkioBTjNfaW-kay-traQEZwDgKR2nOqObLhq7_2gEA5fpKO3htKfRIqullMlU-V7ptJfT3kbbWI2qGAuBR2kfp9m_XZzGsaJZPrTXfAGGuKEg
[2021-05-06T22:59:15.930Z INFO]:  There are 0 changes to process
[2021-05-06T22:59:15.932Z DEBUG]:  Import loop complete, 200 changes total
[2021-05-06T22:59:15.933Z DEBUG]:  Performing an import batch of up to 10000 changes
[2021-05-06T22:59:15.937Z DEBUG]:  Downloading CouchDB changes feed from 0
[2021-05-06T22:59:28.551Z INFO]:  There are 166 changes to process
[2021-05-06T22:59:28.551Z DEBUG]:  There are 12 deletions and 154 new / changed documents
[2021-05-06T22:59:30.190Z DEBUG]:  Pulled 154 results from couchdb
[2021-05-06T22:59:30.190Z DEBUG]:  Clearing any existing documents from postgresql
[2021-05-06T22:59:30.201Z DEBUG]:  Inserting 154 results into postgresql
[2021-05-06T22:59:30.222Z DEBUG]:  Batch completed with 166 changes
[2021-05-06T22:59:30.222Z DEBUG]:  Performing an import batch of up to 10000 changes
[2021-05-06T22:59:30.223Z DEBUG]:  Downloading CouchDB changes feed from 397-g1AAAAJjeJyd0ksKwjAQBuBgBd16Al25U5KMNemqBQ-ieVJK1ZVrvYneRG-iN6lJUxChCJHABBLyMfOTGiE0LhONJup4UqWWBaFsid0itbsaCCSnTdNUZSLme3cwYtwCkHXfgx-MnLkq805atBIlaUYZjpUKL22_etIrgTnoWGnnpXMn0dCTtJKDipQOQ1fRxW0Ou3pt02pW6zRj5i_tFrT7Jy9jlaLwn_YI2tNreZgUhExtbPpBewWtzQ3CrzAEc9ObW_UG4kyg1A
[2021-05-06T22:59:31.425Z INFO]:  There are 0 changes to process
[2021-05-06T22:59:31.426Z DEBUG]:  Import loop complete, 166 changes total
[2021-05-06T22:59:31.427Z INFO]:  Refreshing materialised views
[2021-05-06T22:59:31.495Z DEBUG]:  anonymous { refresh_matviews: 1 }
[2021-05-06T22:59:31.498Z DEBUG]:  { debug: true,
  v4Mode: false,
  couchdbUrl: 'gamma-b.dev.medicmobile.org/medic',
  postgresqlUrl: 'localhost:15432/mariano',
  docLimit: 1000,
  changesLimit: undefined,
  retryCount: 5,
  sleepMins: 120 }
[2021-05-06T22:59:31.499Z INFO]:  Run completed. Next run at Fri May 07 2021 12:59:31 GMT+1200

Switched to medic-couch2pg master
run node .
see error

➜  medic-couch2pg git:(master) node .
[2021-05-06T23:02:02.822Z DEBUG]:  { debug: true,
  v4Mode: false,
  couchdbUrl: 'gamma-b.dev.medicmobile.org/medic',
  postgresqlUrl: 'localhost:15432/mariano',
  docLimit: 1000,
  changesLimit: undefined,
  retryCount: 5,
  sleepMins: 120,
  couchdbUsersMetaDocLimit: 50 }
[11:02:02 am] version of database is: 201803202020
[11:02:02 am] migrating up to 201803202020
[11:02:02 am] verifying checksum of migration 201606091247.do.2318-standardise-schema.sql
[11:02:02 am] verifying checksum of migration 201606091248.do.2124-support-edit-and-delete-of-documents.sql
[11:02:02 am] verifying checksum of migration 201607132040.do.2506-unique-id-constraint.sql
[11:02:02 am] verifying checksum of migration 201607210832.do.2523-remove-unused-index.sql
[11:02:02 am] verifying checksum of migration 201611271809.do.2923-couch2-support.sql
[11:02:02 am] verifying checksum of migration 201803202020.do.3423-multiple-db-support.sql
[2021-05-06T23:02:02.997Z INFO]:  Adapter is running in NORMAL mode
[2021-05-06T23:02:03.054Z ERROR]:  An unrecoverable error occurred
[2021-05-06T23:02:03.055Z ERROR]:  { Error: MD5 checksum failed for migration [201606200952]
    at sequence.then.then.then.results (/Users/bede/tests/medic-couch2pg/node_modules/postgrator/postgrator.js:165:21)
    at process._tickCallback (internal/process/next_tick.js:68:7) appliedMigrations: [] }
[2021-05-06T23:02:03.055Z ERROR]:  exiting

Switch to medic-couch2pg git:(78-fix-checksum-migration-issue)
Run node .
Run complete successfully this time

medic-couch2pg git:(78-fix-checksum-migration-issue) node .
[2021-05-06T23:03:06.512Z DEBUG]:  { debug: true,
  v4Mode: false,
  couchdbUrl: 'gamma-b.dev.medicmobile.org/medic',
  postgresqlUrl: 'localhost:15432/mariano',
  docLimit: 1000,
  changesLimit: undefined,
  retryCount: 5,
  sleepMins: 120,
  couchdbUsersMetaDocLimit: 50 }
[11:03:06 am] version of database is: 201803202020
[11:03:06 am] migrating up to 201803202020
[11:03:06 am] verifying checksum of migration 201606091247.do.2318-standardise-schema.sql
[11:03:06 am] verifying checksum of migration 201606091248.do.2124-support-edit-and-delete-of-documents.sql
[11:03:06 am] verifying checksum of migration 201607132040.do.2506-unique-id-constraint.sql
[11:03:06 am] verifying checksum of migration 201607210832.do.2523-remove-unused-index.sql
[11:03:06 am] verifying checksum of migration 201611271809.do.2923-couch2-support.sql
[11:03:06 am] verifying checksum of migration 201803202020.do.3423-multiple-db-support.sql
[2021-05-06T23:03:06.652Z INFO]:  Adapter is running in NORMAL mode
[2021-05-06T23:03:06.791Z INFO]:  Beginning couch2pg and xmlforms run at Fri May 07 2021 11:03:06 GMT+1200 (New Zealand Standard Time)
[2021-05-06T23:03:06.794Z DEBUG]:  Performing an import batch of up to 10000 changes
[2021-05-06T23:03:06.812Z DEBUG]:  Downloading CouchDB changes feed from 467-g1AAAAJjeJyd0M0KwjAMAODiBL36BPPmbXTttq4gujfRNusYY-rJgyd9E30TfRN9k9mfHTwMoSOQQEI-SFqE0LwOSrSA0xnqUhYxYRHWEbd6NBFIhl3XNXUgVgfdmBGmMBAxtPCHkUud5baXsJUk8JLnla9UGGnXS6GVkioBTjNfaW-kay-traQEZwDgKR2nOqObLhq7_2gEA5fpKO3htKfRIqullMlU-V7ptJfT3kbbWI2qGAuBR2kfp9m_XZzGsaJZPrTXfAGGuKEg
[2021-05-06T23:03:09.427Z INFO]:  There are 0 changes to process
[2021-05-06T23:03:09.431Z DEBUG]:  Import loop complete, 0 changes total
[2021-05-06T23:03:09.432Z DEBUG]:  Performing an import batch of up to 10000 changes
[2021-05-06T23:03:09.434Z DEBUG]:  Downloading CouchDB changes feed from 397-g1AAAAJjeJyd0ksKwjAQBuBgBd16Al25U5KMNemqBQ-ieVJK1ZVrvYneRG-iN6lJUxChCJHABBLyMfOTGiE0LhONJup4UqWWBaFsid0itbsaCCSnTdNUZSLme3cwYtwCkHXfgx-MnLkq805atBIlaUYZjpUKL22_etIrgTnoWGnnpXMn0dCTtJKDipQOQ1fRxW0Ou3pt02pW6zRj5i_tFrT7Jy9jlaLwn_YI2tNreZgUhExtbPpBewWtzQ3CrzAEc9ObW_UG4kyg1A
[2021-05-06T23:03:11.875Z INFO]:  There are 0 changes to process
[2021-05-06T23:03:11.877Z DEBUG]:  Import loop complete, 0 changes total
[2021-05-06T23:03:11.878Z DEBUG]:  Performing an import batch of up to 10000 changes
[2021-05-06T23:03:11.879Z DEBUG]:  Downloading CouchDB changes feed from 9-g1AAAAJTeJyd0V0KgkAQAOBNg-qxE9QJwl1dd_cpb1L7i4nZk891k7pJ3aRuYqsraBCCsjALM8zHDJMDAJapr8BaXkqZKpFARHaBfTC3JY8DsamqKkt97p1tYhHjiChO_zUMMGJro9i3EmgkZVBEeajAqiyUNqdCqyEhqYVDK8wawWAs6PhZjrV0_ZFUgGRM2EipmNsIbvaz2L3TQso0xGSS9nDas9b8RsMcG4HGbum0l9Pe3WwxRYGJ4CTt47TeDTVljEPZv2H2BTp6nDw
[2021-05-06T23:03:14.423Z INFO]:  There are 0 changes to process
[2021-05-06T23:03:14.425Z DEBUG]:  Import loop complete, 0 changes total
[2021-05-06T23:03:14.426Z DEBUG]:  { debug: true,
  v4Mode: false,
  couchdbUrl: 'gamma-b.dev.medicmobile.org/medic',
  postgresqlUrl: 'localhost:15432/mariano',
  docLimit: 50,
  changesLimit: undefined,
  retryCount: 5,
  sleepMins: 120,
  couchdbUsersMetaDocLimit: 50 }
[2021-05-06T23:03:14.427Z INFO]:  Run completed. Next run at Fri May 07 2021 13:03:14 GMT+1200

NOTE
Just a reminder @mrsarm - I have just created a ticket about node versions support . At the moment, with the v14.16.0 , I couldn't get the node . command to do anything. No error in the logs just the first json.

mrsarm · 2021-05-07T13:42:58Z

Merged changes to master and closing the ticket, @garethbowen I think we are ready for a patch release.

mrsarm added the bug label Apr 21, 2021

mrsarm self-assigned this Apr 21, 2021

mrsarm added this to Ready for dev in Dev team workstream via automation Apr 21, 2021

mrsarm moved this from Ready for dev to Dev in progress in Dev team workstream Apr 21, 2021

garethbowen added the Priority: 1 - High Blocking the next release label Apr 21, 2021

mrsarm changed the title ~~Error "Checksum failed for migration ..." when upgrading when upgrading from 3.1.0 to latest~~ Error "Checksum failed for migration ..." when upgrading from 3.1.0 to latest Apr 29, 2021

mrsarm mentioned this issue Apr 29, 2021

78 - Fix checksum migration issue #79

Merged

mrsarm moved this from Dev in progress to Ready for AT in Dev team workstream May 5, 2021

ngaruko moved this from Ready for AT to AT in progress in Dev team workstream May 5, 2021

ngaruko self-assigned this May 5, 2021

ngaruko moved this from AT in progress to Ready to merge in Dev team workstream May 6, 2021

mrsarm closed this as completed May 7, 2021

Dev team workstream automation moved this from Ready to merge to Done May 7, 2021

mrsarm mentioned this issue May 10, 2021

Release 3.2.1 #84

Merged

This was referenced May 17, 2021

updated useview_telemetry #88

Closed

6915 - Telemetry daily frequency medic/cht-core#7095

Merged

abbyad added this to the 3.2.1 milestone May 19, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error "Checksum failed for migration ..." when upgrading from 3.1.0 to latest #78

Error "Checksum failed for migration ..." when upgrading from 3.1.0 to latest #78

mrsarm commented Apr 21, 2021

dianabarsan commented Apr 27, 2021 •

edited

dianabarsan commented Apr 27, 2021

mrsarm commented Apr 28, 2021 •

edited

mrsarm commented Apr 28, 2021

mrsarm commented Apr 28, 2021

dianabarsan commented Apr 28, 2021

dianabarsan commented Apr 28, 2021 •

edited

nomulex commented Apr 28, 2021

mrsarm commented Apr 28, 2021

mrsarm commented May 5, 2021

ngaruko commented May 6, 2021 •

edited

mrsarm commented May 7, 2021

Error "Checksum failed for migration ..." when upgrading from 3.1.0 to latest #78

Error "Checksum failed for migration ..." when upgrading from 3.1.0 to latest #78

Comments

mrsarm commented Apr 21, 2021

dianabarsan commented Apr 27, 2021 • edited

dianabarsan commented Apr 27, 2021

mrsarm commented Apr 28, 2021 • edited

mrsarm commented Apr 28, 2021

mrsarm commented Apr 28, 2021

dianabarsan commented Apr 28, 2021

dianabarsan commented Apr 28, 2021 • edited

nomulex commented Apr 28, 2021

mrsarm commented Apr 28, 2021

mrsarm commented May 5, 2021

ngaruko commented May 6, 2021 • edited

mrsarm commented May 7, 2021

dianabarsan commented Apr 27, 2021 •

edited

mrsarm commented Apr 28, 2021 •

edited

dianabarsan commented Apr 28, 2021 •

edited

ngaruko commented May 6, 2021 •

edited