Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a bit irritated because of some warnings in mysql errorlog. Example 1:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. 
The statement is unsafe because it uses a LIMIT clause. 
This is unsafe because the set of rows included cannot be predicted. 
Statement: UPDATE `table` SET `status` = '1' WHERE `ID` = '15800' LIMIT 1

My colleague wrote these statements and I always wondered about the purpose of limiting a statement while using explicit ID. Nonetheless, this warning also occurs by not using LIMIT.
Example 2:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. 
The statement is unsafe because it uses a LIMIT clause. 
This is unsafe because the set of rows included cannot be predicted. 
Statement: DELETE FROM table WHERE id = 426888

What could be wrong in this case?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

You have LIMIT without ORDER BY. Therefore, it is meaningless.

Sets do not have implied orders without ORDER BY. The error message tells you this:

This is unsafe because the set of rows included cannot be predicted

Does your DELETE have a trigger or such with a LIMIT?

share|improve this answer
2  
We indeed had triggers set (on both update and delete) which statements also contained the limit clause. Corrected and no warning occurs anymore, thank you. –  32bitfloat Jul 30 '12 at 15:26
1  
@32bitfloat: he he Gotta watch for those pesky triggers... :-) –  gbn Jul 30 '12 at 16:26

What scares me about this question is that MySQL does not report a problem at the trigger level in the error. I wrote about this problem before back on Jan 06, 2012 : Has anyone ran into this replication breaking bug before?

I wrote in that past post that the bug report shows that as of Dec 10, 2010, there was a problem with trigger level issues not showing up in the error log while the reported problem would appear to be the original SQL. In your case, you see DELETE FROM table WHERE id = 426888 and just scratch your head, asking "how in the world would such a straightforward DELETE statement be deemed unsafe"?

MORAL OF THE STORY

@gbn's answer helped you realize that you needed to look to your trigger (+1 for @gbn). I wanted to reveal further how MySQL dropped the ball in not properly logging errors from the trigger level.

share|improve this answer
    
Thanks for the background-information - as I am not that familiar with triggers, I assumed that there would in general be no possibility to get problems of trigger-queries directly reported. –  32bitfloat Jul 30 '12 at 17:39
    
+1. MySQL frightens me when I compare it to SQL Server. It has some nice stuff (say INSERT IGNORE) but otherwise some stuff is just crazy... –  gbn Jul 30 '12 at 19:53
    
@gbn some known/unknown bugs linger from MySQL 4.x I don't know what worse, being MySQL Certified or being Windows Vista Certified : microsoft.com/learning/en/us/certification/… –  RolandoMySQLDBA Jul 30 '12 at 19:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.