MySQL Bugs: #25510: Federated: double trigger activation

Bug #25510	Federated: double trigger activation
Submitted:	9 Jan 2007 23:59	Modified:	28 Jan 2007 13:49
Reporter:	Peter Gulutzan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.0.36-BK, 5.2.0	OS:	Linux (Linux, SUSE 10.0 / 64-bit)
Assigned to:		CPU Architecture:	Any

Description:
On a remote computer, I create a table with an update trigger.
On a local computer, I create a matching federated table, with an update trigger.
On the local computer, I update a row.
Both triggers are activated.

I believe that this is odd behaviour, but if somebody (anybody)
decides "nah, that's okay", then it's okay to say: Not a Bug.
Then we'll have a Documented Decision.

How to repeat:
On remote computer, say:
        USE test
        CREATE TABLE t1 (s1 INT);
        CREATE TABLE t2 (s1 INT);
        CREATE TRIGGER t1_bu BEFORE UPDATE ON t1
        FOR EACH ROW INSERT INTO t2 VALUES (new.s1);

On local computer, say:
        USE test
        CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1), s2 INT)
        ENGINE=FEDERATED
        CONNECTION='mysql://Remote@192.168.1.106/test/t1';
        /* Change User and Host to what's appropriate for you. */

        CREATE TABLE t2 (s1 INT, PRIMARY KEY (s1), s2 INT)
        ENGINE=FEDERATED
        CONNECTION='mysql://Remote@192.168.1.106/test/t2';
        /* Change User and Host to what's appropriate for you. */

        INSERT INTO t1 VALUES (1);
        UPDATE t1 SET s1 = s1 + 1;
        SELECT * FROM t1;

The final SELECT will return two rows.

Thank you for a problem report. I do not see any triggers created on FEDERATED table in your "How to repeat" section. Is it intentional?

I am sorry, I made some mistakes when I was copying.
The "How to Repeat" section should be:

On remote computer, say:
        USE test
        CREATE TABLE t1 (s1 INT);
        CREATE TABLE t2 (s1 INT);
        CREATE TRIGGER t1_bu BEFORE UPDATE ON t1
        FOR EACH ROW INSERT INTO t2 VALUES (new.s1);

On local computer, say:
        USE test
        CREATE TABLE t1 (s1 INT)
        ENGINE=FEDERATED
        CONNECTION='mysql://Remote@192.168.1.106/test/t1';
        /* Change User and Host to what's appropriate for you. */

        CREATE TABLE t2 (s1 INT)
        ENGINE=FEDERATED
        CONNECTION='mysql://Remote@192.168.1.106/test/t2';
        /* Change User and Host to what's appropriate for you. */

        CREATE TRIGGER t1_bu BEFORE UPDATE ON t1
        FOR EACH ROW INSERT INTO t2 VALUES (new.s1);

        INSERT INTO t1 VALUES (1);
        UPDATE t1 SET s1 = s1 + 1;
        SELECT * FROM t1;

The final SELECT will return two rows.

Verified just as described with 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.36 Source distribution

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

mysql> CREATE TABLE t1 (s1 INT)         ENGINE=FEDERATED         CONNECTION='my
sql://root:root@192.168.0.1:3307/test/t1';
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t2 (s1 INT)
    ->         ENGINE=FEDERATED
    ->         CONNECTION='mysql://root:root@192.168.0.1:3307/test/t2';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER t1_bu BEFORE UPDATE ON t1
    ->         FOR EACH ROW INSERT INTO t2 VALUES (new.s1);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;
+------+
| s1   |
+------+
|    1 |
+------+
1 row in set (0.05 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> UPDATE t1 SET s1 = s1 + 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+
| s1   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

mysql> select * from t2;
+------+
| s1   |
+------+
|    2 |
|    2 |
+------+
2 rows in set (0.01 sec)

Note that, in MySQL 5.1.22 and newer, you can create Federated tables referring to tables in the same host. Thus, you can have multiple triggers for the same event on the same table.

The following example proves the case.
create table t1 (msg varchar(100));
create table t1f (msg varchar(100)) engine=federated 
    connection='mysql://user:pwd@127.0.0.1:3306/test/t1';
create table t1f2 (msg varchar(100)) engine=federated 
    connection='mysql://user:pwd@127.0.0.1:3306/test/t1f';
create trigger t1_bi before insert on t1 
    for each row set new.msg= concat(new.msg, ' - t1'));
create trigger t1f_bi before insert on t1f 
    for each row set new.msg = concat(new.msg, ' - t1f');
create trigger t1f2_bi before insert on t1f2 
    for each row set new.msg = concat(new.msg, ' - t1f2');

insert into t1 values ('test');
select * from t1;
#+-----------+
#| msg       |
#+-----------+
#| test - t1 | 
#+-----------+

truncate t1;
insert into t1f2 values ('test');
select * from t1;
#+------------------------+
#| msg                    |
#+------------------------+
#| test - t1f2 - t1f - t1 | 
#+------------------------+