[MDEV-19026] Unable to create a function in mariadb even when function in deterministic Created: 2019-03-22  Updated: 2019-03-23  Resolved: 2019-03-22

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.2.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: anup b Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

While creating a function in mariadb, I am getting the error - SQL Error (1419): 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)

DELIMITER $$
 
 
 
USE `sbldprod`$$
 
 
 
DROP FUNCTION IF EXISTS `NULLIFZERO`$$
 
 
 
CREATE FUNCTION `NULLIFZERO`(STRING_INPUT VARCHAR(255)) RETURNS VARCHAR(255) 
	LANGUAGE SQL
 
	DETERMINISTIC 
	CONTAINS SQL
 
	sql security invoker
 
BEGIN
 
	RETURN NULLIF(STRING_INPUT, 0);
 
END$$
 
 
 
DELIMITER ;

I am using mariadb on AWS RDS, it's version is 10.2.15.

*Following are the server params - *
log_bin_trust_function_creators - 0
log_bin - 1
binlog_format - MIXED

grants on the users creating this function -
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER

 Comments   
Comment by Elena Stepanova [ 2019-03-22 ]

You do not have the SUPER privilege, binary logging is enabled, log_bin_trust_function_creators is not. The error message seems perfectly accurate. What are you reporting as a bug here?

If you need help configuring your instance, please seek it from public forums and community mailing lists. You might also consider acquiring a support contract. If you already have one, please file a support ticket there.

Comment by anup b [ 2019-03-23 ]

I am referring documentation at - https://mariadb.com/kb/en/library/stored-routine-privileges/

It says - "To create a stored routine, the CREATE ROUTINE privilege is needed. The SUPER privilege is required if a DEFINER is declared that's not the creator's account (see DEFINER clause below). The SUPER privilege is also required if statement-based binary logging is used. See Binary Logging of Stored Routines for more details."

I am not specifying definer (defaulting to creator's account), with the set up mentioned above, I am able to create procedures but not functions. It nowhere mentions about SUPER priv.

Generated at Thu Feb 08 08:48:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.