MySQL Bugs: #25563: Nested Triggers Produce Non-Existant Table

Bug #25563	Nested Triggers Produce Non-Existant Table
Submitted:	11 Jan 2007 23:39	Modified:	4 Jun 2007 4:10
Reporter:	Matthew Boehm	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Linux (RHEL4)
Assigned to:		CPU Architecture:	Any
Tags:	cluster, nested, table, triggers

Description:
Having two triggers that at some point call each other results in a table not found error when updating main table. Commands are given in order.

DELIMITER ;;

UPDATE rooms SET defaultvideoquality = 1 WHERE roomID = 138;;
Query OK, 1 row affected (0.00 sec)

CREATE TRIGGER `room_deleter`
BEFORE DELETE ON `rooms`
FOR EACH ROW
BEGIN
   DELETE FROM archivedmovies WHERE roomID = OLD.roomID;
   DELETE FROM bannedusers WHERE roomID = OLD.roomID;
   DELETE FROM bufferseats WHERE roomID = OLD.roomID;
   DELETE FROM meetings WHERE roomID = OLD.roomID;
   DELETE FROM events WHERE roomID = OLD.roomID;
   DELETE FROM favoritelinks WHERE roomID = OLD.roomID;
   DELETE FROM onetime_passwords WHERE roomID = OLD.roomID;
   DELETE FROM roomsounds WHERE roomID = OLD.roomID;
   DELETE FROM savedannouncements WHERE roomID = OLD.roomID;
   DELETE FROM savedsurveys WHERE roomID = OLD.roomID;
   DELETE FROM smsreports WHERE roomID = OLD.roomID;
   DELETE FROM smsusers WHERE roomID = OLD.roomID;
   DELETE FROM spreadsheets WHERE roomID = OLD.roomID;
   DELETE FROM subscriptions WHERE custom = OLD.roomID;
   DELETE FROM users_rooms WHERE roomID = OLD.roomID;
END; ;;
Query OK, 0 rows affected (0.00 sec)

UPDATE rooms SET defaultvideoquality = 1 WHERE roomID = 138;;
Query OK, 1 row affected (0.00 sec)

CREATE TRIGGER `meetings_deleter`
BEFORE DELETE ON `meetings`
FOR EACH ROW
BEGIN
   DELETE FROM attedance WHERE meetingNo = OLD.no;
END; ;;
Query OK, 0 rows affected (0.00 sec)

UPDATE rooms SET defaultvideoquality = 1 WHERE roomID = 138;;
ERROR 1146 (42S02): Table 'omnovia_conf.attedance' doesn't exist

(uh?)

DROP TRIGGER `meetings_deleter`;;
Query OK, 0 rows affected (0.01 sec)

UPDATE rooms SET defaultvideoquality = 1 WHERE roomID = 138;;
Query OK, 1 row affected (0.00 sec)

<re-add `meetings_deleter` trigger>
<rerun update..same error>

DROP TRIGGER `rooms_deleter`;;
Query OK, 0 rows affected (0.01 sec)

UPDATE rooms SET defaultvideoquality = 1 WHERE roomID = 138;;
Query OK, 1 row affected (0.00 sec)

<re-add `rooms_deleter` trigger>
<rerun update..same error>

What's going on here?  I'm trying to replicate foreign key behavior in NDB as per an article I read on mysql.com.

How to repeat:
keep adding/removing the two triggers in question and running the update query to produce various results.

Suggested fix:
none.

every table referenced in the queries does exist btw..

Can you provide the following output;
mysql> SHOW TRIGGERS\G

[omnovia_conf]>show triggers\G
*************************** 1. row ***************************
  Trigger: meetings_deleter
    Event: DELETE
    Table: meetings
Statement: BEGIN
   DELETE FROM attedance WHERE meetingNo = OLD.no;
END
   Timing: BEFORE
  Created: NULL
 sql_mode: 
  Definer: root@localhost
*************************** 2. row ***************************
  Trigger: room_deleter
    Event: DELETE
    Table: rooms
Statement: BEGIN
   DELETE FROM archivedmovies WHERE roomID = OLD.roomID;
   DELETE FROM bannedusers WHERE roomID = OLD.roomID;
   DELETE FROM bufferseats WHERE roomID = OLD.roomID;
   DELETE FROM meetings WHERE roomID = OLD.roomID;
   DELETE FROM events WHERE roomID = OLD.roomID;
   DELETE FROM favoritelinks WHERE roomID = OLD.roomID;
   DELETE FROM onetime_passwords WHERE roomID = OLD.roomID;
   DELETE FROM roomsounds WHERE roomID = OLD.roomID;
   DELETE FROM savedannouncements WHERE roomID = OLD.roomID;
   DELETE FROM savedsurveys WHERE roomID = OLD.roomID;
   DELETE FROM smsreports WHERE roomID = OLD.roomID;
   DELETE FROM smsusers WHERE roomID = OLD.roomID;
   DELETE FROM spreadsheets WHERE roomID = OLD.roomID;
   DELETE FROM subscriptions WHERE custom = OLD.roomID;
   DELETE FROM users_rooms WHERE roomID = OLD.roomID;
END
   Timing: BEFORE
  Created: NULL
 sql_mode: 
  Definer: root@localhost
2 rows in set (0.30 sec)

Because this bug is so old, I didn't see what I was originally referring to. Looking over this again I see the issue:

If you delete a row from the `rooms` table, one of the lines in the 'room_deleter' trigger will DELETE FROM `meetings`.

The `meetings` table has its own trigger that will delete from `attendance` (faking a foreign key relationship).

For whatever reason, having these two triggers present at the same time will produce the 'table not found' issue.

Further adding to this is that the SQL that I am running to produce this error is NOT a DELETE but an UPDATE and as you can see, neither trigger are supposed to respond to UPDATE statements.

Hello Matthew,
This is what I was trying to clarify by SHOW TRIGGERS, If you still have your full trigger set (schema would be nice too) I can still try and reproduce the table error to see if this is a bug too. Otherwise nothing else to do here.

giving the three relevant ones...let me know what else you need cause I don't see how this isn't a bug...

CREATE TABLE `attendance` (
  `no` int(11) NOT NULL auto_increment,
  `meetingNo` int(11) NOT NULL default '0',
  `name` varchar(40) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `phone` varchar(17) default NULL,
  `role` tinyint(4) NOT NULL default '0',
  `companyCustomData` varchar(100) default NULL,
  `entryTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `reentryTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `duration` int(6) NOT NULL default '-1',
  `sessionEnded` smallint(6) NOT NULL default '0',
  `ipaddress` int(11) unsigned default NULL,
  PRIMARY KEY  (`no`),
  KEY `meetingNo` (`meetingNo`)
) ENGINE=MyISAM AUTO_INCREMENT=669043 DEFAULT CHARSET=latin1

//////////////

CREATE TABLE `meetings` (
  `no` int(11) NOT NULL auto_increment,
  `roomID` int(11) NOT NULL default '0',
  `startTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `ended` tinyint(4) NOT NULL default '0',
  `duration` int(11) NOT NULL default '0',
  `history` mediumtext NOT NULL,
  `announcementHistory` text NOT NULL,
  `SMSHistory` text NOT NULL,
  PRIMARY KEY  (`no`),
  KEY `roomIDEnded` (`roomID`,`ended`)
) ENGINE=MyISAM AUTO_INCREMENT=71373 DEFAULT CHARSET=latin1

////////////

CREATE TABLE `rooms` (
  `roomID` int(4) NOT NULL auto_increment,
  `shorturl` varchar(40) NOT NULL default '',
  `name` varchar(30) NOT NULL default '',
  `companyID` int(11) NOT NULL default '0',
  `isTrial` tinyint(4) NOT NULL default '0',
  `nbVideo` tinyint(4) NOT NULL default '1',
  `defaultVideoQuality` tinyint(4) NOT NULL default '2',
  `allowBigLiveVideo` tinyint(1) NOT NULL default '0'
  PRIMARY KEY  (`roomID`),
  UNIQUE KEY `shorturl_2` (`shorturl`,`companyID`),
  KEY `companyIDFK` (`companyID`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Just for simplicity sake, you can trim down the room_deleter trigger to just this..

CREATE TRIGGER `room_deleter`
BEFORE DELETE ON `rooms`
FOR EACH ROW
BEGIN
   DELETE FROM meetings WHERE roomID = OLD.roomID;
END; ;;

and the problem still exists.

The symptom seems related to Bug#8407, which has been fixed in 5.0.38 and 5.1.17.
Please use 5.0.38 or better when trying to reproduce this.

Ok. I will find some time this week to upgrade to 5.0.38 and will report back.

Please provide your feedback - when you have it. Thanks.

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".