Question about 0 rows updated when there should have been 1

Sometimes I have to refresh before I can change the same line again (0 rows updated when there should have been 1).

Sometimes it allows me to do whatever I want.

What do I have to do to get the database that gives me the error to stop the warning. Do I need to change the database type somehow or what?

Thank you.

The MySQL server silently does some data conversions when you insert or update a row. For example when you provide a value of "128" for a TINYINT column which already has the maximum numeric value of 127:

UPDATE `relation` SET `product`=128 WHERE id=3;
/* Affected rows: 0 */

So, the error "0 rows updated when there should have been 1" is the result of a INSERT or UPDATE which had zero affected rows.

If you after such a situation fire a SHOW WARNINGS, you will see such a warning:
"Out of range value for column 'product' at row 1"

Sorry, I am still confused. I have a varchar column and if I change it (or enter data for the first time) after I click out of the box to another row and the change is executed, all is good.

When I go back to this column after the execution and add something else, I get the "0 rows updated when there should have been 1" . I cannot make the change until I refresh the DB and go back to the row again.

I would guess that it is just the way it is, except I have a different database where I can go back as many times as I want and edit a varchar with no problem. I am not exceeding any character limits.

This is not a new thing, I just finally decided to ask.

The only difference I see is there is not a primary key on the DB that causes the problem.

Please post the CREATE TABLE and one or two UPDATE queries here.

I made a miniature version of the issue (way less columns). I put a 1 for the number.
Then after refreshing I used this filter
`number` LIKE '%1%'
I put test in the short description. Then clicked out of the row to execute.
I then tried to go back and add test 2 to the word test.
This is when it says (0 rows updated when there should have been 1)

It only happens when there is a filter in use (just figured that out).



CREATE CODE

CREATE TABLE `sample issue` (
`number` VARCHAR(255) NOT NULL COMMENT 'number',
`short_description` VARCHAR(255) NOT NULL COMMENT 'short_description',
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'timestamp'
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;



QUERIE OUTPUT


SELECT * FROM `misc notes`.`sample issue` WHERE `number` LIKE '%1%' LIMIT 100000;
SHOW CREATE TABLE `misc notes`.`sample issue`;
SHOW TABLE STATUS LIKE 'sample issue';
UPDATE `misc notes`.`sample issue` SET `short_description`='test' WHERE `number`='1' AND `short_description`='' AND `timestamp`='2013-11-27 00:10:11' LIMIT 1;
SELECT `number`, `short_description`, `timestamp` FROM `misc notes`.`sample issue` WHERE `number`='1' AND `short_description`='test' AND `timestamp`='2013-11-27 00:10:11' LIMIT 1;
SHOW TABLE STATUS LIKE 'sample issue';
UPDATE `misc notes`.`sample issue` SET `short_description`='test test 2' WHERE `number`='1' AND `short_description`='test' AND `timestamp`='2013-11-27 00:10:11' LIMIT 1;
SHOW TABLE STATUS LIKE 'sample issue';

You did not send any INSERTs. My "sample issue" is empty and I cannot update anything.

I did not use inserts, I clicked in the cell and made the edit???

And how shall i reproduce it without a single row?

Sorry, I created the row manually, as stated in my last post. Here is an insert that I got by exporting the DB.

INSERT INTO `sample issue` (`number`, `short_description`, `timestamp`) VALUES
('1', '', '2013-11-27 00:10:44');

Guess I could have exported the grid rows.

Ah got it. The timestamp column is defined with a default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. So, after you entered and stored the "test", the timestamp of that row was updated by the server, but HeidiSQL still had the old time. Then you updated "test" to "test 2" or whatever, and HeidiSQL sent a WHERE clause with the old timestamp, which was no longer present.

Phew, quite tricky to reproduce and understand. Two things must come together here:
* Timestamp column with ON UPDATE CURRENT_TIMESTAMP
* No primary or unique key on the table, so HeidiSQL is forced to use all columns in an UPDATE and/or DELETE query

Very cool, I will add a primary, I sort of suspected that might have had something to do with it. It had not occurred to me that no primary id forced Heidi to use all columns in an UPDATE and/or DELETE query.

I did not see a specific need for it before. I tried un checking on_update, and the problem was eliminated. It kind of interferes with the usefulness of the timestamp, so I will use a primary key in the future.

I am sorry for the trouble and I would like to thank you for your help.