delete queries and union subclass inheritance fails with MySQL 8.0.29

[DavideD]

If we upgrade to MySQL 8.0.29 (without chaniging anything else) the test org.hibernate.reactive.UnionSubclassInheritanceTest will fail because the delete of the entity
doesn't occurs:

Expected null
java.lang.AssertionError: Expected null
	at io.vertx.ext.unit.impl.TestContextImpl.reportAssertionError(TestContextImpl.java:362)

Something doesn't work quite right when running these queries:

create temporary table if not exists ht_BookUS (id integer not null) 
insert into ht_BookUS select unionsubcl0_.id as id from ( select id, published, title, null as forbidden, 0 as clazz_ from BookUS union all select id, published, title, forbidden, 1 as clazz_ from SpellBookUS ) unionsubcl0_ where unionsubcl0_.title=?
delete from BookUS where (id) in (select id from ht_BookUS)
delete from SpellBookUS where (id) in (select id from ht_BookUS)
drop temporary table ht_BookUS

This seems to be a bug with MySQL 8.0.29: https://bugs.mysql.com/bug.php?id=107447

[DavideD]

@tsegismont this only happens when I change the docker image from 8.0.28 to 8.0.29. I suspect it's an issue with the Vert.x SQL client but the test I've tried to write for it actually works. Would you be able to have a look at this, please?

This is the branch with MySQL 8.0.29 + docker configure already as the default: https://github.com/DavideD/hibernate-reactive/tree/mysql-8.0.29

This the branch Vert.x SQL test that tries to replicate the issue: https://github.com/DavideD/vertx-sql-client/tree/hr-issue

[tsegismont]

@DavideD I have a reproducer with Vert.x MySQL Client. The key point is that prepared statement caching needs to be enabled (which HR does by default).

I don't understand yet why prepared statement caching is broken by upgrading to 8.0.29.

[DavideD]

Thanks for looking into this

[tsegismont]

For the record, I have been able to reproduce with JDBC:

    Properties connectionProps = new Properties();
    connectionProps.put("user", "mysql");
    connectionProps.put("password", "password");
    connectionProps.put("useServerPrepStmts", "true");
    connectionProps.put("cachePrepStmts", "true");
    connectionProps.put("prepStmtCacheSqlLimit", "2048");
    //connectionProps.put("sslMode", "DISABLED"); // to allow traffic inspection with Wireshark

    try (
      Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testschema", connectionProps);
    ) {

      // Setup
      try (PreparedStatement ps = conn.prepareStatement("DROP TABLE IF EXISTS Book")) {
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("CREATE TABLE Book (id INTEGER NOT NULL, title VARCHAR(255), PRIMARY KEY (id)) ENGINE = InnoDB")) {
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("INSERT INTO Book (id, title) VALUES (1, 'MySQL and JSON')")) {
        ps.executeUpdate();
      }

      // Update
      try (PreparedStatement ps = conn.prepareStatement("CREATE TEMPORARY TABLE IF NOT EXISTS ht_Book (id INTEGER NOT NULL)")) {
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("INSERT INTO ht_Book SELECT sub.id FROM (SELECT id, title FROM Book UNION ALL SELECT id, title FROM Book) sub WHERE sub.title = ?")) {
        ps.setString(1, "MySQL and JSON");
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("UPDATE Book SET title=CONCAT(title, ?) WHERE (id) IN (SELECT id FROM ht_Book)")) {
        ps.setString(1, ": A Practical Programming Guide");
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("DROP TEMPORARY TABLE ht_Book")) {
        ps.executeUpdate();
      }

      // Read
      try (
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Book");
        ResultSet rs = ps.executeQuery()
      ) {
        while (rs.next()) {
          System.out.println("id = " + rs.getInt("id"));
          System.out.println("title = " + rs.getString("title"));
        }
      }

      // Delete
      try (PreparedStatement ps = conn.prepareStatement("CREATE TEMPORARY TABLE IF NOT EXISTS ht_Book (id INTEGER NOT NULL)")) {
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("INSERT INTO ht_Book SELECT sub.id FROM (SELECT id, title FROM Book UNION ALL SELECT id, title FROM Book) sub WHERE sub.title = ?")) {
        ps.setString(1, "MySQL and JSON: A Practical Programming Guide");
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("DELETE FROM Book WHERE (id) IN (SELECT id FROM ht_Book)")) {
        ps.executeUpdate();
      }
      try (PreparedStatement ps = conn.prepareStatement("DROP TEMPORARY TABLE ht_Book")) {
        ps.executeUpdate();
      }

      // Read
      try (
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM Book");
        ResultSet rs = ps.executeQuery()
      ) {
        while (rs.next()) {
          System.out.println("id = " + rs.getInt("id"));
          System.out.println("title = " + rs.getString("title"));
        }
      }

    } catch (SQLException e) {
      e.printStackTrace();
    }

This gives the following output:

id = 1
title = MySQL and JSON: A Practical Programming Guide
id = 1
title = MySQL and JSON: A Practical Programming Guide

If client side caching is disabled:

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

Then I get as expected:

id = 1
title = MySQL and JSON: A Practical Programming Guide

If you execute the above with 8.0.28, you get the expected result, with or without client side caching enabled.

[tsegismont]

@DavideD @Sanne @gavinking given this is not a Vert.x client issue, would you mind following-up with the MySQL community?

[gavinking]

So what would the workaround be?

[tsegismont]

Disabling prepared statement caching

[DavideD]

ok, I will create an issue for MySQL

[gavinking]

Disabling prepared statement caching

Like, globally, or for some particular statement?

[DavideD]

I've reported the bug: https://bugs.mysql.com/bug.php?id=107447

[tsegismont]

Like, globally, or for some particular statement?

@gavinking I think so yes. I don't know how you can do for some particular statement if you don't know the statement in advance (the connect options have a filter field which could help if the query was known beforehand).

[tsegismont]

I've reported the bug: https://bugs.mysql.com/bug.php?id=107447

@DavideD Thank you. I would recommend to update the description: the issue is not the DELETE query, but rather the INSERT into the temporary table. The second time it is executed (just before the DELETE) it does not insert anything.