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

[expired user #5936]'s profile image [expired user #5936] posted 12 years ago in General Permalink
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!
ansgar's profile image ansgar posted 12 years ago Permalink
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.
[expired user #5936]'s profile image [expired user #5936] posted 12 years ago Permalink
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.
ansgar's profile image ansgar posted 12 years ago Permalink
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
[expired user #6074]'s profile image [expired user #6074] posted 12 years ago Permalink
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.
ansgar's profile image ansgar posted 11 years ago Permalink
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.