| 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: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.1.8 bk | OS: | Microsoft Windows (Windows, Linux) |
| Assigned to: | CPU Architecture: | Any | |
[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".
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.