MySQL Bugs: #19308: REPAIR/OPTIMIZE/ANALYZE supported in SP but not in PS

Bug #19308	REPAIR/OPTIMIZE/ANALYZE supported in SP but not in PS
Submitted:	24 Apr 2006 20:09	Modified:	10 May 2006 17:04
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any (All)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
After the fix for Bug#13012, the following commands became supported in Stored Procedures: 
REPAIR
OPTIMIZE
ANALYZE

We need to allow the same statements in Stored Procedures as we allow in Prepared Statements. Therefore, we should be able to prepare REPAIR,  OPTIMIZE, ANALYZE.

How to repeat:
create table t1 (a int);
prepare stmt from "repair table t1";
execute stmt;
execute stmt;
prepare stmt from "optimize table t1";
execute stmt;
execute stmt;
prepare stmt from "analyze table t1";
execute stmt;
execute stmt;
deallocate prepare stmt;

Suggested fix:
Add the corresponding SQLCOM_ commands to the switch in sql_prepare.cc:check_prepared_stmt

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5444

Fixed in 5.0.22 and 5.1.10

Noted in 5.0.22, 5.1.10 changelogs.

SQL syntax for prepared statements now supports
<literal>ANALYZE TABLE</literal>, <literal>OPTIMIZE
TABLE</literal>, and <literal>REPAIR TABLE</literal>.

Other commands that work in stored procedures and should be supported in dynamic queries are:

CHANGE MASTER TO
RESET MASTER
RESET SLAVE
START SLAVE
STOP SLAVE
FLUSH LOGS|TABLES|PRIVILEGES
SHOW BINLOG EVENTS 
SHOW SLAVE|MASTER STATUS
SHOW MASTER LOGS
SHOW SLAVE HOSTS
LOAD DATA FROM MASTER

Giuseppe, I opened Bug#20665 "All commands supported in Stored Procedures should work in Prepared Statements" for this problem.