Bug #41483 Merge tables don't correctly fire triggers

• Submitted: 15 Dec 2008 22:39
• Modified: 16 Dec 2008 4:56
• Reporter: Chris Stephens
• Status: Not a Bug
• Category: MySQL Server: Merge storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.51a-3ubuntu5.4
• OS: Linux
• CPU Architecture: Any
• Tags: merge table, MRG_MyISAM, triggers

[15 Dec 2008 22:39] Chris Stephens

Description:
An insert to a merge table doesn't fire a trigger on the merged table which is setup to receive inserts, etc..

Tested in 5.0.51a-3ubuntu5.4 and 5.0.19-standard.

How to repeat:
CREATE TABLE `t1` (
  `id` varchar(36) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t2` (
  `id` varchar(36) NOT NULL default '',
  PRIMARY KEY  (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t_merged` (
  `id` varchar(36) NOT NULL default '',
  PRIMARY KEY  (`id`) 
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`);

CREATE TABLE `t_history` (
  `t_history_id` int(11) NOT NULL auto_increment,
  `id` varchar(36) NOT NULL default '',
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`t_history_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TRIGGER IF EXISTS t2_ainsert;
delimiter |
CREATE TRIGGER t2_ainsert AFTER INSERT ON t2
FOR EACH ROW BEGIN
	INSERT INTO t_history (id) VALUES (NEW.id);
END;
|

INSERT INTO t2 (id) VALUES ('llama');
INSERT INTO t_merged (id) VALUES ('trauma');

Result:

+--------------+-------+---------------------+
| t_history_id | id    | date                |
+--------------+-------+---------------------+
|            1 | llama | 2008-12-15 14:52:44 |
+--------------+-------+---------------------+

Expected Result:

+--------------+-------+---------------------+
| t_history_id | id    | date                |
+--------------+-------+---------------------+
|            1 | llama | 2008-12-15 14:52:44 |
+--------------+-------+---------------------+
|            2 | trauma| 2008-12-15 14:52:44 |
+--------------+-------+---------------------+

Suggested fix:
It would make the most sense that the trigger would be fired for the underlying table, if this isn't the case the documentation should clearly state that triggers do not work in an intuitive manner for merge tables and that it may be necessary to create an additional trigger on the merge table itself (I haven't tested this).

[16 Dec 2008 4:56] Valeriy Kravchuk

I can repeat this behaviour with 5.0.72, but I do not consider it a bug. t_merged tables is NOT a t2 table, so why you expect trigger defined on t2 to fire? You can declare separate trigger on t_merged:

mysql> delimiter |
mysql> CREATE TRIGGER tm_ainsert AFTER INSERT ON t_merged
    -> FOR EACH ROW BEGIN
    ->  INSERT INTO t_history (id) VALUES (NEW.id);
    -> END;
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> INSERT INTO t_merged (id) VALUES ('trauma2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_history;
+--------------+---------+---------------------+
| t_history_id | id      | date                |
+--------------+---------+---------------------+
|            1 | llama   | 2008-12-16 06:50:55 |
|            2 | trauma2 | 2008-12-16 06:53:08 |
+--------------+---------+---------------------+
2 rows in set (0.03 sec)

and it will work, as you can see.