MySQL Bugs: #20665: All commands supported in Stored Procedures should work in Prepared Statements

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

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.

More commands:
KILL
FLUSH xxxx
RESET xxxx

LOAD DATA (FROM MASTER) is not allowed in a SP

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)

Reviewed is sent by email, OK to push with a few little changes.

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)

Queued into 5.1-rt

Pushed to 5.1.12

Noted in 5.1.12 changelog, and in manual section
on prepared statements.

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