MySQL Bugs: #41638: cursor no longer supports DESCRIBE table

Bug #41638	cursor no longer supports DESCRIBE table
Submitted:	19 Dec 2008 13:32	Modified:	9 Jan 2009 20:29
Reporter:	Scott Noyes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	regression

Description:
5.0 allowed a cursor in a stored routine to contain DESCRIBE table. 5.1 fails with a syntax error. Nothing in the manual indicates if this is a deliberate change or a regression error.

How to repeat:
CREATE TABLE t1 (id int);
DELIMITER //
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN DECLARE x CURSOR FOR DESC t1; END//
DELIMITER ;

5.1 fails with: 
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 'DESC t1; END' at line 1

Suggested fix:
Workaround is to rewrite stored routine to select from information_schema.

The support for declaring cursors to SHOW commands (DESC is a alias for SHOW FIELDS) was intentionally disabled in the patch for Bug#29223. The syntax was an un-intended feature that leaked as a result of a coding bug.

Reclassifying as a documentation issue, should have been listed as a incompatible change.

It was listed as an incompatible change, in the 5.1.23 changelog.

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

However, I've expanded the changelog entry to point out the workaround:

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 provides the same
information as the SHOW statement.