How to get execution history of a stored procedure in MySQL

Question

I have created a stored procedure in MySQL named: inventory_procedure(fromDateTime)

A third party system executes this stored procedure at regular 1 hour interval by passing fromDateTime in dd-mm-yyyy hh:mm:ss format.

Sometimes, third party systems reports that stored procedure didn't returned any data.

When I hit the stored procedure it returns results for me.

is there any way where I can fetch information like:

When stored procedure was executed?
What was the fromDateTime passed in storedProcedure?
How many rows stored procedure returned?

You question is not clear somehow. Can you add your code of Stored Procedure. So that someone can look into your errors. Thanks. — Robin, Jul 27 '22 at 05:13
@Robin there is no error in stored procedure. Here third party is claiming that stored procedure didn't returned results but when I execute I get proper results. I want to know that when the store procedure was executed and with what params. I hope now the question is clear. Let me know if still there is any confusoin. — Lalit Dashora, Jul 27 '22 at 05:25
Now got your question. We can do this in SQL Server with profiler but I don't have any idea of MySQL. You can edit the title of your question to: How to get execution history of a stored procedure in MySQL. That will be more declarative to others. Thanks. — Robin, Jul 27 '22 at 06:00
This seems a simple, but effective approach: [How to debug a MySQL stored procedure.](https://stackoverflow.com/a/20755176/724039) — Luuk, Jul 29 '22 at 06:01
I would create a custom table to track the execution history of your stored procedure. The SP will feed this table with whatever data you need. — madmax, Jul 29 '22 at 08:33
@AnotherOne Can you elaborate more on this. How to create and feed data in custom table in this case? — Lalit Dashora, Jul 29 '22 at 10:07

score 1 · Accepted Answer · answered Jul 29 '22 at 17:52

Create a log table like this one:

create table inventory_procedure_log (
  id int unsigned auto_increment primary key,
  ts timestamp,
  fromDateTime datetime,
  rows_returned int
);

You will probably want to include some indexes for fast searches.

Insert the log entries within your procedure right after the final SELECT:

create procedure inventory_procedure (fromDateTime datetime)
begin
  -- random dummy result set
  select n
  from (select 1 n union select 2 union select 3) as x
  where rand() < 0.5;
  
  -- insert log entry
  insert into inventory_procedure_log(ts, fromDateTime, rows_returned) values (
    now(),
    fromDateTime,
    found_rows()
  );
end

Now you can search in your log table. For example - show all entries for the last 24 hours:

select *
from inventory_procedure_log
where ts > now() - interval 24 hour;

Se demo on dbfiddle.uk

You can extend the log table for more info (like session ID) as needed.

Let's improve on this slightly -- If the SP starts but dies before finishing, you have not clues. So do the INSERT into the Log at the very beginning of the SP. Then, at the very end, update that row (if convenient) or add another row. — Rick James, Jul 30 '22 at 05:35
Thanks @RickJames and Paul for your suggestions. The solution worked for me. — Lalit Dashora, Aug 04 '22 at 07:42

How to get execution history of a stored procedure in MySQL

1 Answers1