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:Microsoft Windows (Windows, Linux)
Assigned to: CPU Architecture:Any

[1 Mar 2006 18:47] Markus Popp
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.
[24 Apr 2006 14:42] Valeriy Kravchuk
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.
[2 May 2006 5:23] Markus Popp
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.
[12 May 2006 7:03] Valeriy Kravchuk
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?
[12 Jun 2006 23:00] Bugs System
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".