Taking common_schema’s rdebug to a test-drive

This is a simple step-by-step introduction to rdebug: Debugger and Debugging API for MySQL Stored Routines, as part of common_schema.

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.