We are using MySQL mysql "Ver 14.14 Distrib 5.1.26-rc, for redhat-linux-gnu (x86_64) using readline 5.1"
We have master & slave databases. We did a small test with respect to trigger replication.
We ran below sql statements in the master.
CREATE TABLE account_three (acct_num INT, amount DECIMAL(10,2));
CREATE TABLE account_three_trigger (total DECIMAL(10,2), server_time VARCHAR(64));
DELIMITER //
CREATE TRIGGER ins_sum_three AFTER INSERT ON account_three
FOR EACH ROW
BEGIN
SET @sum = @sum + NEW.amount;
INSERT INTO account_three_trigger VALUES(@SUM, cast(timestampadd(FRAC_SECOND,999999,NOW()) as char));
SET @SUM=0;
END;//
INSERT INTO account_three VALUES(137,14.98),(141,1937.50),(97,-100.00)//
//after few minutes
INSERT INTO account_three VALUES(131,11.96),(161,1337.50),(17,-100.00)
mysql>SELECT * from account_three_trigger//
+---------+----------------------------+
| total | server_time |
+---------+----------------------------+
| NULL | 2009-02-20 11:22:25.999999 |
| 1937.50 | 2009-02-20 11:22:25.999999 |
| -100.00 | 2009-02-20 11:22:25.999999 |
| NULL | 2009-02-20 11:25:03.999999 |
| 1337.50 | 2009-02-20 11:25:03.999999 |
| -100.00 | 2009-02-20 11:25:03.999999 |
+---------+----------------------------+
6 rows in set (0.00 sec)
Now, in slave, ran the same SQL
+---------+----------------------------+
| total | server_time |
+---------+----------------------------+
| NULL | 2009-02-20 11:22:25.999999 |
| 1937.50 | 2009-02-20 11:22:25.999999 |
| -100.00 | 2009-02-20 11:22:25.999999 |
| NULL | 2009-02-20 11:25:03.999999 |
| 1337.50 | 2009-02-20 11:25:03.999999 |
| -100.00 | 2009-02-20 11:25:03.999999 |
+---------+----------------------------+
6 rows in set (0.00 sec)
mysql>show triggers;
+---------------+--------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+---------------+--------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| ins_sum_three | INSERT | account_three | BEGIN
SET @sum = @sum + NEW.amount;
INSERT INTO account_three_trigger VALUES(@SUM, cast(timestampadd(FRAC_SECOND,999999,NOW()) as char));
SET @SUM=0;
END | AFTER | NULL | | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |
+---------------+--------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
Results :
1. Trigger Runs in master
2. Data copied to the slave
3. Trigger copied to the slave
4. Trigger does not run in slave
This is quite different from the faq in the manual -
http://dev.mysql.com/doc/refman/5.1/en/faqs-triggers.html#qandaitem-24-5-1-12
To stress it more wrote another trigger to insert a system variable instead of time - @@general_log_file, again we found the log file name of master db in both master & slave account_three_trigger table.
Is this a bug or expected behavior? If it is expected behavior please update the documentation.
Thanks
Arun