Error using gocd-database-migrator H2 - PostgreSQL GoCD 20.4.0 to 20.9.0

[luanadotcom]

Issue Type

• Bug Report

Summary

When migrating the H2 database on our GoCD 20.4.0 to PostgreSQL, in order to upgrade GoCD to 20.9.0 we get:

2020-11-25 15:41:33,329 ERROR [main] ChangeSet:22 - Change Set db-migration-scripts/initial/create-index.xml::92::gocd(generated) failed.  Error: ERROR: insert or update on table "modifiedfiles" violate
s foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]
2020-11-25 15:41:33,331 ERROR [main] Main:22 - Unexpected error running Liquibase: ERROR: insert or update on table "modifiedfiles" violates foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]
2020-11-25 15:41:33,331 ERROR [main] Main:22 - For more information, please use the --logLevel flag
2020-11-25 15:41:33,331 ERROR [main] Main:27 - Unexpected error running Liquibase: ERROR: insert or update on table "modifiedfiles" violates foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]
liquibase.exception.MigrationFailedException: Migration failed for change set db-migration-scripts/initial/create-index.xml::92::gocd(generated):
     Reason: liquibase.exception.DatabaseException: ERROR: insert or update on table "modifiedfiles" violates foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]

Environment

• GoCD 20.4.0 running on Amazon Linux 2, m5.large EC2 instance. It is using H2 as its database.
• Postgres 12.4 running on RDS, db.r5.large db instance

Basic environment details

• Go Version: 20.4.0
• JAVA Version: openjdk 13.0.2 2020-01-14
• Postgres: 12.4
• OS: Amazon Linux 2

Steps to Reproduce

1. Create Postgres RDS instance
2. Get the latest release of the gocd-database-migrator
3. Run the migration tool from the GoCD server

Expected Results

The H2 database is fully migrated to Postgres

Actual Results

After the step:

INFO  [main] DbSync:133 - Initializing database indices and constraints on target database. This may take several minutes, depending on the size of the database.

We've got the errors:

2020-11-25 15:41:33,329 ERROR [main] ChangeSet:22 - Change Set db-migration-scripts/initial/create-index.xml::92::gocd(generated) failed.  Error: ERROR: insert or update on table "modifiedfiles" violate
s foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]
2020-11-25 15:41:33,331 ERROR [main] Main:22 - Unexpected error running Liquibase: ERROR: insert or update on table "modifiedfiles" violates foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]
2020-11-25 15:41:33,331 ERROR [main] Main:22 - For more information, please use the --logLevel flag
2020-11-25 15:41:33,331 ERROR [main] Main:27 - Unexpected error running Liquibase: ERROR: insert or update on table "modifiedfiles" violates foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]
liquibase.exception.MigrationFailedException: Migration failed for change set db-migration-scripts/initial/create-index.xml::92::gocd(generated):
     Reason: liquibase.exception.DatabaseException: ERROR: insert or update on table "modifiedfiles" violates foreign key constraint "fk_modifiedfiles_modifications"
  Detail: Key (modificationid)=(1) is not present in table "modifications". [Failed SQL: (0) ALTER TABLE public.MODIFIEDFILES ADD CONSTRAINT FK_MODIFIEDFILES_MODIFICATIONS FOREIGN KEY (MODIFICATIONID) R
EFERENCES public.MODIFICATIONS (ID) ON UPDATE RESTRICT ON DELETE CASCADE]

Log snippets

Just above

[maheshp]

It looks like the the modifications table from H2 is not migrated to the RDS instance. Can you please check the modifications table on RDS, do you see any records. Will you be able to share the entire logs, wondering if there are any other errors.

[avatarworf]

@maheshp There are some other errors, during the import around java.lang.RuntimeException: page[21915] data leaf [table
https://gist.github.com/avatarworf/78ba8d06c8e0b85ab0123a2c6ad42bb3
and there is nothing in the modifications table within the Postgres DB

Interestingly, when accessing the H2 database and trying to select from MODIFICATIONS i generate the same error; So it looks like this table is somehow corrupted?

sql> select * from MODIFICATIONS;
Error: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.RuntimeException: page[21915] data leaf table:131 MODIFIEDFILES entries:18 parent:20654 keys:[139427, 139428, 139429, 139430, 139431, 139432, 139433, 139434, 139435, 139436, 139437, 139438, 139439, 139440, 139441, 139442, 139443, 139444] offsets:[1982, 1931, 1880, 1829, 1778, 1727, 1680, 1614, 1521, 1422, 1326, 1230, 1165, 1095, 1021, 942, 877, 807] parent 20654 expected 14845"; SQL statement:
select * from MODIFICATIONS [50000-200]

[maheshp]

Looks like H2 db is corrupted, I am seeing the following errors,

Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: File corrupted while reading record: "page[21913] b-tree leaf table:141

I am assuming you are running the migration against a backup db of your GoCD server. If you have the GoCD server running, can you please take a backup and try to run the migration using that.

And I hope you are following the instructions provided in the upgrade docs

[luanadotcom]

Thanks @maheshp - that MODIFICATIONS table was corrupted on our development box. We took the backup from the production one and once the H2 database was clean the migration script went through fine and we were able to upgrade.