MySQL Bugs: #17833: Event doesn't work when trigger is active for the table

Bug #17833	Event doesn't work when trigger is active for the table
Submitted:	1 Mar 2006 18:47	Modified:	12 Jun 2006 7:03
Reporter:	Markus Popp	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.8 bk	OS:	Windows (Windows, Linux)
Assigned to:		CPU Architecture:	Any

Description:
When both an event and a trigger are active on a table, they don't (at least in some cases) work.

The example below should delete all records from table tt before the new record is inserted by the event. So there should always be one record with the row that was inserted last. Instead, neither the records are deleted (by the trigger), nor the new row is inserted (by the event).

How to repeat:
E:\>mysql -h 10.2.4.1 -u user -P 3307
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.8-beta-log

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

mysql> use mpopp_test
Database changed

mysql> CREATE TABLE  `mpopp_test`.`tt` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `d` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER //
mysql>
mysql> DROP EVENT insertIntoTT //
ERROR 1517 (HY000): Unknown event 'insertIntoTT'
mysql>
mysql> CREATE EVENT insertIntoTT
    -> ON SCHEDULE EVERY 5 SECOND
    -> STARTS current_timestamp + INTERVAL 5 SECOND
    -> DO
    -> BEGIN
    ->    insert into tt (d) values (floor(rand() * 100));
    -> END //
Query OK, 1 row affected (0.00 sec)

mysql> DROP TRIGGER mpopp_test.bi_tt //
ERROR 1360 (HY000): Trigger does not exist
mysql>
mysql> CREATE TRIGGER mpopp_test.bi_tt BEFORE INSERT ON mpopp_test.tt FOR EACH ROW
    -> BEGIN
    ->   DELETE FROM tt;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> select * from tt;
+----+----+
| id | d  |
+----+----+
|  1 | 64 |
|  2 | 70 |
+----+----+
2 rows in set (0.00 sec)

(Remark: the 2 rows have been inserted after the creation of the event, but before the creation of the trigger)

mysql> select * from tt;
+----+----+
| id | d  |
+----+----+
|  1 | 64 |
|  2 | 70 |
+----+----+
2 rows in set (0.00 sec)

(Remark: after waiting a while ...)

mysql> select * from tt;
+----+----+
| id | d  |
+----+----+
|  1 | 64 |
|  2 | 70 |
+----+----+
2 rows in set (0.00 sec)

(Remark: no more records are inserted)

mysql> show events\G
*************************** 1. row ***************************
            Db: mpopp_test
          Name: insertIntoTT
       Definer: user@%
          Type: RECURRING
    Execute at: NULL
Interval value: 5
Interval field: SECOND
        Starts: 2006-03-01 18:39:59
          Ends: 0000-00-00 00:00:00
        Status: ENABLED
1 row in set (0.00 sec)

Suggested fix:
In this example, the event should insert a new row every 5 seconds, after the trigger has deleted all records just before.

I was able to repeat the same behaviour you described, but... Have you tried to INSERT into that table with trigger in place? I've tried:

mysql> insert into tt values(20, 20);
ERROR 1442 (HY000): Can't update table 'tt' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

So, your event simply can not insert any more rows with that trigger in place. Check, please. I think, it is not a bug.

I would understand it this way:

* event driven INSERT fires trigger to delete all rows
* new row is being inserted

So there should always be one (the most recently added) row in the table.

My understanding is different. EVENT caused INSERT to be executed. As a part of INSERT statement trigger is invoked, but it produce unhandled exception (can not work, as I had demonstrated), so entire INSERT statement does not work. Statements are atomic - either all changes are performed or no one. Do you agree with me?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".