Bug #48379 Cursors on SHOW are not working

Description:
In 5.0 it was possible to open a cursor on a SHOW statement in order to work with its output inside a stored procedure. In 5.1 this is not working any more.

How to repeat:
The following SQL script works OK in 5.0:

=================================================
delimiter $$$

drop procedure if exists count_com;

$$$

create procedure count_com (OUT cntr bigint)
begin
  declare done int default 0;
  declare var_name varchar(255);
  declare cur_nb bigint default 0;
  declare metrics cursor for
    show global status where variable_name like ('Com%');
  declare continue handler for not found SET done = 1;

  open metrics;
  repeat
   fetch metrics into var_name, cur_nb;
   if not done then
     set cntr = cntr + cur_nb;
   end if;
  until done end repeat;

  close metrics;
end;

$$$
=================================================

The same script fails on 5.1.39 with the following error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show global status where variable_name like ('Com%');

Thank you for the bug report.

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.88-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > delimiter $$$
mysql 5.0 >
mysql 5.0 > drop procedure if exists count_com;
    ->
    -> $$$
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql 5.0 >
mysql 5.0 > create procedure count_com (OUT cntr bigint)
    -> begin
    ->   declare done int default 0;
    ->   declare var_name varchar(255);
    ->   declare cur_nb bigint default 0;
    ->   declare metrics cursor for
    ->     show global status where variable_name like ('Com%');
    ->   declare continue handler for not found SET done = 1;
    ->
    ->   open metrics;
    ->   repeat
    ->    fetch metrics into var_name, cur_nb;
    ->    if not done then
    ->      set cntr = cntr + cur_nb;
    ->    end if;
    ->   until done end repeat;
    ->
    ->   close metrics;
    -> end;
    ->
    -> $$$
Query OK, 0 rows affected (0.07 sec)

mysql 5.0 >

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >delimiter $$$
mysql 5.1 >
mysql 5.1 >drop procedure if exists count_com;
    ->
    -> $$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 >
mysql 5.1 >create procedure count_com (OUT cntr bigint)
    -> begin
    ->   declare done int default 0;
    ->   declare var_name varchar(255);
    ->   declare cur_nb bigint default 0;
    ->   declare metrics cursor for
    ->     show global status where variable_name like ('Com%');
    ->   declare continue handler for not found SET done = 1;
    ->
    ->   open metrics;
    ->   repeat
    ->    fetch metrics into var_name, cur_nb;
    ->    if not done then
    ->      set cntr = cntr + cur_nb;
    ->    end if;
    ->   until done end repeat;
    ->
    ->   close metrics;
    -> end;
    ->
    -> $$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL serv
er version for the right syntax to use near 'show global status where variable_name like ('Com%');
  declare continue handler' at line 7
mysql 5.1 >

How is it going?
For me this bug is very much in the way as I need to process counters from SHOW GLOBAL STATUS from within a stored procedure. Please either fix in in 5.1 or provide a workaround!

You can use SELECTs from tables in INFORMATION_SCHEMA as a workaround. Check http://dev.mysql.com/doc/refman/5.1/en/status-table.html.

This was a deliberate behaviour change.
The MySQL Reference Manual says:
"Incompatible change: As of MySQL 5.1.23, within a stored routine, it is no longer allowable to declare a cursor for a SHOW or DESCRIBE statement. This happened to work in some instances, but is no longer supported. In many cases, a workaround for this change is to use the cursor with a SELECT  query to read from an INFORMATION_SCHEMA  table that produces the same information as the SHOW statement."
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html