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 22:09	Modified:	10 May 2006 19:04
Reporter:	Konstantin Osipov
Status:	Closed
Category:	Server: PS	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any (All)
Assigned to:	Konstantin Osipov	Target Version:
Triage:	D5 (Feature request)

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.