MySQL Warning Statements when you have a LIMIT clause.
The MySQL log contains 1000s of statements like this:
“[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 score WHERE user_id = 12345 AND created = ‘2012-04-15’ LIMIT 1”
I know some advice seems to say that well written code doesn't need the "LIMIT 1" but it sure makes me feel better to know that if I made a mistake, I can't accidently wipe out a whole DB.
Am I wrong in my feeling that this is safer?
If this is okay, is there a way to get around it so that the my log isn't FILLED with those warning?
“[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 score WHERE user_id = 12345 AND created = ‘2012-04-15’ LIMIT 1”
I know some advice seems to say that well written code doesn't need the "LIMIT 1" but it sure makes me feel better to know that if I made a mistake, I can't accidently wipe out a whole DB.
Am I wrong in my feeling that this is safer?
If this is okay, is there a way to get around it so that the my log isn't FILLED with those warning?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am running mysql Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1.
Julian, thanks for the four options. There are some good ideas in there.
I think it's just paranoia on my part because I test and retest and make sure that option four is true. All of my tables have a key index field and here's my progressive thinking regarding how a delete would work.
Level One: DELETE FROM score WHERE user_id = 12345
This in itself will always work because score uses user_id as a key field and their can be only ONE.
Level Two: But, being slightly paranoid I add this: DELETE FROM score WHERE user_id = 12345 AND created = ‘2012-04-15’
The check on created is already a secondary protection. It just gives one more field on which to be extra cautious.
Level Three: Most likely overkill, I add the limit: DELETE FROM score WHERE user_id = 12345 AND created = ‘2012-04-15’ LIMIT 1”
This is the final level of protection, just to make sure.
But from what you're saying, going all the way to level three is not a "best practice" and in light of the fact that it's issuing a warning really isn't a good idea anyway.
Is that right?
Julian, thanks for the four options. There are some good ideas in there.
I think it's just paranoia on my part because I test and retest and make sure that option four is true. All of my tables have a key index field and here's my progressive thinking regarding how a delete would work.
Level One: DELETE FROM score WHERE user_id = 12345
This in itself will always work because score uses user_id as a key field and their can be only ONE.
Level Two: But, being slightly paranoid I add this: DELETE FROM score WHERE user_id = 12345 AND created = ‘2012-04-15’
The check on created is already a secondary protection. It just gives one more field on which to be extra cautious.
Level Three: Most likely overkill, I add the limit: DELETE FROM score WHERE user_id = 12345 AND created = ‘2012-04-15’ LIMIT 1”
This is the final level of protection, just to make sure.
But from what you're saying, going all the way to level three is not a "best practice" and in light of the fact that it's issuing a warning really isn't a good idea anyway.
Is that right?
The way I look at it is I flip it around - if more than one record WERE to be matched by those criteria - what would be the ramifications for the application - would it be immediately noticeable or not. If so then long before you get to delete the record you should see something is not right.
Personally I use the `status` field or archive table mostly because the applications I build have auditing on - you can see who changed the state when from what to what. With this approach nothing is lost - it's state is just changed.
Personally I use the `status` field or archive table mostly because the applications I build have auditing on - you can see who changed the state when from what to what. With this approach nothing is lost - it's state is just changed.
ASKER
I accidently hit "close" before I commented. I was going to say thanks. That makes sense. I have a number of major applications that I'll soon start working through to begin the process of repairing query statements. You've given me some good tracks to run on.
Thanks again.
Thanks again.
You are welcome.
I ask, because I found this:
https://bugs.mysql.com/bug.php?id=42415