MySQL Bugs: #100747: AUTO_INCREMENT IDs fail in updatable prepared statements with useServerPrepStmt

Bug #100747	AUTO_INCREMENT IDs fail in updatable prepared statements with useServerPrepStmt
Submitted:	4 Sep 2020 20:25	Modified:	5 Oct 2020 21:12
Reporter:	Björn Voigt (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, jdbc, prepared statement, ResultSet

Description:
All Connector/J 8.0 versions (tested with 8.0.17, 8.0.19, 8.0.20, 8.0.21) fail to retrieve AUTO_INCREMENT IDs when the inserted row is used in a updatable prepared statement when the connection option 'useServerPrepStmt' is set.

Connector/J 5.1 (tested with 5.1.48 and 5.1.49) does not have this bug.

The bug seems to be independent of the MySQL server. I tested with MySQL 5.6.49 and MariaDB 10.4.13.

How to repeat:
Short version:
1. Download MySQL Connector/J 8.0.21
2. Update username/password and compile the test program (attached in next comment)
3. Execute the test program

Long version:
1. Download MySQL Connector/J 8.0.21
2. Create a table with at least an AUTO_INCREMENT column and another column
3. Insert at least one row in a table
4. Make a JDBC connection with option 'useServerPrepStmt=true'
5. Create a prepared statement: SELECT * FROM table ...
6. Update at least one row in the ResultSet
7. Insert a now row
8. Try to retrieve the generated ID (SQL Exception "Exception in thread "main" java.lang.NumberFormatException: Invalid integer format for value '?")

Suggested fix:
Work-Arounds:
- do not use the 'useServerPrepStmt' option
or
- do not use prepared statements in this situation
or
- use MySQL/Connector/J 5.1

A fix for MySQL/Connector/J 8.0 is currently unknown.

This demo shows the bug with Connector/J 8.0.21 (works with 5.1.49)

Attachment: ConnectorJBug100747.java (text/x-java), 3.25 KiB.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

The stacktrace (within '   ' there is an unprintable character in original stacktrace)

Attachment: stacktrace1.log (text/x-log), 947 bytes.

Hello Björn,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

The bug could be also triggered by non-key Integer columns. See ConnectorJBug100747B.java.

The driver source code shows that internal representations of server-prepared and client-prepared statement are copied in this use-case. This could not work for numbers.

This demo shows the bug with Connector/J 8.0.21 and an Integer column

Attachment: ConnectorJBug100747B.java (text/x-java), 2.17 KiB.

The stacktrace for example ConnectorJBug100747B.java

Attachment: stacktrace2.log (text/x-log), 891 bytes.