distal-attribute
distal-attribute
distal-attribute
distal-attribute

Heidi SQL appending 'limit 1' to update / delete queries when not needed

User, date Message
Written by BenTrimble
3 years ago
Category: General
2 posts since Mon, 26 Sep 11
Hi,

I've been running Heidi SQL on a few tables and it's adding a 'limit 1' to the end of update / delete queries when it's not needed (as the key field is specificed in the function) and this is causing errors to drop out into our logs such as:

'The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.'

Is it possible to change the behaviour so that the limit is not applied?

Thanks in advance!
Written by ansgar
3 years ago
4973 posts since Fri, 07 Apr 06
I don't recall the exact issue id right now but as you may imagine this was meant as a safety check, proposed by some user.

In what logs do these messages occur ? ("The statement is unsafe...") I never saw these.
Written by BenTrimble
3 years ago
2 posts since Mon, 26 Sep 11
Thanks for your reply, the logs came from the guy in charge of our reporting server (shouting at me for breaking replication!), I don't have any information as to what system they came from. What information would be helpful? I'll ask for it and get back to you.
Written by ansgar
3 years ago
4973 posts since Fri, 07 Apr 06
The log message seems to be coming from the MySQL error log on the server.

Seems we're not the first guys discussing this log message:
http://bugs.mysql.com/bug.php?id=42415
http://bugs.mysql.com/bug.php?id=62537
Written by elauri
3 years ago
1 posts since Tue, 06 Dec 11
Agree with BenTrimble, the unnecessary (?) LIMIT in HeidiSQL causes the warning and *will* break statement-based replication (still the default replication mode AFAIK).

I do not see the benefit of appending a LIMIT 1 to the UPDATE statements as a sanity check - it makes the statement "somewhat non-deterministic" and without an ORDER BY clause, it still does not guarantee that the right row would be updated. In any case, if the row is referenced via a PRIMARY or UNIQUE NOT NULL key, it seems totally redundant..

Sanity check or not, this unfortunately makes Heidi unsfe for use in most replication setups.
Written by ansgar
2 years ago
4973 posts since Fri, 07 Apr 06
LIMIT clauses are removed now in r4206. Sorry for the delay. Another thread reminded me.
 

Please login to leave a reply, or register at first.