Blog

Debugging stored programs in MySQL?

The headline above has two parts: 1) some letters 2) a question mark. The question mark is the important thing here! It resolves to:

* Can we debug stored programs in MySQL?
* Do you debug stored programs in MySQL?
* How do you debug stored programs in MySQL?

We have tried, but we gave up! In the early stages of our IssueBurner application we actually used Stored Procedures quite a lot. As the complexity increased debugging became so tedious that we recoded the application ‘the traditional way’ coding the logic in the application code instead.

In simple cases you may of course add a little debugging code that writes some data to user variables, temporary tables etc. Next remove them or comment them when you want to turn debugging off. If you want to work more systematically you can add a IN-parameter (debug: integer) to a Stored Procedure paramer-list and CALL mysp(….,0|1) what would then control if the stored program should enter or bypass debugging code when executing.

It should not be like that in the 21st century. Debugging options should be part of the server code itself and there should be an API for it. You should be able to execute line-by-line, set breakpoints, view the status of variables, handlers and cursors for every stepĀ  just like you would do in a true Integrated Development Environment (IDE). Stored programs is code just like application code is.

Some years ago (I think around 3 years ago) I joined a ‘vote’ on the MySQL website where I had an option to vote for my personal priorities of future MySQL development. An API for debugging stored programs was one of the options listed and I cast all my 10 votes to it.

I would not live without Stored Functions and Events. They will also most often be pretty simple and thus rarely cause problems. Stored Procedures is another matter – they can be and will often need to be very complex to be usable for what you want to achieve (and let us forget Triggers here – they are close to being a joke in MySQL).

I have seen some solutions claiming to be able to debug MySQL Stored Procedures. What I have seen all use some kind of (very simple, really) emulation (like replacing loops with sequential statements, local variables with user variables or they will rewrite the original SP to a ‘cascaded series’ of SP’s calling each others). All what I have seen fails with examples just a little bit more complex than trivial (a few nested loops, some handlers or cursors and similar is usually enough to get weird results).

I write this because we now again had a request for a ‘stored program debugger’ in SQLyog. We would be happy to develop it (it is around 5 years ago we discussed first time I think), but without proper API support from the server we will not even attempt it. Past attempts are not encouraging.

I wonder:
* Does anybody know about any progress with stored programs debugging API/functionalities in MySQL?
* How do
you debug your stored programs?

4 thoughts on “Debugging stored programs in MySQL?

  1. I vaguely remember working on the WorkLog for stored procedure debugging. Alas, Sun acquired MySQL before I was able to work on it.

  2. Peter,
    I’m using session variables to debug, as well as trace writes to some pre-defined log table.
    The latter is not very different from dumping data to standard output.
    With stored functions, the former is a problem with loops, since you can only check at the end.
    With procedures this is easier since result sets can be dumped at any stage.

  3. Pingback: Debugging stored programs in MySQL? | DCon Web - Information Technology Solutions

  4. I’m now offering “rdebug”: debugger & debugging API for MySQL stored routines, as part of common_schema. It is in alpha stage; here’s a quick walk-through.

    It is a server-side component, and runs in itself from within stored routines (stored routines controlling and debugging stored routines).

    It does not do emulation, but instead work by injecting code onto one’s routines (so it “compiles” routines with/out debug info). You’ll need a worker session which runs the routine, and a debugger session which controls (i.e. steps, sets breakpoint, modifies variables etc.) it.

    I’m happy to get any feedback!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>