[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17485611#comment-17485611
]
Pradeep Agrawal commented on RANGER-3594:
-
2.3 branch commit :
https://github.com/apache/ranger/commit/a435f34005c0f8d4beea6d122e5118f847df61b2
> 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
> Fix For: 3.0.0, 2.3.0
>
> Attachments:
> 0001-RANGER-3594-Ranger-setup-fails-for-mariadb-mysql-whe.patch,
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17485581#comment-17485581
]
Pradeep Agrawal commented on RANGER-3594:
-
[~bpatel] / [~kirbyzhou] : is this good to go ?
> 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
> Fix For: 3.0.0, 2.3.0
>
> Attachments:
> 0001-RANGER-3594-Ranger-setup-fails-for-mariadb-mysql-whe.patch,
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17483545#comment-17483545
]
kirby zhou commented on RANGER-3594:
Test passed on ranger master and 2.2.0 branch with
rh-mariadb103-3.3-5.el7.x86_64
Fresh installation and
> 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-RANGER-3594-Ranger-setup-fails-for-mariadb-mysql-whe.patch,
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17483175#comment-17483175
]
Pradeep Agrawal commented on RANGER-3594:
-
[~bpatel] No, but please test and let me know.
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17483164#comment-17483164
]
Bhavik Patel commented on RANGER-3594:
--
[~kirbyzhou] did you tried to upgrade with the provide patch?
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17483161#comment-17483161
]
Bhavik Patel commented on RANGER-3594:
--
[~pradeep] do need to set 'SET GLOBAL log_bin_trust_function_creators = 1; '
with your patch?
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17480873#comment-17480873
]
Pradeep Agrawal commented on RANGER-3594:
-
[~kirbyzhou] : yes, will try to change the function to procedure.
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17480872#comment-17480872
]
kirby zhou commented on RANGER-3594:
There seems no file reference the getXportalUIdByLoginId / getModulesIdByName
except '*.sql'. Only SQL scripts use them.
{code:java}
kirbyzhou@Kirby-WorkBook ranger-master % egrep -r
'getModulesIdByName|getXportalUIdByLoginId' . --exclude '*.jar' --exclude
'*.gz' --exclude='*.class' --exclude '*.sql'
kirbyzhou@Kirby-WorkBook ranger-master %
{code}
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17480865#comment-17480865
]
kirby zhou commented on RANGER-3594:
On many situations, we can not do
SET GLOBAL log_bin_trust_function_creators = 1;
It requires SUPER. But we can only have ' ALL ON ranger.* '.
Maybe remove 'store procedure' is a better idea, the 2 function looks too
simple to be exist as 'store procedure'.
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17480862#comment-17480862
]
kirby zhou commented on RANGER-3594:
On centos-7:
]] yum install -y rh-mariadb103-mariadb-server rh-mariadb103-mariadb-syspaths
]] vim /etc/rh-mariadb103-my.cnf.d/mariadb-server.cnf
# enable binlog by insert log-bin=... under [server]
[server]
log-bin=/var/lib/mysql/binlog
]] service rh-mariadb103-mariadb restart
]] mysql
{code:java}
CREATE DATABASE IF NOT EXISTS test;
USE test
DELIMITER $$
DROP FUNCTION if exists testFunc$$
CREATE FUNCTION `testFunc`() RETURNS int(11)
BEGIN DECLARE myid INT; SELECT 1 into myid;
RETURN myid;
END $$ {code}
# the output:
{code:java}
]# mysql
Server version: 10.3.28-MariaDB-log MariaDB Server
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> USE test
Database changed
MariaDB [test]> DELIMITER $$
MariaDB [test]> DROP FUNCTION if exists testFunc$$
Query OK, 0 rows affected, 1 warning (0.000 sec)
MariaDB [test]> CREATE FUNCTION `testFunc`() RETURNS int(11)
-> BEGIN DECLARE myid INT; SELECT 1 into myid;
-> RETURN myid;
-> END $$
ERROR 1418 (HY000): 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)
{code}
binlog is a very common mysql feature in production environment. Without
binlog, we can not have High-Availability,
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17480859#comment-17480859
]
Pradeep Agrawal commented on RANGER-3594:
-
References :
# [https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html]
#
[https://mariadb.com/kb/en/would-some-one-tell-me-what-is-wrong-with-this-function/+comments/2730]
# [https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/]
#
[https://docs.microsoft.com/en-us/azure/mysql/howto-troubleshoot-common-errors]
#
[https://community.cloudera.com/t5/Support-Questions/Ranger-service-is-failing-to-install/td-p/310115]
> 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)
[jira] [Commented] (RANGER-3594) mysql setup scripts failed with binlog-enabled mysql
[
https://issues.apache.org/jira/browse/RANGER-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17480856#comment-17480856
]
Pradeep Agrawal commented on RANGER-3594:
-
before ranger installation you have to run below statement in mysql, after
ranger installation you can reset it.
{code:java}
SET GLOBAL log_bin_trust_function_creators = 1; {code}
> 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)
