Bug #20665 All commands supported in Stored Procedures should work in Prepared Statements
Submitted: 23 Jun 2006 15:26 Modified: 29 Sep 2006 2:27
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.1 OS:Any (All)
Assigned to: Andrey Hristov CPU Architecture:Any

[23 Jun 2006 15:26] Konstantin Osipov
Description:
This is a follow up on Bug#19308 "REPAIR/OPTIMIZE/ANALYZE supported in SP but not in PS"

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

How to repeat:
See description: try to prepare a statement from every command and create a procedure. The first will fail, whereas the second will succeed.
[2 Aug 2006 14:40] Andrey Hristov
More commands:
KILL
FLUSH xxxx
RESET xxxx
[2 Aug 2006 15:39] Andrey Hristov
LOAD DATA (FROM MASTER) is not allowed in a SP
[3 Aug 2006 8:37] 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/9992

ChangeSet@1.2244, 2006-08-03 10:36:10+02:00, andrey@lmy004. +10 -0
  Fix for bug #20665 All commands supported in Stored Procedures
  should work in Prepared Statements
  
  SHOW BINLOG EVENTS
  SHOW (MASTER | SLAVE) STATUS
  SHOW (MASTER | BINARY) LOGS
  SHOW (PROCEDURE | FUNCTION) CODE (parsable only in debug builds)
  SHOW CREATE (PROCEDURE | FUNCTION | EVENT | TABLE | VIEW)
  SHOW (AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS)
  CHANGE MASTER
  RESET (MASTER | SLAVE | QUERY CACHE)
  SLAVE (START | STOP)
  CHECKSUM (TABLE | TABLES)
  INSTALL PLUGIN
  UNINSTALL PLUGIN
  CACHE INDEX
  LOAD INDEX INTO CACHE
  GRANT
  REVOKE
  KILL
  (CREATE | RENAME | DROP) DATABASE
  (CREATE | RENAME | DROP) USER
  FLUSH (TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES |
         LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES)
[21 Aug 2006 22:18] Konstantin Osipov
Reviewed is sent by email, OK to push with a few little changes.
[22 Aug 2006 13:41] 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/10727

ChangeSet@1.2279, 2006-08-22 15:40:26+02:00, andrey@example.com +11 -0
  Fix for bug #20665 All commands supported in Stored Procedures
  should work in Prepared Statements. Post-review changeset.
  
  Problem: There are some commands which are avaiable to be executed in SP
           but cannot be prepared. This patch fixes this and makes it possible
           prepare these statements.
  
  Changes: The commands later are made available in PS. RESET has been forbidden
           in SF/Trigger.
  
  Solution: All current server commands where checked and those missing (see later)
            we added. Tests for all of the commands with repeated executions were
            added - testing with SP, SF and PS.
  
  SHOW BINLOG EVENTS
  SHOW (MASTER | SLAVE) STATUS
  SHOW (MASTER | BINARY) LOGS
  SHOW (PROCEDURE | FUNCTION) CODE (parsable only in debug builds)
  SHOW CREATE (PROCEDURE | FUNCTION | EVENT | TABLE | VIEW)
  SHOW (AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS)
  CHANGE MASTER
  RESET (MASTER | SLAVE | QUERY CACHE)
  SLAVE (START | STOP)
  CHECKSUM (TABLE | TABLES)
  INSTALL PLUGIN
  UNINSTALL PLUGIN
  CACHE INDEX
  LOAD INDEX INTO CACHE
  GRANT
  REVOKE
  KILL
  (CREATE | RENAME | DROP) DATABASE
  (CREATE | RENAME | DROP) USER
  FLUSH (TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES |
         LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES)
[24 Aug 2006 17:47] Andrey Hristov
Queued into 5.1-rt
[24 Aug 2006 20:56] Petr Chardin
Pushed to 5.1.12
[29 Sep 2006 2:27] Paul DuBois
Noted in 5.1.12 changelog, and in manual section
on prepared statements.
[10 Nov 2006 18:05] Paul DuBois
Clarification: The additional statements are supported both
for SQL syntax for prepared statements (PREPARE/EXECUTE)
and by the binary protocol.  The additional supported statements
are noted in both these places:

http://dev.mysql.com/doc/refman/5.1/en/sqlps.html
http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statements.html