MySQL Bugs: #65897: Re-allow server side cursors for SHOW commands

Bug #65897	Re-allow server side cursors for SHOW commands
Submitted:	14 Jul 2012 6:53	Modified:	10 Mar 2015 11:11
Reporter:	Shlomi Noach (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	Any	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	cursor, server side, show, stored programming

Description:
This is a feature request to allow server side cursors to be used with SHOW commands, as they once used to, like:

DECLARE cur1 CURSOR FOR SHOW SLAVE STATUS;
DECLARE cur2 CURSOR FOR SHOW ERRORS;

The INFORMATION_SCHEMA is simply not enough. Versions go by, and yet we do not get the complementary I_S tables for such common SHOW commands.

In particular, SHOW SLAVE STATUS, SHOW MASTER LOGS, SHOW MASTER STATUS, SHOW ERRORS, SHOW WARNINGS, SHOW OPEN TABLES, SHOW PROFILES -- all these have no complementary I_S solution.

Not being able to read the above on server side renders a lot of server side activities useless, and this includes the use of the event scheduler -- if you can do timely actions, but are not able to parse some metadata -- this is a major show stopper.

Thanks and regards

How to repeat:
Nothing to repeat -- re-enable what used to work before!

Suggested fix:
Enable "DECLARE cur2 CURSOR FOR SHOW" syntax for at least those SHOW commands listed above which have no complementary I_S solution.

Thank you for the feature request.

Let's look at each SHOW command listed by this report in detail.

1)
SHOW SLAVE STATUS,
SHOW MASTER LOGS,
SHOW MASTER STATUS,

MySQL 5.6 contains the following tables:
mysql.slave_master_info
mysql.slave_relay_log_info
mysql.slave_worker_info

which cover replication.

2)
SHOW ERRORS,
SHOW WARNINGS,

MySQL 5.6 supports the GET DIAGNOSTICS statement,
which is the proper way to access the diagnostics area of a statement,
especially from SQL code inside a stored procedure / stored function / trigger / event scheduler event.

3)
SHOW OPEN TABLES,

There are no tables exposing the same data currently.

4)
SHOW PROFILES

The performance schema STAGE instrumentation and SHOW PROFILE are basically two different ways to expose the same data.

MySQL 5.6 supports the following tables:
- performance_schema.events_stages_current
- performance_schema.events_stages_history
- performance_schema.events_stages_history_long
plus aggregates.

The performance schema STATEMENT instrumentation and SHOW PROFILES (plural) are also two different ways to expose the same data (the executed statements).

MySQL 5.6 supports the following tables:
- performance_schema.events_statements_current
- performance_schema.events_statements_history
- performance_schema.events_statements_history_long
plus aggregates

To access the last N queries executed by a thread (what SHOW PROFILES does),
use table performance_schema.events_statements_history (or performance_schema.events_statements_history_long).

To access the last N stages executed by a query (what SHOW PROFILE does),
use table performance_schema.events_stages_history (or performance_schema.events_stages_history_long).

---

So, the only missing part in fact is to expose the result of SHOW OPEN TABLES as a table.

As per Marc's comment, I stand corrected.

I did not realize all the above exist in 5.6

Concerning the last missing part, which was:

3)
SHOW OPEN TABLES,

There are no tables exposing the same data currently.

Tables currently opened can be seen in:
  SELECT * from performance_schema.table_handles

Tables currently name locked can be seen in:
  SELECT * from performance_schema.metadata_locks;

both are available starting with MySQL 5.7.3