But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time:

1. Variables/status moved to a different table
2. Privileges required on said table

As an example, my non-root user gets:

mysql> show session variables like 'tx_isolation';
ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'

Who gets affected by this? Nearly everyone and everything.

• Your Nagios will not be able to read status variables
• Your ORM will not be able to determine session variables
• Your replication user will fail connecting (see this post by Giuseppe)
• And most everyone else.

The problem with the above is that involves two unrelated changes to your setup, which are not entirely simple to coordinate:

1. Change your app code to choose the correct schema (information_schema vs. performance_schema)
2. GRANT the permissions on your database

Perhaps at this point you still do not consider this to be a problem. You may be thinking: well, let’s first prepare by creating the GRANTs, and once that is in place, we can, at our leisure, modify the code.

Not so fast. Can you really that simply create those GRANTs?

Migration woes

How do you migrate to a new MySQL version? You do not reinstall all your servers. You want an easy migration path, and that path is: introduce one or two slaves of a newer version, see that everything works to your satisfaction, slowly upgrade all your other slaves, eventually switchover/upgrade your master.

This should not be any different for 5.7. We would like to provision a 5.7 slave in our topologies and just see that everything works. Well, we have, and things don’t just work. Our Nagios stops working for that 5.7 slave. Orchestrator started complaining (by this time I’ve already fixed it to be more tolerant for the 5.7 problems so no crashes here).

I hope you see the problem by now.

You cannot issue a GRANT SELECT ON performance_schema.global_variables TO ‘…’ on your 5.6 master.

The table simply does not exist there, which means the statement will not go to binary logs, which means it will not replicate on your 5.7 slave, which means you will not be able to SHOW GLOBAL VARIABLES on your slave, which means everything remains broken.

Yes, you can issue this directly on your 5.7 slaves. It’s doable, but undesired. It’s ugly in terms of automation (and will quite possibly break some assumptions and sanity checks your automation uses); in terms of validity testing. It’s unfriendly to GTID (make sure to SET SQL_LOG_BIN=0 before that).

WHY in the first place?

It seems like a security thing. I’m not sure whether this was intended. So you prevent a SHOW GLOBAL VARIABLES for a normal user. Makes sense. And yet:

mysql> show global variables like 'hostname';
ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'global_variables'

mysql> select @@global.hostname;
+---------------------+
| @@global.hostname   |
+---------------------+
| myhost.mydomain.com |
+---------------------+

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.8-rc-log |
+--------------+

Seems like I’m allowed access to that info after all. So it’s not strictly a security design decision. For status variable, I admit, I don’t have a similar workaround.

Solutions?

The following are meant to be solutions, but do not really solve the problem:

• SHOW commands. SHOW GLOBAL|SESSION VARIABLES|STATUS will work properly, and will implicitly know whether to provide the results via information_schema or performance_schema tables.
  • But, aren’t we meant to be happier with SELECT queries? So that I can really do stuff that is smarter than LIKE ‘variable_name%’?
  • And of course you cannot use SHOW in server side cursors. Your stored routines are in a mess now.
  • This does not solve the GRANTs problem.
• show_compatibility_56: an introduced variable in 5.7, boolean. It truly is a time-travel-paradox novel in disguise, in multiple respects.
  • Documentation introduces it, and says it is deprecated.
    • time-travel-paradox :O
  • But it actually works in 5.7.8 (latest)
    • time-travel-paradox plot thickens
  • Your automation scripts do not know in advance whether your MySQL has this variable
    • Hence SELECT @@global.show_compatibility_56 will produce an error on 5.6
    • But the “safe” way of SHOW GLOBAL VARIABLES LIKE ‘show_compatibility_56’ will fail on a privilege error on 5.7
    • time-travel-paradox :O
  • Actually advised by my colleague Simon J. Mudd, show_compatibility_56 defaults to OFF. I support this line of thought. Or else it’s old_passwords=1 all over again.
  • show_compatibility_56 doesn’t solve the GRANTs problem.
  • This does not solve any migration path. It just postpones the moment when I will hit the same problem. When I flip the variable from “1” to “0”, I’m back at square one.

Suggestion

I claim security is not the issue, as presented above. I claim Oracle will yet again fall into the trap of no-easy-way-to-migrate-to-GTID in 5.6 if the current solution is unchanged. I claim that there have been too many changes at once. Therefore, I suggest one of the alternative two flows:

1. Flow 1: keep information_schema, later migration into performance_schema
   • In 5.7, information_schema tables should still produce the data.
   • No security constraints on information_schema
   • Generate WARNINGs on reading from information_schema (“…this will be deprecated…”)
   • performance_schema also available. With security constraints, whatever.
   • In 5.8 remove information_schema tables; we are left with performance_schema only.
2. Flow 2: easy migration into performance_schema:
   • In 5.7, performance_schema tables should not require any special privileges. Any user can read from them.
   • Keep show_compatibility_56 as it is.
   • SHOW commands choose between information_schema or performance_schema on their own — just as things are done now.
   • In 5.8, performance_schema tables will require SELECT privileges.

As always, I love the work done by the engineers; and I love how they listen to the community.

Comments are most welcome. Have I missed the simple solution here? Are there even more complications to these features? Thoughts on my suggested two flows?

[UPDATE 2015-08-19]

Please see this followup by Morgan Tocker of Oracle.

However I wish to discuss the use of stored routines as integral part of your application code, which I discourage.

The common discussion on whether to use or not use stored routines typically revolves around data transfer (with stored routines you transfer less data since it’s being processed on server side), security (with stored routines you can obfuscate/hide internal datasets, and provide with limited and expected API) and performance (with MySQL this is not what you would expect, as routines are interpreted & their queries re-evaluated, as opposed to other RDBMS you may be used to).

But I wish to discuss the use of stored routines from an engineering standpoint. The first couple of points I raise are cultural/behavioural.

2nd grade citizens

Your stored routines are not likely to integrate well with your IDE. While your Java/Scala/PHP/Ruby/whatnot code comfortably lies within your home directory, the stored routines live in their own space: a database container. They’re not as visible to you as your standard code. Your IDE is unaware of their existence and is unlikely to have the necessary plugin/state of mind to be able to view these.

This leads to difficulty in maintaining the code. People typically resort to using some SQL-oriented GUI tool such as MySQL Workbench, SequelPro or other, commercial tools. But these tools, while make it easy to edit your routine code, do not integrate (well?) with your source control. I can’t say I’ve used all GUI tools; but how many of them will have Git/SVN/Mercurial connectors? How many of them will keep local history changes once you edit a routine? I’m happy to get introduced to such a tool.

Even with such integration, you’re split between two IDEs. And if you’re the command line enthusiast, well, you can’t just svn ci -m “fixed my stored procedure bug”. Your code is simply not in your trunk directory.

It can be done. You could maintain the entire routine code from within your source tree, and hats off to all those who do it. Most will not. See later on about deployments for more on this.

Testing

While engineers are keen on writing unit tests for every class and method they create, they are less keen on doing the same for stored routines. This is an observation, having seen many instalments. And I can tell you why: your stored routine testing will not integrate well with your JUnit/PHPUnit/…

There are testing frameworks for databases, and indeed I hacked my own mini unit testing code with common_schema. But it’s a different testing framework. You might also have realized by now that testing databases is somewhat different. It can be done, and hats off again to those that implement it as common practice. Many don’t. Database are often more heavyweight to test. Not all operations done by routines are easily rolled back, which leads to having to rebuild the entire dataset before tests. This in itself leads to longer test periods and a need for multiple test databases so as to allow for concurrent builds.

How many companies practice both version control and unit testing over their routine code? I believe not many (and am happy to hear about those who do). To be more direct, of all the companies I ever consulted to: I have never seen one that does both.

Debugging

MySQL stored routines do not have built in debugging capabilities. To debug your routines, you will have to use one of two methods:

• Simulate your routine code (ie mimic their execution on top of some interpreter). There are tools to do that. For me this is a complete NO GO and utterly untrustworthy. You can mimic what you think is how the routine should behave, but never they full behaviour. While developing common_schema I came upon plenty weird behaviour, some of it bugs, that you just can’t build into your emulation.
• Inject debugging code into your routine code. I do that with RDebug. You can do breakpoints, step into, step out, most of the interesting stuff. Other tools do that as well. It is not the right way to go: you’re essentially modifying your code, placing more locks, communicating, and losing some functionality. It is a necessary evil solution for a necessary evil programming method… How good can that be?

The right way to go would be to have debugging API built into the MySQL server.

But, wait, that would still be next to worthless, since our discussion is over programming with stored routines: letting your application call upon stored routines in your database. Until the day where I could use my IntelliJ debugger to step from my java method which calls upon a stored procedure, and into the stored procedure itself, debugging your code is completely detached from your stored routine debugging.

Refactoring & deploying

Say you wanted to add a column to your table: you would go ahead and add it, and perhaps populate it. You would then modify your application code to support this new column, and deploy. Say you wanted to drop a table column. You would first deploy changes to your application code that ignore said column, and once the code is in place you would go and actually make the DROP.

How do you do the same with a stored routine? Support your routine accepts two parameters, and you wish to add a third?

There is no support for optional parameters. Your routine either accepts two parameters or three. Your application code will have to provide the exact number of parameters. You will have to deploy both your SQL changes and your application changes at the same time. This is by definition impossible, unless you are OK with a stop the world approach, which is unlikely in production.

Code constraints

One solution to the above is to create a new routines. Somehow “overload” it. But you can’t overload a stored routine; you’ll have to create a routine by a new name. This will allow you to slowly and smoothly migrate between the two.

Ahem, smoothly? How easy is it to find all invocations of a certain routines from your code? It will be typically lie in some String, or within some XML config file. There is no safe “find references to this procedure” IDE mechanism. There is no constraint in your IDE that will tell you “there is no such procedure” if you misspell the name.

Trash bin

Suppose you overcame the above. You now have two routines. You need to remember to DROP the old one, right? Will you?

When presenting common_schema, a common question I ask the audience is as follows:

Suppose I accessed your database and listed the entire set of stored functions and procedures. How many of them are you not even sure are in use anymore? How many of them you think you can DROP, but are too afraid to, and keep them in just in case?

I wouldn’t commonly ask that question had it not always provides a common nodding and smiling in the audience. People forget to drop their routines, and then forget about them, and are never sure whether they are used (your IDE doesn’t easily tell you that, remember? Sure, you can grep around; that’s not what most engineers would do). And those routines pile up to become trash.

Data or code?

Last but not least: a stored routine is a piece of code, right? Well, as far as the database is concerned, it’s really a piece of data. It’s located within a schema. It’s stored. It is an integral part of your data set: when you back up your data, you’re most likely to backup the code as well. When you restore, you’re likely to restore both. There are obvious advantages to that, DB-wise. Or should I say, DBA-wise. Engineering-wise? Does a database-restore operation count as code deployment? We can argue over beer.

Final notes

Having said all that: yes, I’m using an occasional stored routine. I see these occasions as a necessary evil, and sometimes it’s just the correct solution.

I’m happy to know what methods have been developed out there to overcome the above, please share; and please feel free to contradict the above.

I had the pleasure of joining into @DBHangOps today, and speak about common_schema and openark-kit. What was meant to be a 15 minute session turned to be 50 — sorry, people, I don’t talk as much at home, but when it comes to my pet projects…

I also realized I was missing on a great event: DBHangOps is a hangout where you can chat and discuss MySQL & related technologies with friends and colleagues, with whom you typically only meet at conferences. I will certainly want to attend future events.

Thanks to John Cesario and Geoffrey Anderson who invited me to talk, and to the friends and familiar faces who attended; I was happy to talk about my work, and very interested in hearing about how it’s being put to use. We also had time to discuss ps_helper with no other than Mark Leith!

The video is available on Twitter/YouTube.

OurSQL

openark-kit has also been featured on the OurSQL podcast by Sheeri & Gerry, who did great coverage of some tools. I will disclose that more is to come; I’m happy this is in capable hands and look further to hear the next episode!

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.

MySQL does not provide server side debugging capabilities for stored routines. Some tools exist, including MySQL’s own, that assist in stored routine debugging. These are all GUI based and, to the best of my knowledge, MS Windows based. There is one solution in alpha stage that is developed for Java/eclipse; I did not look at the code. See discussion here and here.

An ideal solution would be to have debugging API in the server itself – independently of your client, programming language or operating system. To the best of my knowledge, nothing like that is being developed.

I’m now presenting a rdebug: a stored routines server-side debugger, Pure-SQL, based on stored routines. rdebug is developed as part of common_schema, and actually relies on some of its power.

Like some other tools, it uses code injection and manipulation: it injects debugging info into your stored routine. You need to “compile” your routine with debugging info.

Unlike some other tools, it actually runs your stored routines. It does not mimic or simulate them on client side. It does not break them into smaller routines, attempting to assemble the original behavior from lego bricks.

The quick technical overview is that you use two processes (MySQL threads): the worker process running the routine (your natural call my_routine()), and the debugger process. The debugger process attaches itself to the worker process; it controls the worker by commands like “step over”; it gets data from the worker: what’s the current stack trace? What variables are now available and what are their values?; it manipulates the worker’s data: it can utilize breakpoints to modify worker’s local & session variables.

The debugger code is itself written with stored routines. Your own routines are analyzed by stored routine code. Stored routines inject code into your routine. Stored routines invoke and control the debugging progress. Stored routines manipulate your routine’s data. All access to the debugger is via (did I mention?) stored routines. This leads to server-side (alas not server-code) debugger & debugger API: any tool, GUI or command line, can utilize these stored routine calls so as to implement a debugger.

Since the solution is written with stored routines — thus allowing you to debug your existing 5.1, 5.5 & 5.6 servers — it is also limited by what info can be retrieved by stored routines. One can view/modify local routine variables & user defined variables, set breakpoints etc. One cannot get the “state of a cursor”, though, since no such info exists for a stored routine code.

having injected code operate from within your routine does mean a lot more actions are taken, like hidden INSERTs and UPDATEs. Calls to ROW_COUNT() and LAST_INSERT_ID() can be skewed. That’s a limitation I’m unsure as yet how to overcome; but not overcoming it won’t bring everything down as far as I’m concerned.

Some of the injected code calls upon dynamic SQL. This means at current stored functions are not possible to debug – just stored procedures. There’s a way around this for future development. There is no support for triggers, and I don’t expect there will be in the near future. No investigation into events as yet.

A video is worth a thousand blogs

Watch a live demo of debugging a simple routine; the demo presents a step-by-step debugging of a routine, listing, getting and setting variables.

What’s the status?

What you’ve seen in the video is a first actual milestone – not a POC. Still need to implement nested stack level, step-in and step-out commands, conditional breakpoints, worker temporary table access from debugger process. Lot’s of stuff, but looking good.

This work is the result of a personal interest. All input, feedback, profound mistakes, feature requests, “dude, there’s already a far better tool that does all that” comments, questions — are welcome!

What was my reasoning for rewriting good code? I wish to explain that, and provide with a couple examples.

I’m generally interested in pushing as much functionality into the MySQL server. When using an external script, one:

• Needs the right dependencies (OS, Perl/Python version, Perl/Python modules).
• Needs to provide with connection params,
• Needs to get acquainted with a lot of command line options,
• Is limited by whatever command line options are provided.
• Has to invoke that script (duh!) to get the work done.

This last bullet is not so trivial: it means you can’t work some operation with your favorite GUI client, because it has no notion of your Perl script; does not run on the same machine where your Python code resides; simply can’t run those scripts for you.

With server-side code, functionality is accessible via any client. You run your operation via a query (e.g. CALL some_procedure). That can be done from your GUI client, your command line client, your event scheduler, your cronjob, all equally. You only need access to your MySQL server, which is trivial.

Of course, server side scripting is limited. Some stuff simply can’t be written solely on server side. If you want to consult your replicating slave; gracefully take action on user’s Ctrl+C, send data over the web, you’ll have to do it with an external tool. There are actually a lot of surprising limitations to things one would assume are possible on server side. You may already know how frustrated I am by the fact one can hardly get info from SHOW commands.

But, when it works, it shines

Let’s review a couple examples. The first one is nearly trivial. The second less so.

Example: getting AUTO_INCREMENT “free space”

openark kit offers oak-show-limits. It’s a tool that tells you if any of your AUTO_INCREMENT columns are running out of space (and so you might want to ALTER that INT to BIGINT).

It’s a very simple Python script. It gets your MAX(auto_increment_column) FROM tables_with_auto_increment, and compares that MAX value to the column type. It pre-computes:

max_values['tinyint'] = 2**8
max_values['smallint'] = 2**16
max_values['mediumint'] = 2**24
max_values['int'] = 2**32
max_values['bigint'] = 2**64

takes care of SIGNED/UNSIGNED, and does the math. Why is this tool such a perfect candidate for replacement on server side? For two reasons.

First, It turns out it takes very little effort to build a query which does the same. In which case it is also easy to build a view which provides the same.

Second, there’s this thing with command line arguments. The openark tool provides with –threshold (only output those columns where capacity is larger than x%), –database (only scan given database), –table (only for tables matching name), –column (only for columns matching name).

I don’t like this. See, the above is essentially an extra layer for saying:

• WHERE auto_increment_ratio >= x
• WHERE table_schema = …
• WHERE table_name = …
• WHERE column_name = …

The command line arguments each take the role of some WHERE/AND condition.Wow, what a 1-1 mapping. How about if I wanted the results sorted in some specific order? I would have to add a command line argument for that! How about only listing the SIGNED columns? I would have to add a command line argument for that, too! How about showing top 10? Yes, another command line argument!

Some of the above can be solved via shell scripting (sort -k 3 -n, head -n 10, etc.). But, hey, we’re OK with SQL, aren’t we? Why add now these two extra layers? Get to know all the command line options, get to script it? I love scripting, but this is an abuse.

So it makes much more sense, in my opinion, to SELECT * FROM auto_increment_columns WHERE table_schema=’my_db’ AND auto_increment_ratio >= 0.8 ORDER BY auto_increment_ratio DESC LIMIT 10. It doesn’t require SQL-fu skills, just basic SQL skills which every DBA and DB user are expected to have. And it allows one to work from whatever environment one feels comfortable with. Heck, with your GUI editor you can probably get off with it by right-clicking and left-clicking your mouse buttons, never typing one character.

Example: blocking user accounts

The above mapped very easily to a query, and was just a read-only query. What if we had to modify data? oak-block-accounts is a tool which allows one to block grantees from logging in, then releasing them later on. common_schema offers sql_accounts and eval().

Let’s skip the command line arguments issue, as it is identical to the above. How should we best provide with “taking action” interface? A script would have no problem to first SELECT stuff, then UPDATE, or SET PASSWORD, or DROP etc. How easy is it to do the same on server side?

The immediate solution is to write a stored procedure to do that. I reject the idea. Why? Because the procedure would look like this:

PROCEDURE block_account(user VARCHAR(64), host VARCHAR(64), only_if_empty_password BOOL, ...);

Can you see where I’m getting at? Doing the above re-introduces command line options, this time disguised as procedure parameters. We would again have to list all available filtering methods, only this time things are worse: since stored procedures have no such notion as overloading, and change to the params will break compatibility. Once we introduce this routine, we’re stuck with it.

common_schema tries to stay away as far as it can from this pitfall. It presents another solution: the view solution. Just as with auto_increment_columns, SELECT your way to get the right rows. But this time, the result is a SQL query:

mysql> SELECT sql_block_account FROM sql_accounts WHERE USER = 'gromit';
+-------------------------------------------------------------------------------------+
| sql_block_account                                                                   |
+-------------------------------------------------------------------------------------+
| SET PASSWORD FOR 'gromit'@'localhost' = '752AA50E562A6B40DE87DF0FA69FACADD908EA32*' |
+-------------------------------------------------------------------------------------+

Do your own WHERE/AND combination in SQL. But, how to take action? Our view cannot take the actual action for us!

eval() is at the core of many common_schema operations, like this one:

CALL eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'gromit'");

The SET PASSWORD query just got evaluated. Meaning it was executed. eval() is a very powerful solution.

Conclusion

I prefer stuff on server side. It requires basic SQL skills (or a smart GUI editor), and allows you easy access to a lot of functionality, removing dependency requirements. It is not always possible, and external scripts can do miracles not possible on server side, but server side scripting has its own miracles.

• Cursor for SHOW statements: can’t be done — this is explicitly blocked from operating (it once used to work).
• Get detailed error information on exceptions: apparently 5.6 has support for this. 5.1 and 5.5 do not.
• Change binlog_format: this is obvious, if you think about it. binlog_format dictates how the routine itself is replicated in the first place.
• Set sql_log_bin. Again, this makes sense.
• Work out different results depending on current machine. For example, you can’t have a routine that returns with ‘master’ on the master and with ‘slave’ on the slave. That is, not under any condition. Consider: if Row Based Replication is used, you don’t actually have a routine executing on the slave. I’m happy to be proven wrong on this.
• Know what database was in use by calling code. The routine executes within the context of the database where it is defined. But you can’t tell what database was in use just a couple milliseconds before.
• Likewise, know what sql_mode was in use by calling code. Stored routines have their own sql_mode – the one they were created with. No way to check up on the calling stack.
• And you can’t USE another database (database as in schema). USE is a client command.
• Reconnect after failure (kind of obvious, isn’t it?)
• Connect to other servers (not so obvious to SQL Server DBAs). You can’t issue queries on other servers. Bummer.
• Shutdown the server
• Fork (you’re in a connection, you can’t issue a new connection from your own connection)

Well, some of the above can be solved using plugins or User Defined Functions, but I’m looking at standard servers.

Things that can be done

I can’t list anything that can be done from within a routine, but, to balance, here’s a brief list of things that can be done:

• Recover from errors (e.g. deadlocks) via DECLARE CONTINUE HANDLER.
• Perform table operations (ANALYZE, OPTIMIZE, …) – though not read the results of these operations other than knowing they succeeded.
• Perform all DDL statements (create/drop/modify views, routines, triggers, events, tables, users)
• Modify session/global variables (group_concat_max_len, innodb_stats_on_metadata, …)

You should be aware of

• max_sp_recursion_depth: the maximum recursion depth, if you’re thinking of recursions.
• thread_stack: I find that setting to 256K makes a huge difference over the 5.1 default of 192K. With 192K, I frequently run into “out of stack space” problems. With 256K – I have yet to encounter that. Dunno, some kind of magic number? This is my observation.

To illustrate, consider the following query:

INSERT INTO my_table (my_column) VALUES (300);

What could go wrong with this query?

• We might hit a UNIQUE KEY violation
• Or a FOREIGN KEY violation
• my_column could be TINYINT UNSIGNED, and with strict sql_mode this makes for out-of-range
• Or, similarly, it could be an ENUM (2,3,5,8)

Is that it? Not remotely:

• This could be a read-only MyISAM table
• We may have issued a LOCK TABLES my_table READ — this violates our lock
• Or this could be an InnoDB table, and this INSERT would make for a deadlock
• Or we have read_only=1 configuration
• Or the user is not allowed access to this table
• Or the table does not exist
• Or the column does not exist

Or… I’m pretty sure there could be many other issues.

Now, if I write a Java program, perhaps using Hibernate, I get the error nicely wrapped up in a SQLException object, with easy access to error code and error message.

But can I have the same on server side? No.

Take a look at the following code:

CREATE PROCEDURE some_procedure ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @error_found = 1;
  INSERT INTO my_table (my_column) VALUES (300);
  IF @error_found THEN -- Any what error exactly is this? What's the message? END IF;
END;

If I wanted to take specific action for specific errors, I would need to:

  DECLARE CONTINUE HANDLER FOR 1146 SET @error_found = 1146;
  DECLARE CONTINUE HANDLER FOR 1147 SET @error_found = 1147;
  DECLARE CONTINUE HANDLER FOR 1148 SET @error_found = 1148;
  DECLARE CONTINUE HANDLER FOR 1149 SET @error_found = 1149;
  ...

But if I can’t expect in advance the specific error, yet wish to note it down, that would mean defining hundreds and hundreds of HANDLERs, never being able to actually cover all cases since new codes are introduced in every version, sometimes in minor versions…

Weren’t SINGAL and RESIGNAL introduced in 5.5?

They were, but they do nothing to help here. You can RESIGNAL an error – but that doesn’t mean you get to be told what the error actually was!

But, what’s the problem, anyway?

There’s a variety of stuff I would like to do on server side, not via external Python/Perl/Java/Ruby/Shell scripts. Consider the event scheduler: I mean, what’s the point? It’s nearly useless if there’s so much that you cannot do on server side. You cannot recognize errors, you cannot get enough metadata (see below). It’s only good for a fraction of the jobs you would like to perform.

In common_schema/QueryScript I provide with scripting capabilities. But how about error handling? I’ve written a completely different error handling approach in common_schema (this is not released yet, tons of documentation to produce). But since common_schema works on server side, it is limited to whatever server side programming allows. And this, as explained, is really very little to work with.

What would have been nice

There’s the error_count session variable. Doesn’t actually do anything useful. It would have been nice to have the following session STATUS VARIABLEs:

• last_error_code
• last_error_message

And if a query made for multiple errors, pick one (just make both variables consistent).

Or, please, make some way to parse SHOW commands on server side! (also refer to this). If only I could parse the SHOW ERRORS command, that would solve everything!

MySQL 5.0 introduced INFORMATION_SCHEMA, albeit an incomplete one. Shortly after, SHOW commands were excluded from server side cursors. But that left us with so many missing parts. I’ve opened a bug report/feature request. Would you please support it?