Reading results of SHOW statements, on server side

November 25, 2011

SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side.

On server side, that is, from within MySQL itself, one cannot:

SELECT `Database` FROM (SHOW DATABASES);

One cannot:

DECLARE show_cursor CURSOR FOR SHOW TABLES;

One cannot:

SHOW TABLES INTO OUTFILE '/tmp/my_file.txt';

So it is impossible to get the results with a query; impossible to get the results from a stored routine; impossible to get the results by file reading...

Bwahaha! A hack!

For some SHOW statements, there is a way around this. I've been banging my head against the wall for weeks now on this. Now I have a partial solution: I'm able to read SHOW output for several SHOW statements. Namely, those SHOW statements which allow a LIKE or a WHERE clause.

For example, most are familiar with the following syntax:

USE mysql;
SHOW TABLE STATUS LIKE 'user';

However not so many know that any SHOW statement which accepts LIKE, can also accept WHERE:

SHOW TABLE STATUS WHERE Name='user'\G
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 17
 Avg_row_length: 69
    Data_length: 1184
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-10-03 08:23:48
    Update_time: 2011-07-30 19:31:00
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Users and global privileges

It's not just about "Name". I can filter using any column I like:

SHOW TABLE STATUS WHERE Rows > 1000;
SHOW TABLE STATUS WHERE Rows > 1000 AND Index_length > 65536;

etc.

If you've been to my talk on Programmatic Queries: things you can code with SQL, you have a good guess as for where I'm taking this.

Where there's WHERE, there's code

I can write code within the WHERE clause. Specifically, I can work with user defined variables. Shall we cut to the point and provide with an example?

mysql> SET @databases := '';

mysql> SHOW DATABASES WHERE (@databases := CONCAT(@databases, `Database`, ',')) IS NULL;

mysql> SELECT @databases;
+-------------------------------------------------------------------+
| @databases                                                        |
+-------------------------------------------------------------------+
| information_schema,common_schema,mycheckpoint,mysql,sakila,world, |
+-------------------------------------------------------------------+

Let's discuss the above. We:

  • Set a user variables called @databases to an empty text
  • Iterate through the SHOW DATABASES rowset. The WHERE clause is always false (the expression is in fact NOT NULL for all rows), so rows are not printed out, and we get an empty result set (we're not really interested in a result set here, since there's no way to read it anyhow).
  • However we do take care to "remember" the value we visit, by concatenating the `Database` column value.
  • We end up with a delimited string of database names. You'll forgive the ending ','. This is just a simple example, it is of no importance.

Further notes

What can we do with the concatenated list of database names? Whatever we want to. We can parse it again, INSERT it INTO some table, save to file, iterate, what have you!

We can wrap the above in a stored routine. Alas, not with a stored function, since the SHOW command, although returns with an empty result set, does return with a result set, not allowed withing functions.

Limitations

  • Sadly, SHOW SLAVE STATUS, SHOW MASTER LOGS etc., do not support LIKE or WHERE syntax. Bummer.
  • Stored functions, as just mentioned, cannot utilize this hack. Hey, I'm still working on this!

To what use?

Originally I wanted to avoid the time & locking it takes for INFORMATION_SCHEMA queries, such as on TABLES, COLUMNS, etc. Ironically, in a few days apart I've found another interesting solution (well, two, actually) to manage reads from INFORMATION_SCHEMA with less overhead than in normal use. I'll talk about that another time; am about to use this in common_schema.

Further notes

I met Roland in London, and he liked the solution. As Baron joined, Roland said: "Baron, do you know Shlomi devised a method to read the output of SHOW commands?"

And Baron said: "Without using files? Then a SHOW statement can have a WHERE clause, in which case you can use a variable", and went on looking for his wife.

And we remained speechless.

[UPDATE: I've manually changed timestamp of this post due to failure in its aggregation in planet.mysql, being a major source of incoming traffic to this site]

tags: , , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

9 Comments to "Reading results of SHOW statements, on server side"

  1. Log Buffer #247, A Carnival of the Vanities for DBAs | The Pythian Blog wrote:

    [...] Shlomi Noach blogs about reading results of SHOW statements, on server side. [...]

  2. Roland Bouman wrote:

    Brilliant hack!

    Indeed, very humbling to see it took Baron just seconds to guess how to make it work...But IMO, that's just more proof of his intellect and MySQL expertise, it doesn't make your hack less brilliant.

    Cheers, and kudos :)

    Roland.

  3. Baron Schwartz wrote:

    It wasn't quite that simple for me to guess the solution... you hinted at it somehow, if I recall :-)

  4. shlomi wrote:

    @Baron,
    I did not hint at it, my memory serves me right; Roland is my witness. Any further disagreement on your side will be moderated ;)

  5. Peter Laursen wrote:

    Good catch indeed, but wasn't it easier to "SELECT .. FROM information_schema ..." after all?

  6. shlomi wrote:

    @Peter,
    Easier -- yes! Also more correct to do so. But I have recently crashed a production server (my own one, a slave) by querying INFORMATION_SCHEMA. In openark-kit tools I've added options to tell the tools some meta-info rather than let them find it out themselves, because the lockdown they would incur by querying INFORMATION_SCHEMA was measures in *minutes*.

    anyway, like I said, I've found a wonderful solution for that. Unfortunately, this comment is too small for me to put it in writing. I'll blog separately.

  7. Roland Bouman wrote:

    I think we did somehow talk about it in the context of user defined variables. That was apparently enough for Baron's braingears.

  8. More MySQL foreach() | code.openark.org wrote:

    [...] Reading results of SHOW statements, on server side [...]

  9. MySQL error handling on server side: a NO GO! | code.openark.org wrote:

    [...] Reading results of SHOW statements, on server side [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org