Conflict on insert gets translated to DataIntegrityViolationException instead of DuplicateKeyException in Spring 6

[lukas-krecan]

Followup from #29511

If there is primary key conflict on insert, Spring 6.0.3 returns DataIntegrityViolationException instead of DuplicateKeyException as it did in Spring 5. It would make sense to me to implement similar fix as you did for #29511. The SQL state seems to be 23000.

[jhoeller]

I suppose this is coming from MySQL/MariaDB, MS SQL and Oracle, as per your comment on the other issue?

23000 is a general constraint violation error. I suspected that Oracle might report that SQL state already, with its old error code 1 attached. But for MySQL, I thought I saw it supporting 23505 but must have mis-read that. MariaDB inherited that legacy from MySQL, I suppose. All of them really should be reporting 23505 instead, there is an old enhancement request for MySQL for it that never got addressed.

Anyway, it would be helpful to analyze the exact exception reported by the JDBC driver for those cases, ideally with exception class, SQL state, error code and exception message. If you have the chance to check the remaining databases for those exception details, that would be super helpful. We'll try to integrate corresponding detection rules then since those databases are unlikely to fix their SQL state.

[lukas-krecan]

Hi,

• MariaDB - state=23000 errorCode=1062 java.sql.SQLIntegrityConstraintViolationException: (conn=9) Duplicate entry 'my-lock' for key 'PRIMARY'
• MySQL - state=23000 errorCode=1062 java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'my-lock' for key 'shedlock.PRIMARY'
• MsSQL - state=23000 errorCode=2627 com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__shedlock__72E12F1A058F9D72'. Cannot insert duplicate key in object 'dbo.shedlock'. The duplicate key value is (my-lock).
• Oracle - state=23000 errorCode=1 java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST.SYS_C008305) violated

Postgres, H2, HSQL, DB2 look good (return 23505)

[jhoeller]

Thanks, @lukas-krecan! I've got a change ready that specifically looks at those error codes within SQL state 23000, along the following lines:

	static boolean indicatesDuplicateKey(@Nullable String sqlState, int errorCode) {
		return ("23505".equals(sqlState) ||
				("23000".equals(sqlState) &&
						(errorCode == 1 || errorCode == 1062 || errorCode == 2627)));
	}

As far as I was able to find out, those error codes are vendor-specific but documented at least, and not overlapping with error codes in other affected databases - in particular not within SQL state 23000. From that perspective, the above is a pragmatic solution for the time being, within a specific SQL state condition - and still avoiding database production name detection and the parsing of an error codes mapping file, so still a significant improvement over using sql-error-codes.xml by default.

To be committed tomorrow.