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

0 rows updated when should have been 1

User, date Message
Written by geoffschultz
3 years ago
Category: General
10 posts since Thu, 03 Nov 11
I have been running the latest beta versions of HeidiSQL and I regularly get an error message "0 rows updated when should have been 1". HeidiSQL is connecting to a remote database via TCP. The server is running MySQL 5.0.90 and the server is running FreeBSD 7.3-STABLE.

The SQL which gets generated to update the table is very simple:

UPDATE `weather_bb` SET `rotation`=-90 WHERE `id`=110 LIMIT 1;

Note that `id` is a primary key.

I get this error on the betas, but I get no error on 6.0.0.3603 stable BUT the row doesn't get updated!

I don't know what additional info is needed, but I'll be glad to provide it.

-- Geoff
Written by geoffschultz
3 years ago
10 posts since Thu, 03 Nov 11
Here's the table create code:

CREATE TABLE `weather_bb` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`source` CHAR(4) NULL DEFAULT NULL,
`type` CHAR(8) NULL DEFAULT NULL,
`ll_lat` SMALLINT(3) NOT NULL COMMENT 'lower left lat',
`ll_long` SMALLINT(3) NOT NULL COMMENT 'lower left long - swap with lr_long for E longitides',
`ur_lat` SMALLINT(3) NOT NULL COMMENT 'upper right lat',
`ur_long` SMALLINT(3) NOT NULL COMMENT 'upper right long - swap with ul_long for E longitudes',
`filename` CHAR(64) NOT NULL,
`description` CHAR(64) NULL DEFAULT NULL,
`rotation` SMALLINT(1) UNSIGNED ZEROFILL NULL DEFAULT '0' COMMENT 'image rotation in degrees',
`level` TINYINT(3) UNSIGNED NULL DEFAULT '0' COMMENT 'display level. Set large scale charts > small scale charts',
PRIMARY KEY (`id`),
INDEX `source` (`source`),
INDEX `type` (`type`),
INDEX `ll_lat` (`ll_lat`),
INDEX `ll_long` (`ll_long`),
INDEX `ur_lat` (`ur_lat`),
INDEX `ur_long` (`ur_long`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
Written by ansgar
3 years ago
5022 posts since Fri, 07 Apr 06
Your "rotation" column is defined as UNSIGNED, which means it can only hold positive values from 0 to 65535. However, MySQL does not throws some error message when you violate these bounds. Instead, it just assumes you want "0" when you say "-90". Now, when the rotation value in a row is already "0", and you try to update it with -90, Heidi checks the affected row number and sees there was nothing updated. This is an uncritical warning which was not present in old HeidiSQL versions.
Written by geoffschultz
3 years ago
10 posts since Thu, 03 Nov 11
Duh...Thanks for pointing this out to me!

-- Geoff
Written by martin.sauter
2 years ago
3 posts since Tue, 10 Apr 12
I'm having the same error message when updating existing records, but in my case there where new field values to write into the database, so the above explanation does not fit. Are there other reasons for HeidiSQL to generate this type of error message?
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
You will need to post the exact SQL query, and the CREATE TABLE query here.
Written by feinstev
2 years ago
3 posts since Thu, 09 Aug 12
We're having the same problem as well when trying to update using a Query:

UPDATE `ITC Individual Tax Client Access` SET `PRIN_NAME`='Test', `PRIN_ADR_1`='234' WHERE `OFFICE`=7112 AND `CODE`=0 AND `PRIN_NAME`='' AND `PRIN_ADR_1`='' AND `PRIN_ADR_2`='' AND `PRIN_ADR_3`='' AND `LASTNAME`='' AND `1ST_MTH`='' AND `TAX_RETN`='' AND `SCH_C`='' AND `IND_FREE`='' AND `IND_FEEAMT`=0 AND `EMAILPRIN`='' AND `PRIN_PHONE`='' AND `PRIN_BDAY`='' AND `SPOUSE_NAM`='' AND `SPOUSE_BDA`='' AND `BL`='' AND `PL`='' LIMIT 1;

Here's the CREATE:

CREATE TABLE `CLIENTS` (
`CODE` DOUBLE NULL DEFAULT '0' COMMENT 'Enter 4 digit Client Code ',
`BUSINESS_N` VARCHAR(32) NULL DEFAULT '',
`OWNER_NAME` VARCHAR(32) NULL DEFAULT '',
`ADDRESS_1` VARCHAR(32) NULL DEFAULT '',
`ADDRESS_2` VARCHAR(32) NULL DEFAULT '',
`ADDRESS_3` VARCHAR(32) NULL DEFAULT '',
`CC_LOC` VARCHAR(12) NULL DEFAULT '' COMMENT 'If this client COÜLD be cold called, enter Town',
`SALUTATION` VARCHAR(22) NULL DEFAULT '',
`PHONE` VARCHAR(10) NULL DEFAULT '',
`FAX` VARCHAR(10) NULL DEFAULT '',
`EMAIL` VARCHAR(32) NULL DEFAULT '',
`LEAD_TYPE` VARCHAR(3) NULL DEFAULT '',
`OFFICE` DOUBLE NULL DEFAULT '0',
`KEY_CONTAC` VARCHAR(10) NULL DEFAULT '',
`KEY_EMPLOY` VARCHAR(20) NULL DEFAULT '',
`1ST_MTH` VARCHAR(8) NULL DEFAULT '',
`DRAFT?` VARCHAR(1) NULL DEFAULT '',
`90_DAY_REV` VARCHAR(1) NULL DEFAULT '',
`NOMAIL` VARCHAR(6) NULL DEFAULT '',
`BKPR_#` DOUBLE NULL DEFAULT '0',
`PRIORITY` DOUBLE NULL DEFAULT '0' COMMENT 'Enter Priority',
`PDEP` VARCHAR(2) NULL DEFAULT '',
`PST` VARCHAR(3) NULL DEFAULT '',
`PRTN` VARCHAR(5) NULL DEFAULT '',
`STAX` VARCHAR(5) NULL DEFAULT '',
`MEALPIN` VARCHAR(4) NULL DEFAULT '',
`NAME_CODE` VARCHAR(4) NULL DEFAULT '',
`PENSION` VARCHAR(8) NULL DEFAULT '',
`CNT` DOUBLE NULL DEFAULT '0',
`CKWRITER` VARCHAR(5) NULL DEFAULT '',
`PR_TYPE` VARCHAR(2) NULL DEFAULT '',
`EOM_RPT` VARCHAR(3) NULL DEFAULT '',
`BK_LAST_PR` VARCHAR(8) NULL DEFAULT '',
`LAST_INCR` DATETIME NULL DEFAULT NULL,
`INCR_RSN` VARCHAR(8) NULL DEFAULT '',
`REFER` DOUBLE NULL DEFAULT '0',
`BINDER` VARCHAR(7) NULL DEFAULT '',
`HISTORY_1` VARCHAR(20) NULL DEFAULT '',
`HISTORY_2` VARCHAR(20) NULL DEFAULT '',
`HISTORY_3` VARCHAR(20) NULL DEFAULT '',
`CKS_MTH` DOUBLE NULL DEFAULT '0',
`CWCKS_MTH` DOUBLE NULL DEFAULT '0',
`PRCKS_MTH` DOUBLE NULL DEFAULT '0',
`#_DPSTS` DOUBLE NULL DEFAULT '0',
`CWDEP_MTH` DOUBLE NULL DEFAULT '0',
`DPST_TYPE` VARCHAR(5) NULL DEFAULT '',
`#_SUBS` DOUBLE NULL DEFAULT '0',
`PRFREQ` VARCHAR(4) NULL DEFAULT '',
`#_EMP` DOUBLE NULL DEFAULT '0',
`#_NOTES` DOUBLE NULL DEFAULT '0',
`#M_SLSTAX` DOUBLE NULL DEFAULT '0',
`#Q_SLSTAX` DOUBLE NULL DEFAULT '0',
`#_LOC` DOUBLE NULL DEFAULT '0',
`#_BUSCC` DOUBLE NULL DEFAULT '0',
`COMPARE` VARCHAR(1) NULL DEFAULT '',
`AVG_HRS` DOUBLE NULL DEFAULT '0',
`WCPREP` VARCHAR(1) NULL DEFAULT '',
`#_ACCTS` DOUBLE NULL DEFAULT '0',
`BANK_ELEC` VARCHAR(1) NULL DEFAULT '',
`ACCRUAL` VARCHAR(1) NULL DEFAULT '',
`SURVEY` VARCHAR(1) NULL DEFAULT '',
`MONTHLY` DOUBLE NULL DEFAULT '0',
`YEFEE` DOUBLE NULL DEFAULT '0',
`YE_AMORT` DOUBLE NULL DEFAULT '0',
`BWI` DOUBLE NULL DEFAULT '0',
`BUS_PACKGE` VARCHAR(1) NULL DEFAULT '',
`ENT` VARCHAR(1) NULL DEFAULT '',
`TAX_RESP` DOUBLE NULL DEFAULT '0',
`YEAREND` DOUBLE NULL DEFAULT '0',
`BUS_FLOC` DOUBLE NULL DEFAULT '0',
`BUS_QSENT` VARCHAR(8) NULL DEFAULT '',
`BUS_QRCVD` VARCHAR(8) NULL DEFAULT '',
`BUS_ADMIN` VARCHAR(8) NULL DEFAULT '',
`BUS_13TH` VARCHAR(8) NULL DEFAULT '',
`BUS_ELFCLN` VARCHAR(8) NULL DEFAULT '',
`BUS_ELFXMT` VARCHAR(8) NULL DEFAULT '',
`BUS_ELFACK` VARCHAR(8) NULL DEFAULT '',
`BUS_MAILED` VARCHAR(8) NULL DEFAULT '',
`BUS_QCOMM` VARCHAR(20) NULL DEFAULT '',
`BUS_EXT` VARCHAR(8) NULL DEFAULT '',
`BL` VARCHAR(1) NULL DEFAULT '',
`PL` VARCHAR(1) NULL DEFAULT '',
`LTR_NOTE` VARCHAR(68) NULL DEFAULT '',
`PRIN_NAME` VARCHAR(32) NULL DEFAULT '',
`PRIN_ADR_1` VARCHAR(32) NULL DEFAULT '',
`PRIN_ADR_2` VARCHAR(32) NULL DEFAULT '',
`PRIN_ADR_3` VARCHAR(32) NULL DEFAULT '',
`LASTNAME` VARCHAR(3) NULL DEFAULT '',
`TAX_RETN` VARCHAR(1) NULL DEFAULT '',
`SCH_C` VARCHAR(1) NULL DEFAULT '',
`EMAILPRIN` VARCHAR(32) NULL DEFAULT '',
`PRIN_PHONE` VARCHAR(10) NULL DEFAULT '',
`PRIN_BDAY` VARCHAR(8) NULL DEFAULT '',
`SPOUSE_NAM` VARCHAR(12) NULL DEFAULT '',
`SPOUSE_BDA` VARCHAR(8) NULL DEFAULT '',
`IND_FEEAMT` DOUBLE NULL DEFAULT '0',
`IND_FREE` VARCHAR(1) NULL DEFAULT '',
`IND_FLOC` DOUBLE NULL DEFAULT '0',
`IND_QSENT` VARCHAR(8) NULL DEFAULT '',
`IND_QRCVD` VARCHAR(8) NULL DEFAULT '',
`IND_ADMIN` VARCHAR(8) NULL DEFAULT '',
`IND_ELFCLN` VARCHAR(8) NULL DEFAULT '',
`IND_ELFXMT` VARCHAR(8) NULL DEFAULT '',
`IND_ELFACK` VARCHAR(8) NULL DEFAULT '',
`IND_MAILED` VARCHAR(8) NULL DEFAULT '',
`IND_QCOMM` VARCHAR(20) NULL DEFAULT '',
`IND_EXT` VARCHAR(8) NULL DEFAULT '',
`W2` VARCHAR(2) NULL DEFAULT '',
`EIN` VARCHAR(10) NULL DEFAULT '',
`NAMECODE` DOUBLE NULL DEFAULT '0',
`SUPERVIS` VARCHAR(4) NULL DEFAULT '',
`GL_BKPR` VARCHAR(3) NULL DEFAULT '',
`PHISTORY_1` LONGTEXT NULL,
`PHISTORY_2` LONGTEXT NULL,
`ADDTXRTN1` VARCHAR(16) NULL DEFAULT '',
`STATEID1` VARCHAR(16) NULL DEFAULT '',
`ADDTXRTN2` VARCHAR(16) NULL DEFAULT '',
`STATEID2` VARCHAR(16) NULL DEFAULT '',
`ADDTXRTN3` VARCHAR(16) NULL DEFAULT '',
`STATEID3` VARCHAR(16) NULL DEFAULT '',
`LASTPP` VARCHAR(24) NULL DEFAULT '',
`DIRDEP` VARCHAR(2) NULL DEFAULT '',
`FEEBASIS` VARCHAR(64) NULL DEFAULT '',
`BASECOST` DOUBLE NULL DEFAULT '0',
`PERCHECK` DOUBLE NULL DEFAULT '0',
`AUTCK1` DOUBLE NULL DEFAULT '0',
`MANCK1` DOUBLE NULL DEFAULT '0',
`ADDLCHG1` DOUBLE NULL DEFAULT '0',
`COMMENT1` VARCHAR(32) NULL DEFAULT '',
`AUTCK2` DOUBLE NULL DEFAULT '0',
`MANCK2` DOUBLE NULL DEFAULT '0',
`ADDLCHG2` DOUBLE NULL DEFAULT '0',
`COMMENT2` VARCHAR(32) NULL DEFAULT '',
`AUTCK3` DOUBLE NULL DEFAULT '0',
`MANCK3` DOUBLE NULL DEFAULT '0',
`ADDLCHG3` DOUBLE NULL DEFAULT '0',
`COMMENT3` VARCHAR(32) NULL DEFAULT '',
`AUTCK4` DOUBLE NULL DEFAULT '0',
`MANCK4` DOUBLE NULL DEFAULT '0',
`ADDLCHG4` DOUBLE NULL DEFAULT '0',
`COMMENT4` VARCHAR(32) NULL DEFAULT '',
`AUTCK5` DOUBLE NULL DEFAULT '0',
`MANCK5` DOUBLE NULL DEFAULT '0',
`ADDLCHG5` DOUBLE NULL DEFAULT '0',
`COMMENT5` VARCHAR(32) NULL DEFAULT '',
`NFSCOUNT` DOUBLE NULL DEFAULT '0',
`FC_STAFF` DOUBLE NULL DEFAULT '0',
`FC_EFFDATE` VARCHAR(8) NULL DEFAULT '',
`FC_START` VARCHAR(8) NULL DEFAULT '',
`FC_COMP` VARCHAR(8) NULL DEFAULT '',
`FC_STATUS` VARCHAR(32) NULL DEFAULT '',
INDEX `ADDRESS_3` (`ADDRESS_3`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
That table has neither a unique key nor a primary key, which makes UPDATEs quite risky. However, HeidiSQL can do that, by including all column values for a WHERE clause.

If you want me to analyze that warning message, you need to post the row data which was in the table prior to that UPDATE.
Written by Paronity
2 years ago
2 posts since Tue, 11 Sep 12
I am seeing this issue on several tables accross several installations of MySQL. The way I am seeing it though, is the data is getting updated, but its running the query twice and the second one is failing (even though it shouldn't').

I also notices that if I submit the changes quickly, I don't see the error. Meaning if I change one row, I'll get the error, but then if I change the next row quickly, no error. It's only when the connection sits idle for a minute that it happens. (It only takes about a 15 second delay to hit the error between changes).

The simplest example I have is this table:
CREATE TABLE `survey` (
`id` INT(200) NOT NULL AUTO_INCREMENT,
`full_name` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',
`email` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',
`phone` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',
`address` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',
`city` VARCHAR(200) NOT NULL COLLATE 'utf8_unicode_ci',
`state` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',
`age` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci',
`clan` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`clansite` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`gender` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci',
`longhave` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`hoursplayed` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`spendgames` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`spenddlc` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`spendacc` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`gamesplay` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`gamesplay2` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`gamesplay3` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`playcomp` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`playcomp2` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`playcomp3` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`clancom` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`tourny` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`gamingsites` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`pay` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`wins` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`games3` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`strangers` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`cheaters` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`foundhack` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`cheatfree` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`goal` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
`newsletter` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
AUTO_INCREMENT=332;

(Stupid table structure, I know - I just work with what I'm given...).

All I am attempting to do is update one row. Any column triggers it.

Updating one row gives me this output:
SHOW STATUS;
SHOW VARIABLES;
USE `warzones_survey`;
UPDATE `survey` SET `clan`='f' WHERE `id`=82 LIMIT 1;
SELECT `id`, `full_name`, `email`, `phone`, `address`, `city`, `state`, `age`, `clan`, `clansite`, `gender`, `longhave`, `hoursplayed`, `spendgames`, `spenddlc`, `spendacc`, `gamesplay`, `gamesplay2`, `gamesplay3`, `playcomp`, `playcomp2`, `playcomp3`, `clancom`, `tourny`, `gamingsites`, `pay`, `wins`, `games3`, `strangers`, `cheaters`, `foundhack`, `cheatfree`, `goal`, `newsletter` FROM `stuff` WHERE `id`=82 LIMIT 1;
SHOW CREATE TABLE `stupid_database`.`stuff`;
SHOW COLLATION;
SHOW ENGINES;
UPDATE `survey` SET `clan`='f' WHERE `id`=82 LIMIT 1;
SELECT `id`, `full_name`, `email`, `phone`, `address`, `city`, `state`, `age`, `clan`, `clansite`, `gender`, `longhave`, `hoursplayed`, `spendgames`, `spenddlc`, `spendacc`, `gamesplay`, `gamesplay2`, `gamesplay3`, `playcomp`, `playcomp2`, `playcomp3`, `clancom`, `tourny`, `gamingsites`, `pay`, `wins`, `games3`, `strangers`, `cheaters`, `foundhack`, `cheatfree`, `goal`, `newsletter` FROM `stupid_database`.`stuff` LIMIT 1000;
SHOW CREATE TABLE `stupid_database`.`stuff`;

It's running it twice for some reason and the second one throws the error.
Written by Paronity
2 years ago
2 posts since Tue, 11 Sep 12
Note that I changed all the table names and database for sake of safety and missed the CREATE code. happy

That is why the table name is different....
Written by geoffschultz
2 years ago
10 posts since Thu, 03 Nov 11
I regularly get this error when (I assume) the connection between my PC and the SQL host times out.

A simple example is that I'll use the "duplicate row" command after having let the connection idle for a while and I get the "0 rows updated when should have been 1" error, but what has really happened is that it duplicates the row 2 times & I have to delete one of the copies.

-- Geoff
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
MySQL returns an "affected rows" number for any query. If MySQL e.g. silently converts some given float value to the same one which is present in a row, you get 0 affected rows in an UPDATE query where you would expect 1. That's what HeidiSQL cannot know before the query is sent to the server. However, it's just a warning, not some fatal crash, so you are informed but not blocked to proceed.
Written by geoffschultz
2 years ago
10 posts since Thu, 03 Nov 11
Should I start a separate thread related to the "0 rows updated when should have been 1" error message & timeouts or keep it here?

-- Geoff
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
No new thread required. The error popup in conjunction with a timeout is not solvable I think.
Written by geoffschultz
2 years ago
10 posts since Thu, 03 Nov 11
Is it possible to implement a "keep-alive" exchange to keep the connection open?
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
Yes, I guess that makes really sense due to the myriad of problems with disconnected SSH tunnels. Although there is a auto-reconnect logic in HeidiSQL, this causes the ssh tunnel to be gone in such cases.
Written by muzza4
2 years ago
51 posts since Mon, 04 Dec 06
Ooooohhh, yes please - keep alive.

Muzza
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
r4207 now calls mysql_ping() at least every 20 seconds. In MS SQL, we have a simple "SELECT 1" instead. The 20s are not yet configurable, and I hope this value is perfect in almost all situations.
Written by geoffschultz
2 years ago
10 posts since Thu, 03 Nov 11
Wonderful! It works just as expected.

-- Geoff
Written by muzza4
2 years ago
51 posts since Mon, 04 Dec 06
Keep alive feature is fantastic, thanks.

Muzza
Written by ansgar
2 years ago
5022 posts since Fri, 07 Apr 06
Hoping that dramatically decreases the number of disconnects per day (= annoyance factor) for people with SSH tunnels.
 

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