This is a follow-up to "database disk image is malformed" with an in-memory database?. I'm still looking for help to debug and solve. Am I misusing SQLite in my program? If so, how? SQLite is running in "serialized" threading mode.

I have reproduced the "database malformed" issue with a simple C code program (link to my program's source):

• Create a "vfs=memdb" database
• Insert records at a high rate using one database connection, running in its own thread.
• From a different thread, using a new connection, do "SELECT COUNT"
• The SQLite API returns "database malformed" for the query - specifically when I call sqlite3_step()

What I see:

Link to simple C program

$ gcc in-memory.c -pthread -l sqlite3
$ ./a.out 
Running SQLite version 3.39.4
THREADSAFE=1
Failed to step data: database disk image is malformed
$

Following up on this, previous testing shows this looks like an issue in memdb VFS or SQLite source itself: See this forum post for more details.

This issue has also been reproduced with SQLite 3.40.0. See this GitHub issue update.

A few points, in no particular priority order.

I saw the same problem with your code as of about 00:00 GMT. I did not see anything in it that goes outside of defined behavior. Hence, I looked at it further and will do so more tomorrow.

You should not expect this to get a lot of attention on a Sunday. That's a slow day here.

Your code had a number of oddities: An unused (and leaked) prepared statement; Not handling the SQLITE_BUSY return from sqlite3_step; a function with a declared return not returning anything¹, and some other nits I forget just now. When I remedied those, I got more consistent behavior, including the early exit but for seeing SQLITE_BUSY during stepping.

I'm still puzzled by the malformed DB situation. Yet it seems to demonstrate that as weird corners of the input space are explored, as-yet-untested outcomes can be uncovered. This may in fact represent a bug. Once I reduce your exploration to its essential features, I will either know that it is a bug, be able to report how you are abusing the API, or see that a commonly understood API usage detail needs to be documented.

1. ^{^} This missing return makes me think you dislike or ignore warnings, which makes me want to eagle-eye your code more closely.

Thanks for taking a look.

On the contrary, I am very particular about my programming. I do it for a living after all. :-) But I no longer write C day-to-day, so it's going to be rough when I do. In any event, this program is just meant to demonstrate the problem -- it's not meant to be production-quality code. That said if it's the manner in which I am using the API is actually the problem, it would be great to learn that, so I could feed it back into the real system I am building.

I originally hit this problem when accessing SQLite via a Go library, and the point of this program was to show it is happening with C too. Ultimately I don't understand why a query of an in-memory database would ever get "malformed database" when I believe I am a) not violating the threading considerations for SQLite, and b) not doing anything that would corrupt the database.

Of course, I could only surmise what a warning implies. No offense intended, but I would not want neophyte programmers seeing such an oddity as normal.

Whether the DB is in-memory or not may or may not be related to the "malformed" outcome. (It's certainly related to how fast that can happen!)

Regarding threading considerations: The code is written as if setting a long busy timeout will remove the need to handle a SQLITE_BUSY return. I wanted to make clear that such is not the case. It may make the penalty for not handling it be imposed less often, tending to obscure the problem. But it does not eliminate the issue. This is why I refer to "weird corners of the input space".

That said, it is not a threading misuse as far as I can tell (so far.)

Beware that undefined behavior (the kind of behavior many (most) people including you seem to be able to produce quite easily) is incorrect and has no value even as demonstration code. You can use clang and pass -Weverything -Werror on that kind of code (demonstration code) just to be (99.9%) sure that it is usable as C code.

(you can use gcc, or pass other flags, but you must then be a hardened expert in order to be sure that your C code is indeed C code and not a satanical invocation)

This missing return makes me think you dislike or ignore warnings, which makes me want to eagle-eye your code more closely.

Note that gcc does not warn about this by default, so people who do not code in C regularly and don't know to pass -Wall (or similar) won't observe any warning.

gcc does not warn about this by default, ...

Yeah, lamentable isn't it? That's why adding the option is so often recommended. Its absence was why I used the word "dislike".

Great -- I am glad it helped improve the software, I'll upgrade to the latest version once it's available. Thanks for the fix.

FYI: https://www.sqlite.org/src/info/15f0be8a640e7bfa