question Ranger admin install fails with "007-updateBlankPolicyName.sql import failed" in Archives of Support Questions (Read Only)

Ranger admin install fails with "007-updateBlankPolicyName.sql import failed"

richard_xu — Fri, 18 Mar 2016 23:15:36 GMT

Installing Ranger via Ambari on AWS, got this:

2016-03-18 16:10:44,048 [JISQL] /usr/jdk64/jdk1.8.0_60/bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://mysqldb/ranger -u 'user' -p '********' -noheader -trim -c \; -input /usr/hdp/current/ranger-admin/db/mysql/patches/007-updateBlankPolicyName.sql

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
2016-03-18 16:10:44,438  [E] 007-updateBlankPolicyName.sql import failed!

Re: Ranger admin install fails with "007-updateBlankPolicyName.sql import failed"

richard_xu — Sat, 19 Mar 2016 00:31:20 GMT

It is Ambari 2.2.0.0, HDP-2.3.4.0-3485

Re: Ranger admin install fails with "007-updateBlankPolicyName.sql import failed"

sdutta — Tue, 22 Mar 2016 09:27:49 GMT

@Richard Xu

Can you please remove the hostname :). It is a security risk and customer may not like it.

Re: Ranger admin install fails with "007-updateBlankPolicyName.sql import failed"

vperiasamy — Wed, 23 Mar 2016 01:52:22 GMT

@Richard Xu - Can you please execute the below command in MySQL and retry the install?

SET GLOBAL log_bin_trust_function_creators = 1

If you'd like to roll back to the default value of 0, you can do that after the install goes through.

Re: Ranger admin install fails with "007-updateBlankPolicyName.sql import failed"

chri — Mon, 02 May 2016 17:50:53 GMT

@vperiasamy

I had the same error and I can confirm that above command fixed the issue. Thx!

Re: Ranger admin install fails with "007-updateBlankPolicyName.sql import failed"

shyamshaw — Wed, 11 Jan 2017 21:39:41 GMT

@vperiasamy

It worked for me as well.. Thanks.