Bug #49758 Declare Cursor throws errors for show global status
Submitted: 17 Dec 2009 7:50 Modified: 11 Jan 2010 19:14
Reporter: Kedar Vaijanapurkar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0, 5.1.33 OS:Microsoft Windows
Assigned to: Paul DuBois CPU Architecture:Any
Tags: cursor, mysql server 5.1, stored procedure

[17 Dec 2009 7:50] Kedar Vaijanapurkar
Description:
Following stored procedure run smoothly on 5.0 but not on 5.1.33 on windows.

DELIMITER $$
DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test` ()
BEGIN
DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES;
END $$
DELIMITER ;

How to repeat:
Just try create procedure on 5.1.
[17 Dec 2009 7:52] Kedar Vaijanapurkar
Error:
Script line: 4	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 VARIABLES;
END' at line 3
[17 Dec 2009 8:10] Valeriy Kravchuk
I can confirm this:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.41-community MySQL Community Server (GPL)

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

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `test` $$
Query OK, 0 rows affected, 1 warning (0.58 sec)

mysql> CREATE PROCEDURE `test` ()
    -> BEGIN
    -> DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES;
    -> END $$
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 VARIABLES;
END' at line 3
mysql> DELIMITER ;
mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.86-community-nt-log MySQL Community Edition (GPL)

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

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `test` $$
Query OK, 0 rows affected, 1 warning (0.39 sec)

mysql> CREATE PROCEDURE `test` ()
    -> BEGIN
    -> DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES;
    -> END $$
Query OK, 0 rows affected (0.64 sec)

So, it worked in 5.0 (there was no other way to deal with server and status variables in SPs), but does NOT work in 5.1. 

But I'd consider this a documentation bug. http://dev.mysql.com/doc/refman/5.0/en/declare-cursor.html should say that SHOW statements can also be used, not only SELECT. While http://dev.mysql.com/doc/refman/5.1/en/declare-cursor.html should say that we made an incompatible change and SHOW can NOT be used any more. Instead, you should use SELECT from new tables in INFORMATION_SCHEMA. See http://dev.mysql.com/doc/refman/5.1/en/status-table.html and http://dev.mysql.com/doc/refman/5.1/en/variables-table.html.
[11 Jan 2010 19:13] Paul DuBois
SHOW became disallowed as of 5.1.23. The changelog entry looks like this:

Incompatible Change: 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. (Bug#29223)

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-23.html

The wording ("in some instances") suggests to me that it is not a good idea to encourage use of SHOW prior to 5.1.23, since it might or might not work.

I will point out that SELECT can be used with INFORMATION_SCHEMA statements.
[11 Jan 2010 19:14] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added to DECLARE CURSOR page:

For information available through SHOW statements, it is possible in
many cases to obtain equivalent information by using a cursor with an
INFORMATION_SCHEMA table.