Re: [percona-group] Are triggers really unsafe with replication?

[Ovais Tariq]

Hi,

Statement-based replication and triggers have some pretty interesting bugs, one such bug is related to triggers doing an insert into auto_increment column, please see the test case and the bug details here:

http://bugs.mysql.com/bug.php?id=45677

All in all the warning message is pretty self-explanatory: "Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly"

The consequence of the bug is that wrong auto_inc ID will be generated and used on the slave which will either cause a duplicate key error causing replication to break or will cause the slave to diverge from the master.

If you use MIXED mode replication then such statements are logged in ROW format, which means that the trigger does not get executed on the slave and the rows changed by the trigger on the master are replicated.

Best,

On Mon, May 13, 2013 at 6:10 PM, Rolandow - <rola...@gmail.com> wrote:

For mysql I read that triggers and replication could create errors. I did some tests with out Percona 5.5.30-30.2 servers that are replicated in master-master setup.

For my test setup I created the tables topic, post and post_history. On the post table I added a trigger AFTER INSERT that increments the PostQty in the topic table. This doesn't generate an auto increment value, so I'd think this is a safe query.

I do get this notic though: "Note: #1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.".

For my second test I created an AFTER UPDATE trigger on my posts table. This trigger inserts a record to post_history, so this query would/should be unsafe, since it uses an auto_increment.

Then I added and editted a few records in the tables. All data was saved on both servers as expected. I was also able to insert/update the records on both servers.

So my question is: when will this be unsafe to do, and what will hapen? Will replication stop? These results of my tests are ok, so when can I expect any issues?

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Ovais Tariq, Principal Support Engineer, Percona

http://www.percona.com | http://www.mysqlperformanceblog.com
Phone               : +1 (888) 401-3401 Ext. 552
24/7 Emergency : +1 888 401 3401 ext 911
Skype               : ovaistariq

Training : http://www.percona.com/training/
Support  : http://www.percona.com/mysql-support/

Percona Live London MySQL Conference 2013
http://www.percona.com/live/london-2013/

[Rolandow -]

Hi,

The weird thing is that it gives me the same error message while I am *not* performing a query that uses an auto increment. For example, I installed a trigger that updates a counter, when a date changes from 0000-00-00 to a value. This doesn't insert a record, so it does not increment a value at all.

This is my trigger:

delimiter |

DROP TRIGGER IF EXISTS after_insert_member|

 CREATE TRIGGER after_insert_member AFTER UPDATE ON mVibesEMail.EmailPromotionQueue

  FOR EACH ROW BEGIN

 IF ( (OLD.SentDateTime IS NULL OR OLD.SentDateTime = '0000-00-00 00:00:00') AND NEW.SentDateTime >= '0000-00-00 00:00:00')

 THEN

    UPDATE mVibes.Member SET PromotionDateTime = NOW(), NumberOfPromotions = NumberOfPromotions+1 WHERE MemberId = NEW.MemberId;

 END IF;

  END;

|

delimiter ;

Also, I ran some tests to see what happens if I do an insert, which is using an auto increment. From the results that I could see, everything was fine. The records where created on master and slave, the replication was still running. So that's why I ask here; is this a real error message, or is this error / warning always displayed when you install a trigger on a replication setup?

I'd like to know the real danger here, not just the 'it could fail, so don't complain if it does' explanation that I read all over the internet now (no offence to you, of course).

That's why I am trying to reproduce the error, so I can figure out what triggers actually are safe. Also was hoping maybe this was solved in Percona 5.5 :-)

Thank you for your reply!

 

Op dinsdag 14 mei 2013 10:45:58 UTC+2 schreef Ovais Tariq het volgende:

Hi,

Statement-based replication and triggers have some pretty interesting bugs, one such bug is related to triggers doing an insert into auto_increment column, please see the test case and the bug details here:

http://bugs.mysql.com/bug.php?id=45677

All in all the warning message is pretty self-explanatory: "Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly"

The consequence of the bug is that wrong auto_inc ID will be generated and used on the slave which will either cause a duplicate key error causing replication to break or will cause the slave to diverge from the master.

If you use MIXED mode replication then such statements are logged in ROW format, which means that the trigger does not get executed on the slave and the rows changed by the trigger on the master are replicated.

Best,

On Mon, May 13, 2013 at 6:10 PM, Rolandow - <rola...@gmail.com> wrote:

For mysql I read that triggers and replication could create errors. I did some tests with out Percona 5.5.30-30.2 servers that are replicated in master-master setup.

For my test setup I created the tables topic, post and post_history. On the post table I added a trigger AFTER INSERT that increments the PostQty in the topic table. This doesn't generate an auto increment value, so I'd think this is a safe query.

I do get this notic though: "Note: #1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.".

For my second test I created an AFTER UPDATE trigger on my posts table. This trigger inserts a record to post_history, so this query would/should be unsafe, since it uses an auto_increment.

Then I added and editted a few records in the tables. All data was saved on both servers as expected. I was also able to insert/update the records on both servers.

So my question is: when will this be unsafe to do, and what will hapen? Will replication stop? These results of my tests are ok, so when can I expect any issues?

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.

To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsub...@googlegroups.com.

To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ovais Tariq]

Hi,

On Wed, May 15, 2013 at 12:02 PM, Rolandow - <rola...@gmail.com> wrote:

Hi,

The weird thing is that it gives me the same error message while I am *not* performing a query that uses an auto increment. For example, I installed a trigger that updates a counter, when a date changes from 0000-00-00 to a value. This doesn't insert a record, so it does not increment a value at all.

This is my trigger:

delimiter |

DROP TRIGGER IF EXISTS after_insert_member|

 CREATE TRIGGER after_insert_member AFTER UPDATE ON mVibesEMail.EmailPromotionQueue

  FOR EACH ROW BEGIN

 IF ( (OLD.SentDateTime IS NULL OR OLD.SentDateTime = '0000-00-00 00:00:00') AND NEW.SentDateTime >= '0000-00-00 00:00:00')

 THEN

    UPDATE mVibes.Member SET PromotionDateTime = NOW(), NumberOfPromotions = NumberOfPromotions+1 WHERE MemberId = NEW.MemberId;

 END IF;

  END;

|

delimiter ;

Also, I ran some tests to see what happens if I do an insert, which is using an auto increment. From the results that I could see, everything was fine. The records where created on master and slave, the replication was still running. So that's why I ask here; is this a real error message, or is this error / warning always displayed when you install a trigger on a replication setup?

I'd like to know the real danger here, not just the 'it could fail, so don't complain if it does' explanation that I read all over the internet now (no offence to you, of course).

I dont know what tests you are running, but I can also run simplistic tests to prove that the trigger when updating an auto-increment column will not fail. The purpose why I shared the bug report http://bugs.mysql.com/bug.php?id=45677 is so that you can see the exact test case that shows how trigger can break replication when SBR is in use. I do not want to repeat any of that since everything is already present in the test case.

 

That's why I am trying to reproduce the error, so I can figure out what triggers actually are safe. Also was hoping maybe this was solved in Percona 5.5 :-)

The bug is already fixed, you need to use MIXED mode replication or ROW based replication.

 

To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.

To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Justin Swanhart]

Hi,

These warnings may be overly sensitive and can be triggered without actual violating consistency.  As long as there is no trigger on mVibes.Member which inserts into tables with AUTO_INCREMENT, then your UPDATE is safe but MySQL is warning you that MIXED or ROW based mode is really better to use to be absolutely safe, because if a trigger is added to mVibes.member then replication consistency could break.

I would suggest simply switching to MIXED or ROW, but if you insist on using triggers and SBR then your triggers must absolutely not insert into tables which have auto_increment columns.

--Justin

[Rolandow]

Thank you Justin and Ovais. That answers my question. I think we will switch to mixed mode indeed.

Hi,

--
You received this message because you are subscribed to a topic in the Google Groups "Percona Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/percona-discussion/21Em_gdE0_E/unsubscribe?hl=en-US.
To unsubscribe from this group and all its topics, send an email to percona-discuss...@googlegroups.com.

[Rolandow]

I guess this may sound as a newbie question, but since our managed hosting company doesn't know the answer either, I take my chances and ask the guru's here :-)

Can we safely switch to MIXED mode? I think we need to shut down both servers, change the binlog_format=MIXED, and fire them both up again. Is that all there is to it?

Thanks!

On 5/15/13 10:37 , Justin Swanhart wrote:

Hi,

--
You received this message because you are subscribed to a topic in the Google Groups "Percona Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/percona-discussion/21Em_gdE0_E/unsubscribe?hl=en-US.
To unsubscribe from this group and all its topics, send an email to percona-discuss...@googlegroups.com.

[Justin Swanhart]

Hi,

Yes, that is the safest way to switch.  

--Justin

[Bryan O'Neal]

Late to the conversation but here is my experience.
1) Use mixed mode replication.
2) Switching is safe. I would set it on all servers in the cluster.
3) Use at least 5.5. 5.0 and 5.1 have a tendency to drop things on the floor under high load. 5.5 does not.
4) Make sure innodb rollback on time out is enabled. If you run command and cancel it will look clean but the trigger may still fire even with the first transaction canceled (again I have not seen this in 5.5 but I have in 5.0)
5) Avoid triggers.

[rola...@gmail.com]

Thank you for your reply. Could you elaborate on point 5? :)

I get the feeling that people rather stay away from trigger, but isn't it a nice feature actually? Wouldn't this be better than doing these updates in the code?

Verzonden vanaf mijn BlackBerry®-toestel

---

From: "Bryan O'Neal" <br...@theonealandassociates.com>

Sender: percona-d...@googlegroups.com

Date: Wed, 15 May 2013 08:44:59 -0700

To: <percona-d...@googlegroups.com>

ReplyTo: percona-d...@googlegroups.com

Subject: Re: [percona-group] Are triggers really unsafe with replication?