CODEWITHSUNDEEP
mysqlloggingstored-procedures
Daniel Castro
Daniel Castro

Reputation: 157

MySQL Stored Procedure logging

I have an app with all the business rules in stored procedures. Every stored procedure calls one stored procedure called verificaToken. The function of this procedure is to verify if the session token is correct. i want to log all the stored procedure calls and I would like to use this verificaToken because is already implemented in all stored procedures. Is there anyway to verificaToken identify it's caller and parameters?

Thank you !

Upvotes: 1

Views: 1738

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

You're asking for something analogous to a reflection package so verificaToken can get information about the call stack.

No, there is no information function about the call stack in MySQL stored procedures.

The only things you can do are:

  • Pass the information as arguments to verificaToken every time you call it.
  • Set user-defined variables, which can be read by verificaToken.

User defined variables are documented here: https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

Example:

mysql> delimiter ;;

mysql> create procedure myproc()
    -> begin
    -> set @mycaller='myproc';
    -> call verificaToken();
    -> end;;

mysql> create procedure verificaToken()
    -> begin
    -> select @mycaller;
    -> end;;

mysql> delimiter ;

mysql> call myproc();
+-----------+
| @mycaller |
+-----------+
| myproc    |
+-----------+

The result set returned is coming from verificaToken(). You can do anything you want with that variable in the body of that procedure, like log it as you said. I am just demonstrating that one can set a variable in one procedure, and it's automatically visible to any other procedure you call.

These variables are "global" in the sense that any procedure can read it, but it's limited to your own MySQL session, so no concurrent clients can read the variable.

Upvotes: 1

Related Questions