[ https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17692757#comment-17692757 ]
Andrew Luo commented on RANGER-3756: ------------------------------------ Also, upgrading EclipseLink as you suggested in RANGER-4104 does not help this particular bug as it requires temp tables even in the latest 2.x version: [eclipselink/ExpressionQueryMechanism.java at 2.7 · eclipse-ee4j/eclipselink · GitHub|https://github.com/eclipse-ee4j/eclipselink/blob/2.7/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/queries/ExpressionQueryMechanism.java] (Search for "// Main Case: Descriptor is mapped to more than one table and/or the query references other tables"). Even in 3.x/master this is the case. So there's only two solutions: # Upgrade MySQL to >= 8.0.13 # Refactor the delete statement to get the IDs in the Java code and then do a DELETE without any other table references (basically moving the temporary table to Java side) I'll let other people comment but I think upgrading MySQL is a reasonable approach, since (2) would negatively affect performance on other databases/non-GTID setups or otherwise add code complexity (if we were to only write code for the < 8.0.13 GTID MySQL case) > ranger SQL-transaction can not work with GTID-enabled mysql server > ------------------------------------------------------------------ > > Key: RANGER-3756 > URL: https://issues.apache.org/jira/browse/RANGER-3756 > Project: Ranger > Issue Type: Bug > Components: admin > Reporter: kirby zhou > Priority: Critical > Attachments: image-2023-02-17-15-23-26-423.png, > image-2023-02-17-15-23-46-271.png, image-2023-02-17-15-24-11-315.png > > > A lot of cloud mysql service provider enable GTID_MODE by default. > Such as TencentCloud, AliCloud, HuaWeiCloud. > But ranger is not compatible with GTID_MODE. > {code:java} > 2022-05-11 07:19:12,533 [http-nio-6080-exec-3] INFO > n.s.l.Slf4jSpyLogDelegator (Slf4jSpyLogDelegator.java:226) CREATE TEMPORARY > TABLE IF NOT EXISTS TL_x_rms_resource_mapping (id BIGINT NOT NULL, > change_timestamp > DATETIME, hl_resource_id BIGINT, ll_resource_id BIGINT, PRIMARY KEY (id)) > 2022-05-11 07:19:12,543 [http-nio-6080-exec-3] ERROR > n.s.l.Slf4jSpyLogDelegator (Slf4jSpyLogDelegator.java:111) 1. > PreparedStatement.executeUpdate() CREATE TEMPORARY TABLE IF NOT EXISTS > TL_x_rms_resource_mapping (id BIGINT NOT NULL, change_timestamp > DATETIME, hl_resource_id BIGINT, ll_resource_id BIGINT, PRIMARY KEY (id)) > java.sql.SQLException: Statement violates GTID consistency: CREATE TEMPORARY > TABLE and DROP TEMPORARY TABLE can only be executed outside transactional > context. These statements are also not allowed in a function or trigger > because functions and triggers are also considered to be multi-statement > transactions. > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771) > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) > ... > at > org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:890) > at > org.apache.ranger.db.XXRMSServiceResourceDao.purge(XXRMSServiceResourceDao.java:248) > at > org.apache.ranger.biz.ServiceDBStore.deleteService(ServiceDBStore.java:1809) > Error! Exception [EclipseLink-4002] (Eclipse Persistence Services - > 2.5.2.v20140319-9ad6abd): > org.eclipse.persistence.exceptions.DatabaseException Internal Exception: > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table > 'ranger.tl_x_rms_resource_mapping' doesn't exist Error Code: 1146 Call: > INSERT INTO TL_x_rms_resource_mapping (id) SELECT t0.id FROM > x_rms_resource_mapping t0 WHERE (t0.hl_resource_id IN (SELECT t1.id FROM > x_rms_service_resource t1 WHERE (t1.service_id = ?)) OR t0.ll_resource_id IN > (SELECT t2.id FROM x_rms_service_resource t2 WHERE (t2.service_id = ?))) bind > => [2 parameters bound] Query: > DeleteAllQuery(name="XXRMSResourceMapping.deleteByServiceId" > referenceClass=XXRMSResourceMapping sql="DELETE FROM > TL_x_rms_resource_mapping") > {code} > > Because CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed > outside transactional context. > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)