0

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.

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • 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.
    – kasi
    Mar 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 using CREATE FUNCTION. 2) If you wrote the stored function in C/C++ and placed that compiled code in some .so file, you must use CREATE AGGREGATE FUNCTION ... SONAME ... and the function name goes into mysql.func.
    – RolandoMySQLDBA
    Mar 18, 2015 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.