MySQL Forums
Forum List  »  Replication

Trigger Replication is not same as in the documentation
Posted by: Arun Kumar
Date: February 20, 2009 01:43PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger Replication is not same as in the documentation
3399
February 20, 2009 01:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.