[RANGER-3594] mysql setup scripts failed with binlog-enabled mysql

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0, 2.2.0, 2.3.0
• Fix Version/s: 3.0.0, 2.3.0
• Component/s: Ranger
• Assignee: Pradeep Agrawal
• Reporter: kirby zhou
• Created: 21/Jan/22 09:33
• Updated: 02/Feb/22 07:41
• Resolved: 02/Feb/22 07:38

Description

There are some sql scripts which create functions in mysql, failed with binlog-enabled mysql.

• security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
• security-admin/db/mysql/patches/013-permissionmodel.sql
• security-admin/db/mysql/patches/037-create-security-zone-schema.sql 
• security-admin/db/mysql/patches/046-insert-statename-in-x-ranger-global-state.sql

Codes like:

 

DELIMITER $$
DROP FUNCTION if exists getXportalUIdByLoginId$$
CREATE FUNCTION `getXportalUIdByLoginId`(input_val VARCHAR(100)) RETURNS int(11)
BEGIN DECLARE myid INT; SELECT x_portal_user.id into myid FROM x_portal_user
WHERE x_portal_user.login_id = input_val;
RETURN myid;
END $$
DELIMITER ;
DELIMITER $$
DROP FUNCTION if exists getModulesIdByName$$
CREATE FUNCTION `getModulesIdByName`(input_val VARCHAR(100)) RETURNS int(11)
BEGIN DECLARE myid INT; SELECT x_modules_master.id into myid FROM x_modules_master
WHERE x_modules_master.module = input_val;
RETURN myid;
END $$ 

 

When setup with  binlog-enabled MySQL database, it will cause failure.

Because of 2 problem.

1. CREATE FUNCTION with  binlog requires some "characteristic" flag.

Otherwise, error:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary.

getXportalUIdByLoginId and getModulesIdByName are both read-only, so we can put 'READS SQL DATA' here.

 

2.   CREATE FUNCTION with binlog requires SUPER privilege ON .

Otherwise, error:

 

You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

 

But our dba_setup.py do not grant SUPER to $db_user ( default is rangeradmin ), and it seems too danger to  grant SUPER to $db_user. Maybe we can let db_setup.py runs with $db_root_user instead of $db_user, or DO NOT use store procedure any more to avoid such problems.

 

---

 

There are lots of sql contains the same function, which one should I patch it?

It seems that "security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql" is called by setup.sh, but what about others?

 

Issue Links

• is duplicated by: RANGER-3581 setup.sh can not "CREATE FUNCTION" on MySQL with Master/Slave profile.
• is related to: RANGER-3587 Failed to apply 046*.sql patch
• is related to: RANGER-1521 Ranger database script for mysql requires admin privileges for replicated database
• is related to: RANGER-1694 Execute 007-updateBlankPolicyName.sql failed in some mysql environment, which will cause ranger-admin start fail.
• is related to: RANGER-1710 When install ranger-1.0.0-SNAPSHOT-admin,execute ./setup.sh find [E] 007-updateBlankPolicyName.sql import failed
• is related to: RANGER-1912 Ranger setup fails with mariadb/mysql when binary logging is enabled
• links to: Review request