{"id":6211,"date":"2013-04-09T09:36:17","date_gmt":"2013-04-09T07:36:17","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6211"},"modified":"2013-04-09T09:36:17","modified_gmt":"2013-04-09T07:36:17","slug":"taking-common_schemas-rdebug-to-a-test-drive","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/taking-common_schemas-rdebug-to-a-test-drive","title":{"rendered":"Taking common_schema&#8217;s rdebug to a test-drive"},"content":{"rendered":"<p>This is a simple step-by-step introduction to <strong><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/rdebug.html\">rdebug<\/a>: Debugger and Debugging API for MySQL Stored Routines<\/strong>, as part of <a href=\"https:\/\/code.google.com\/p\/common-schema\/\">common_schema<\/a>.<\/p>\n<p>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&#8230;<\/p>\n<p>Command line geeks, this one&#8217;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.<\/p>\n<h4>Requirements:<\/h4>\n<ul>\n<li>Install <a href=\"https:\/\/code.google.com\/p\/common-schema\/\"><em>common_schema<\/em> 2.0<\/a> or above (at this time of writing <a href=\"http:\/\/code.openark.org\/blog\/mysql\/common_schema-2-0-0-alpha-rdebug-gpl\">2.0.0-alpha is released<\/a>).<\/li>\n<li>Get sample data &amp; routine file [download id=&#8221;4&#8243; format=&#8221;1&#8243;]<\/li>\n<li>mysql&gt; <strong>SOURCE rdebug_demo.sql_.txt<\/strong>\n<ul>\n<li>You should now have a table called <strong>color_count<\/strong> in the test database, along with two routines: <strong>review_colors()<\/strong> and <strong>review_single_color()<\/strong>.<\/li>\n<\/ul>\n<\/li>\n<li>Open two sessions. We call them the <em>debugger<\/em> session and the <em>worker<\/em> session. The <em>worker<\/em> session will execute the routine; the <em>debugger<\/em> session will control it.<\/li>\n<\/ul>\n<h4>Walk-through: preparation<\/h4>\n<p>Walk this thing with me. We will alternate between the <em>debugger<\/em> and the <em>worker<\/em>.<!--more--><\/p>\n<p><strong>1. worker session:<\/strong> get connection ID.<\/p>\n<blockquote>\n<pre><em><span style=\"color: #003300;\">mysql [worker]&gt; <strong>select CONNECTION_ID();<\/strong>\r\n+-----------------+\r\n| CONNECTION_ID() |\r\n+-----------------+\r\n|            1234 |\r\n+-----------------+<\/span><\/em><\/pre>\n<\/blockquote>\n<p>I&#8217;ll use <strong>1234<\/strong>, you will use whatever connection ID your worker has.<\/p>\n<p><strong>2. debugger session:<\/strong> &#8220;compile&#8221; routine with debug info (this injects code into your routines).<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>use common_schema;<\/strong>\r\nmysql [debugger]&gt; <strong>call rdebug_compile_routine('test', 'review_colors', true);<\/strong>\r\nmysql [debugger]&gt; <strong>call rdebug_compile_routine('test', 'review_single_color', true);<\/strong><\/span><\/pre>\n<\/blockquote>\n<p>If you like, review the routines after compilation as follows:<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_show_routine('test', 'review_colors');<\/strong>\r\n+---------------------------------------------------------------------------------+\r\n| `test`.`review_colors` breakpoints\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------------------------------------------------------------------+\r\n| begin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 declare done bool default false;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 declare current_color varchar(32) default null;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 declare current_count int unsigned;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 declare color_cursor cursor for\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select color_name, count from test.color_count order by color_name; |\r\n|\u00a0\u00a0 declare continue handler for not found set done := true;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 [:94]open color_cursor;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 [:100]cursor_loop: while not done do\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0 [:112]fetch color_cursor into current_color, current_count;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0 [:125]if done then\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [:132]leave cursor_loop;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0 [:138]end if;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0 [:145]call review_single_color(current_color);\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 [:154]end while;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 [:160]close color_cursor;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| [:165]end\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------------------------------------------------------------------+\r\n\r\nmysql [debugger]&gt; <strong>call rdebug_show_routine('test', 'review_single_color');<\/strong>\r\n+----------------------------------------------------------------+\r\n| `test`.`review_single_color` breakpoints\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----------------------------------------------------------------+\r\n| begin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 [:4]set @review_message := concat(color_name, ' is pretty'); |\r\n|\u00a0\u00a0 [:20]select @review_message;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| [:25]end\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----------------------------------------------------------------+\r\n<\/span><\/pre>\n<\/blockquote>\n<p>The above shows the routine code with symbolic breakpoint IDs.<\/p>\n<h4>Walk-through &#8211; start debugging<\/h4>\n<p><strong>3. debugger session:<\/strong> Start a debug session, attach to <i>worker<\/i> session using its connection ID:<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_start(1234);<\/strong><\/span><\/pre>\n<\/blockquote>\n<p>Replace <strong>1234<\/strong> with your own worker&#8217;s connection ID as read above.<\/p>\n<p>Let&#8217;s set verbose mode on; more fun on command line, less typing.<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_set_verbose(true);<\/strong><\/span><\/pre>\n<\/blockquote>\n<p>And step into it!<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_step_into();<\/strong><\/span><\/pre>\n<\/blockquote>\n<p>This should hang the debugger. Why? Because it&#8217;s stepping into, and is expecting the worker to actually do something.<\/p>\n<p><strong>4. worker session:<\/strong> execute routine<\/p>\n<blockquote>\n<pre><span style=\"color: #003300;\"><em>mysql [worker]&gt; <strong>call test.review_colors();<\/strong><\/em><\/span><\/pre>\n<\/blockquote>\n<h4>Walk-through &#8211; debug<\/h4>\n<p>The debugger session should immediately follow with the following (all by <strong>entry_time<\/strong> should be identical to your output):<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">+-------------+----------------+---------------+--------------+---------------------+<\/span>\r\n<span style=\"color: #000080;\">| stack_level | routine_schema | routine_name\u00a0 | statement_id | entry_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">+-------------+----------------+---------------+--------------+---------------------+<\/span>\r\n<span style=\"color: #000080;\">|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 | test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94 | 2013-04-08 15:41:28 |<\/span>\r\n<span style=\"color: #000080;\">+-------------+----------------+---------------+--------------+---------------------+<\/span>\r\n\r\n<span style=\"color: #000080;\">+----------------+---------------+---------------+---------------+----------------+<\/span>\r\n<span style=\"color: #000080;\">| routine_schema | routine_name\u00a0 | variable_name | variable_type | variable_value |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------+---------------+---------------+----------------+<\/span>\r\n<span style=\"color: #000080;\">| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors | current_color | local\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors | current_count | local\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors | done\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | local\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------+---------------+---------------+----------------+<\/span>\r\n\r\n<span style=\"color: #000080;\">+----------------+---------------+--------------+-------------------+<\/span>\r\n<span style=\"color: #000080;\">| routine_schema | routine_name\u00a0 | statement_id | statement\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------+--------------+-------------------+<\/span>\r\n<span style=\"color: #000080;\">| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 94 | open color_cursor |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------+--------------+-------------------+<\/span><\/pre>\n<\/blockquote>\n<p>That&#8217;s the result of setting verbose mode. From here, if you&#8217;ve ever debugged code, the way is clear:<\/p>\n<p><strong>5. debugger session:<\/strong> Step into a few more times:<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_step_into();<\/strong>\r\nmysql [debugger]&gt; <strong>call rdebug_step_into();<\/strong>\r\nmysql [debugger]&gt; <strong>call rdebug_step_into();<\/strong>\r\n...<\/span><\/pre>\n<\/blockquote>\n<p>Until the stack shows that you have entered the second routine: <strong>review_single_color():<\/strong><\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">+-------------+----------------+---------------------+--------------+---------------------+<\/span>\r\n<span style=\"color: #000080;\">| stack_level | routine_schema | routine_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | statement_id | entry_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">+-------------+----------------+---------------------+--------------+---------------------+<\/span>\r\n<span style=\"color: #000080;\">|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 | test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 145 | 2013-04-08 15:41:28 |<\/span>\r\n<span style=\"color: #000080;\">|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>2<\/strong> | <strong>test<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | <strong>review_single_color<\/strong> |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20 | 2013-04-08 15:45:23 |<\/span>\r\n<span style=\"color: #000080;\">+-------------+----------------+---------------------+--------------+---------------------+<\/span>\r\n\r\n<span style=\"color: #000080;\">+----------------+---------------------+-----------------+---------------+-----------------+<\/span>\r\n<span style=\"color: #000080;\">| routine_schema | routine_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | variable_name\u00a0\u00a0 | variable_type | variable_value\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------------+-----------------+---------------+-----------------+<\/span>\r\n<span style=\"color: #000080;\">| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_single_color | @review_message | user_defined\u00a0 | green is pretty |<\/span>\r\n<span style=\"color: #000080;\">| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_single_color | color_name\u00a0\u00a0\u00a0\u00a0\u00a0 | param\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | green\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------------+-----------------+---------------+-----------------+<\/span>\r\n\r\n<span style=\"color: #000080;\">+----------------+---------------------+--------------+------------------------+<\/span>\r\n<span style=\"color: #000080;\">| routine_schema | routine_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | statement_id | statement\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------------+--------------+------------------------+<\/span>\r\n<span style=\"color: #000080;\">| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_single_color |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20 | select @review_message |<\/span>\r\n<span style=\"color: #000080;\">+----------------+---------------------+--------------+------------------------+<\/span><\/pre>\n<\/blockquote>\n<p>You can further call <strong>rdebug_step_out()<\/strong> to leave this routine, <strong>rdebug_step_over()<\/strong> to avoid re-entry&#8230;<\/p>\n<p><strong>6. debugger session:<\/strong> modify variables<\/p>\n<p>Assuming you are inside the <strong>review_single_color()<\/strong> routine, would you like to modify a variable?<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_set_variable('color_name', 'A flower');<\/strong><\/span><\/pre>\n<\/blockquote>\n<p>Step over a few more times till the worker produces:<\/p>\n<blockquote>\n<pre><span style=\"color: #003300;\"><em>+--------------------+<\/em><\/span>\r\n<span style=\"color: #003300;\"><em>| @review_message\u00a0\u00a0\u00a0 |<\/em><\/span>\r\n<span style=\"color: #003300;\"><em>+--------------------+<\/em><\/span>\r\n<span style=\"color: #003300;\"><em>| A flower is pretty |<\/em><\/span>\r\n<span style=\"color: #003300;\"><em>+--------------------+<\/em><\/span><\/pre>\n<\/blockquote>\n<p>Continue playing with <strong>rdebug_step_into()<\/strong>, <strong>rdebug_step_over()<\/strong>, <strong>rdebug_step_out()<\/strong>.<\/p>\n<p><strong>7. debugger session:<\/strong> setting a breakpoint<\/p>\n<p>Based on the output of <strong>rdebug_show_routine(&#8216;test&#8217;, &#8216;review_colors&#8217;)<\/strong>, above, we now choose to set a non-conditional breakpoint, just before the statement <strong>call review_single_color(current_color)<\/strong>. That makes breakpoint ID <strong>145<\/strong>.<\/p>\n<blockquote><p><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_set_breakpoint(&#8216;test&#8217;, &#8216;review_colors&#8217;, 145, NULL, true);<\/strong><\/span><\/p><\/blockquote>\n<p><strong>8. debugger session:<\/strong> running up to a breakpoint<strong><br \/>\n<\/strong><\/p>\n<p>Now, let&#8217;s allow the worker to run until it reaches this breakpoint:<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_run();<\/strong>\r\n+-------------+----------------+---------------+--------------+---------------------+\r\n| stack_level | routine_schema | routine_name\u00a0 | statement_id | entry_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------+----------------+---------------+--------------+---------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 | test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 145 | 2013-04-08 15:41:28 |\r\n+-------------+----------------+---------------+--------------+---------------------+\r\n\r\n+----------------+---------------+---------------+---------------+----------------+\r\n| routine_schema | routine_name\u00a0 | variable_name | variable_type | variable_value |\r\n+----------------+---------------+---------------+---------------+----------------+\r\n| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors | current_color | local\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | white\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors | current_count | local\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors | done\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | local\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----------------+---------------+---------------+---------------+----------------+\r\n\r\n+----------------+---------------+--------------+-----------------------------------------+\r\n| routine_schema | routine_name\u00a0 | statement_id | statement\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----------------+---------------+--------------+-----------------------------------------+\r\n| test\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | review_colors |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>145<\/strong> | <strong>call review_single_color(current_color)<\/strong> |\r\n+----------------+---------------+--------------+-----------------------------------------+<\/span><\/pre>\n<\/blockquote>\n<p>Run the above a few times: we always get back to the same statement. That is, until there&#8217;s nothing more to do and the routine leaves.<\/p>\n<h4>Walk-through &#8211; stopping and cleanup<\/h4>\n<p><strong>8. debugger session:<\/strong> Stop the debugging session:<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_stop();<\/strong><\/span><\/pre>\n<\/blockquote>\n<p>You can start again via <strong>rdebug_start()<\/strong>. If, however, you&#8217;re no longer interested in debugging, you should remove debugging code from your routines:<\/p>\n<blockquote>\n<pre><span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_compile_routine('test', 'review_colors', false);<\/strong><\/span>\r\n<span style=\"color: #000080;\">mysql [debugger]&gt; <strong>call rdebug_compile_routine('test', 'review_single_color', false);<\/strong><\/span><\/pre>\n<\/blockquote>\n<h4>Conclusion<\/h4>\n<p>This is most there is to it. Read the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/rdebug_api.html\"><strong>API<\/strong><\/a> for a complete list of functionality<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230; [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,99,59],"class_list":["post-6211","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-rdebug","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Cb","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=6211"}],"version-history":[{"count":35,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6211\/revisions"}],"predecessor-version":[{"id":6266,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6211\/revisions\/6266"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}