MySQL Bugs: #40532: create trigger damages database

Bug #40532	create trigger damages database
Submitted:	5 Nov 2008 16:07	Modified:	10 Dec 2008 12:46
Reporter:	Andrea Venturi	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.0.37, 5.0.70	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	create trigger, database damaged, ERROR 1064 (42000)

Description:
'create trigger' statement have damaged the database because any other statement after that fails with the same error.

How to repeat:
In mysql command prompt, do like below. 

1. create database
mysql> CREATE DATABASE test2 ;

2. create tables
mysql> USE test2;

mysql> CREATE TABLE customer (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 ) NOT NULL
) ENGINE = MYISAM ;

mysql> CREATE TABLE customer2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 ) NOT NULL
) ENGINE = MYISAM ;

3. create trigger
mysql> CREATE TRIGGER customer_copy
AFTER INSERT ON customer
FOR EACH ROW
INSERT INTO customer2 SELECT NEW.*;

4. then if you try to insert a row into table 'customer'
mysql> insert into customer(name) values ('james')

you get this error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

5. if you try to do a select statement

mysql> select * from customer;

you get the same error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

6. in addition you can't drop the trigger

mysql> DROP TRIGGER test2.customer_copy;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

7. and also drop database fails:

mysql> DROP DATABASE test2;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

i changed tags and category

Thank you for a problem report. 5.0.37 is very old, and many bugs were already fixed. So, please, try to repeat with a newer version, 5.0.67, and inform about the results.

thank you for your quick reply.
just few minute ago, i installed the version 5.0.67-community and i tried to reapet the same query.
unfortunately i've encountered the same error.

Verified just as described with 5.0.70:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.70-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE customer (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> name VARCHAR( 100 ) NOT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.22 sec)

mysql> CREATE TABLE customer2 (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> name VARCHAR( 100 ) NOT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TRIGGER customer_copy
    -> AFTER INSERT ON customer
    -> FOR EACH ROW
    -> INSERT INTO customer2 SELECT NEW.*;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into customer(name) values ('james')
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 4
mysql> select * from customer;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 4

mysql> select * from customer2;
Empty set (0.05 sec)

mysql> drop trigger customer_copy;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 4
mysql> exit
Bye

5.1.29 is NOT affected:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.29-rc-community MySQL Community Server (GPL)

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

mysql> CREATE TABLE customer (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> name VARCHAR( 100 ) NOT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE customer2 (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> name VARCHAR( 100 ) NOT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TRIGGER customer_copy
    -> AFTER INSERT ON customer
    -> FOR EACH ROW
    -> INSERT INTO customer2 SELECT NEW.*;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into customer(name) values ('james')
    -> ;
ERROR 1051 (42S02): Unknown table 'NEW'
mysql> select * from customer;
+----+-------+
| id | name  |
+----+-------+
|  1 | james |
+----+-------+
1 row in set (0.01 sec)

Workaround is to use trigger body enclosed with BEGIN ... END

CREATE TRIGGER customer_copy
AFTER INSERT ON customer
FOR EACH ROW
BEGIN
INSERT INTO customer2 SELECT NEW.*;
END//

The bug is duplicate of bug#25411.

This bug is a duplicate of 25411. The fix for 25411 is fairly complex to port to 5.0 and this is why Marc decided not to put his fix for bug 25411 into 5.0 to begin with.
Please either use the provided workaround (enclose the body in BEGIN/END) or consider migrating to 5.1.