[
https://issues.apache.org/jira/browse/RANGER-1521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15972804#comment-15972804
]
Eric Yang commented on RANGER-1521:
-----------------------------------
[~yzhou2001]: 1) the purpose of this change is to reduce the requirement of
Ranger DDL to use non-admin privileges for modifying the database. In some
company, it is more work to obtain mysql admin privileges for external mysql
database. The workaround does not work in such settings.
2) rand does not guarantee uniqueness. The transaction id is only useful to
see the changes that are being made on the same batch. By changing it to
Now(), it does not make it less unique since rand() could produce duplicated
number. We can also make the change to produce micro-seconds number to make
transaction number more unique. The transaction id in the DDL are only used
for recording the changes to x_resource table. There is no use of transaction
id for further processing beyond the patches. Therefore, existing semantics
are preserved to the closest possible details.
[~pradeep.agrawal] The SQL statements are non-deterministic by default. If it
always produce the same result, then it is deterministic. Insert statement
does not imply non-deterministic. Insert and Update can further annotate the
data being MODIFIES SQL DATA. However, the changes of statements are for
making the DDL work without admin privileges, and make it future proof for
binlog_format=MIXED environment. These changes are improvement to reduce the
need of asking mysql admin privileges because rand() is used.
> 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)
