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

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

User, date Message
Written by domaniqs
6 months ago
Category: General
3 posts since Thu, 03 Apr 14
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
Written by ansgar
6 months ago
5045 posts since Fri, 07 Apr 06
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.
Written by domaniqs
6 months ago
3 posts since Thu, 03 Apr 14
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?
Written by imre
6 months ago
12 posts since Thu, 30 Dec 10
This is not a bug.

http://www.heidisql.com/forum.php?t=12462
Written by domaniqs
6 months ago
3 posts since Thu, 03 Apr 14
Thank you for your help.
Maciej
 

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