Bug #114016 CompoundCacheKey does not include generatedKey
Submitted: 16 Feb 13:00 Modified: 19 Mar 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: MySQL Verification Team CPU Architecture:Any

[16 Feb 13:00] JungHoon Lee 
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.
[19 Feb 8:35] MySQL Verification Team 
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.
[20 Mar 1:00] Bugs System 
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".