hibernate-reactive
hibernate-reactive copied to clipboard
hibernate
delete queries and union subclass inheritance fails with MySQL 8.0.29
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
@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
@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.
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.
@DavideD @Sanne @gavinking given this is not a Vert.x client issue, would you mind following-up with the MySQL community?
Disabling prepared statement caching
Like, globally, or for some particular statement?
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).
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.
Ah ... I thought the problem was that it couldn't read the value inserted. Thanks!
Anyway, I can't figure out how to edit the issue, so I've just added a comment. Feel free to comment directly on the MySQL issue if something else is missing.
Wow, their response to your bug report wasn't very encouraging, was it @DavideD?
And this seems to be a bug that would impact users of regular Hibernate, correct? (It's not specific to reactive, is it?)
And this seems to be a bug that would impact users of regular Hibernate, correct? (It's not specific to reactive, is it?)
But I've converted the test for ORM and it didn't fail on CI
@tsegismont: Doesn't their answer means that something needs to be fixed in the vertx client?
Does ORM activate prepared statement caching as well? Are the tests exactly the same?
When I read the answer from MySQL verification team, I see "We are aware of the issue. It is related with the new server code".
Does ORM activate prepared statement caching as well? Are the tests exactly the same?
When I read the answer from MySQL verification team, I see "We are aware of the issue. It is related with the new server code".
When I read the answer from MySQL verification team, I see "We are aware of the issue. It is related with the new server code".
It also continues with "... and how connector/j use server prepared statements.". It's also categorized as Connector / J. But yes, it's a vague answer, who knows?
Are the tests exactly the same?
I've pushed the tests on my branch mysql-29 (already set up to work with MySQL and docker): https://github.com/DavideD/hibernate-reactive/tree/mysql-29
The test that fails for reactive is UnionSubclassInheritanceTest#testQueryUpdateWithParameters
The same test for ORM is in ORMReactivePersistenceTest
They use the same Hibernate properties but I haven't checked how ORM set the options on the JDBC connector.
@tsegismont Sounds good. I will check that the branch still works, I haven't touched it in a while
I can take a look but not before next Monday.
This comment comes from another issue, and I had sent it weeks ago... :sweat_smile:
It looks like a GH infra incident. I had the same problem on a Quarkus issue with a user comment.