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