Affects: 6.0.0
Hi, I just upgraded from 5.3.24 to 6.0.0 and noticed a different behaviour of Spring JDBC/TX. I have a class containing simple JDBC statements executed with JdbcTemplate (no JPA, no ORMs), and I wrote some test cases based on H2.
In case of duplicate key errors, I expect DuplicateKeyException, but after the upgrade I'm getting a more generic DataIntegrityViolationException (that obviously breaks the test):
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO "STORAGE" (filename, last_modified, compressed, file_contents) VALUES (?, ?, ?, ?)]; Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_B ON PUBLIC.STORAGE(FILENAME) VALUES ( /* 1 */ 'myfile.txt' )"; SQL statement:
INSERT INTO "STORAGE" (filename, last_modified, compressed, file_contents) VALUES (?, ?, ?, ?) [23505-214]
Looking into the sources, I noticed that v5 instantiates a DuplicateKeyException in SQLErrorCodeSQLExceptionTranslator while v6 instantiates a DataIntegrityViolationException in SQLExceptionSubclassTranslator.
- My code: https://github.com/albertus82/simple-jdbc-filestore/blob/spring6/src/main/java/io/github/albertus82/filestore/jdbc/SimpleJdbcFileStore.java#L273
- Test: https://github.com/albertus82/simple-jdbc-filestore/blob/spring6/src/test/java/io/github/albertus82/filestore/jdbc/SimpleJdbcFileStoreTest.java#L188
- Failure log: https://github.com/albertus82/simple-jdbc-filestore/actions/runs/3492195047/jobs/5845701283#step:4:13616
Thank you.
Comment From: sbrannen
This is to be expected due to the switch to using SQLExceptionSubclassTranslator by default.
See:
- commit 083113d8a4ce1220ee4ee7e21395830e34ea8450
-
28216
If you absolutely need to differentiate between DataIntegrityViolationException and DuplicateKeyException (which is a subclass of DataIntegrityViolationException), you can configure your JdbcTemplate to use SQLErrorCodeSQLExceptionTranslator instead.
For example:
DataSource dataSource = ...
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setExceptionTranslator(new SQLErrorCodeSQLExceptionTranslator(dataSource));
jdbcTemplate.afterPropertiesSet();
In light of the above, I am closing this issue as "works as designed"; however, I have also created #29518 to update the Upgrading to Spring Framework 6.x wiki page.
Comment From: albertus82
Hi @sbrannen,
I cannot set anything on JdbcTemplate because my class is part of a library that depends on the JdbcOperations provided by the client. However this new behaviour seems to me a slight regression: I could catch DataIntegrityViolationException but how to differentiate a duplicate key violation from other substantially different violations without binding my library to a specific RDBMS?
Thanks a lot.
Comment From: sbrannen
I cannot set anything on
JdbcTemplatebecause my class is part of a library that depends on theJdbcOperationsprovided by the client.
Understood.
However this new behaviour seems to me a slight regression:
Technically speaking it is indeed a regression in the default behavior, but there are ways to get the old behavior back (the one I mentioned previously and one that I'll mention below).
I could catch
DataIntegrityViolationExceptionbut how to differentiate a duplicate key violation from other substantially different violations without binding my library to a specific RDBMS?
I believe you should be able to introduce an empty sql-error-codes.xml file in the root of the classpath to achieve the same result.
If neither of these options works for you, please post back here, and we will continue the discussion.
Comment From: albertus82
Fixed adding the following "empty" sql-error-codes.xml file in the root of the classpath:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "https://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans />
Thank you again.
Comment From: lukas-krecan
As a library author, I can't use any of the mentioned workarounds
Comment From: sbrannen
Reopening to improve documentation.
See https://github.com/spring-projects/spring-framework/issues/29673#issuecomment-1345383721
Comment From: jhoeller
As a documentation issue for the default exception translator change in 6.0, we should highlight DuplicateKeyException specifically indeed since that is a common exception subclass not covered by standard JDBC 4 exception subclasses (SQLExceptionSubclassTranslator), and not by SQL state analysis (in our fallback SQLStateSQLExceptionTranslator) either. Further such non-covered sub-subclasses are CannotAcquireLockException and DeadlockLoserDataAccessException.
That said, if these turn out to be common enough scenarios, we can try to make DuplicateKeyException and also CannotAcquireLockException etc work for common databases in our new default arrangement, e.g. through discovering database-specific exception variants in SQLExceptionSubclassTranslator or even by some hard-coded error code checks. However, all of this would have to be covered through programmatic checks rather than an XML file to be parsed by default, and also without having to discover the database product name through a live JDBC connection check first.
Comment From: jhoeller
It turns out that all databases that I checked support 23505 as a common SQL state value for duplicate keys, within the general 23xxx range that indicates data integrity violations. I'm therefore adding a specific 23505 check to SQLExceptionSubclassTranslator and also to SQLStateSQLExceptionTranslator, within the corresponding subclass/state range. While this may not cover every potential scenario, I expect it to cover the common duplicate key cases out there. If this is not sufficient, let me know; 6.0.3 snapshot builds including this change will be available for early testing soon.
Of course, re-enabling the SQLErrorCodeSQLExceptionTranslator with its legacy mappings file or providing a custom sql-error-codes.xml file still remains as a workaround in any case, providing database-specific error codes which require database product name detection at runtime.
From the perspective above, I'm turning this ticket into a regression issue that we will address in the 6.0.3 release, also documenting the general default change and its potential impact a bit better in the upgrade notes.
Comment From: lukas-krecan
Hi, the fix did not help with confilct on insert. In MariaDb, MySQL, MS SQL and Oracle the SQL state is 23000.
Comment From: sbrannen
@lukas-krecan, this issue is closed, and the changes have already been released in Spring Framework 6.0.3.
Please create a new issue to discuss the "conflict on insert".
Thanks