MySQL Bugs: #18971: drop trigger with prepare and execute statement inside stored procedure

Bug #18971	drop trigger with prepare and execute statement inside stored procedure
Submitted:	10 Apr 2006 16:54	Modified:	29 Apr 2006 12:39
Reporter:	Samir Seba	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-net	OS:	Windows (windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

I tried to drop trigger using prepare and execute statement.

it looks not wokring.

to get that i did the following script under mysql command line:

delimiter \\
DROP PROCEDURE IF EXISTS test;
\g
Create procedure test()
begin		
	PREPARE stmt FROM 'DROP TRIGGER DLXTG_DEL_VOIPDOMGW';
	execute stmt;	
end;
\g
call test();
\g

that was the error :
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

any solution until the prepare / statement support that.

Thank you.

How to repeat:
delimiter \\
DROP PROCEDURE IF EXISTS test;
\g
Create procedure test()
begin		
	PREPARE stmt FROM 'DROP TRIGGER DLXTG_DEL_VOIPDOMGW';
	execute stmt;	
end;
\g
call test();
\g

Thank you for a problem report. Please, provide a complete test case, with CREATE TABLE and CREATE TRIGGER, and try to repeat with a newer version, 5.0.19. Inform about the results.

Hello,

following your request i did :

1- uninstall 5.0.13
2- Install 5.0.20
3- execute the following script ( with new staffs as you required) , on command line :

delimiter \\

DROP TABLE IF EXISTS dlxtest;
CREATE TABLE dlxtest (
  field1 varchar(50) NOT NULL,
  field2 char(1) NOT NULL default 'G',
  field3 varchar(50) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
\g
  
  DROP TABLE IF EXISTS dlxtest1;
CREATE TABLE dlxtest1 (
  field4 varchar(50) NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
\g
  
CREATE TRIGGER DLXTG_DEL_VOIPDOMGW AFTER DELETE ON dlxtest
FOR EACH ROW 
BEGIN
	UPDATE dlxtest1 SET field4='1';
END;
\g
  
DROP PROCEDURE IF EXISTS test;
\g
Create procedure test()
begin		
	PREPARE stmt FROM 'DROP TRIGGER DLXTG_DEL_VOIPDOMGW';
	execute stmt;	
end;
\g
call test();
\g

And the result was :
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet.

Thank you for a problem report. Sorry, but it is not a bug, according to the manual (http://dev.mysql.com/doc/refman/5.0/en/sqlps.html):

"The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not yet supported."