Skip to content

# Taking common_schema’s rdebug to a test-drive

MySQL common_schema rdebug Stored routines April 9, 2013

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

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

### Leave a Reply

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

Subscribe
Notify of
wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.