Bug #107447 Delete-select not working with temporary tables
Submitted: 1 Jun 2022 15:25 Modified: 25 Oct 2023 0:25
Reporter: Davide D'Alto Email Updates:
Status: Can't repeat Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[1 Jun 2022 15:25] Davide D'Alto
When running an SQL query of the this kind:

And ht_Book is a temporary table, the delete query is not going to match any id even if the table has been populated.
This works with MySQL 8.0.28 but it doesn't work with MySQL 8.0.29 when that cache is enabled: connectionProps.put("cachePrepStmts", "true");.

How to repeat:
I've created a testcase on my GitHub repository: https://github.com/DavideD/mysql-issue

The test uses testcontainers, docker and the mysql JDBC driver 8.0.29.
It runs the same tests with MySQL 8.0.28 and 8.0.29 with the cache enabled/disabled.

Suggested fix:
I don't have a fix but disabling the cache (connectionProps.put("cachePrepStmts", "false");) will work as a workaround.
[1 Jun 2022 22:23] Davide D'Alto
Actually, it seems the delete fails because the temporary table is empty after the SQL insert.

Anyway, the test I've attached should help you figure out what's going on.
[2 Jun 2022 13:37] MySQL Verification Team
Hi Mr. Alto,

We need a fully repeatable test case, not just description. A test case should consist of the series of SQL statement, that can be run in the batch. Please, read further on .....

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php 

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.
[2 Jun 2022 16:04] MySQL Verification Team

We are aware of the issue. It is related with the new server code and how connector/j use server prepared statements.

 connectionProps.put("useServerPrepStmts", "true");

 connectionProps.put("useServerPrepStmts", "false");

should solve your problem

At the moment there is no known path for this to be fixed in the near future so proper status of this bug might be "unsupported" but I will verify it for now.

Thank you for your report
[25 Oct 2023 0:25] Filipe Silva
This was caused by the MySQL Server Bug#107230, that is fixed in the version 8.0.30, as such, this behavior can't be reproduced anymore.