MySQL8 delete SQL need setting system variable

Apiman Version

3.1.3.Final

Apiman Manager Distro

WildFly

Apiman Gateway Distro

Vert.x

Java Version

openjdk version "11.0.8" 2020-07-14 LTS OpenJDK Runtime Environment 18.9 (build 11.0.8+10-LTS) OpenJDK 64-Bit Server VM 18.9 (build 11.0.8+10-LTS, mixed mode, sharing)

Operating System

Debian 8.3.0-6

Are you running Apiman in a container, or on an orchestration platform?

Kubernetes

Describe the bug

I use MySQL8 for storage

MySQL version is 8.0.21

apiman-manager.storage.type=jpa
apiman-manager.storage.jpa.initialize=true
apiman.db.driver=mysql8
apiman.hibernate.connection.datasource=java:/apiman/datasources/apiman-manager
apiman.hibernate.hbm2ddl.auto=validate
apiman.hibernate.dialect=io.apiman.manager.api.jpa.ApimanMySQL8Dialect

When I delete API，I got error message：

Caused by: java.sql.SQLException: You can't specify target table 'contracts' for update in FROM clause
    at com.mysql@8.0.33//com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
    at org.jboss.ironjacamar.jdbcadapters@1.4.27.Final//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
    at org.hibernate@5.3.20.Final//org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    ... 181 more

Relate code may be ：

    private void deleteAllContracts(ApiBean apiBean) throws StorageException {
        String jpql =
            "DELETE FROM ContractBean deleteBean " +
            "   WHERE deleteBean IN ( " +
            "       SELECT b " +
            "           FROM ContractBean b " +
            "           JOIN b.api apiVersion " +
            "           JOIN apiVersion.api api " +
            "           JOIN api.organization o " +
            "       WHERE o.id = :orgId " +
            "       AND api.id = :apiId " +
            "   )";
        Query query = getActiveEntityManager().createQuery(jpql);
        query.setParameter("orgId", apiBean.getOrganization().getId());
        query.setParameter("apiId", apiBean.getId());
        query.executeUpdate();
    }

I guess that is because we cannot delete a table and select directly from the same table in a subquery: https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause https://dev.mysql.com/doc/refman/8.4/en/update.html

I don't think it is a good idea to change system variable cause it is global.

Expected behaviour

delete API success

Actual behaviour

Snip20240909_32

How to Reproduce

Delete API with MySQL 8

Relevant log output

No response

Anything else?

No response

Save API with “Feature” also has problem Snip20240909_33

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, value) values ('featured', null)' at line 1
    at com.mysql@8.0.33//com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
    at com.mysql@8.0.33//com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
    at org.jboss.ironjacamar.jdbcadapters@1.4.27.Final//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
    at org.hibernate@5.3.20.Final//org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    ... 145 more

apiman / apiman