Hi Team, primary key inserting as null when generatortype.identity from 2.0.202 but it was worked for 1.04.200

[up185014]

actuval behaviour:

It should not insert null as a primary key and when generatortype is identity

expected behaviour:

it should support generatortype is identity and doesn't create null to insert into DB.

it was worked for 1.4.200 but not working from 2.0.202.

exception is below:

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
	
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
	
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: 
NULL not allowed for column "TEST_KEY"; SQL statement:
insert into TEST(TEST_key, TEST_id, TESTTIME, TEST_off, TEST_on) values (null, ?, ?, ?, ?) [23502-206]

[lukaseder]

Thanks a lot for your report. Since you mention JPA, am I right in assuming you're using the JPADatabase to generate jOOQ code, and perhaps, the identity information is now missing from the jOOQ-generated code?

To be sure I'll be fixing the right thing, I'd love to see some actual reproducer. In the issue template, we're asking for an MCVE (template here: https://github.com/jOOQ/jOOQ-mcve), which would be perfect for this. But some sample code (code generation configuration, jOOQ code, your actual query, etc.) would also be helpful

[katzyn]

Hibernate ORM generates invalid INSERT statement for H2 for unknown reason where NULL is used instead of DEFAULT for identity columns. This issue was already fixed on their side, but only in the main branch for upcoming Hibernate ORM 6.0.

H2, since the version 2.0.4, has a LEGACY compatibility mode, it this mode H2 accepts some wrong commands that were incorrectly allowed in H2 1.4.200.

[lukaseder]

Thanks for chiming in, @katzyn. So the query is Hibernate generated? I was assuming it might have been a jOOQ native query...

[up185014]

Hibernate ORM generates invalid INSERT statement for H2 for unknown reason where NULL is used instead of DEFAULT for identity columns. This issue was already fixed on their side, but only in the main branch for upcoming Hibernate ORM 6.0.

H2, since the version 2.0.4, has a LEGACY compatibility mode, it this mode H2 accepts some wrong commands that were incorrectly allowed in H2 1.4.200.

[up185014]

Hi @katzyn i have seen few fixes in version 2.1.210 which is similar to this issue are Issue #3321,PR #3381.

Issue #3321 is specifically calling out null insertion here where agreed it as bug.
h2database/h2database#3321

hope its an issue from h2 rather than from hibernate orm

[katzyn]

h2database/h2database#3321 isn't related in any way. It is about a bug in ALTER TABLE command.

---

insert into TEST(TEST_key, TEST_id, TESTTIME, TEST_off, TEST_on) values (null, ?, ?, ?, ?)

is a wrong command and it is rejected by H2 2.x as it should. H2 has a LEGACY compatibility mode, you can enable it by appending ;MODE=LEGACY to JDBC URL, but you need to upgrade H2 from 2.0.202 to a newer version. In that mode such commands will be accepted. You can use this workaround until release of Hibernate ORM 6 or until fixes from it will be backported to 5.6.

[up185014]

@katzyn I really appreciate and thank you for your quickresponse and fix on it. now its working fine with ;MODE=LEGACY configuration.

[lukaseder]

Since jOOQ does not seem to be involved in this particular interaction, I'm closing this issue.