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

Error 1592 Statement is not safe to log in statement format

fireman_sam posted 2 years ago in General
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 posted 2 years ago
Can you post the query you find in the SQL log prior to the error popup?
fireman_sam posted 2 years ago
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 posted 2 years ago
caused by use of LIMIT - cf here.
fireman_sam posted 2 years ago
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 posted 2 years ago
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 posted 2 years ago
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 ?
fireman_sam posted 2 years ago
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 posted 2 years ago
> 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.
fireman_sam posted 2 years ago
Is an AUTO_INCREMENT integer not a reasonable primary key?

ansgar posted 2 years ago
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 posted 2 years ago
See also here, another thread with the same topic. With the only difference that I'm now going to fix that :)
ansgar posted 2 years ago
Et voilà: fixed in r4206. Please update your HeidiSQL, ladies.

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