[RANGER-1521] Ranger database script for mysql requires admin privileges for replicated database

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 0.6.2
• Component/s: admin
• Assignee: Pradeep Agrawal
• Reporter: Eric Yang
• Created: 18/Apr/17 01:35
• Updated: 24/Jan/22 07:29
• Resolved: 29/Jan/20 05:28

Description

When running install or upgrade on replicated MySQL or MariaDB, ranger installation might fail with:

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!

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.

Issue Links

• relates to: RANGER-1912 Ranger setup fails with mariadb/mysql when binary logging is enabled
• relates to: RANGER-3594 mysql setup scripts failed with binlog-enabled mysql