| Bug #48379 | Cursors on SHOW are not working | ||
|---|---|---|---|
| Submitted: | 28 Oct 2009 14:14 | Modified: | 7 Jul 2010 15:06 |
| Reporter: | Michael Skulsky | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.39 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[28 Oct 2009 14:41]
MySQL Verification Team
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 >
[7 Jul 2010 5:34]
Michael Skulsky
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!
[7 Jul 2010 5:37]
Valeriy Kravchuk
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.
[7 Jul 2010 15:06]
Peter Gulutzan
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
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%');