Result set larger than one row when Corda node use postgresql and schema

Question

I'm currently trying to have 2 node (one notary , one normal node) using the same Postgresql DB for storing data (instead of the embedded H2), but with a different schema for each (node schema, notary schema) .

Ah , I'm using the non-enterprise version btw, version 4.3 .

The notary node is the first one to start, it creates the needed tables in the notary schema , all seems fine there .

But when the 'normal' node boot, it crash saying :

[ERROR] 10:17:50+0000 [main] changelog.ChangeSet. - Change Set migration/node-core.changelog-postgres-blob.xml::modify checkpoint_value column type::R3.Corda failed.  Error: Migration failed for change set mig │
│      Reason:                                                                                                                                                                                                      │
│           master.changelog.json : liquibase.precondition.core.SqlPrecondition@7c682e26 : Result set larger than one row                                                                                           │
│  {changeSet=migration/node-core.changelog-postgres-blob.xml::modify checkpoint_value column type::R3.Corda, databaseChangeLog=master.changelog.json}                                                              │
│ [ERROR] 10:17:50+0000 [main] internal.NodeStartupLogging. - Exception during node registration: Could not create the DataSource: Migration failed for change set migration/node-core.changelog-postgres-blob.xml: │
│      Reason:                                                                                                                                                                                                      │
│           master.changelog.json : liquibase.precondition.core.SqlPrecondition@7c682e26 : Result set larger than one row                                                                                           │
│  [errorCode=14m37zv, moreInformationAt=https://errors.corda.net/OS/4.3/14m37zv]                                                                                                                                   │
[ERROR] 10:17:50+0000 [main] internal.NodeStartupLogging. - Exception during node startup: net.corda.core.utilities.Try$Failure cannot be cast to net.corda.core.utilities.Try$Success [errorCode=rmnkpl, moreInf │

The node.conf I have for the notary is the following :

dataSourceProperties: {
    dataSourceClassName="org.postgresql.ds.PGSimpleDataSource"
    dataSource.url="jdbc:postgresql://psql-path:5432/postgres?currentSchema=notary"
    dataSource.user="dbUser"
    dataSource.password="xxxxxx"
}

And the one for the 'normal' node is :

dataSourceProperties: {
    dataSourceClassName="org.postgresql.ds.PGSimpleDataSource"
    dataSource.url="jdbc:postgresql://psql-path:5432/postgres?currentSchema=node"
    dataSource.user="dbUser"
    dataSource.password="xxxxxx"
}

So as you can see, the only difference is the ?currentSchema value .

Am I missing something ?

EDIT :

in the doc , they are saying :

If your PostgresSQL database is hosting multiple schema instances (using the JDBC URL currentSchema=my_schema) for different Corda nodes, you will need to create a hibernate_sequence sequence object manually for each subsequent schema added after the first instance. Corda doesn’t provision Hibernate with a schema namespace setting and a sequence object may be not created. Run the DDL statement and replace my_schema with your schema namespace:

I have done that and when connecting to the db , I can see taht for both schema I have tables created . Though for the notary I have 32 tables and only 26 for the 'normal' node .

tbrunain tbrunain · Accepted Answer · 2020-02-20T07:17:27

Classic case of documentation overlooking , the solution is :

If your PostgresSQL database is hosting multiple schema instances (using the JDBC URL currentSchema=my_schema) for different Corda nodes, you will need to create a hibernate_sequence sequence object manually for each subsequent schema added after the first instance. Corda doesn’t provision Hibernate with a schema namespace setting and a sequence object may be not created. Run the DDL statement and replace my_schema with your schema namespace:

Result set larger than one row when Corda node use postgresql and schema

2 Answers