Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Duplicate key violation gets translated to DataIntegrityViolationException instead of DuplicateKeyException in Spring 6 #29511

Closed
albertus82 opened this issue Nov 17, 2022 · 10 comments
Assignees
Labels
in: data Issues in data modules (jdbc, orm, oxm, tx) type: regression A bug that is also a regression type: wiki-documentation A documentation update required on the wiki
Milestone

Comments

@albertus82
Copy link

albertus82 commented Nov 17, 2022

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.

Thank you.

@spring-projects-issues spring-projects-issues added the status: waiting-for-triage An issue we've not yet triaged or decided on label Nov 17, 2022
@sbrannen sbrannen self-assigned this Nov 18, 2022
@sbrannen sbrannen added in: data Issues in data modules (jdbc, orm, oxm, tx) status: invalid An issue that we don't feel is valid and removed status: waiting-for-triage An issue we've not yet triaged or decided on labels Nov 18, 2022
@sbrannen
Copy link
Member

This is to be expected due to the switch to using SQLExceptionSubclassTranslator by default.

See:

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.

@sbrannen sbrannen closed this as not planned Won't fix, can't repro, duplicate, stale Nov 18, 2022
@albertus82
Copy link
Author

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.

@sbrannen
Copy link
Member

I cannot set anything on JdbcTemplate because my class is part of a library that depends on the JdbcOperations provided 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 DataIntegrityViolationException but 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.

albertus82 added a commit to albertus82/relatable-storage that referenced this issue Nov 18, 2022
albertus82 added a commit to albertus82/relatable-storage that referenced this issue Nov 18, 2022
@albertus82
Copy link
Author

albertus82 commented Nov 18, 2022

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.

@lukas-krecan
Copy link
Contributor

As a library author, I can't use any of the mentioned workarounds

@sbrannen
Copy link
Member

Reopening to improve documentation.

See #29673 (comment)

@sbrannen sbrannen reopened this Dec 10, 2022
@sbrannen sbrannen added this to the 6.0.3 milestone Dec 10, 2022
@sbrannen sbrannen added type: documentation A documentation task type: wiki-documentation A documentation update required on the wiki and removed status: invalid An issue that we don't feel is valid labels Dec 10, 2022
@jhoeller
Copy link
Contributor

jhoeller commented Dec 12, 2022

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.

@jhoeller
Copy link
Contributor

jhoeller commented Dec 12, 2022

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.

@lukas-krecan
Copy link
Contributor

Hi, the fix did not help with confilct on insert. In MariaDb, MySQL, MS SQL and Oracle the SQL state is 23000.

@sbrannen
Copy link
Member

@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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: data Issues in data modules (jdbc, orm, oxm, tx) type: regression A bug that is also a regression type: wiki-documentation A documentation update required on the wiki
Projects
None yet
Development

No branches or pull requests

5 participants