Dialect/db bug

[jkiddo]

When running docker run -p 8080:8080 -e hapi.fhir.default_encoding=json -e "--spring.config.location=https://gist.githubusercontent.com/jkiddo/bf86297d5326b285877bea468f664fc5/raw/44114e4a440daac6aa45efe280cba191a404ad89/application.yaml" hapiproject/hapi:v5.7.0

Configuration can be seen on https://gist.github.com/jkiddo/bf86297d5326b285877bea468f664fc5

Bug is also present on other db's than H2.

I get the following error:

2022-03-08 16:35:02.925 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:142] Value too long for column "BINARY VARYING": "1f8b08000000000000007c7bc79683ca92edbfdc29bd165e829ee1bdf7ccf0de235cfffca3ce3de3... (1915111)" [22001-210]
2022-03-08 16:35:11.182 [main] ERROR o.a.c.c.C.[Tomcat].[localhost].[/] [DirectJDKLog.java:175] Servlet.init() for servlet [jpaRestfulServer] threw exception
org.springframework.dao.DataIntegrityViolationException: could not insert: [ca.uhn.fhir.jpa.model.entity.ResourceHistoryTable]; SQL [insert into hfj_res_ver (partition_date, partition_id, res_deleted_at, res_version, has_tags, res_published, res_updated, res_encoding, res_text, res_id, res_text_vc, res_type, res_ver, pid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.DataException: could not insert: [ca.uhn.fhir.jpa.model.entity.ResourceHistoryTable]

Is it because of a malformed IG or is it HFJ_RES_VER that needs fixing?

[jamesagnew]

Just speculating (but unlike all other DBs we support) H2 requires you to declare the maximum length for LOB columns when you create the schema. We've never worried about it too much since H2 is only for testing.. If you're inserting a huge resource that could be the reason. You might need to put a breakpoint into H2 to know for sure though..

[jkiddo]

I gave it a shot with postgres and got another error:

fhir-server_1  | 2022-03-08 19:40:37.542 [main] ERROR o.h.e.j.batch.internal.BatchingBatch [BatchingBatch.java:130] HHH000315: Exception executing batch [java.sql.BatchUpdateException: Batch entry 0 insert into npm_package_ver (current_version, pkg_desc, desc_upper, fhir_version, fhir_version_id, package_pid, binary_res_id, package_id, package_size_bytes, saved_time, updated_time, version_id, pid) values ('TRUE', 'Gravitate Health is an EU project aiming to empower and equip Europeans with health information for active personal health management and adherence to treatment. (built Fri, Mar 4, 2022 17:30+0000...', 'GRAVITATE HEALTH IS AN EU PROJECT AIMING TO EMPOWER AND EQUIP EUROPEANS WITH HEALTH INFORMATION FOR ACTIVE PERSONAL HEALTH MANAGEMENT AND ADHERENCE TO TREATMENT. (BUILT FRI, MAR 4, 2022 17:30+0000...', 'R5', '5.0.0-snapshot1', 1, 1, 'hl7.eu.fhir.gh', 1902912, '2022-03-08 19:40:35.473+00', '2022-03-08 19:40:35.477+00', '0.1.0', 1) was aborted: ERROR: value too long for type character varying(10)  Call getNextException to see other errors in the batch.], SQL: insert into npm_package_ver (current_version, pkg_desc, desc_upper, fhir_version, fhir_version_id, package_pid, binary_res_id, package_id, package_size_bytes, saved_time, updated_time, version_id, pid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
fhir-server_1  | 2022-03-08 19:40:37.543 [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:137] SQL Error: 0, SQLState: 22001

Looking more closely at the entity definitions in core if found the following:

https://github.com/hapifhir/hapi-fhir/blob/cde39dcc6caba4cb12dff6b6949570a794e9129d/hapi-fhir-jpaserver-model/src/main/java/ca/uhn/fhir/jpa/model/entity/NpmPackageVersionEntity.java#L84

which I think is the reason for the bug as the IG that I load into HAPI is based on R5 => 5.0.0-snapshot1which is a bit longer than the 10 chars allowed in the FHIR_VERSION_ID field in the database

[jamesagnew]

Hmm, that's a different issue than the one above for sure.

Agree with your assessment, the cause is that field's max length. Looks like we should probably double that.

FWIW the fix is just to bump that length from 10 to 20 (?), and add a migration task to HapiFhirJpaMigrationTasks.

[jkiddo]

So something like the following (for PG):

		version.addTableRawSql("20220309.1", "NPM_PACKAGE_VER")
			.addSql(DriverTypeEnum.POSTGRES_9_4, "alter table NPM_PACKAGE_VER ALTER COLUMN fhir_version_id TYPE varchar(20)");
		version.addTableRawSql("20220309.2", "NPM_PACKAGE_VER_RES")
			.addSql(DriverTypeEnum.POSTGRES_9_4, "alter table NPM_PACKAGE_VER_RES ALTER COLUMN fhir_version_id TYPE varchar(20)");

[jamesagnew]

You'd want it to apply to all databases, so it would be something more like this one:

version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT")
	.modifyColumn("20210617.1","TARGET_DISPLAY").nullable().withType(ColumnTypeEnum.STRING, 500);

..as well as of course updating the Hibernate annotation.

[jamesagnew]

FYI I just tripped over the same bug reported originally in this ticket.

It looks like H2 now imposes a maximum length of 1 MiB for LOBs provided without streaming. I don't understand why, I'm going to file a ticket on their tracker.

[jamesagnew]

https://github.com/h2database/h2database/issues/3457

[jkiddo]

Would you like a PR to 6.0.0 or to a 5.7.X?