SQLite Forum

Timeline
Login

20 most recent forum posts

2021-09-27
16:02 Reply: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately (artifact: 6a66501e4d user: crawshaw)

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!

15:25 Reply: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately (artifact: 32bac245c0 user: 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?

10:48 Reply: Test SQLITE_NOMEM result code on Linux (Memory : 8Gb) (artifact: 4203b0ca76 user: drh)

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.

10:22 Post: parse.y: a typo in a special comment (artifact: b6376bbe30 user: mandel59)

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

07:49 Reply: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately (artifact: b0680bf448 user: anonymous)

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.

05:10 Post: Test SQLITE_NOMEM result code on Linux (Memory : 8Gb) (artifact: 678ac0c4ec user: anonymous)

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

2021-09-26
19:33 Post: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately (artifact: b1d109d934 user: crawshaw)

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

13:19 Reply: SIGBUS in sqlite (artifact: ebeb240cfe user: slavin)

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.

07:05 Reply: SIGBUS in sqlite (artifact: b484895ced user: liziqi)
Yes, still use this room.
In our actual scenario, this situation has only occurred once. Under normal circumstances, this happens only if the index file is illegally truncated. So we have checked the code. There is a way of directly deleting the db, wal, shm files in our code, followed by the reconstruction process, so it is suspected to be introduced in this place, but there is no direct evidence. The problem is suspended because it rarely occur.
2021-09-25
21:57 Reply: V3.36 (artifact: 91119eff09 user: larrybr)

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.

21:40 Reply: V3.36 (artifact: ff0d088fdb user: larrybr)

(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.

21:37 Reply: V3.36 (artifact: 9dcc644e3c user: anonymous)

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?

21:06 Reply: V3.36 (artifact: b66e1c3b4a user: slavin)

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.)

21:00 Edit reply: V3.36 (artifact: 62c5ba45b0 user: larrybr)

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.

20:57 Reply: V3.36 (artifact: 7be6e06884 user: larrybr)

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.)

20:08 Post: V3.36 (artifact: da4d820656 user: anonymous)

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

16:04 Post: sqlite3_busy_handler missing (artifact: ce406b9379 user: anonymous)

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?

15:16 Reply: Multi-Process Access (artifact: c7e49620b2 user: larrybr)

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

15:09 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 960a6e5851 user: larrybr)

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.

14:43 Reply: question about sqlite3_close_v2 call! (artifact: 59e65edb5c user: larrybr)

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.

More ↓