Cannot modify database - No Such Savepoint

[chrisjlocke]

Details for the issue

Using the latest nightly. Created a database fine. However, if I edit a table and click 'Write Changes', an error is displayed. Interestingly, the changes are saved. Closing the database and re-opening it contains the modifications made.

Useful extra information

I'm opening this issue because:

• [ ] DB4S is crashing
• [ ] DB4S has a bug
• [ ] DB4S needs a feature
• [x] DB4S has another problem

  I'm using DB4S on:

• [x] Windows: ( version: _10 (64bit)__ )
• [ ] Linux: ( distro: ___ )
• [ ] Mac OS: ( version: ___ )
• [ ] Other: ___

  I'm using DB4S version:

• [ ] 3.9.1
• [ ] 3.9.0
• [x] Other: _3.9.99 (64bit)__

  I have also:

• [x] Tried out the nightly version: https://github.com/sqlitebrowser/sqlitebrowser#windows
• [ ] Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

[justinclift]

Interesting. Sounds like a side effect of https://github.com/sqlitebrowser/sqlitebrowser/commit/e76e5b3241af5e79474ae32fda393da921696d96.

@MKleusberg Probably of interest to you? :smile:

[chrisjlocke]

Just an additional note too that clicking 'Close Database' and selecting 'Yes' to the 'Do you want to save changes?' doesn't display an error. I guess that means that way of closing a database either isn't saving the savepoints like the 'Write Changes' button, or if it is, its not displaying any errors...

[NicoDupont]

Hi everyone, I exactly have the same issue on my installation (Ubuntu 16.04 64b) Version 3.9.99 Version de Qt : 5.5.1 Version de SQLite : 3.11.0

[chrisjlocke]

V3.9.99 means it's a nightly build. What's the date of this version (file modified date) Have you tried one of the latest nightlies?

[NicoDupont]

It seems I'm on the testing ppa "ubuntu xenial" where I can see the file modified date ? Thank you

[justinclift]

Hmmm, looking at the testing ppa listing:

    https://launchpad.net/~linuxgndu/+archive/ubuntu/sqlitebrowser

It seems to be saying the last build was 2016-08-24.

@NicoDupont You may need to attempt building from source yourself. It should go fine, but if it doesn't then cut-n-paste any error message here and we'll probably be able to help. :smile:

[justinclift]

Oops, no. I was looking at the "stable" one. The testing one says it's last build was an hour ago:

    https://launchpad.net/~linuxgndu/+archive/ubuntu/sqlitebrowser-testing

This might still be a bug we need to look into then.

[justinclift]

@NicoDupont Does the error occur for often / all the time, or just sometimes, or ? If it happens often, is there a way to reliably trigger it?

[NicoDupont]

The error occur all the time.

The error appear if I click "Write changes" on the interface or "Write changes" in file menu. If I close the database in the file menu. I click yes to save changes made and it's ok. Database is ok

Database is also saved if I just click on "Write changes" and close the database without saving it. I got the error but the database is saved..

I don't know if I answer to your question ?

Error while saving the database file. This means that not all changes to the database were saved. You need to resolve the following error first.

no such savepoint: RESTOREPOINT (RELEASE RESTOREPOINT;)

[justinclift]

@NicoDupont Interesting. That's an error message we - in theory - fixed a while ago.

I'll take some time this week to go through our Linux compiling instructions and update them, so you can then try compiling things yourself. It should be pretty simple, once I've done the update.

[MKleusberg]

I'll close this issue because it has been solved by @prutz1311 and @innermous a while ago. The Ubuntu problem reported by @NicoDupont seems to be fixed, too. See issue #1008.

If it turns out to be still an issue, feel free to reopen the issue :smiley:

[mgrojo]

The reason to reopen this it's in #1003, but what I read there is that it was happening with 3.10.0 and 3.10.1.

Couldn't be that the issue was fixed after that release and consequently can be closed for 3.11?

[nwhitmont]

@MKleusberg

I just ran into this issue in version 3.11.2 (current as of this writing)

Error: no such savepoint: RESTOREPOINT (RELEASE "RESTOREPOINT";)

I was able to write/save changes just the other day, so not sure what's going on here.

[justinclift]

Uh Oh. Sounds like there's more to do for this.

[chrisjlocke]

I just ran into this issue in version 3.11.2

Do you the steps to reproduce it at all? Do you know what you did to trigger it?

[lewtwo]

I am having the same/similar problem but it is specific to using a db file that is on a linux samba share. reference: https://github.com/sqlitebrowser/sqlitebrowser/issues/1933

[chrisjlocke]

So you're now able to edit it, but its now failing to save? Thats a different issue to #1933.

[lewtwo]

Well it is giving me the same error, but ONLY on the samba mounted file system. In some other cases this error is attributed to file locking but "fusser" is not reporting any active locks on either machine.

[chrisjlocke]

No problem - just identifying. Thanks for clarifying - all helps identify where the issue lies.

[torian257x]

Version 3.11.99 (Feb 19 2019)

Built for x86_64-little_endian-lp64, running on x86_64

Qt Version 5.9.5

SQLCipher Version 3.4.1 (based on SQLite 3.15.2)

[chrisjlocke]

Do you have steps to reproduce this?

[garciaarthur]

Do you have steps to reproduce this? …

In my case I can trigger the message by doing a simple update and then a commit, it happens in any database i've tried with.

Version 3.11.2 Built for x86_64-little_endian-llp64, running on x86_64 Qt Version 5.11.3 SQLite Version 3.27.2

Open a fresh instance of SQLiteBrowser, "Open Database" and select any .DB3 file. On "Execute SQL" tab do the following: 1) Write command [UPDATE table SET column = value WHERE clause] 2) Click button "Execute all/selected SQL" 3) The result box shows "Result: query executed successfully. Took 0ms, 1 rows affected"

Beyond this point SQLiteBrowser behaves differently:

PATH 1) If I click the button "Write Changes", everything works (update rows, saves db3, etc) and no error message at all;

PATH 2) If I write the command [commit] and click "Execute all/selected SQL" the result box shows "Result: query executed successfully. Took 0ms At line 3: commit"and then the button "Write Changes" become enabled and doesn't get disabled anymore. If I click it, I receive the error message stated above. The only way to solve this is by closing the application.

[scottfurry]

This sounds like confused GUI logic. Write state of database is not being queried and is otherwise assumed to be in a certain condition. When a user issues a commit statement via SQL commend, which alters transaction state of the database, that upsets the "assumed" database state in the code. Faulty logic/database state then causes cascading problems (confused button enable states).

It may not be easy/practical to constantly check the database condition during GUI event loops(that's how GUI libraries roll - there's a background event loop happening so quick Boolean conditions are needed to enable/disable GUI controls). However, IMHO, it sounds like some extra check is needed in this instance.

[chrisjlocke]

If I write the command [commit]

Just to unravel this a bit more, each time you execute something in the execute SQL tab, a 'begin' and 'commit' is tagged around the statements, so by providing your own, you're committing manually and then the program is trying to perform its own commit. As my wife would say, "Stop it!".
No need for begins and commits - they're assumed, implied, applied, slapped on, and used a-plenty. "Aah, no-one told me!" Nope, you're right. Enter the wiki, stage left... "Aah, I never knew that existed!" Not many people do...

[garciaarthur]

No need for begins and commits - they're assumed, implied, applied, slapped on, and used a-plenty.

My bad... I've been working with Oracle for a long time now and the [commit] comes without a second thought

Nope, you're right. Enter the wiki, stage left...

My bad again... Didn't took the time to read it. I was just using it (mistakenly) the same way I use other DBMS. When I received the error message, I took the time to find this thread and post my steps to reproduce the error, thinking it was going to help. Won't make the same mistake twice.

[torian257x]

for me it says FOREIGN KEY constraint

but it doesnt tell me what or why, or how to skip that constraint

it was in fact some entry in some other table blocking the delete. Would be nice to skip foreign key checks, this is sqlite and we use it as a testing DB. Nothing worse than unable to test because some temporary test data wasnt perfectly clean deleted

[chrisjlocke]

Won't make the same mistake twice

Not a problem. Taking the time to find this thread and post your steps to reproduce the error has been a massive benefit, that not all our users do, so doing that is greatly appreciated.

On a side-note, I'm (and others) are happy to assist wherever possible to make your transition from Oracle to SQLite smooth!

[chrisjlocke]

Would be nice to skip foreign key checks

There is a pragma for that.

https://www.sqlitetutorial.net/sqlite-foreign-key/

see 'pragma foreign_keys = off '

[AndrewMcSwain]

I'm getting this error while trying to save my edits to the 'Network Activity Predictor' db of Chrome. I'm on Windows 8.

[rrakso]

I have also this problem. While trying to delete an record from sqlite DB :disappointed:

[chrisjlocke]

Can you close DB4S, try again, and if it occurs, let me know what steps you're doing. Also, I don't know what version of DB4S you're using - can you provide that information? Also, what operating system are you using?

[frapeshka]

Same issue, Linux, Debian.

[chrisjlocke]

What version of DB4S are you using?

[frapeshka]

What version of DB4S are you using?

Version 3.10.1 Qt Version 5.9.3 SQLite Version 3.21.0 It's not crucial, I just restarted the app and the problem gone.

[raffaem]

I have just stumbled upon this:

How can I fix the problem?

Will my edits be saved if I exit from the db?

[chrisjlocke]

What version of DB4S are you using? What operating system? What were you doing to cause this? If you try it again, does it occur again?

[raffaem]

I am using 3.12.2 on Fedora Workstation 34. I installed from the Fedora repositories, since the AppImage doesn't work.

I don't know how to reproduce it ... maybe interrupt a query in the middle? Something like this

[vilhok]

I also encounter this when working with transactions.

To reproduce: first, create a table:

DROP TABLE IF EXISTS accounts;
CREATE TABLE "accounts" (
    "accountnum"    INTEGER,
    "money" INTEGER NOT NULL,
    CHECK (money >= 0), 
    PRIMARY KEY("accountnum")
);

INSERT INTO accounts VALUES(123,500);
INSERT INTO accounts VALUES(456,500);

At this point, you can click "Write Changes"

Then, run following:

BEGIN TRANSACTION;

INSERT INTO accounts VALUES(123456,100);

SELECT * FROM accounts;

You can observe that the insert has been done succesfully.

Then, run:

ROLLBACK;

SELECT * FROM accounts;

You can now observe that the ROLLBACK was effective.

Try to click "Write Changes" you get the error dialog for no such savepoint: RESTOREPOINT (RELEASE "RESTOREPOINT";)

Useful info:

$ sqlitebrowser -v
DB Browser for SQLite Version 3.12.2

Built for x86_64-little_endian-lp64, running on x86_64
Qt Version 5.15.6
SQLCipher Version 4.5.2 community (based on SQLite 3.39.2).

Installed via pacman: https://archlinux.org/packages/community/x86_64/sqlitebrowser/

[chrisjlocke]

Then, run following: BEGIN TRANSACTION;

The process of using the SQL tabs, and the purpose of the 'Write changes' button is it is already using transactions. By adding your own, you're erasing the history and journal needed for the transactions to occur .. so when you press 'Write Changes' you've already committed, so the 'no such savepoint' is going to fail .. as you've wiped the journal.

There is no need to use transactions in the 'Execute SQL' tabs. That is the point of the 'Write changes' button. Perform the SQL. If you want to commit, click the 'write changes'. If you don't, click 'Discard'.
This is expected behaviour.

[vilhok]

This is expected behaviour.

Thank's for the reply, I'm sorry for misunderstanding this. I can see now that this is documented here in the wiki .

On the other hand, using commands such as ROLLBACK and COMMIT still works (at least during specific states) but the UI does not reflect this. For example BEING TRANSACTION; just prints Execution finished without errors. and if after that I do an INSERT and then ROLLBACK it works the first time. The problem is that if I run the same SQL tab again, I start getting errors about no active transactions.

I feel like this is not immediately obvious to the user. I think like it should warn the user about trying to do transactions on their own, or at least it should not end up in such inconsistent state. Another example:

INSERT INTO some_table VALUES(123,345);
ROLLBACK;

This makes the insert and then discards the changes.

Run that again, the rollback now fails with an error message about transaction being active, even BEGIN TRANSACTION now fails. I can also click "Revert Changes" but that does not actually revert the latest change.

The Wiki is clear about this and warns about trying to do nested transactions, but I'd consider if it was worth to add a feature that disables this behavior and hands over the control to the user completely. Alternate solution would be to give a specific warning when trying to do transactions manually and yet another solution would be to bind the button actions/states to the begin and rollback -commands. I might want to open a separate issue about this, unless this idea has already been discarded before.

[chrisjlocke]

Its a fine balance between having instructions not to do something, and trying not to bloat the software with code to stop the user doing something. The code to do what you're asking isn't impossible, but poses risks without a lot of testing - switching tabs may then not work, or the program would have to monitor the state of every tab, etc.

Do-able, but we don't have a team of 90 programmers all itching for something to do.

Here in the UK, we have tried to stop kids sticking forks in the plug sockets. We have done a lot towards that, and could stick a wire cage around each socket, but then they'd pour water in. Point being, we could add a bunch of code to do all that, then someone will find another way of breaking it. Sometimes, a list of 'known issues' is at least recognising the problem and a way of stopping people losing forks.