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

0 rows updated when that should have been 1. - only in Heidi, not in other programs

domaniqs posted 7 months ago in General
Hi,

I have a problem updating table using View, as the program returns "0 rows updated when that should have been 1." error.
Strangely when I perform the same update using Navicat or phpMyAdmin, everything works well.

Table definitions:

DROP TABLE IF EXISTS `scq`;
CREATE TABLE `scq` (
`SCQID` int(11) NOT NULL AUTO_INCREMENT,
`NicolaID` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`IssueSCQ` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`SCQAccepted` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`SCQ_RefusalReason` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`SCQVersionold` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`scqVersion` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`SCQ_checkcode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`SCQID`),
KEY `SCQ_checkcode` (`SCQ_checkcode`) USING BTREE,
KEY `NicolaID` (`NicolaID`) USING BTREE,
CONSTRAINT `scq_ibfk_1` FOREIGN KEY (`NicolaID`) REFERENCES `ids` (`NicolaID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=507 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `ids`;
CREATE TABLE `ids` (
`NicolaID` varchar(10) CHARACTER SET utf8 NOT NULL,
`ParticipantID` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Respondent_Serial` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`HHNO` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`HHNOX` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`NicolaID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `personal`;
CREATE TABLE `personal` (
`PersonalID` int(11) NOT NULL AUTO_INCREMENT,
`NicolaID` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Title` varchar(6) CHARACTER SET utf8 DEFAULT NULL,
`FirstName` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`LastName` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`InterviewDate` varchar(10) DEFAULT NULL,
`TelephoneNo` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`EmailAddress` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
`ConsentToContact` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`Gender` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`ReportedAge` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`MonthOfBirth` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`YearOfBirth` varchar(5) CHARACTER SET utf8 DEFAULT NULL,
`IncentivePayeeName` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`PersonalID`),
KEY `NicolaID` (`NicolaID`) USING BTREE,
CONSTRAINT `personal_ibfk_1` FOREIGN KEY (`NicolaID`) REFERENCES `ids` (`NicolaID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=508 DEFAULT CHARSET=latin1;


View code that is causing problem:

select `scq`.`SCQID` AS `SCQID`,`scq`.`NicolaID` AS `NicolaID`,`ids`.`ParticipantID` AS `ParticipantID`,`personal`.`InterviewDate` AS `InterviewDate`,`scq`.`IssueSCQ` AS `IssueSCQ`,`scq`.`SCQAccepted` AS `SCQAccepted`,`scq`.`SCQ_RefusalReason` AS `SCQ_RefusalReason`,`scq`.`SCQVersionold` AS `SCQVersionold`,`scq`.`scqVersion` AS `scqVersion`,`scq`.`SCQ_checkcode` AS `SCQ_checkcode`
from ((`scq` join `ids` on((`scq`.`NicolaID` = `ids`.`NicolaID`))) join `personal` on((`ids`.`NicolaID` = `personal`.`NicolaID`)));


then when I attemp to change SCQ_checkcode by typing into data window, the results are:

UPDATE `CAPI`.`scqView4Angie` SET `SCQ_checkcode`='23cd' WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `InterviewDate`='2014-03-27' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='' LIMIT 1;

SHOW WARNINGS;
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0.000 sec. */


I need to add, that I have modified this view by adding InterviewDate from table personal to it. Previous version of this view did not have any problems with updating/inserting data:

select `scq`.`SCQID` AS `SCQID`,`scq`.`NicolaID` AS `NicolaID`,`ids`.`ParticipantID` AS `ParticipantID`,`scq`.`IssueSCQ` AS `IssueSCQ`,`scq`.`SCQAccepted` AS `SCQAccepted`,`scq`.`SCQ_RefusalReason` AS `SCQ_RefusalReason`,`scq`.`SCQVersionold` AS `SCQVersionold`,`scq`.`scqVersion` AS `scqVersion`,`scq`.`SCQ_checkcode` AS `SCQ_checkcode`
from (`scq` join `ids` on((`scq`.`NicolaID` = `ids`.`NicolaID`)));


query produces results:

UPDATE `CAPI`.`scqView4Angie-OLD` SET `SCQ_checkcode`='23cd' WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='' LIMIT 1;
SELECT `SCQID`, `NicolaID`, `ParticipantID`, `IssueSCQ`, `SCQAccepted`, `SCQ_RefusalReason`, `SCQVersionold`, `scqVersion`, `SCQ_checkcode` FROM `CAPI`.`scqView4Angie-OLD` WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='23cd' LIMIT 1;

UPDATE `CAPI`.`scqView4Angie-OLD` SET `SCQ_checkcode`='23cd' WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='' LIMIT 1;
SELECT `SCQID`, `NicolaID`, `ParticipantID`, `IssueSCQ`, `SCQAccepted`, `SCQ_RefusalReason`, `SCQVersionold`, `scqVersion`, `SCQ_checkcode` FROM `CAPI`.`scqView4Angie-OLD` WHERE `SCQID`=573 AND `NicolaID`='N10494' AND `ParticipantID`='201582' AND `IssueSCQ`='2' AND `SCQAccepted`='' AND `SCQ_RefusalReason`='' AND `SCQVersionold`='2' AND `scqVersion`='' AND `SCQ_checkcode`='23cd' LIMIT 1;

SHOW WARNINGS;
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 1 query: 0.000 sec. */


There are no triggers nor procedures. I am just wondering why Heidi does not like my code while other programs do not complain.

I would appreciate any suggestions what is wrong or how to fix what is wrong.

Thanks
Maciej
ansgar posted 7 months ago
There is no real error, it's just that the server returned 0 affected rows after the UPDATE. That's happening when you effectively do not change any value, taking silent changes on the server into account. For example, when you have a TINYINT column value which is 255 (maximum for TINYINT), and then you try to change it to 1000 - the server automatically assumes 255 again, and returns "0 affected" rows.
domaniqs posted 7 months ago
Thank you for your reply, ansgar.

Is there any way to deal with this problem in order to make Heidi perform this operation successfully?

The inserted value (SCQ_checkcode) is only 4 characters (23cd) into a VARCHAR field of size 10. Strangely, everything has started to fall apart after I joined InterviewDate column from personal table, which is also VARCHAR 10, so in case of date treated as a varchar (dates entered in the format 2014-03-27), there should be no problem. I am not attempting to insert data that is larger than the size of the field. What possible other silent changes could be taken into account in this case?
Am I wrong?
imre posted 7 months ago
This is not a bug.

http://www.heidisql.com/forum.php?t=12462
domaniqs posted 7 months ago
Thank you for your help.
Maciej

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