0 rows updated when should have been 1

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

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

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.

Duh...Thanks for pointing this out to me!

-- Geoff

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?

You will need to post the exact SQL query, and the CREATE TABLE query here.

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;

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.

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.

Note that I changed all the table names and database for sake of safety and missed the CREATE code.

That is why the table name is different....

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

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.

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

No new thread required. The error popup in conjunction with a timeout is not solvable I think.

Is it possible to implement a "keep-alive" exchange to keep the connection open?

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.

Ooooohhh, yes please - keep alive.

Muzza

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.

Wonderful! It works just as expected.

-- Geoff

Keep alive feature is fantastic, thanks.

Muzza

Hoping that dramatically decreases the number of disconnects per day (= annoyance factor) for people with SSH tunnels.

Hi,

I have this message when updating row in updatable views (MERGE CASCADED). If a table has a trigger to save an updated timestamp, it is not possible to modify the same record twice without refreshing it before the second modification. HeidiSQL should automatically reload the modified record or detect the primary key used by the view to update using the primary key.

Not really a bug but a bit disturbing.

Thanks,