This is a sneak peek video introduction/preview of an in-development free and open source server side debugger & debugging API for MySQL stored routines.
MySQL does not provide server side debugging capabilities for stored routines. Some tools exist, including MySQL’s own, that assist in stored routine debugging. These are all GUI based and, to the best of my knowledge, MS Windows based. There is one solution in alpha stage that is developed for Java/eclipse; I did not look at the code. See discussion here and here.
An ideal solution would be to have debugging API in the server itself – independently of your client, programming language or operating system. To the best of my knowledge, nothing like that is being developed.
I’m now presenting a rdebug: a stored routines server-side debugger, Pure-SQL, based on stored routines. rdebug is developed as part of common_schema, and actually relies on some of its power.
Like some other tools, it uses code injection and manipulation: it injects debugging info into your stored routine. You need to “compile” your routine with debugging info.
Unlike some other tools, it actually runs your stored routines. It does not mimic or simulate them on client side. It does not break them into smaller routines, attempting to assemble the original behavior from lego bricks.
The quick technical overview is that you use two processes (MySQL threads): the worker process running the routine (your natural call my_routine()), and the debugger process. The debugger process attaches itself to the worker process; it controls the worker by commands like “step over”; it gets data from the worker: what’s the current stack trace? What variables are now available and what are their values?; it manipulates the worker’s data: it can utilize breakpoints to modify worker’s local & session variables.
The debugger code is itself written with stored routines. Your own routines are analyzed by stored routine code. Stored routines inject code into your routine. Stored routines invoke and control the debugging progress. Stored routines manipulate your routine’s data. All access to the debugger is via (did I mention?) stored routines. This leads to server-side (alas not server-code) debugger & debugger API: any tool, GUI or command line, can utilize these stored routine calls so as to implement a debugger.
Since the solution is written with stored routines — thus allowing you to debug your existing 5.1, 5.5 & 5.6 servers — it is also limited by what info can be retrieved by stored routines. One can view/modify local routine variables & user defined variables, set breakpoints etc. One cannot get the “state of a cursor”, though, since no such info exists for a stored routine code.
having injected code operate from within your routine does mean a lot more actions are taken, like hidden INSERTs and UPDATEs. Calls to ROW_COUNT() and LAST_INSERT_ID() can be skewed. That’s a limitation I’m unsure as yet how to overcome; but not overcoming it won’t bring everything down as far as I’m concerned.
Some of the injected code calls upon dynamic SQL. This means at current stored functions are not possible to debug – just stored procedures. There’s a way around this for future development. There is no support for triggers, and I don’t expect there will be in the near future. No investigation into events as yet.
A video is worth a thousand blogs
Watch a live demo of debugging a simple routine; the demo presents a step-by-step debugging of a routine, listing, getting and setting variables.
What’s the status?
What you’ve seen in the video is a first actual milestone – not a POC. Still need to implement nested stack level, step-in and step-out commands, conditional breakpoints, worker temporary table access from debugger process. Lot’s of stuff, but looking good.
This work is the result of a personal interest. All input, feedback, profound mistakes, feature requests, “dude, there’s already a far better tool that does all that” comments, questions — are welcome!
Any chance of peeking the code?
@iwan,
Of course; shortly. Will commit to trunk in a few days. Will drop a note on these comments if you like.
There are about 800 lines of code so far, I expect it to remain less than double that number.