[ https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
kirby zhou updated RANGER-3594: ------------------------------- 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: {code:java} 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 $$ {code} 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: {code:java} This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary.{code} 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: {code:java} 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){code} 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? was: There are some sql scripts which create functions in mysql, failed with master/slave 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: {code:java} 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 $$ {code} When setup with Master/Slave MySQL database, it will cause failure. Because of 2 problem. 1. CREATE FUNCTION with Master/Slave requires some "characteristic" flag. Otherwise, error: {code:java} This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary.{code} getXportalUIdByLoginId and getModulesIdByName are both read-only, so we can put 'READS SQL DATA' here. 2. CREATE FUNCTION with Master/Slave requires SUPER privilege ON *.* Otherwise, error: {code:java} 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){code} But our dba_setup.py do not grant SUPER to $db_user ( default is rangeradmin ). ---- 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? > mysql setup scripts failed with binlog-enabled mysql > ---------------------------------------------------- > > Key: RANGER-3594 > URL: https://issues.apache.org/jira/browse/RANGER-3594 > Project: Ranger > Issue Type: Bug > Components: admin > Affects Versions: 3.0.0, 2.2.0, 2.3.0 > Reporter: kirby zhou > Assignee: Pradeep Agrawal > Priority: Major > Attachments: > 0001-add-FUNCTION-description-for-mysql-master-slave.patch > > > 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: > > {code:java} > 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 $$ {code} > > 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: > {code:java} > This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its > declaration and binary.{code} > 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: > > {code:java} > 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){code} > > 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? > -- This message was sent by Atlassian Jira (v8.20.1#820001)