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

BenTrimble posted 6 years ago in General

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 posted 6 years ago
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.
BenTrimble posted 6 years ago
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 posted 6 years ago
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:
elauri posted 6 years ago
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 posted 5 years ago
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.