New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

certain pragmas revert to original values rather than roundtripping #1518

Open
twest820 opened this Issue Aug 25, 2018 · 7 comments

Comments

Projects
None yet
3 participants
@twest820
Copy link

twest820 commented Aug 25, 2018

Details for the issue

What did you do?

Change pragmas on Edit Pragmas tab or via SQL.

What did you expect to see?

  1. Modified values set for pragmas when Pragmas tab's Save button are clicked are persisted to database and appear when closed reopened. The Write Changes button does not enable in this case.

  2. Modified values set for pragmas via the Execute SQL tab are persisted to database and appear when closed and reopened. The Write Changes button does not enable in this case either, though, it does enable if a query only pragma is issued rather than a write pragma (e.g. pragma temp_store rather than pragma temp_store = memory).

What did you see instead?

Original pragma values under both pathways described above. This affects attempts to specify at least

Auto Vacuum = Incremental
Synchronous = Normal
Temp Store = Memory
WAL Auto Checkpoint = <any value besides 1000>

Setting Journal Mode = WAL roundtrips successfully.

Useful extra information

The info below often helps, please fill it out if you're able to. :)

What operating system are you using?

Windows 10

What is your DB4S version?

3.10.1

Did you also

Review of the SQLite documentation at https://www.sqlite.org/pragma.html suggests that, in the case of pragma auto_vacuum the DB Browser may not be issuing the subsequent vacuum command required to change pragma to or from none in a database with existing tables. In this case, a workaround for this issue is to execute both the pragma change and vacuum commands in the Execute SQL tab.

Some experimentation with the current SQLite nuget (https://www.nuget.org/packages/System.Data.SQLite.Core/) suggests pragma temp_store may be scoped to a specific connection rather than to the database and therefore non-durable. It also appears auto_vacuum and journal_mode are sensitive to whether SQLite's caller includes them within a transaction. The SQLite documentation appears unclear on these points.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Aug 25, 2018

As a general thought, it's probably worth having a go with the latest nightly build too:

    https://nightlies.sqlitebrowser.org/latest/

We've done a fair bit of work in the ~year since our last major release, including improvements to how we work with PRAGMAs. 😄

@twest820

This comment has been minimized.

Copy link
Author

twest820 commented Aug 25, 2018

Thanks, Justin. I just tried the latest build and, unfortunately, at least temp_store and wal_autocheckpoint exhibit the same behaviour with it as I saw with 3.10.1.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Aug 26, 2018

Thanks @twest820. 😄

Sounds like we still have more stuff to fix with PRAGMAs then after all.

@justinclift justinclift added the bug label Aug 26, 2018

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Aug 28, 2018

You're right about calling VACUUM after changing the auto_vacuum pragma. We did that for page_size but not for auto_vacuum. I'll change that in a second.

temp_store is about temporary tables and indices and therefore most likely not saved in the database file because it doesn't affect the database at all.

For the wal_autocheckpoint PRAGMA there is also a C-API function and in the documentation for that function they say that the value is only saved for the connection but not in the database file.

And finally for synchronous I couldn't find anything but because it's related to WAL as well I assume it's also per connection.

MKleusberg added a commit that referenced this issue Aug 28, 2018

Run VACUUM after changing the auto_vacuum PRAMGA
Changing the auto_vacuum PRAGMA requires us to run VACUUM most of the
time. So to be safe we just do that for every change to the PRAGMA now.

See issue #1518.

MKleusberg added a commit that referenced this issue Aug 28, 2018

Add some PRAGMA which are only connection persistent to the project file
The temp_store, wal_autocheckpoint and synchronous PRAGMAs are only
stored for the current database connection but not saved in the database
file. So to fully restore the working environment after closing a
database, this commit adds these three PRAGMAs to the project file
format.

See issue #1518.
@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Aug 28, 2018

I have made two changes now:

  1. Run VACUUM after changing the auto_vacuum PRAGMA. This should be obvious 😄
  2. Store the values of the three other PRAGMAs in the project file. We did this for some other PRAGMAs with the same problem already, so it only makes sense to do it here as well. It's the only thing we can offer you: if you want these PRAGMAs to be persistent between connections, you can now change them, then save all your settings to a project file (File -> Save Project), and then always open that project file instead of the original database file.

Are you ok to download tomorrow's nightly build and see if it's improving things for you, @twest820? 😄

@twest820

This comment has been minimized.

Copy link
Author

twest820 commented Aug 31, 2018

Cool, thanks for the quick turnaround (though, admittedly, I missed tomorrow's nightly and therefore picked up yesterday's :-p). Thinking about this from a user interface standpoint, maybe the thing to do for clarity would be to add a couple group boxes to the pragmas tab in order to indicate which settings can be persisted in the database and which are saved in the project? At least in my use case, the SQLite browser serves as development tool for maintaining default databases installed as part of another app. In this circumstance the key consideration is whether I need to add code in the app to provide the same connection settings as the browser does when when loading project settings.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Aug 31, 2018

maybe the thing to do for clarity would be to add a couple group boxes to the pragmas tab in order to indicate which settings can be persisted in the database and which are saved in the project?

That does sound like a good idea. 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment