[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17480851#comment-17480851
]
Pradeep Agrawal commented on RANGER-3594:
-----------------------------------------
[~kirbyzhou] : I am trying with default installation of mysql, anything beyond
default config "details" should be mentioned in Jira and User need to handle
such env. specific constraints as per the underlying database env. Developers
may not have everything in their pc so "steps to reproduce" should be added in
the Jira if the issue is not reproducible with default settings.
> mysql setup scripts failed with Master/Slave 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
> 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?
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
