False Warning On Update

[expired user #8565]'s profile image [expired user #8565] posted 9 years ago in General Permalink
I am very consistently getting an error that says "0 rows updated when it should've been 1" when I edit the table rows under the "data" tab.

Does anyone else see this and/or know of a way to fix it?
ansgar's profile image ansgar posted 9 years ago Permalink
This is a warning, not an error. I have to be picky about that, a I had difficult discussions about that thing before.

The warning says exactly what it means: HeidiSQL does send an UPDATE or DELETE to the server (depending on what you clicked), and the server returns "0 rows affected".

You get affected rows, and if your update contains values which are already present in the row, affected rows is 0, so you know the number of physically changed rows. That's good, isn't it?
[expired user #8565]'s profile image [expired user #8565] posted 9 years ago Permalink
That makes sense, but it doesn't seem to be the reality of the situation.

If I change a number from 1 to 2, I get "0 rows updated"... but the row really _IS_ being updated. I am completely certain that the value is being changed and it is being reflected in the database.

Is there a way to turn this off? It literally happens every single time I do an update through the data tab.
ansgar's profile image ansgar posted 9 years ago Permalink
There must be some reason for the server to return 0 affected rows. Can you send me a small dump (data + structure) of the table, or at least the structure?
[expired user #8565]'s profile image [expired user #8565] posted 9 years ago Permalink
Ok, looking at things a little more... it looks like the query is actually being run twice -- so the 2nd time, it is correct that no rows have been updated.

SHOW STATUS;
SHOW VARIABLES;
USE `db_c8f99`;
UPDATE `db_c8f99`.`test` SET `field1`='record2X' WHERE  `id`=6;
SELECT `id`, `field1` FROM `db_c8f99`.`test` WHERE  `id`=6;
SHOW CREATE TABLE `db_c8f99`.`test`;
SHOW COLLATION;
SHOW ENGINES;
UPDATE `db_c8f99`.`test` SET `field1`='record2X' WHERE  `id`=6;
SELECT * FROM `db_c8f99`.`test` LIMIT 1000;
SHOW CREATE TABLE `db_c8f99`.`test`;


Another interesting thing to note is that it looks like INSERT statements are being run twice, too.

SHOW STATUS;
SHOW VARIABLES;
USE `db_c8f99`;
INSERT INTO `db_c8f99`.`test` (`field1`) VALUES ('record2');
SELECT LAST_INSERT_ID();
SELECT `id`, `field1` FROM `db_c8f99`.`test` WHERE  `id`=6;
SHOW CREATE TABLE `db_c8f99`.`test`;
SHOW COLLATION;
SHOW ENGINES;
INSERT INTO `db_c8f99`.`test` (`field1`) VALUES ('record2');
SELECT `id`, `field1` FROM `db_c8f99`.`test` WHERE  `id`=6;
SELECT * FROM `db_c8f99`.`test` LIMIT 1000;
SHOW CREATE TABLE `db_c8f99`.`test`;


Maybe it's my workflow? I am double-clicking the field in the grid under the "data" tab, typing my changes, hitting enter, and then clicking the green "post" check mark.

I can provide you with credentials to my server if you'd like to play around a bit and see if anything makes sense.

I'm using HeidiSQL 9.1.0.4867.
ansgar's profile image ansgar posted 9 years ago Permalink
Thanks for finding out the multiple insertion thing. I only have no clue why that's happening. I guess it's a good idea to try that on your server as I am not able to reproduce that here on my servers. You could mail me to the address listed on the imprint page. Thanks!
[expired user #8565]'s profile image [expired user #8565] posted 9 years ago Permalink
E-mail has been sent. Thanks!
ansgar's profile image ansgar posted 9 years ago Permalink
Thanks. I am just on your server in the db_c8f99.test table. And I get lots of disconnects between most queries. Running a small SELECT 1 in a query tab runs smoothly only when I do it in short succession. As soon as I leave 2 seconds or more between these, I get disconnected, then HeidiSQL automatically reconnects and runs the query fine. The same happens in the data grid.

So, what's the cause of these disconnects?
I just looked into the server variables:
* max_connections is set to 100, should be enough
* extra_max_connections is set to 1, which is the server default. This makes me scratch my head. I am not really sure what this does, even after reading the docs about extra_max_connections and extra_port.
* wait_timeout is 28800 (seconds), should be ok
* interactive_timeout is also at 28800

So the only weird thing I see is that extra_max_connections, which I have never seen before.
[expired user #8565]'s profile image [expired user #8565] posted 9 years ago Permalink
SOLVED.

The mysterious disconnections were to blame. This was a setting on my end that was invisible to you since the server you were actually hitting was a load balancer and there was a 5 second timeout being enforced there.

So... after changing that, Heidi is working as one would expect now. However, the initial problem seems to suggest that Heidi doesn't handle UPDATEs and INSERTs very well when things happen very close to a timeout/disconnection... it looks like this causes the SQL statement to be run twice. Likely an edge case, but something that can still happen.

Thanks for looking into things -- it is much appreciated that a server configuration issue was solved for me through all of this.

Cheers!
angel's profile image angel posted 5 years ago Permalink

I've the same problem.

HeidiSQL 9.5.0.5196, Windows 10, All kind of databases.

In the UI, when I insert or update a row, always performs the task 2 times.

ansgar's profile image ansgar posted 5 years ago Permalink

This thread is 4 years old. Please post a new thread, or if it's an issue then please in the bugtracker. And please use the latest build of HeidiSQL before reporting issues.

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