Reading results of SHOW statements, on server side

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]