[RANGER-3756] ranger SQL-transaction can not work with GTID-enabled mysql server

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Component/s: admin
• Assignee: Unassigned
• Reporter: kirby zhou
• Created: 11/May/22 07:05
• Updated: 23/Feb/23 15:53

Description

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.

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")

 

Because CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.