In other words: let me take you through the steps for debugging your stored routines on your own server. We will step into, step over, step out, modify variables, set a breakpoint, run to breakpoint…

Command line geeks, this one’s for you. GUI lovers, this is actually an API; I am hoping for someone wrap it up with a plugin for your favorite GUI editor.

Requirements:

• Install common_schema 2.0 or above (at this time of writing 2.0.0-alpha is released).
• Get sample data & routine file [download id=”4″ format=”1″]
• mysql> SOURCE rdebug_demo.sql_.txt
  • You should now have a table called color_count in the test database, along with two routines: review_colors() and review_single_color().
• Open two sessions. We call them the debugger session and the worker session. The worker session will execute the routine; the debugger session will control it.

Walk-through: preparation

Walk this thing with me. We will alternate between the debugger and the worker.

1. worker session: get connection ID.

mysql [worker]> select CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|            1234 |
+-----------------+

I’ll use 1234, you will use whatever connection ID your worker has.

2. debugger session: “compile” routine with debug info (this injects code into your routines).

mysql [debugger]> use common_schema;
mysql [debugger]> call rdebug_compile_routine('test', 'review_colors', true);
mysql [debugger]> call rdebug_compile_routine('test', 'review_single_color', true);

If you like, review the routines after compilation as follows:

mysql [debugger]> call rdebug_show_routine('test', 'review_colors');
+---------------------------------------------------------------------------------+
| `test`.`review_colors` breakpoints                                              |
+---------------------------------------------------------------------------------+
| begin                                                                           |
|   declare done bool default false;                                              |
|   declare current_color varchar(32) default null;                               |
|   declare current_count int unsigned;                                           |
|   declare color_cursor cursor for                                               |
|             select color_name, count from test.color_count order by color_name; |
|   declare continue handler for not found set done := true;                      |
|                                                                                 |
|   [:94]open color_cursor;                                                       |
|   [:100]cursor_loop: while not done do                                          |
|     [:112]fetch color_cursor into current_color, current_count;                 |
|     [:125]if done then                                                          |
|       [:132]leave cursor_loop;                                                  |
|     [:138]end if;                                                               |
|                                                                                 |
|     [:145]call review_single_color(current_color);                              |
|   [:154]end while;                                                              |
|   [:160]close color_cursor;                                                     |
| [:165]end                                                                       |
+---------------------------------------------------------------------------------+

mysql [debugger]> call rdebug_show_routine('test', 'review_single_color');
+----------------------------------------------------------------+
| `test`.`review_single_color` breakpoints                       |
+----------------------------------------------------------------+
| begin                                                          |
|   [:4]set @review_message := concat(color_name, ' is pretty'); |
|   [:20]select @review_message;                                 |
| [:25]end                                                       |
+----------------------------------------------------------------+

The above shows the routine code with symbolic breakpoint IDs.

Walk-through – start debugging

3. debugger session: Start a debug session, attach to worker session using its connection ID:

mysql [debugger]> call rdebug_start(1234);

Replace 1234 with your own worker’s connection ID as read above.

Let’s set verbose mode on; more fun on command line, less typing.

mysql [debugger]> call rdebug_set_verbose(true);

And step into it!

mysql [debugger]> call rdebug_step_into();

This should hang the debugger. Why? Because it’s stepping into, and is expecting the worker to actually do something.

4. worker session: execute routine

mysql [worker]> call test.review_colors();

Walk-through – debug

The debugger session should immediately follow with the following (all by entry_time should be identical to your output):

+-------------+----------------+---------------+--------------+---------------------+
| stack_level | routine_schema | routine_name  | statement_id | entry_time          |
+-------------+----------------+---------------+--------------+---------------------+
|           1 | test           | review_colors |           94 | 2013-04-08 15:41:28 |
+-------------+----------------+---------------+--------------+---------------------+

+----------------+---------------+---------------+---------------+----------------+
| routine_schema | routine_name  | variable_name | variable_type | variable_value |
+----------------+---------------+---------------+---------------+----------------+
| test           | review_colors | current_color | local         | NULL           |
| test           | review_colors | current_count | local         | NULL           |
| test           | review_colors | done          | local         | 0              |
+----------------+---------------+---------------+---------------+----------------+

+----------------+---------------+--------------+-------------------+
| routine_schema | routine_name  | statement_id | statement         |
+----------------+---------------+--------------+-------------------+
| test           | review_colors |           94 | open color_cursor |
+----------------+---------------+--------------+-------------------+

That’s the result of setting verbose mode. From here, if you’ve ever debugged code, the way is clear:

5. debugger session: Step into a few more times:

mysql [debugger]> call rdebug_step_into();
mysql [debugger]> call rdebug_step_into();
mysql [debugger]> call rdebug_step_into();
...

Until the stack shows that you have entered the second routine: review_single_color():

+-------------+----------------+---------------------+--------------+---------------------+
| stack_level | routine_schema | routine_name        | statement_id | entry_time          |
+-------------+----------------+---------------------+--------------+---------------------+
|           1 | test           | review_colors       |          145 | 2013-04-08 15:41:28 |
|           2 | test           | review_single_color |           20 | 2013-04-08 15:45:23 |
+-------------+----------------+---------------------+--------------+---------------------+

+----------------+---------------------+-----------------+---------------+-----------------+
| routine_schema | routine_name        | variable_name   | variable_type | variable_value  |
+----------------+---------------------+-----------------+---------------+-----------------+
| test           | review_single_color | @review_message | user_defined  | green is pretty |
| test           | review_single_color | color_name      | param         | green           |
+----------------+---------------------+-----------------+---------------+-----------------+

+----------------+---------------------+--------------+------------------------+
| routine_schema | routine_name        | statement_id | statement              |
+----------------+---------------------+--------------+------------------------+
| test           | review_single_color |           20 | select @review_message |
+----------------+---------------------+--------------+------------------------+

You can further call rdebug_step_out() to leave this routine, rdebug_step_over() to avoid re-entry…

6. debugger session: modify variables

Assuming you are inside the review_single_color() routine, would you like to modify a variable?

mysql [debugger]> call rdebug_set_variable('color_name', 'A flower');

Step over a few more times till the worker produces:

+--------------------+
| @review_message    |
+--------------------+
| A flower is pretty |
+--------------------+

Continue playing with rdebug_step_into(), rdebug_step_over(), rdebug_step_out().

7. debugger session: setting a breakpoint

Based on the output of rdebug_show_routine(‘test’, ‘review_colors’), above, we now choose to set a non-conditional breakpoint, just before the statement call review_single_color(current_color). That makes breakpoint ID 145.

mysql [debugger]> call rdebug_set_breakpoint(‘test’, ‘review_colors’, 145, NULL, true);

8. debugger session: running up to a breakpoint

Now, let’s allow the worker to run until it reaches this breakpoint:

mysql [debugger]> call rdebug_run();
+-------------+----------------+---------------+--------------+---------------------+
| stack_level | routine_schema | routine_name  | statement_id | entry_time          |
+-------------+----------------+---------------+--------------+---------------------+
|           1 | test           | review_colors |          145 | 2013-04-08 15:41:28 |
+-------------+----------------+---------------+--------------+---------------------+

+----------------+---------------+---------------+---------------+----------------+
| routine_schema | routine_name  | variable_name | variable_type | variable_value |
+----------------+---------------+---------------+---------------+----------------+
| test           | review_colors | current_color | local         | white          |
| test           | review_colors | current_count | local         | 10             |
| test           | review_colors | done          | local         | 0              |
+----------------+---------------+---------------+---------------+----------------+

+----------------+---------------+--------------+-----------------------------------------+
| routine_schema | routine_name  | statement_id | statement                               |
+----------------+---------------+--------------+-----------------------------------------+
| test           | review_colors |          145 | call review_single_color(current_color) |
+----------------+---------------+--------------+-----------------------------------------+

Run the above a few times: we always get back to the same statement. That is, until there’s nothing more to do and the routine leaves.

Walk-through – stopping and cleanup

8. debugger session: Stop the debugging session:

mysql [debugger]> call rdebug_stop();

You can start again via rdebug_start(). If, however, you’re no longer interested in debugging, you should remove debugging code from your routines:

mysql [debugger]> call rdebug_compile_routine('test', 'review_colors', false);
mysql [debugger]> call rdebug_compile_routine('test', 'review_single_color', false);

Conclusion

This is most there is to it. Read the API for a complete list of functionality

With a different license in mind for rdebug, common_schema changes license to GPL (2 or above).

common_schema 2.0 is ready for download. All things rdebug, it is alpha — otherwise it’s a stable release.

rdebug

I’m very happy to release this alpha version of rdebug, and urge everyone to try it out.

The idea is to have an open, free, server side debugger and debugging API for MySQL stored routines. To elaborate:

• It’s server side by that it’s implemented by stored routines. Not by a connector; not an emulator; not a GUI tool hack. The entire functionality lies within common_schema, a schema in your server.
• It’s a debugger: you can debug your own stored routines (with limitations)
• It’s a debugging API: there’s a distinct specification and a set of calls which makes for a debugging process
• It’s open since the source code is yours to browse.
• It’s free as in free beer.
• It’s free as it makes you independent of a specific debugger. It provides an API that anyone can use. You can run the API yourself from the command line; or plugins for your favorite GUI editor can be developed to use this API.

On a separate blog post I will take you to a rdebug test drive.

As always, nothing is released before extensive documentation is in place.

I’d love to get input on this.

GPL

common_schema < 2.0 was released under the BSD license, which is less restrictive. I was pondering the BSD license for a couple years now, and with the arrival of rdebug have decided to switch to GPL. I’ve been through this thinking of change of license in other projects of mine; am generally agreeing that best not to change licensing throughout lifetime. I actually do see GPL as promoting open source software better than BSD, and with all the issues around GPL this actually means something to me. I write open source; I love people using it; I love people extending it; I want to be re-released as open source, or I want better control of the code.

So this turns out to be something that is important to me, and just before common_schema takes the world in storm (212 downloads today, 212,000,000 tomorrow), I want to have this settled. If no storm comes, well, I’ll have up to 212 people banging on my door (I provide free coffee).

Text routines

Two text routines are added:

• replace_sections(): replace a text given a from-to combination, and a replacement string, which could include a back-reference. For example:

mysql> select replace_sections('The <b>quick</b> brown <b>fox</b>', 
        '<b>', '</b>', 
        '<span>\\0</span>') as result;
+-----------------------------------------------+
| result                                        |
+-----------------------------------------------+
| The <span>quick</span> brown <span>fox</span> |
+-----------------------------------------------+

• hexcode_text(): a convenience routine which shows a beautified hex-code of a given text. I get to need it when using UNICODE characters which are hard to detect, are visually identical to other characters, or are copied from MS Word.

mysql> call hexcode_text('the quick brown fox jumps over the lazy dog');
+-----------------------------------------------------------------------------------+
| >0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f   0 1 2 3 4 5 6 7 8 9 a b c d e f |
+-----------------------------------------------------------------------------------+
| 74 68 65 20 71 75 69 63 6B 20 62 72 6F 77 6E 20   t h e   q u i c k   b r o w n   |
| 66 6F 78 20 6A 75 6D 70 73 20 6F 76 65 72 20 74   f o x   j u m p s   o v e r   t |
| 68 65 20 6C 61 7A 79 20 64 6F 67                  h e   l a z y   d o g           |
+-----------------------------------------------------------------------------------+

Get it

Download common_schema here.

rdebug will be released as part of common_schema, free and open sourced.

In this sneak preview I present:

• Compiling multiple routines with debug info
• Starting/stopping a debug session
• Step-over, step-in, step-out
• Showing stack trace
• Showing the next-statement to execute
• Viewing and manipulating local routine variables
• Misc. meta routines

The quick technical overview

rdebug is a server-side mechanism, itself written in MySQL stored routines. It manipulates your routines by injecting debug code (easily removed afterwards).

To debug a routine you will need two connections: one is the debugging connection, and the other is the worker connection. The debugger connection attaches itself to the worker connection, where your routines execute.

rdebug is controlled by an API of stored routines. This means any GUI tool may choose to use rdebug as its routine debugging mechanism. Your are not bound to a specific tool, a specific OS or framework. You may choose to invoke the API via command line, if you like; it’s all in your server.

A video is worth a thousand blogs

The following video demonstrates the debugging process of two stored procedures, one invoking the other. This allows for step-in/over/out commands, stack trace analysis, and of course variable inspection and modification.

I can’t say I caught the hang of capturing my desktop and editing the movie (doing it one on Linux and once on Mac), so please excuse any poor quality video/sound.

The code is not yet released, but will be, shortly, under an open source license.