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

Error 1592 Statement is not safe to log in statement format

User, date Message
Written by fireman_samMoney, Euro
2 years ago
Category: General
6 posts since Thu, 04 Oct 12
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!
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
Can you post the query you find in the SQL log prior to the error popup?
Written by fireman_samMoney, Euro
2 years ago
6 posts since Thu, 04 Oct 12
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;
Written by jfalchMoney, Euro
2 years ago
354 posts since Sat, 17 Oct 09
caused by use of LIMIT - cf here.
Written by fireman_samMoney, Euro
2 years ago
6 posts since Thu, 04 Oct 12
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.
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
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.
Written by jfalchMoney, Euro
2 years ago
354 posts since Sat, 17 Oct 09
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 ?
Written by fireman_samMoney, Euro
2 years ago
6 posts since Thu, 04 Oct 12
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?
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
> 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.
Written by fireman_samMoney, Euro
2 years ago
6 posts since Thu, 04 Oct 12
Is an AUTO_INCREMENT integer not a reasonable primary key?
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
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.
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
See also here, another thread with the same topic. With the only difference that I'm now going to fix that :)
Written by ansgar
2 years ago
4800 posts since Fri, 07 Apr 06
Et voilĂ : fixed in r4206. Please update your HeidiSQL, ladies.
 

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