SQLite Forum

That's it. Setting PRAGMA wal_autocheckpoint=0; on every connection resolves the issue and lets PRAGMA wal_checkpoint(RESTART); succeed reliably. (I did see a single SQLITE_BUSY in my load test, but there is a lot of code involved and I am investigating if I accidentally created a connection inappropriately.)

Turns out this is well-documented, I somehow missed it.

All calls obtain an exclusive "checkpoint" lock on the database file. If any other process is running a checkpoint operation at the same time, the lock cannot be obtained and SQLITE_BUSY is returned. Even if there is a busy-handler configured, it will not be invoked in this case.

Thank you Dan!

If there is another checkpointer running, sqlite3_wal_checkpoint_v2() will return SQLITE_BUSY immediately. It only uses the busy handler for database readers and writers. The PRAGMA is similar.

Any connections configured to do auto-checkpoints that might be running a checkpoint?

You can register your own memory allocator using the [sqlite3_config(SQLITE_CONFIG_MALLOC, ...)1 interface. This is what the various SQLite test harnesses do. Normally calls to the custom malloc fall through into system malloc, but it is possible to inject faults. There is an example of doing this in the CLI source code.

You might also set the PRAGMA hard_heap_limit to some small value in one connection, and then open a separate connection. The hard_heap_limit applies to the entire process, not to individual connections.

Hello,

I found a typo in a comment in parse.y, line 1175:

likeop(A) ::= NOT LIKE_KW|MATCH(X). {A=X; A.n|=0x80000000; /*A-overwrite-X*/}

/*A-overwrite-X*/ would be a typo of /*A-overwrites-X*/, and it would not work as a special comment.

I'm not sure how much this typo will affect the functionality, but just wanted to report it.

Thanks,

Ryusei

Are you setting PRAGMA journal_mode=WAL; on the connection you're using to run your application-initiated checkpoints on? When I last tried a similar setup to yours, I had to set it explicitly again on the checkpoint connection for those calls to work, even though WAL mode is meant to be persistent.

Is there any way to test SQLITE_NOMEM result code on the following interfaces : 1. sqlite_open(:memory:) 2. sqlite_prepare_v2

I am using it in a library and have to simulate for test purpose.

Also please provide any pointers on how to use sqlite_test_control for limiting memory and fault injections.

Sqlite Version : 3.22

Hello,

I have a busy, very concurrent SQLite database: 36 connections open from the same process, about 1000 writes per second. As is apparently expected, the WAL file is growing without bound. To counter this, I am attempting to deliberately insert a periodic "stop-the-world" style event by calling:

PRAGMA wal_checkpoint(RESTART);

As I understand it, this will grab an exclusive lock, invoking the busy handler.

All of these connections are configured with a busy_timeout of 10 seconds. But the PRAGMA wal_checkpoint(RESTART) returns busy within a microsecond. Similarly, sqlite3_wal_checkpoint_v2 returns SQLITE_BUSY within a microsecond.

Am I misunderstanding the documentation of sqlite3_wal_checkpoint?

Thank you,

David

This worries me, because it suggests you can delete these files while the database is in use.

If SQLite closes the last connection to a database in WAL mode, it deletes the .shm file. So if you see a .shm file on disk SQLite thinks that one or more connection is still using the database, so you shouldn't delete any of the above files.

If you think a previous run of the database has crashed, and these flies really aren't being used, you can check this by using

sqlite_open()
"PRAGMA user_version" (or any other operation which requires reading the file)
sqlite_close()

on the database. If no other connections are using the database then this will delete the .shm file.

In the CLI ... tells me which databases are in scope in the current session (as Simon Slavin points out) ... but I see the alias as main and not the handle i.e. as returned by the out parameter of sqlite3_open(dbname, out handle).

What you (or the System.Data.SQLite .Net library, I suppose) are calling a "handle" is just that pointer to the (externally opaque) sqlite3 object. That .Net library has no way of knowing about any other "handle" it may have passed to its caller(s). The CLI (in recent versions) does track a small set of database connections (another term for references to those opaque objects) it has open, but does not traffic in raw pointer values at its external interface.

What you are calling an "alias" is the schema name, significant with just the connection which might have more than one database attached to the "main" one. This attachment is different from having a whole, separate connection.

To paraphrase, my original question was simply to figure out whether I could get the list of databases I've opened together with their corresponding handles ... programmatically

No.

It looks like I have to manage this within my application. Correct?

I think my answer to this was clear in post #2. It amounts to "yes" here.

(I spend a few minutes trying to find out how to iterate through the connections for an instance of SQLite and failed. Contributions welcome.)

My only contribution is this advice: Be sure to limit that effort to just a few minutes.

The nature of SQLite library "instances" is one clue to when that search should terminate. The library code will normally be limited to one copy per process when statically linked (without special renaming to avoid link-time collisions.) The instantiation of data managed by the library is precisely the allocation and initialization of the opaque (to applications) struct whose typename is "sqlite3". There are no instances broader than that, except that one could contemplate all such instances in a process, on a computer, in some user's processes, in the world, across the universe, etc. Those broader sets are not tracked in any way or related to each other by the library.

Given that the OP's inquiry was about "all opened databases", the obvious answer is "No" but that's too easy to just say.

In the CLI

,databases

or

PRAGMA database_list

tells me which databases are in scope in the current session (as Simon Slavin points out) ... but I see the alias as main and not the handle i.e. as returned by the out parameter of sqlite3_open(dbname, out handle).

Programmatically, I will need to use sqlite3_open--which creates handles and not aliases as the CLI--to open databases.

To paraphrase, my original question was simply to figure out whether I could get the list of databases I've opened together with their corresponding handles ... programmatically .

It looks like I have to manage this within my application. Correct?

Your question doesn't quite correspond with how SQLite manages open databases. When you want SQLite to access a database you create a 'connection'. That database will be the 'main' database for that connection. If you want SQLite to access a second database you can either attach another database to the first connection, or open another connection. So …

One instance of SQLite can have many connections. Each connection has a main database and can have many attached databases.

Given the above, to discover all databases you currently have access to, for each SQLite connection, use

https://sqlite.org/pragma.html#pragma_database_list

PRAGMA database_list

It returns one line per database, second column is the connection's name for that database, third column is the name of the database file.

Your next natural question is how to find out what tables are in each of those databases, and you'll find it on the above page, under

PRAGMA schema.table_xinfo(table-name)

(I spend a few minutes trying to find out how to iterate through the connections for an instance of SQLite and failed. Contributions welcome.)

Such information is not kept by the SQLite library. It should be easy for an application to keep track of this. (I'm not sure what a "database name" or "handle" are in this context, but can answer nonetheless because the library does not rely on in-process data other than what is kept in per-connection structs or in structs held by them.)

Also: Please choose more descriptive thread titles going forward. Yours is ambiguous at best and misleading at worst.

Can I get a dictionary (name = database name, value = handle) for all opened databases?

In the latest versions of SQLite.Interop.dll the api entry sqlite3_busy_handler is no longer being exported. Is it possible to have it back?

Yes. Look for "concurrency" in the keyword index.

Thanks for the tips, Warren. I will follow up on this in about 4 weeks when I get back to that workstation to do the implied experiments.

WSL2 uses an actual Linux kernel with regular Linux filesystems.

That's why I was surprised at the failure, in part. The other part is that, at times, it has passed the whole suite of SQLite's TCL tests except for ones doing full precision FP value comparisons. This makes me think I must have switched that system back to WSL1 for some reason and forgotten to switch it back.

If either (1) Your single (and therefor auto-transacted) statements completed past the stepping phase (returning SQLITE_DONE), or (2) your multiple statement sets, bounded by transaction begin and end, completed (in the same sense), then the persistent data will be handled safely (where that means adhering to the ACID promise.) This includes effects upon the auxiliary files that SQLite uses to deliver on that promise.

The main penalty for leaving unfinalized statements lying around is memory leaks and reports thereof which responsible programmers generally heed and cure.