Error 1592 Statement is not safe to log in statement format

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!

Can you post the query you find in the SQL log prior to the error popup?

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;

caused by use of LIMIT - cf here.

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.

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.

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 ?

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?

> 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.

Is an AUTO_INCREMENT integer not a reasonable primary key?

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.

See also here, another thread with the same topic. With the only difference that I'm now going to fix that :)

Et voilà: fixed in r4206. Please update your HeidiSQL, ladies.