[
https://issues.apache.org/jira/browse/RANGER-1912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pradeep Agrawal updated RANGER-1912:
------------------------------------
Description:
Ranger Admin installation fails when using MariaDB/MySQL with binary logging
enabled.
The install should work even if binary logging is enabled.
*Problem Statement:* Currently MySQL DB patch 007 and 008 is having MySQL UDF
functions which are not DETERMINISTIC
When you create a stored function, you must declare either that it is
deterministic or that it does not modify data. Otherwise, it may be unsafe for
data recovery or replication.
By default, for a CREATE FUNCTION statement to be accepted, at least one of
DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly.
Otherwise, an error occurs:
{code:java}
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}
Although it is possible to create a deterministic stored function without
specifying DETERMINISTIC, you cannot execute this function using
statement-based binary logging. To execute such a function, you must use
row-based or mixed binary logging. Alternatively, if you explicitly specify
DETERMINISTIC in the function definition, you can use any kind of logging,
including statement-based logging.
To relax the preceding conditions on function creation (that you must have the
SUPER privilege and that a function must be declared deterministic or to not
modify data), set the global log_bin_trust_function_creators system variable to
1. By default, this variable has a value of 0, but you can change it like this:
{code:java}
SET GLOBAL log_bin_trust_function_creators = 1;
{code}
If binary logging is not enabled, log_bin_trust_function_creators does not
apply. SUPER is not required for function creation unless, as described
previously, the DEFINER value in the function definition requires it.
*Proposed Solution:* We can remove usage of stored function and it can be
replaced with the stored procedure.
was:
Ranger Admin installation fails when using MariaDB/MySQL with binary logging
enabled.
The install should work even if binary logging is enabled.
A workaround to solve this by modifying the global MySQL configuration
attribute log_bin_trust_function_creators = 1.
Proposed Solution: Currently MySQL DB patch 007 and 008 is having MySQL UDF
functions which are not DETERMINISTIC
When you create a stored function, you must declare either that it is
deterministic or that it does not modify data. Otherwise, it may be unsafe for
data recovery or replication.
By default, for a CREATE FUNCTION statement to be accepted, at least one of
DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly.
Otherwise an error occurs:
> Ranger setup fails with mariadb/mysql when binary logging is enabled
> --------------------------------------------------------------------
>
> Key: RANGER-1912
> URL: https://issues.apache.org/jira/browse/RANGER-1912
> Project: Ranger
> Issue Type: Bug
> Components: Ranger
> Affects Versions: 0.4.0, 0.5.0, 0.6.0, 0.7.0
> Reporter: Pradeep Agrawal
> Assignee: Pradeep Agrawal
> Fix For: 1.0.0
>
>
> Ranger Admin installation fails when using MariaDB/MySQL with binary logging
> enabled.
> The install should work even if binary logging is enabled.
> *Problem Statement:* Currently MySQL DB patch 007 and 008 is having MySQL UDF
> functions which are not DETERMINISTIC
> When you create a stored function, you must declare either that it is
> deterministic or that it does not modify data. Otherwise, it may be unsafe
> for data recovery or replication.
> By default, for a CREATE FUNCTION statement to be accepted, at least one of
> DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly.
> Otherwise, an error occurs:
> {code:java}
> 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}
> Although it is possible to create a deterministic stored function without
> specifying DETERMINISTIC, you cannot execute this function using
> statement-based binary logging. To execute such a function, you must use
> row-based or mixed binary logging. Alternatively, if you explicitly specify
> DETERMINISTIC in the function definition, you can use any kind of logging,
> including statement-based logging.
> To relax the preceding conditions on function creation (that you must have
> the SUPER privilege and that a function must be declared deterministic or to
> not modify data), set the global log_bin_trust_function_creators system
> variable to 1. By default, this variable has a value of 0, but you can change
> it like this:
> {code:java}
> SET GLOBAL log_bin_trust_function_creators = 1;
> {code}
> If binary logging is not enabled, log_bin_trust_function_creators does not
> apply. SUPER is not required for function creation unless, as described
> previously, the DEFINER value in the function definition requires it.
> *Proposed Solution:* We can remove usage of stored function and it can be
> replaced with the stored procedure.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
