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

[expired user #7957]'s profile image [expired user #7957] posted 10 years ago in General Permalink
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's profile image ansgar posted 10 years ago Permalink
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.
[expired user #7957]'s profile image [expired user #7957] posted 10 years ago Permalink
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?
[expired user #5421]'s profile image [expired user #5421] posted 10 years ago Permalink
This is not a bug.

http://www.heidisql.com/forum.php?t=12462
[expired user #7957]'s profile image [expired user #7957] posted 10 years ago Permalink
Thank you for your help.
Maciej

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