[RANGER-1694] Execute 007-updateBlankPolicyName.sql failed in some mysql environment, which will cause ranger-admin start fail.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Invalid
• Component/s: admin
• Labels: patch
• Flags: Patch, Important
• Assignee: peng.jianhua
• Reporter: peng.jianhua
• Created: 17/Jul/17 08:11
• Updated: 24/Jan/22 07:29
• Resolved: 13/Sep/17 07:49

Description

Environment:
mysql Ver 14.14 Distrib 5.5.35, for linux2.6 (x86_64).

There are following error when installed ranger admin.
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

Lots of tables are not created in ranger db after installed ranger admin. So start ranger admin fail.

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