[
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)
