Why am I unable to create this aggregate function?
As far as I can tell, I've granted the appropriate privileges (CREATE ROUTINE
and ALTER ROUTINE
) both on *.*
and on my database (peacock
):
mysql> SELECT User, Host, Create_priv, Create_routine_priv, Alter_routine_priv FROM mysql.user WHERE user='glpy';
+------+-----------+-------------+---------------------+--------------------+
| User | Host | Create_priv | Create_routine_priv | Alter_routine_priv |
+------+-----------+-------------+---------------------+--------------------+
| glpy | localhost | Y | Y | Y |
+------+-----------+-------------+---------------------+--------------------+
mysql> SELECT User, Host, db, Create_priv, Create_routine_priv, Alter_routine_priv FROM mysql.db WHERE user='glpy';
+------+-----------+---------+-------------+---------------------+--------------------+
| User | Host | db | Create_priv | Create_routine_priv | Alter_routine_priv |
+------+-----------+---------+-------------+---------------------+--------------------+
| glpy | localhost | peacock | N | Y | Y |
+------+-----------+---------+-------------+---------------------+--------------------+
However, when I try to create the function, I get Access denied ... to database 'mysql'
:
mysql> use peacock;
mysql> CREATE AGGREGATE FUNCTION coverage RETURNS INT SONAME 'my-library.so';
ERROR 1044 (42000): Access denied for user 'glpy'@'localhost' to database 'mysql'
In particular:
mysql
table to create the aggregate function?*.*
, why is access denied?mysql
, but access was still denied.CREATE [AGGREGATE] FUNCTION function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}
SONAME shared_library_name;
This is the declaration for a User Defined Function, which appears to be what you are trying to install. This is not declaring a Stored Function. CREATE FUNCTION
has two different uses in MySQL. This is one of them.
For this to work:
CREATE ROUTINE
privilegeAVG()
and SUM()
and MIN()
and MAX()
.13.7.3.1. CREATE FUNCTION Syntax for User-Defined Functions
To create a function, you must have the
INSERT
privilege for the mysql database. This is necessary becauseCREATE FUNCTION
adds a row to themysql.func
system table that records the function's name, type, and shared library name.— http://dev.mysql.com/doc/refman/5.6/en/create-function-udf.html
If you don't have this privilege, the function will have to be installed by someone who holds this privilege, or it will need to be granted to you. You can't grant it to yourself.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments