how to debug MySql stored procs without breaking control flow from application

Question

Is there a way to do the following:

I have a MySQL DB , and there are many stored procs written in it as well. I use MySQL client library in C to connect to this DB and amongst other things , call the stored procedures. Is there a way to set breakpoints in the stored procedures such that when the call is made from C program ( using mySql client library ) into the stored proc , then control flow is halted in the C program and we can step into the stored proc called to whatever level of nesting and insspecting variables etc ( like any decent C debugged provides )?

Is there ANY way to do the above ? Through some third party tool or the like if not through plain MySql .

Help is appreciated.

thanks

Answer 1

It's not easy to debug MySQL stored procedures.

However, what you can do is instrument them using INSERTs into a log table of some description. Likewise, if you have a user-defined function which prints stuff out into a log, you can call that at various points to see what's going on.

Neither of these would interrupt the flow.

If you have a user-defined function called, you can set a breakpoint in there easily and attach to mysqld using gdb. However, inspecting state (i.e. variables) is not straightforward.

Answer 2

Thanks for your reply Mark. So the approach you are recommending is akin to the one in the following Dr.Dobbs Journal article drdobbs.com/database/218100564 As per my understanding the "debug" tools for MySql like DbForge for MySql also automate the process but in essence use the same approach . i.e. they print out the params or other stuff out to a debug table of sorts ( can include timestamp etc to get a sense of control flow and params when viewing the debug table later) . The only help these tools do is that they save the trouble of creating the debug table and manually writing insert statements at various points in the stored procs. is that so? That is still some help but what i was looking for is something available that just breaks into the stored proc at the breakpoint in continuation of control flow from C program , and lets us inspect the params passed from C app to the stored proc. stepping in , so on and so forth. Apparently there is no tool that does the same?

Anyone used the debug feature of DbForge? from this URL , it seems they might be supporting what i am looking for even though partially?

http://www.devart.com/dbforge/mysql/studio/articles/debugging.html

MArk , i did not understand your comment about breaking into mysql daemon itself .