[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17692728#comment-17692728
]
Andrew Luo commented on RANGER-3756:
[~kirbyzhou] the issue should only be reproducible on MySQL < 8.0.13 since
8.0.13+ adds support for temporary tables in GTID transactions: [MySQL ::
Temporary tables are now allowed in transactions when GTIDs are
enabled|https://dev.mysql.com/blog-archive/temporary-tables-are-now-allowed-in-transactions-when-gtids-are-enabled/]
> 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)
[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17690196#comment-17690196
]
kirby zhou commented on RANGER-3756:
Reproduce steps:
# create a cloud mysql-5.7 instance on Tencent Cloud
# setup a ranger admin with the mysql db
# use web browser to open ranger-admin site
# create a HDFS service 【You DONOT need a real hdfs cluster】
# delete the HDFS service
# error happens
!image-2023-02-17-15-23-46-271.png!
!image-2023-02-17-15-24-11-315.png!
Additional Info:
I can not reproduce the bug with my private MySQL-8 server with GTID=on;
> 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)
[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17689578#comment-17689578
]
kirby zhou commented on RANGER-3756:
I had encounter this bug with the cloud service of mysql provided by Tencent
Cloud.
[http://cloud.tencent.com|http://cloud.tencent.com/] 【China】or
[https://www.tencentcloud.com/] 【international】
It happens at the ranger-2.3 branch.
> 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
>
> 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)
[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17688534#comment-17688534
]
Pradeep Agrawal commented on RANGER-3756:
-
[~kirbyzhou]
What is the mysql version are you using ?
Which version of ranger source code are you using ?
Also if possible please share the steps to setup mysql gtid replication so that
i can reproduce the issue.
> 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
>
> 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)
[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17626777#comment-17626777
]
Velmurugan Periasamy commented on RANGER-3756:
--
CC [~pradeepagrawal8184] / [~abhayk]
> 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
>
> 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)
[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17535840#comment-17535840
]
kirby zhou commented on RANGER-3756:
Maybe we can upgrade the version of eclipselink 【JPA Provider used by ranger】
to fix this problem?
eclipse.jpa.version = 2.5.2 now。
Or just set some property of eclipselink?
I am not familiar with eclipse jpa. Anybody have idea?
> 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
>
> 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.7#820007)
[jira] [Commented] (RANGER-3756) ranger SQL-transaction can not work with GTID-enabled mysql server
[
https://issues.apache.org/jira/browse/RANGER-3756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17534746#comment-17534746
]
kirby zhou commented on RANGER-3756:
set InlineIdsInClauseBulkIdStrategy wont fix this problem.
./ews/webapp/WEB-INF/classes/META-INF/persistence.xml
{code:java}
...
{code}
> 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
>
> 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.7#820007)
