MySQL Forums
Forum List  »  Replication

Unsafe statement advice
Posted by: Andrew Ling
Date: July 22, 2014 07:11AM

We've recently moved from 5.1 to 5.5 (5.5.34) and have noticed MySQL is now reporting warnings about unsafe statements in the binlog. So I'm looking for advice on the best way to handle this.

The full warning messages are....

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: DELETE p FROM placeholders p LEFT JOIN clips c ON p.masterID = c.clipID WHERE c.clipID IS NULL

And the same for Statement: DELETE cf FROM copyfragments cf LEFT JOIN copynodes cn ON cn.fragID = cf.copyFragment WHERE cn.fragID IS NULL

And Statement: DELETE cn FROM copynodes cn LEFT JOIN clips c ON c.clipid = cn.clipid LEFT JOIN internalclips ic ON ic.clipid = cn.clipid WHERE c.clipid IS NULL AND ic.clipid IS NULL

I know I can switch the BINLOG_FORMAT to mixed and this stops the warnings, but I'm slightly concerned that if the delete deletes several thousand rows (which it can) it would be a lot slower in row mode than statement. I could also turn warnings off, but then I might miss something important.

Is there a way to change the statements so MySQL thinks they are safe. (I think they are anyway, but I guess it is difficult to tell).

Any advice would be much appreciated.

Regards

Options: ReplyQuote


Subject
Views
Written By
Posted
Unsafe statement advice
2070
July 22, 2014 07:11AM
837
July 23, 2014 08:58AM
767
July 24, 2014 07:04AM


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.