common_schema over traditional scripts

If you are familiar with both openark kit and common_schema, you’ll notice I’ve incorporated some functionality already working in openark kit into common_schema, essentially rewriting what used to be a Python script into SQL/QueryScript.

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.