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
Triage: D5 (Feature request)

[24 Apr 2006 20:09] Konstantin Osipov
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
[25 Apr 2006 0:27] Bugs System
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
[2 May 2006 22:49] Dmitry Lenev
Fixed in 5.0.22 and 5.1.10
[10 May 2006 17:04] Paul Dubois
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>.
[30 May 2006 17:58] Giuseppe Maxia
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
[23 Jun 2006 15:28] Konstantin Osipov
Giuseppe, I opened Bug#20665 "All commands supported in Stored Procedures should work in Prepared Statements" for this problem.