Bug #21625 Definer with compound statements within create trigger statement

• Submitted: 14 Aug 2006 15:20
• Modified: 14 Aug 2006 17:17
• Reporter: Jay Lin
• Status: Can't repeat
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: 5.0.24
• OS: Windows (Window)
• CPU Architecture: Any

[14 Aug 2006 15:20] Jay Lin

Description:
Can't create a trigger with both Definer and compound statement.

Please refer to the "How to repeat" for detail.

How to repeat:
#################### ERROR!!!!!!! ####################################

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

CREATE DEFINER='root'@'localhost' TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;

####################### WORKS FINE... ########################################

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

CREATE DEFINER='root'@'localhost' TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
    INSERT INTO test2 SET a2 = NEW.a1;

######################## WORKS FINE... ###################################

drop table test1;
drop table test2;
drop table test3;
drop table test4;

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;

Suggested fix:
I wish I know.

[14 Aug 2006 17:17] MySQL Verification Team

Thank you for the bug report. I was unable to repeat:

c:\mysql\bin>mysql -uroot dbh
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.24-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> #################### ERROR!!!!!!! ####################################
mysql>
mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE test4(
    ->   a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   b4 INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter //
mysql> CREATE DEFINER='root'@'localhost' TRIGGER testref BEFORE INSERT ON test1
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO test2 SET a2 = NEW.a1;
    ->     DELETE FROM test3 WHERE a3 = NEW.a1;
    ->     UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
    ->   END;//
Query OK, 0 rows affected (0.02 sec)

mysql>