| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1.33 | OS: | Windows |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | cursor, mysql server 5.1, stored procedure | ||
[17 Dec 2009 7:50]
Kedar Vaijanapurkar
[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.
