DropPrimaryKeyTask fails in SQL Server with newer versions of HAPI due to multipart primary keys

[XcrigX]

When running the CLI migrate on a HAPI 7.4.x instance, it looks like multipart primary keys have been added (at some point), which causes the generate primary key drop statement to fail.

The error generated from running the db migrate is:

2025-01-24 11:24:31.41 [main] INFO  c.u.f.j.m.t.DropPrimaryKeyTask 7.4.0.20240618.6: Going to DROP the PRIMARY KEY on table HFJ_RES_SEARCH_URL
2025-01-24 11:24:31.43 [main] ERROR c.u.f.j.m.HapiMigrator Migration failed
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
        at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:193)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:890)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:916)
        at ca.uhn.fhir.jpa.migrate.taskdef.DropPrimaryKeyTask.generateSql(DropPrimaryKeyTask.java:60)
        at ca.uhn.fhir.jpa.migrate.taskdef.DropPrimaryKeyTask.doExecute(DropPrimaryKeyTask.java:80)
        at ca.uhn.fhir.jpa.migrate.taskdef.BaseTask.execute(BaseTask.java:276)
        at ca.uhn.fhir.jpa.migrate.HapiMigrator.executeTask(HapiMigrator.java:209)
        at ca.uhn.fhir.jpa.migrate.HapiMigrator.migrate(HapiMigrator.java:178)
        at ca.uhn.fhir.cli.BaseFlywayMigrateDatabaseCommand.run(BaseFlywayMigrateDatabaseCommand.java:151)
        at ca.uhn.fhir.cli.HapiFlywayMigrateDatabaseCommand.run(HapiFlywayMigrateDatabaseCommand.java:55)
        at ca.uhn.fhir.cli.BaseApp.run(BaseApp.java:272)
        at ca.uhn.fhir.cli.App.main(App.java:42)
2025-01-24 11:24:31.43 [main] ERROR ca.uhn.fhir.cli.App Error during execution:
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
        at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:193)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:890)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:916)
        at ca.uhn.fhir.jpa.migrate.taskdef.DropPrimaryKeyTask.generateSql(DropPrimaryKeyTask.java:60)
        at ca.uhn.fhir.jpa.migrate.taskdef.DropPrimaryKeyTask.doExecute(DropPrimaryKeyTask.java:80)
        at ca.uhn.fhir.jpa.migrate.taskdef.BaseTask.execute(BaseTask.java:276)
        at ca.uhn.fhir.jpa.migrate.HapiMigrator.executeTask(HapiMigrator.java:209)
        at ca.uhn.fhir.jpa.migrate.HapiMigrator.migrate(HapiMigrator.java:178)
        at ca.uhn.fhir.cli.BaseFlywayMigrateDatabaseCommand.run(BaseFlywayMigrateDatabaseCommand.java:151)
        at ca.uhn.fhir.cli.HapiFlywayMigrateDatabaseCommand.run(HapiFlywayMigrateDatabaseCommand.java:55)
        at ca.uhn.fhir.cli.BaseApp.run(BaseApp.java:272)
        at ca.uhn.fhir.cli.App.main(App.java:42)
2025-01-24 11:24:31.43 [Thread-0] INFO  ca.uhn.fhir.cli.App HAPI FHIR is shutting down...

This code appears to be the culprit:

hapi-fhir/hapi-fhir-sql-migrate/src/main/java/ca/uhn/fhir/jpa/migrate/taskdef/DropPrimaryKeyTask.java

Lines 126 to 129 in 251eee8

	return "SELECT tc.constraint_name " + "FROM information_schema.table_constraints tc "
	+ "JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name "
	+ "WHERE tc.constraint_type = 'PRIMARY KEY' "
	+ "AND tc.table_name = ?";

It appears to be unnecessarily joining the information_schema.constraint_column_usage table, which has two rows for this Primary key (partition_id and res_search_url) and thus gets 2 rows in the resultset instead of 1.
But if it just needs the primary key constraint name it shouldn't need to join to the constraint_column_usage table at all?

Here is what my database shows for these tables:

[XcrigX]

More complete screenshot of the data in the info schema tables:

[XcrigX]

Steps I used to reproduce:

Install MSSQL in a local docker instance:

docker pull mcr.microsoft.com/mssql/server:2022-latest

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Test1234" -p 1433:1433 --name sql1 --hostname sql1 -d mcr.microsoft.com/mssql/server:2022-latest

Connect to the local sql server DB using SQL Server Management Studio (SSMS) (free download).

Create a database called 'hapi' in the SSMS ui.

Start a HAPI 7.4.0 JPA instance connected to the Docker sql db to initialize the DB.

Datasource setting in application.yaml:

  datasource:
    url: jdbc:sqlserver://localhost:1433;instance=sql1;database=hapi;encrypt=true;trustServerCertificate=true;loginTimeout=20
    username: sa
    password: Test1234
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    max-active: 15

 jpa:
    properties:
      hibernate.dialect: org.hibernate.dialect.SQLServer2012Dialect

Run the 7.6.0+ CLI:
hapi-fhir-cli migrate-database -d MSSQL_2012 -n "sa" -p "Test1234" -u "jdbc:sqlserver://localhost:1433;instance=sql1;database=hapi;encrypt=true;trustServerCertificate=true;loginTimeout=20" --dry-run

[tadgh]

Heya! I notice you are using the default dialect, whereas HAPI-FHIR uses HapiFhirSQLServerDialect (located here:

hapi-fhir/hapi-fhir-jpa/src/main/java/ca/uhn/fhir/jpa/model/dialect/HapiFhirSQLServerDialect.java

Line 30 in 7090c3f

public class HapiFhirSQLServerDialect extends SQLServerDialect implements IHapiFhirDialect {

)

Are you able to replicate with this dialect? If so, let me know, as we internally are unable to replicate at the moment. For what its worth, either way, i think your fix is valid and we can likely get it merged, just wanted to check with you first for this replication. Cheers

[XcrigX]

Hi @tadgh. I wiped out my SQL database (deleted the container and recreated it) and restarted a new HAPI JPA instance with the hapi sql dialect set to:
"hibernate.dialect: ca.uhn.fhir.jpa.model.dialect.HapiFhirSQLServerDialect"

I let it init the database, then stopped the FHIR server, then ran the CLI migrate - I still see the error.

To be clear, when I'm running the CLI migrate utility, I am not specifying a dialect, only the "-d MSSQL_2012" parameter - which corresponds to the driver name, not the dialect.

But I wouldn't think the dialect should affect this anyway. If the HFJ_RES_SEARCH_URL table has a multi part primary key defined, it looks like SQL Server will create two rows in the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE table for it, which should lead to the error.
The dialect wouldn't change how SQL Server represents the index. And the CLI migrate is constructing the SQL to query the INFORMATION_SCHEMA tables explicitly, so the dialect wouldn't change that either.

FWIW, I did check an older version of a HAPI server (v7.0.2). and it does NOT have a multipart Pkey for 'HFJ_RES_SEARCH_URL', so I assume the partition_id column was added to that primary key fairly recently.

In my error case the DB was initialized from a 7.4.0 HAPI JPA instance, and I'm trying to migrate to 7.6.0.

Anyway, if the instance you are testing against returns 2 rows for the PKEY with this query, I'd think you should see the error:
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name = 'HFJ_RES_SEARCH_URL'

[tadgh]

OK, thanks for doing the replication there. We are trying to replicate still via our closed source wrapper with no success, but I will try it with the jpaserver-starter wrapper. Maybe there's some discrepancy there.

[XcrigX]

I was running from a fork, so I reset back to basics to double-check and still see the error.

From the JPA starter, I pulled from master, then:
git checkout image/v7.4.0

Then:
mvn clean package spring-boot:repackage -DskipTests=true -Pboot

Then I'm taking the root.war and copying to a temp directory that has my application.yaml and running with:
java -jar root.war

Anyway.. if that helps..