When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . Can someone explain me why its not getting logged . it should create row like mysql.proc table right whenever we create any stored procedure. I am using mysql 5.5.40. Thanks in advance.
1 Answer
All stored procedures and stored functions, whose code is written in the MySQL Stored Procedure Language, reside in mysql.proc
.
You can see how many of each with this
SELECT COUNT(1) rowcount,type
FROM mysql.proc GROUP BY type;
The only time I have ever seen mysql.func
is in context with AGGREGATE
functions:
According to MySQL Documentation on CREATE FUNCTION Syntax for User-Defined Functions
An AGGREGATE function works exactly like a native MySQL aggregate (summary) function such as SUM or COUNT(). For AGGREGATE to work, your mysql.func table must contain a type column. If your mysql.func table does not have this column, you should run the mysql_upgrade program to create it (see Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”).
My guess is that mysql.func
stores AGGREGATE
functions and not regular functions. Evidently, you would create some UDF in C/C++ and register it in mysql.func
using the CREATE AGGREGATE FUNCTION
syntax.
-
Thanks Ronaldo. But in the above link it is mentioned like "To create a function, you must have the INSERT privilege for the mysql database. This is necessary because CREATE FUNCTION adds a row to the mysql.func system table that records the function's name, type, and shared library name. If you do not have this table, you should run the mysql_upgrade command to create it. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”. "..it's little bit confusing actually. Please let me know if i am missing any point here.– kasiMar 18, 2015 at 14:17
-
The point is this: 1) If you wrote the stored function using MySQL Stored Procedure Language, it goes into
mysql.proc
usingCREATE FUNCTION
. 2) If you wrote the stored function in C/C++ and placed that compiled code in some.so
file, you must useCREATE AGGREGATE FUNCTION ... SONAME ...
and the function name goes intomysql.func
. Mar 18, 2015 at 15:07