Checking that I understand your post. The program, and the SQLite calls it makes, work fine for a while. Then, without doing anything significant or new, queries which have worked before stop working. Is that right ?

I'm concerned at an in-memory database which has "db_size": 129212416. That's close to a 128Meg database in memory. Is this an unusually big memory for your users ? Are other users using bigger databases than this without problems ? Can you fake up a database bigger than that yourself and see whether your code handles it without problems ?

I'm betting that SQLite is trying and failing to allocate more memory. Or it's busting a cache or something.

According to the initial report (see See https://github.com/rqlite/rqlite/issues/1103#issuecomment-1299062521):

"Just one extra information. In all cases that happened so far, the SELECT was trying to find a record that was just INSERTED. In one case the INSERT was performed at 2022-11-01 10:01:42,601 and the SELECT at 2022-11-01 10:01:42,731"

I was also wondering about the memory of the host machine, but it looks sufficient (see https://github.com/rqlite/rqlite/issues/1103#issuecomment-1299106153). But, of course, perhaps the process itself has lower limits and it's causing SQLite problems.

128MB is not too big. While rqlited isn't designed for very large datasets, I haven't heard of other problems until databases started hitting a 2GB limit (but that is a known limit with SQLite).

I'll ask some more questions on the GitHub ticket.

The recover extension (see source tree) has an user defined function of:

/*

** The implementation of a user-defined SQL function invoked by the

** sqlite_dbdata and sqlite_dbptr virtual table modules to access pages

** of the database being recovered.

**

** This function always takes a single integer argument. If the argument

** is zero, then the value returned is the number of pages in the db being

** recovered. If the argument is greater than zero, it is a page number.

** The value returned in this case is an SQL blob containing the data for

** the identified page of the db being recovered. e.g.

**

** SELECT getpage(0); -- return number of pages in db

** SELECT getpage(4); -- return page 4 of db as a blob of data

*/

I have not had to use it, but might be useful to you.

scratch that suggestion... but a more general one of tossing the recover extension at it might show up something for you (and then trying to get the pages that are covered via the select in question?).

Yeah, this is part of what made me think of something hitting a max-memory boundary which SQLite cannot detect. Because I think it's the INSERT which is failing, leaving a database which is malformed. One way to investigate is to find out whether your user always has trouble when the database reaches or exceeds the size of the one she/he reported.

I understand that there's no reason for SQLite itself to baulk at a 128M database in memory. It's supposed to work fine, as long as the OS supports it, and to give an error if it doesn't.

Can you tell us the OS ? I can see it's amd64, and they used 'cat', but I can't deduce anything else. I was interested to know if the people having this problem are using Kubernetes, but I see that their report says they aren't. Can you confirm that they're running on physical piece of hardware and not some kind of virtual or simulated environment ?

BTW, there's something wrong with the cert for https://www.rqlite.com. Try getting there from a browser you don't normally use.

To elaborate on Simon's point that there is something wrong with the www.rqlite.com cert: the cert being presented is issued by Let's Encrypt for www.philipotoole.com rather than www.rqlite.com (hostname mismatch).

It's Alpine Linux v3.16.

Where you directed to https://www.rqlite.com? I'd like to fix that.

I have more details. To quote https://github.com/mattn/go-sqlite3/pull/1108#issuecomment-1308200668:

"I don't know what the underlying issue is, but I was able to get a bit more information from the SQLite error logs.

11: database corruption at line 69145 of [a29f994989]
11: statement aborts at 2: [SELECT COUNT(*) FROM logs] database disk image is malformed

That means the corruption is detected on line 69145 of sqlite3-binding.c. However, this output is not consistent - sometimes the corruption is detected on line 70169, but usually it is on line 69145.

That is deeper in the internals of the library than I am familiar with. If you can reproduce with a pure C version (using pthreads I guess) then you should file a bug on the forum."

Sometimes this is being triggered in this code:

static int getAndInitPage(
  BtShared *pBt,                  /* The database file */
  Pgno pgno,                      /* Number of the page to get */
  MemPage **ppPage,               /* Write the page pointer here */
  BtCursor *pCur,                 /* Cursor to receive the page, or NULL */
  int bReadOnly                   /* True for a read-only page */
){
  int rc;
  DbPage *pDbPage;
  assert( sqlite3_mutex_held(pBt->mutex) );
  assert( pCur==0 || ppPage==&pCur->pPage );
  assert( pCur==0 || bReadOnly==pCur->curPagerFlags );
  assert( pCur==0 || pCur->iPage>0 );

  if( pgno>btreePagecount(pBt) ){
    rc = SQLITE_CORRUPT_BKPT;     ##### <---- here
    goto getAndInitPage_error1;
  }
  rc = sqlite3PagerGet(pBt->pPager, pgno, (DbPage**)&pDbPage, bReadOnly);
  if( rc ){
    goto getAndInitPage_error1;
  }
  *ppPage = (MemPage*)sqlite3PagerGetExtra(pDbPage);
  if( (*ppPage)->isInit==0 ){
    btreePageFromDbPage(pDbPage, pgno, pBt);
    rc = btreeInitPage(*ppPage);
    if( rc!=SQLITE_OK ){
      goto getAndInitPage_error2;
    }
  }
  assert( (*ppPage)->pgno==pgno || CORRUPT_DB );
  assert( (*ppPage)->aData==sqlite3PagerGetData(pDbPage) );

The URL retrieves the right website. But it came up as a security risk because the certificate had the name of the site wrong.

Would you be able to tell us the result of sqlite3_extended_errcode() ?

I'm 90% sure that the fault is being triggered by the INSERT command preceeding the SELECT. Would it be possible to temporarily add code to execute

PRAGMA quick_check

both before and after the command ? Perhaps something which executes even faster than that would be just as good, for example

SELECT * FROM MyTable LIMIT 1

Once again, the extended error code would be useful. It would be especially interested if the one before the INSERT reports a fault.

Having said that, I'm not sure how much good it will do. Suppose the INSERT command is messing things up. What would you investigate next ? Would it necessarily suggest something ran out of space ?

Thanks for your response. Interestingly when I change my test so that

SELECT COUNT(*) FROM logs`

is replaced with

SELECT * FROM logs LIMIT 1

I can't get the failure to occur. Does that tell us anything?

There is no extended error code. It just gives 11 (i.e., plain SQLITE_CORRUPT).

Running both PRAGMA quick_check and PRAGMA integrity_check on the connection that gave SQLITE_CORRUPT after the failure does not work. Usually it just says "ok", but on occasion one or both with fail with a plain SQLITE_CORRUPT.

Running those pragmas on the connection that is doing the inserts causes the issue to no longer reproduce.

Running SELECT * FROM logs LIMIT 1 causes the issue to no longer reproduce. However, I do observe the same corruption failure with SELECT * FROM logs ORDER BY rowid DESC LIMIT 1. Here I was able to get PRAGMA quick_check to output something:

*** in database main ***
On tree page 6935 cell 17: 2nd reference to page 7182
On tree page 6935 cell 398: Rowid 2239121 out of order
On tree page 6935 cell 398: 2nd reference to page 7181
On tree page 6935 cell 397: 2nd reference to page 7180
On tree page 6935 cell 396: 2nd reference to page 7179
On tree page 6935 cell 395: 2nd reference to page 7178
On tree page 6935 cell 394: 2nd reference to page 7177
On tree page 6935 cell 393: 2nd reference to page 7176
On tree page 6935 cell 392: 2nd reference to page 7175
On tree page 6935 cell 391: 2nd reference to page 7174

This is what the SQLite error logs show:

database corruption at line 69145 of [a29f994989]
statement aborts at 4: [SELECT * FROM logs ORDER BY rowid DESC LIMIT 1] database disk image is malformed

It is random when the failure is observed, which leads me to believe it is due to some race condition either in the memdb VFS or in the SQLite core itself.

FWIW: I have not looked at it closely, and it's just a hunch, yet I wonder if the assert mutex should be held prior to calling getAndInitPage() to prevent the possibility of the ppage and pgno being changed as the function is called? If an insert were to happen while the assert mutex is going about it's thing, would not the ppage and pgno passed values be out of date/incorrect? I would image that only inserts that happen during that time, and only inserts that change those passed values would make it intermittent.

Just closing the loop here, turned out to be a bug in the SQLite source. All fixed now.

https://sqlite.org/forum/forumpost/5adb92e2baca3678