I have merged a debugger for MySQL/MariaDB stored procedures and functions into our GUI client and posted the source and binaries on github. It allows breakpoint, clear, continue, next, skip, step, tbreakpoint, and variable displays. Features which are rare or missing in other debuggers include:
its current platform is Linux;
it allows breakpoints on functions which are invoked within SQL statements;
it never changes existing stored procedures or functions;
it is integrated with a general GUI client;
it allows commands like gdb and allows menu items / shortcut keys like ddd;
it is available on github as C++ source with GPL licence.
It's alpha and it's fragile but it works. Here is a demo.
Start the client and connect to a running server, as root. Actually the required privileges are merely for creation of certain objects and SUPER, but I'm making this simple.
Type the statement: DELIMITER // (For all typed-in instructions, execute by typing carriage-return after the delimiter or by clicking Run|Execute.)
Create a function fdemo which updates and returns a counter.
CREATE FUNCTION fdemo () RETURNS INT BEGIN IF @counter IS NULL THEN SET @counter = 0; END IF; SET @counter = @counter + 1; RETURN @counter; END //
Create a procedure pdemo which contains a loop of "INSERT INTO tdemo VALUES (fdemo())" statements.
CREATE PROCEDURE pdemo () BEGIN DECLARE i INT; CREATE TABLE IF NOT EXISTS tdemo (s1 INT); SET i = 0; WHILE i < 100 DO INSERT INTO tdemo VALUES (fdemo()); SET i = i + 1; END WHILE; SET i = i + 1; END //
Type the statement: DELIMITER ;
Type the statement: $setup fdemo, pdemo;
Type the statement: $debug pdemo;
After this statement is executed a tabbed widget appears. The first line of pdemo is highlighted. There is always a breakpoint before the first line.
Click on the Debug menu to see what options are available. Debugger instructions may be done via the menu, via Alt keys, or via command-line statements. For example to enter a Next instruction one may now click Debug|Next, or type Alt+3, or type the statement: "$next;".
Enter a Next instruction repeatedly, watching how the highlighted line changes, until the INSERT line is highlighted.
Enter a Step instruction. After this is executed, the function now comes into the foreground.
Enter three more Step (or Next) instructions. After these are executed, the procedure now is in the foreground again.
Set a breakpoint for the final executable line of the procedure. This can be done by clicking over the line number, or by moving the cursor to the line and then clicking Debug|Breakpoint, or by typing the statement "$breakpoint pdemo 9;". After this is executed, line 9 of pdemo has a small red mark showing that it has a breakpoint.
Enter a Continue instruction. After this is executed, watch the debugger highlight hop around 100 times as it moves through the loop, and finally settle on line 9.
Type the statements "$refresh variables;" and "select old_value, value from xxxmdbug.variables;". After this is executed, the result-set widget will contain the old value of variable i (99) and the current value (100), This is the way that one examines DECLAREd variables (there are other statements for user variables and server variables).
Enter an $exit instruction. This stops the debugging session, so the effects of the earlier $debug instruction are cancelled. The effects of the earlier $install and $setup instructions are not cancelled, so they will not have to be repeated for the next debugging session involving pdemo and fdemo.
Thus ends our demo. If you would like to confirm it: an introduction for how ocelotgui in general works is in the earlier blog post An open-source MySQL/MariaDB client on Linux" and the source + executable download files for version 0.3.0 are on github.com/ocelot-inc/ocelotgui.
If there is anything Ocelot can do to make your next debugging trip more enjoyable, please leave a comment. If you have private thoughts, please write to pgulutzan at ocelot.ca.