[
https://issues.apache.org/jira/browse/RANGER-1521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15973102#comment-15973102
]
Yan commented on RANGER-1521:
-----------------------------
[~eyang] Not sure can follow your admin privilege arguments. Note that 1) the
binlog_format by default is "statement" prior to 5.7.7 and "row" afterwards.
And changing that value would require admin privilege anyways; 2) Ranger
install/upgrade ops are intrinsically "intrusive" to the DB. If no admin role
is possible, it'd be a bit involved or even tricky to set up the privileges
properly; 3) Ranger install/update ops are not routine jobs to be performed
daily. So it should be feasible/acceptable for the DB Admin and Ranger Admin to
work out a plan to perform the job by, say, temporarily granting the Admin
role. Maybe you have some concrete use cases to share?
On other aspects:
Right, rand() does not guarantee uniqueness and NOW() could be made to be more
unique. My opinion is that in general rand() is much more likely to be unique
than NOW(). Anyways, in terms of entropy, NOW() can't compare with rand().
After all it depends upon the usage of the "trans_id".
On the data integrity side, the point is not deterministic vs.
nondeterministic nor safe vs. unsafe functions. The point is the "binary
logging" in the replication. In the MySQL doc, there is a dazzling array of
conditions, options and exceptions on the topic. Before we find an optimal
solution in that domain, I guess it is more appropriate to first discern the
usage of the "transaction id". For instances, if it is of practically no use,
we should consider its removal; if it's used for identification purpose, then
uniqueness will be an important property; if it's used for verification
purposes, then entropy will be important. Any insights from the community ?
Before we are clear on the usage, IMHO, a proper approach is to find a
workaround.
> Ranger database script for mysql requires admin privileges for replicated
> database
> ----------------------------------------------------------------------------------
>
> Key: RANGER-1521
> URL: https://issues.apache.org/jira/browse/RANGER-1521
> Project: Ranger
> Issue Type: Bug
> Components: admin
> Affects Versions: 0.6.2
> Reporter: Eric Yang
> Attachments: RANGER-1521.patch
>
>
> When running install or upgrade on replicated MySQL or MariaDB, ranger
> installation might fail with:
> {code}
> Error executing: CREATE FUNCTION `getTempPolicyCount`(assetId bigint, resId
> bigint) RETURNS int(11) BEGIN DECLARE tempPolicyCount int default 1; DECLARE
> dbResourceId bigint; DECLARE exitLoop int DEFAULT FALSE; DECLARE policyList
> CURSOR FOR SELECT id from x_resource where asset_id = assetId; DECLARE
> CONTINUE HANDLER FOR NOT FOUND SET exitLoop = true; OPEN policyList;
> readPolicy : LOOP FETCH policyList into dbResourceId; IF exitLoop THEN set
> tempPolicyCount = tempPolicyCount + 1; LEAVE readPolicy; END IF; IF (resId =
> dbResourceId) THEN LEAVE readPolicy; END IF; set tempPolicyCount =
> tempPolicyCount + 1; END LOOP; CLOSE policyList; RETURN tempPolicyCount; END
> java.sql.SQLException: This function has none of DETERMINISTIC, NO SQL, or
> READS SQL DATA in its declaration and binary logging is enabled (you *might*
> want to use the less safe log_bin_trust_function_creators variable)
> SQLException : SQL state: HY000 java.sql.SQLException: This function has none
> of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
> logging is enabled (you *might* want to use the less safe
> log_bin_trust_function_creators variable) ErrorCode: 1418 2017-04-05
> 22:59:00,345 [JISQL]
> /usr/jdk64/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/bin/java -cp
> /usr/iop/current/ranger-admin/ews/lib/mysql-connector-java.jar:/usr/iop/current/ranger-admin/jisql/lib/*
> org.apache.util.sql.Jisql -driver mysqlconj -cstring
> jdbc:mysql://tmh21-3-dal10-bi-mn000.fyre.ibm.com/ranger -u 'ranger' -p
> '********' -noheader -trim -c \; -query "delete from x_db_version_h where
> version='007' and active='N' and
> updated_by='tmh21-3-dal10-bi-mn004.fyre.ibm.com';" 2017-04-05 22:59:00,781
> [E] 007-updateBlankPolicyName.sql import failed!
> {code}
> Two files under ranger-admin/db/mysql/patches:
> 007-updateBlankPolicyName.sql and 008-removeTrailingSlash.sql are using
> rand() functions to generate transaction ID, which makes them
> non-deterministic functions and are causing failures on replicated MySQL
> database.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
