MySQL Bugs: #114016: CompoundCacheKey does not include generatedKey

Bug #114016	CompoundCacheKey does not include generatedKey
Submitted:	16 Feb 2024 13:00	Modified:	19 Mar 2024 8:35
Reporter:	JungHoon Lee	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
cachePrepStmts, useServerPrepStmts are being used.

schema is as follows:

CREATE TABLE `car` (
  `carNo` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` ENUM('SEDAN','SUV','VAN','TRUCK') NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`carNo`)
) ENGINE=InnoDB;

There is an insert statement (B) that fails by entering NULL and an insert statement that succeeds. The insert statement that succeeds (A) uses RETURN_GENERATED_KEYS.

If you run A and B in that order, B will fail.
The failure is expected, the problem is not a SQLIntegrityConstraintViolationException but a "java.lang.RuntimeException: java.sql.SQLException: Operation not allowed after ResultSet closed".

This occurs because RETURN_GENERATED_KEYS is not included in CompoundCacheKey.

I am using spring-data-jdbc, but the stmt cache is running in the driver, so I am reporting it here.

How to repeat:
A:

[PREPARE] INSERT INTO car (type, created_at) VALUES (?, now())
[EXECUTE] INSERT INTO car (type, created_at) VALUES ('SUV', now())

A bulk mode:

[PREPARE] INSERT INTO car (type, created_at) VALUES (?, now()),(?, now()),(?, now())
[EXECUTE] INSERT INTO car (type, created_at) VALUES ('VAN', now()),('SUV', now()),('SEDAN', now())

Suggested fix:
I think CompoundCacheKey needs to be redesigned. This is because sql is the same, but the properties may be different.

Hello JungHoon Lee,

Thank you for the report and feedback.
May I request you to provide a test case(.java) which manifest the issue?

Thank you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".