Proven Scaling - Group Blog

MySQL’s Stored Procedure implementation is far from perfect. One of the major problems has been in debugging tools and information available while writing and using the procedures you’ve written. In particular there is no real way to return customized error messages, e.g. RAISE or SIGNAL, and no way to find out what error actually occurred, e.g. accessing SQLSTATE. However, even with the standard error messages that MySQL already produces, there is another major problem: as a caller of the stored procedure, you can’t tell where the error actually came from.

In order to demonstrate this, let’s first create a few stored procedures that call each other in order to make things interesting:

use test;

delimiter ;;
drop procedure if exists s_test;;
create procedure s_test ()
begin
  call s_test_foo();
end;;

drop procedure if exists s_test_foo;;
create procedure s_test_foo ()
begin
  call s_test_bar();
end;;

drop procedure if exists s_test_bar;;
create procedure s_test_bar ()
begin
  select 1 from blarbo;
end;;

delimiter ;

Notice that the final stored procedure tries to select from a table that we haven’t mentioned yet; that’s because that table doesn’t exist. This should generate an error for us reliably. Here’s what happens when we call the stored procedure:

call test.s_test();

ERROR 1146 (42S02): Table 'test.blarbo' doesn't exist

Now that’s not very helpful: the user didn’t try to do anything with test.blarbo so he has no idea what has just happened! Technically it would be much better if the s_test_bar procedure could have generated a custom error message… I’ll leave that for a future post.

In the past I’ve discussed a patch to add a CALLER() function, and this patch is a natural evolution of that code. We’ve written a proof of concept patch to add a SHOW ERROR STACK TRACE command. This works by saving the call stack, which is tracked using the code from the old CALLER() patch, whenever an error occurs. Let’s try it out, after receiving any error from a SQL statement, you may run SHOW ERROR STACK TRACE as follows:

SHOW ERROR STACK TRACE;

+-------+----------------------+
| Depth | Query                |
+-------+----------------------+
|     0 | select 1 from blarbo |
|     1 | call s_test_bar()    |
|     2 | call s_test_foo()    |
|     3 | call test.s_test()   |
+-------+----------------------+
4 rows in set (0.00 sec)

That’s certainly more useful! I’ve still got a few ideas on how to improve it even more:

• Add something like e.g. SHOW ERROR VARIABLES FOR 1 which could return the contents of all local variables from a particular stack level from the stack trace.
• Add something like e.g. SHOW ERROR CONTEXT FOR 1 which could return the few lines of code before and after the error from a particular stack level from the stack trace.

Essentially, it would be great if you could do most of what gdb is capable of when debugging C code. I hope you like it!