Unexpected update count received in statement 1

已回答

Hello. 

When i'm trying to change 1 column in DataGrip Editor:

Unexpected update count received in statement 1 (Actual: 0, Expected: 1). Changes will be rolled back. 

 

Help!

6

Hi,
Could you provide sample table DDL and some data?
Also, it'll be useful to get detailed steps to reproduce your problem and IDE logs. 
Thank you.

 

0
Avatar
Permanently deleted user

I have experienced  the same thing on multiple occasions.  This happens when I try to delete a row using the datagrip editor...when there are true duplicates.  Thus, probably a MySQL protection rather than issue with DataGrip?

 

Steps to reproduce error:

1. Have a table with some duplicates rows

2. Bring up a pair of the duplicates using a select * statement

3. Select one row and do Ctrl  + Y (delete) and then Ctrl + Enter.

4. You will get the following error:

Error:

Unexpected update count received (Actual: 2, Expected: 1). Changes will be rolled back. SQL:

 

Current workaround: 

1. Create a field that is truly unique (e.g. 1, 2, 3,... )

2. Then the  Ctrl  + Y (delete) and  Ctrl + Enter will work

 

0
Avatar
Permanently deleted user

Hi. Same here.

 

Steps to reproduce ...

CREATE TABLE table_name (column_1 FLOAT);
INSERT INTO table_name (column_1) VALUES (0.1);

When I update 0.1 to 0.2 in DataGrip Editor, the error occured.

0

Could we finally get a fix for this problem? I get this at least once a day.

This report is already up for over a year and nothing has changed....

5
Avatar
Permanently deleted user

Absolutely same problem. Kind a annoying :(

1

@Aweo71, @Denis Butcher

Could you provide sample DDL & DML to reproduce the issue? It looks like you need to add truly unique column for proper modifications

0

I'm facing this issue using large bytea columns. If the data in the bytea column is relatively large, then trying to delete or edit the column raises this error. In more scenario I hold PDF data in the database using bytea types. If the data in the column is around 3KB then it's fine, but when it exceeds around 600KB then it gets the error.

I see that when trying to delete the relavent row, DataGrip uses each value in the column to define which row will be deleted, even though I have a unique key on that table.

So the following table:

 

```

create table test_pdf
(
id serial not null,
date_inserted timestamp with time zone default now() not null,
user_id integer,
mime text default 'application/pdf'::text not null,
pdf_bytes bytea not null,
filename text
);

create unique index test_pdf_id_uindex
on test_pdf (id);

 

```

 

When inserting data to `pdf_bytes` around 3KB and then deleting the row, it is fine. But try inserting more than 500-600KB, then remove the row from DataGrip, you get the following error:

 

```

Unexpected update count received (Actual: 0, Expected: 1). All changes will be rolled back.

```

 

You can truncate the table, or delete the row with a query from the console using the `id`.

Hope this helps defining the bug.

 

PS: First time posting here, don't know how to highlight the code. Sorry about that.

0
Avatar
Permanently deleted user

To anyone that experiences this problem.  I answered how to resolve this already but it has not been accepted as the solution for some reason.   To fix:  Simply add this line to ensure your table has an autoincrementing unique key: 

 

ALTER TABLE yourtablename ADD id int(11) PRIMARY KEY AUTO_INCREMENT;
0

I have been having this error when simply trying to delete rows.  It is very annoying and interrupting my flow.

@... My primary key is a non-sequential 16-byte binary field.

0

Steven I had the same issue today, I solved it by writing a delete statement in the console, instead of using the UI to delete the row. Not sure how to make the issue go away for UI usage, but hope that helps

0
Avatar
Permanently deleted user

To Power User and Jarret... but did you follow my advice and add the following and still experience the problem?   My guess it you haven't followed that exact advice which is why you are having problems.

ALTER TABLE yourtablename ADD id int(11) PRIMARY KEY AUTO_INCREMENT;
-1

I have this problem as well.  In my case the problem was caused because I read in a database that was originally created in "DB Browser for Sqlite."  Datagrip appears to be incompatible with the field type strings that DB browser uses.  For instance DBBrowser calls an integer "int" (or at least mine does) and Datagrip imports "int" only partially recognizes that as an integer.  When I went into Datagrip and relabeled all of my datatypes to the "Datagrip version" in the GUI all was well.  in other words I changed "int" to "integer" for my primary key in datagrip.  Autoindexing now works as well.

In my case this bug would appear to be created by Datagrip (I am using Clion 2020 but same thing) not setting the field types correctly in all cases.  The answer for me was resetting all the field types to one that Datagrip completely understands... which is what Raisenenal's answer probably does, at least in some cases.

This may not work for anyone else but it appears to be one bug that can cause this error.

   

0

I have a case that may be the same, but the situation is odd, it's a PostgreSQL database.  I have a view that is 1:1 with a base table that has a SERIAL primary key.  The view adds in a lookup for one field, and calculates another additional field.

I defined a plpgsql INSTEAD OF trigger on the view, and and the INSTEAD OF cases for UPDATE and DELETE use this primary key to update or delete in the base table. It works correctly when I update one row and commit in the DataGrip data editor.

However, whenever I update multiple rows, it gives the same message:

`Unexpected update count received (Actual: 0, Expected: 1). All changes will be rolled back.`

I can 'un-update' the rows manually until only one is updated, and I can save that one.

The Data Editors are not set to update automatically; I have to choose the `Submit` button.

0

I discovered this issue may be caused by NOCOUNT being turned on (likely at the server or instance level).

To see instance settings run:

EXEC sp_configure

and look for "user options" if this is 512 (or the 10th bit is enabled)

alternatively, run the following to see if it is set:

DECLARE @options INT SELECT @options = @@OPTIONS
PRINT @options
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'

My guess is DataGrip is expecting the "1 row affected" response, which isn't returned if NOCOUNT is on, so DG thinks something didn't work right and rolls back the query.

0

My problem was due to the fact that I have interrupted (network problems) table update. So DR kept it in pending status, rollback didn't help. 

I've had to dump table and recreate it, import data back and than everything worked.

Ugly solution, but it worked.

0

请先登录再写评论。