Error 1592 Statement is not safe to log in statement format

[expired user #6612]'s profile image [expired user #6612] posted 11 years ago in General Permalink
I get this a lot of the time now (though not always). If I run another MySQL client (Navicat), updates work fine. But when I try to edit a field value in Heidi, and move off the record to commit the values, up pops this annoying little dialog. I've scoured Google for an answer, and believe it's something to do with statement based vs. row based replication, but that's where the trail runs cold. Anyone got any ideas? It's driving me nuts!
ansgar's profile image ansgar posted 11 years ago Permalink
Can you post the query you find in the SQL log prior to the error popup?
[expired user #6612]'s profile image [expired user #6612] posted 11 years ago Permalink
Thanks for getting back to me Anse.

Here are some errors from today's log:

Version: '5.1.31-community-log' socket: '' port: 3306 MySQL Community Server (GPL)
121004 8:56:53 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM `nn_diary` WHERE `NNID`=6 LIMIT 1
121004 8:56:53 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM `nn_diary` WHERE `NNID`=7 LIMIT 1
121004 8:56:53 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM `nn_diary` WHERE `NNID`=8 LIMIT 1
121004 8:56:53 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM `nn_diary` WHERE `NNID`=9 LIMIT 1
121004 8:56:53 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM `nn_diary` WHERE `NNID`=10 LIMIT 1
121004 8:56:53 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM `nn_diary` WHERE `NNID`=11 LIMIT 1
121004 8:56:53 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM `nn_diary` WHERE `NNID`=12 LIMIT 1
121004 8:57:05 [Warning] Statement is not safe to log in statement format. Statement: UPDATE `nn_diary` SET `NurseID`='AJR' WHERE `NNID`=13 LIMIT 1
121004 8:59:53 [Warning] Statement is not safe to log in statement format. Statement: UPDATE `nn_diary` SET `NurseID`='AJR' WHERE `NNID`=13 LIMIT 1
121004 9:13:06 [Warning] Statement is not safe to log in statement format. Statement: UPDATE `nn_diary` SET `NurseID`='AJR' WHERE `NNID`=13 LIMIT 1
121004 9:16:27 [Warning] Statement is not safe to log in statement format. Statement: UPDATE `nn_diary` SET `NurseID`='AJR' WHERE `NNID`=13 LIMIT 1
121004 9:51:27 [Warning] Statement is not safe to log in statement format. Statement: UPDATE `nn_diary` SET `NurseID`='AJR' WHERE `NNID`=13 LIMIT 1

and here is the definition of nn_Diary:

CREATE TABLE `nn_diary` (
`NNID` INT(11) NOT NULL AUTO_INCREMENT,
`PupilID` VARCHAR(40) NULL DEFAULT NULL,
`NurseID` VARCHAR(20) NULL DEFAULT NULL,
`EntryType` VARCHAR(20) NULL DEFAULT NULL,
`StartDate` DATETIME NULL DEFAULT NULL,
`DurationHours` DOUBLE NULL DEFAULT NULL,
`EndDate` DATETIME NULL DEFAULT NULL,
`Headline` VARCHAR(50) NULL DEFAULT NULL,
`Notes` VARCHAR(250) NULL DEFAULT NULL,
`ReferringConsultationID` INT(11) NULL DEFAULT NULL,
`DateCreated` DATETIME NOT NULL,
`CreatedBy` VARCHAR(20) NOT NULL,
`DateUpdated` DATETIME NULL DEFAULT NULL,
`UpdatedBy` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`NNID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=16;
jfalch's profile image jfalch posted 11 years ago Permalink
caused by use of LIMIT - cf here.
[expired user #6612]'s profile image [expired user #6612] posted 11 years ago Permalink
Interesting point - but note that I am not building the SQL statement, Heidi is. I am just trying to update a cell in a table. The MySQL documentation you reference above suggests that the problem is either caused by the fact that the table is ordered on one or more columns (or perhaps that it isn't). However, sorting on or off, Heidi is still throwing the same error message every time I try to change a value in that particular table.
ansgar's profile image ansgar posted 11 years ago Permalink
HeidiSQL allows you to apply really unsafe row changes, in situations where you have no primary or unique key in a table. Doing that, the LIMIT is an important addition, even if it does not catch the right row. I think I can change the UPDATEs in a way that Heidi only uses a LIMIT clause if no pkey/ukey is present. That would minimize the error popups in replication mode.
jfalch's profile image jfalch posted 11 years ago Permalink
Yes, Heidisql is building an SQL statement that works perfectly well, and executes without warnings when not using replcation on the server. And this same statement causes the server to return a warning message when using statement based replication - it is not "thrown by heidisql", it just passes on the server´s warning. so what ? what do you suggest should be done about this ?
[expired user #6612]'s profile image [expired user #6612] posted 11 years ago Permalink
Well - I'm not sure what the LIMIT statement achieves. As you can see, the table does have a primary key. If I run the query the way Heidi builds it, ie. 'UPDATE `nn_diary` SET `NurseID`='AJR' WHERE `NNID`=13 LIMIT 1', MySQL trips the error. However, if I remove the LIMIT 1 part, the update goes through fine.

So - on the face of it, I would suggest removing the 'LIMIT 1' part of the statement that Heidi builds when running a simple update. Or at least making it an option perhaps. (I think this is what Anse is suggesting).

But am I the only person to experience this problem?
ansgar's profile image ansgar posted 11 years ago Permalink
> Or at least making it an option perhaps. (I think this is what Anse is suggesting)

No, I meant: Heidi should decide itself whether the LIMIT shall be appended. In tables with a reasonable key this should not be necessairy.
[expired user #6612]'s profile image [expired user #6612] posted 11 years ago Permalink
Is an AUTO_INCREMENT integer not a reasonable primary key?
ansgar's profile image ansgar posted 11 years ago Permalink
Yes, that's a good key, as an AUTO_INCREMENT column is always part of a primary key. But for example no primary key at all is bad. Or a unique key including a column which can be NULL is bad. In such cases Heidi should append the above mentioned LIMIT clause.
ansgar's profile image ansgar posted 11 years ago Permalink
See also here, another thread with the same topic. With the only difference that I'm now going to fix that :)
Code modification/commit from ansgar.becker, 11 years ago, revision 7.0.0.4206
Remove LIMIT 1 clause for safe grid queries. This prevents grid updates on simple tables from running into replication mode warning "Statement is not safe to log in statement format". See http://www.heidisql.com/forum.php?t=11304
ansgar's profile image ansgar posted 11 years ago Permalink
Et voilĂ : fixed in r4206. Please update your HeidiSQL, ladies.

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