Skip to content

# Reading results of SHOW statements, on server side

MySQL common_schema Hacks INFORMATION_SCHEMA SQL 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]

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
9
Leave a Reply

9 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Log Buffer #247, A Carnival of the Vanities for DBAs | The Pythian Blog

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

Guest
Roland Bouman

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.

Guest
Baron Schwartz

It wasn’t quite that simple for me to guess the solution… you hinted at it somehow, if I recall 🙂

Author
shlomi

@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 😉

Guest
Peter Laursen

Good catch indeed, but wasn’t it easier to “SELECT .. FROM information_schema …” after all?

Author
shlomi

@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.

Guest
Roland Bouman

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

More MySQL foreach() | code.openark.org

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

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

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

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.