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

[14 Jul 2012 6:53] Shlomi Noach
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.
[14 Jul 2012 17:36] Valeriy Kravchuk
Thank you for the feature request.
[18 Jul 2012 12:17] Marc ALFF
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.
[18 Jul 2012 12:20] Shlomi Noach
As per Marc's comment, I stand corrected.

I did not realize all the above exist in 5.6
[10 Mar 2015 11:11] Marc ALFF
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