Autocommit setting buggy?

Answered

I think I saw someone already post that default should be off. Personally I can't think of a circumstance where I'd want autocommit on a column by column basis - it's slow for editing and it's dangerous (not discovered yet if there's a functional revert history)

But that's ok, I would just disable it.

But I opened a table for editing and despite unchecking auto commit, it was still commiting columns as I edited them.

That makes me very mistrustful - I will go back and see if I can just disable autocommit permanently.

But there needs to be an icon like a save icon that is greyed out but colored if the row has been modified in my opinion.

And then maybe autocommit (if opted in)  can autocommit if you click outside the row or move rows but otherwise you get an unsaved data warning.

I would rather explicitly commit changes every time I think.

0
29 comments

Hello Troy,
Could not reproduce the issue. If Auto Commit is disabled only when explicitly press the commit button in Table editor the data is commited in the Database. Could be that you checked the data in the same transaction session?

Please vote for related requests about better indication of not commited data: https://youtrack.jetbrains.com/issue/DBE-565

0
Avatar
Permanently deleted user

Hi Andrey

Not sure what you mean by "Could be that you checked the data in the same transaction session?"

  1. I pick a table, right click 'table editor'

  2. I haven't looked for or changed the default so when I first do this, auto commit is checked.

  3. I uncheck auto commit.

  4. I then double click a first field, change the value, then click inside a second field and the ajax spinners do their stuff and it updates the database.

  5. I verify by closing and re-opening the table, the data has changed - without me explicitly saving it.


I looked for a way to set autocommit off by default but couldn't find it.

Windows 10, 64 bit but I just checked and the DataGrip running is the x86 version.
MySQL if it makes any difference.

0

I can not reproduce it, following your scenario. 

When the data was submitted *AND* commited to the database Commit check-mark becomes inactive. In opposite case (without auto-commit) the button needs to be pushed explicitly.

>I looked for a way to set autocommit off by default but couldn't find it.

The settings is located in the General settings tab of the Data Source Properties dialog: https://www.jetbrains.com/datagrip/help/data-sources-and-drivers-dialog.html#databaseTab 

0
Avatar
Permanently deleted user

Hi Andrey

This video clearly shows it happening. Auto-commit is off but you'll see the red stop button light up and the ajax spinners showing the data being written.

http://screencast.com/t/xd1Qj077P

When all I did was change some data and move to the next field.

 

If I understand you correctly, it should NOT be able to save the data unless I explicitly click the tick button.

I next found the setting to turn auto-commit off by default in the data-sources and drivers 

So now, when I choose table editor, the auto-commit is already off.

But the behaviour is EXACTLY the same as in the above video.

 

 

0

May be the data is committed when you reopen the table editor with Auto commit setting enabled?

When you first change the cell and then closing the editor it is seen that Commit button is enabled, so the data was not committed in the database. You can check it by querying the table from another tool window.

0
Avatar
Permanently deleted user

I can prove by the method you suggest that DataGrip is committing the data even when the default setting for auto-commit is off.

The video below (wide screen) shows my other DB tool SQLYog monitoring the same table and clearly shows the issue.

http://screencast.com/t/2RXTlX5BdvgG

 

Now you must believe me :-)

It is clearly writing the data before I close the editor or re-open the editor.

 

0
Avatar
Permanently deleted user

sorry, here is a better video showing more of the screen, the previous one I didn't spot the dialogs were outside the video

http://screencast.com/t/Nfcekjh5

0

Thanks:) it's not that I do not believe you, it's just I can not reproduce the issue)

May be there is some issue with synchronization.. (I see that DB synchronization process is in progress in Database tool window).

Just to try to troubleshoot, does it make a difference if enable Use legacy introspector in Datasource Schemas tab settings? Thanks.

0
Avatar
Permanently deleted user

I know, I was only joking. I'm a s/w developer myself so I'm only too familiar with these kinds of issues of not being able to reproduce.

I switched to legacy introspector, didn't make a difference, the little spinner just keeps spinning.

I also turned off auto-sync in case it was that,no difference.

If you want to screen share via skype, I'm troy.wray

 

0

Thank you very much for the screen captures. Still struggling trying to reproduce this problem. Is it possible for you to check with another database (database version)? Do you have different behaviorin SQLYog?

From the screencast it is seen though that commit button is active after the data has been changed in table editor, which means that DataGrip did not perform commit action for the current transaction.

0
Avatar
Permanently deleted user

Is it possible to check another database/version - you mean something other than mySQL - no it isn't. I Only have access to mySQL databases. I can try a different database that might be a different version of mySQL.

Your second point is wrong, I don't know if the video made it clear enough, I think the frame rate might have been too low...(e.g. you don't see me actually click the refresh button in DataGrip after I change 8 to 7 in sqlYog)

I changed the data to 88 in DataGrip without doing anything that should have committed the data and proved using sqlYog that the underlying db had changed.

So in my eyes, DataGrip had very definitely done the commit action - it's also clear from the ajax spinners while it's doing it.

Do I have different behaviour in sqlYog - of course - it doesn't normally commit until I explicitly save or I move to a different row. That latter behaviour isn't really desired in my opinion but I'm used to it. I would rather it give a confirmation dialog if I move to a different row and it's going to change anything.

But I often change, deliberately or accidentally a single value and for that to auto commit is clearly not wanted by anyone, and I realise it shouldn't happen - but it does.

And I see no way to revert to the previous value or worse, I might not even realise I actually changed it. 

Sometimes I might change a value, get distracted and then I can always cancel changes. 

 

0
Avatar
Permanently deleted user

I will post this bug here but it's an entirely different one - though the story behind finding it is interesting (to us nerds :-D )

I tried a different database, This time the table was 

timestamp

varchar 255

int

float

I had a row that had 51 in the int field and I tried to change it to 52 to reproduce the original issue in this thread...

It would not change the value at all - it just reverted to 51

I tried changing the value in sqlyog to make sure it wasn't e.g. a read only column/table in some way and sure enough, sqlyog wouldn't let me change the column value either!!!

I then realised (because in sqlyog you can preview the sql that will be executed for the update commit) that the query was to be:

update `igen24c_igen24rep`.`trafficbonus_test_6` set `teID` = '52' where `timestamp` = '2012-02-07 13:25:22' and `referrer` = 'http://trafficbonus.com/surf/bonus/f/top/time/10' and `teID` = '51' and `execTime` = '0.00177002'

but because the execTime is a float, it doesn't match when that query is executed (I tried running the query manually too)

And it appears DataGrip must use the same strategy and is subject to the same issue.

Unfortunately, I lost the numeric data because I accidentally changed the field to decimal 10 instead of decimal 5,8 but I manually entered some data and verified that both programs now worked (for this problem)

However, DataGrip showed the same issue on this table that it commits changes as each column changes, not when I ask it to. 

 

0

Thank you very much for the details.

What is the result of the 

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

query?

If the values are set to READ UNCOMMITTED then this would explain the situation.

Otherwise, could I ask you to please do one more thing to try to profile this issue?

Could you please

1. Add `?profileSQL=true` parameter in the JDBC URL,

2. Reconnect the database connection (with auto commit setting off)

3. Open the table editor for the change

4. Note the time (it will help for reading the logs a lot), change the table cell and make sure that the value has changed even auto commit was off

5. Attach produced idea.log: https://intellij-support.jetbrains.com/hc/en-us/articles/207241085 

Thank you for collaboration!

1
Avatar
Permanently deleted user
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

REPEATABLE-READ, REPEATABLE-READ

In both cases I can confirm through an independent tool that the data was changed.

To edit, I double clicked the value, typed the new value and WITHOUT pressing enter, clicked once in the next column

I've seen no way to attach files here. It's 710KB

The timestamp would have to be very accurate for you to find the lines lol but if you search for -123, you'll find it easily.

I made a change from 77 to -123 and then from -123 to 88

Let me know how to send you the file.





1

Thank you Troy for the log file. I see there are no commits of the transaction. There are only `SET autocommit=0` statements and `rollback` statement at the end of the session, so I do not see how it would be possible that DataGrip would commit the transaction.

What is the output from 

show table status where Name like 'customdl_main';

query? Note that if there is MyISAM engine used - it is not a transactional: http://stackoverflow.com/a/8036049/2000323 .

0
Avatar
Permanently deleted user

Hi Andrey

It is a myISAM table. 

The fact that it doesn't support transactions is irrelevant to me. Surely DataGrip should abstract the process so it behaves the same for all db engine types? If not, this needs to be made really clear.

In my opinion (because I'm not the designer), DataGrip should not update the row until I deliberately commit. Under any circumstances. (with auto commit off)

DataGrip should hold and build the update query and only execute it when I deliberately commit. 

At least that's how sqlYog works and I won't be using DataGrip if the JB response is that the db will update automatically every time I alter a field - us developers use these tools on live databases!

you say:

"so I do not see how it would be possible that DataGrip would commit the transaction" 

Well I have proven to you that it does, maybe because now you know it's a myIsam table, you understand and know why. And it seems like you think this is the intended and expected behaviour because it's myISAM.

 

And of course, a rollback has no meaning to this table, being ISAM

[edit]

I'm sure this isn't true but I read into your comments that DataGrip makes all the changes immediately but then does a rollback if the session is closed without committing - that is dangerous and imo, stupid.

Actually, if it's a transactional table then the underlying database won't be updated unless there is actually a commit - so the above 'stupid' statement is probably wrong when talking transactional tables.

[/edit]

And not the behaviour a user would expect.

Would be interesting to see how many other users agree.

As a final thought, if JB don't agree with this and leave the behaviour the way it is, if I unintentionally change a field and then have no way to rollback - because I maybe don't remember what the field was before I changed it then this otherwise brilliant tool is just too dangerous to use on a live database.

I'm not trying to shoot the messenger, I appreciate all your help :-)

 

 

0

>In my opinion (because I'm not the designer), DataGrip should not update the row until I deliberately commit. Under any circumstances. (with auto commit off)

Seems you don't get the idea of how db engine without transaction support works. You can commit or rollback statements, but the change is always saved (written into transaction journal) even without explicit commit.

I do not see how it is possible in this situation (when the database does not support transactions). If you invoke the same update action from any other sql console for the same table - you will get the same result, won't you?

>DataGrip should hold and build the update query and only execute it when I deliberately commit. 

It does not. It issues sql update statement without the commit, it is seen from the logs.

>if I unintentionally change a field and then have no way to rollback - because I maybe don't remember what the field was before I changed it then this otherwise brilliant tool is just too dangerous to use on a live database.

I agree, that this is dangerous to use table editor on such a database in production.

0
Avatar
Permanently deleted user

> It does not. It issues sql update statement without the commit, it is seen from the logs.

That may be what the logs say. But it does update the live data immediately.

I have proven this several times on the videos I send you.By monitoring the db with an independent tool.

There is no question, that I edit one field and the database is updated immediately I move from that field to another.

It seems right now:

(a) we agree that behaviour is not right

(b) you don't believe this happens because the logs say it doesn't commit

Am I right?

0
Avatar
Permanently deleted user

>Seems you don't get the idea of how db engine without transaction support works. You can commit or rollback statements, but the change is always saved (written into transaction journal) even without explicit commit.

Did you mean with transaction support - I agree, I am less familiar and can see that all the changes are written into the transaction journal. But they won't update live data without a commit.

 

>I do not see how it is possible in this situation (when the database does not support transactions). If you invoke the same update action from any other sql console for the same table - you will get the same result, won't you?

Yes, IF I invoke the update. The difference is that in other sql admin programs, I must explicitly invoke a 'save' mechanism before it issues the update action! The update action isn't initiated after every field change.

No, I don't get the same behaviour - at least not in sqlYog just by editing one field and tabbing to the next field. 

 

What sqlYog does is this: there are a series of edit boxes for each field with an original value in. I can edit these as much as I like with nothing happening to the live database. 

Only once I press save, then sqlYog issues one update statement setting each of the fields to the edit box value (maybe it skips those that haven't changed in the update statement but makes no difference)

And that behaviour is the same for transactional or non-transactional databases - and that's how I think any program should be*

 

I changed the table to innoDB and observed the behaviour of DG and can see that with auto-commit off, it doesn't update the row until I commit. If auto-commit is on, it changes every field immediately (which I think is still wrong even with auto-commit on)

* phpMyAdmin updates the same way DG does - but it's not exactly the gold standard of sql interfaces.

 

Thoughts:

This behaviour is important. It's crucial in my opinion that under no circumstances, especially with auto-commit off, should the changing of one field immediately update the live database.

But only you and I have discussed it, you should poll more users.

DG has a potential advantage over sqlYog behaviour. sqlYog updates an entire row at once when focus moves from that row - without confirmation (for transactional and non-transactional tables)

in non-auto-commit mode, DG allows multiple rows and fields to be edited without anything being committed until an explicit commit is performed - this is fantastic.

But, two things need to change for it to be intuitive:

  1. if there is uncommitted data in the view, dirty data should be indicated. If I get distracted having edited some data and I have no way to know after some time what data has been changed and will be committed, that's dangerous and I would have to abort the commit and start again.
  2. a non-transactional db should ideally emulate that same behaviour but it's achieved perhaps by implementing a pseudo transaction buffer. I shouldn't have to know, at the time I'm editing, whether I'm editing a transactional or non-transactional table.

re #2, I would be content if DG worked like sqlYog and needed to commit the change when the row lost focus (ideally with a 'this row is dirty, save or abort' dialog - which sqlYog doesn't have, it just commits)

However, if #2 could be achieved and made to be transparent of the transaction-ability of the table, that would make it a clear winner over sqlYog.

Final thought:

I don't think it's acceptable for JB to say well this is a non-transaction table, therefore we have to issue every update immediately and you can't roll back.

sqlYog proves (not that it needs proof, just the will to do it) that you can buffer the changes for a row even on a non-transactional table so that the behaviour is acceptable with a safety guard between edits in the view and the data becoming live.

I hope this reply hasn't become too long to be useful :-)

 

 

 

 

 

0

>The difference is that in other sql admin programs, I must explicitly invoke a 'save' mechanism before it issues the update action! The update action isn't initiated after every field change.

Thank you for explaining this. Indeed, DataGrip executes DB update the moment you change the cell in the table editor, which is not what you expect. But currenty such behavir is by design. Created the issue about it, please vote and follow it in YouTrack: https://youtrack.jetbrains.com/issue/DBE-2291 . Thank you again for your feedback.

0

Hi! This features is implemented in 2016.3

0
Avatar
Permanently deleted user

Yeah well, this was fixed so well that now, I can't commit changes no matter what I do. Auto commit on/off it won't matter. This thing does not write to the db anymore.

0

Andrei, press Ctrl+Enter — it will submit changes.

And please, update to 2016.3.1 where we removed Auto-commit option to settings and added a button for Submit.

Submit != Auto-commit

0
Avatar
Permanently deleted user

Ctrl+Enter adds a new line in the field. It doesn't submit. Ctrl+Enter outside of the field adds a new empty record to the table. Auto commit is checked. I don't remember this behavior with previous versions.  I am on 2016.3.1. It shouldn't matter but I am on mac os and the DB is a MySQL amazon aws. RDS.

I must be doing something wrong, I just can't figure out what. Must be one of those things...

0

Oh, I am sorry — on Mac Cmd+Enter is Submit

0
Avatar
Permanently deleted user

Yes that worked. With the caveat that when Cmd+Enter, you have to click outside of the field first (otherwise it just adds a new line to the field you're editing). Either way, it works so that's fine. Thanks.

0

Hey I have very similar issue what Troy Wray:
Issue: double clicked on the query result cell by mistake and moved cursor out side of the cell, it was saved automatically, didn't ask for              confirmation, I didn't even noticed which was caused a huge production issue.
 Am using the latest version(i.e: DataGrip 2021.1.3)
          
Is it possible to disable editing the cell? I tried disabling the auto commit(set transaction mode to manual), but still no use.

0

Mahendra Rajesh,

>it was saved automatically, didn't ask for confirmation
Changes are saved but not submitted until you explicitly enable such behavior in settings:

 

To work with production it's better to use read-only mode

0

Please sign in to leave a comment.