Exception translation for (H2) batchUpdate constraint violations has changed in Spring Boot 3.5.7

[advptr]

When a NamedParameterJdbcTemplate.batchUpdate operation violates a data constraint, the method used to throw a DataIntegrityViolationException with a java.sql.BatchUpdateException as the cause

Since upgrading to Spring Boot 3.5.7, this behavior has changed. When using H2, a constraint violation now raises a DataIntegrityViolationException with a org.h2.jdbc.JdbcSQLDataException as the cause.

This is problematic in some cases since there's no way to identify which rows in the batch that actually failed.

[mp911de]

Do you have a stack trace handy? I suspect that H2 has changed its exceptions and it might be that this ticket should rather target Spring Framework as we're using SQLExceptionTranslator infrastructure.

[advptr]

I've created a small test to reproduce (kotlin, h2), and it works with SB 3.5.6. Thought the same h2 version was used but the stack-traces indicates something else...

The test inserts 2 rows, and one with a column value that is too long, the following stack-trace is produced with SB 3.5.7:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TEST(X) VALUES(?)]; Value too long for column "X CHARACTER VARYING(2)": "'nok' (3)"; SQL statement:
INSERT INTO TEST(X) VALUES(?) [22001-232]
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:100)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:701)
	at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1049)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:377)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:394)
	at test.ReproduceBachUpdateTest.expect batch update exception(ReproduceBachUpdateTest.kt:28)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:775)
	at (snip)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: org.h2.jdbc.JdbcSQLDataException: Value too long for column "X CHARACTER VARYING(2)": "'nok' (3)"; SQL statement:
INSERT INTO TEST(X) VALUES(?) [22001-232]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:518)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.getValueTooLongException(DbException.java:334)
	at org.h2.value.Value.getValueTooLongException(Value.java:2612)
	at org.h2.value.Value.convertToVarchar(Value.java:1287)
	at org.h2.value.Value.convertTo(Value.java:1143)
	at org.h2.value.Value.convertForAssignTo(Value.java:1118)
	at org.h2.table.Column.validateConvertUpdateSequence(Column.java:406)
	at org.h2.table.Table.convertInsertRow(Table.java:963)
	at org.h2.command.dml.Insert.insertRows(Insert.java:167)
	at org.h2.command.dml.Insert.update(Insert.java:135)
	at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74)
	at org.h2.command.CommandContainer.update(CommandContainer.java:139)
	at org.h2.command.Command.executeUpdate(Command.java:304)
	at org.h2.command.Command.executeBatchUpdate(Command.java:271)
	at org.h2.jdbc.JdbcPreparedStatement.executeBatchInternal(JdbcPreparedStatement.java:1318)
	at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1267)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$getPreparedStatementCallback$7(JdbcTemplate.java:1617)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
	... 78 more

With SB 3.5.6 the stack-trace looks like:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TEST(X) VALUES(?)]; Value too long for column "X CHARACTER VARYING(2)": "'nok' (3)"; SQL statement:
INSERT INTO TEST(X) VALUES(?) [22001-232]
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:120)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:701)
	at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1049)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:377)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:394)
	at test.ReproduceBachUpdateTest.expect batch update exception(ReproduceBachUpdateTest.kt:28)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:775)
	at (snip)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: org.h2.jdbc.JdbcBatchUpdateException: Value too long for column "X CHARACTER VARYING(2)": "'nok' (3)"; SQL statement:
INSERT INTO TEST(X) VALUES(?) [22001-232]
	at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1277)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$getPreparedStatementCallback$7(JdbcTemplate.java:1617)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
	... 78 more

[mp911de]

Thanks a lot. It seems that Framework has updated its batch exception handling in version 6.2.12 (see also spring-projects/spring-framework#35547). Please file a ticket at https://github.com/spring-projects/spring-framework/issues, feel free to link both so you don't need to duplicate stack traces etc.

I used the following code as reproducer:

template.update("CREATE TABLE test (X varchar(2))", Map.of());
template.batchUpdate("INSERT INTO test(X) VALUES(:foo)", new Map[] { Map.of("foo", "bar") });