SQLITE_FULL on creating table for an in-memory database

(1) By Guanpeng Xu (herberteuler) on 2022-03-10 00:53:22 [link] [source]

Hello,

I have this error for an in-memory database:

$ rlwrap sqlite3 
SQLite version 3.38.0 2022-02-22 18:58:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA max_page_count=1; CREATE TABLE foo (a INT);
1
Runtime error: database or disk is full (13)
sqlite>

The memory on the system is

$ free
              total        used        free      shared  buff/cache   available
Mem:       32291572     3085412    22224748     1095484     6981412    27646520
Swap:             0           0           0

Is it expected behavior or how can I fix it?

Thanks in advance.

(2) By Richard Hipp (drh) on 2022-03-10 01:21:28 in reply to 1 [link] [source]

The "PRAGMA max_page_count=1" statement limits the number of pages in the database to 1. A minimum of two pages are required to create a table (one page for the table itself and another for the sqlite_schema table).

The purpose of the "PRAGMA max_page_count=1" statement is limit the maximum amount of disk space (or memory in the case of an in-memory database) that the database is allowed to use. So SQLITE_FULL seems like an appropriate error if you exceed that limit.

(4) By Guanpeng Xu (herberteuler) on 2022-03-10 04:44:53 in reply to 2 [source]

The statement is an excerpt from direct-sqlite's test case. The test failed in my attempt to upgrade its SQLite library to 3.38.0 from 3.28.0.

I have tested the following program against SQLite 3.28.0 and 3.38.0:

#include <stdio.h>
#include <string.h>

#include "sqlite3.h"

int main(int argc, char* argv[]) {
  sqlite3 *db;
  sqlite3_open_v2(NULL, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_MEMORY, NULL);
  printf("0: %d\n", sqlite3_get_autocommit(db));
  sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
  printf("1: %d\n", sqlite3_get_autocommit(db));
  sqlite3_exec(db, "PRAGMA max_page_count=1; CREATE TABLE foo (a INT)",
               NULL, NULL, NULL);
  printf("2: %d\n", sqlite3_get_autocommit(db));
  return 0;
}

And I see different behaviors between the two. For 3.28.0, I got

$ cc -o main main.c sqlite3.c && ./main
0: 1
1: 0
2: 1

For 3.38.0, I got

$ cc -o main main.c sqlite3.c && ./main
0: 1
1: 0
2: 0

Being only a casual user of SQLite, I cannot tell if this change is expected. Could you please drop some hints? Many thanks.

(5) By Richard Hipp (drh) on 2022-03-10 13:08:53 in reply to 4 [link] [source]

I think the 3.38.0 behavior is correct.

The CREATE TABLE statement hits a serious error (out of disk space - due to the artificial limit created by PRAGMA max_page_count) and so the transaction rolled back to protect the integrity of the database. It used to not roll back in this case. But we discovered that can lead to database corruption in corner cases. See forum thread daa2c728cc for a discussion of the issue.

Change occurred at check-in aa512f72cf5adfec.

It is unclear to me why "direct-sqlite" thinks so strongly that a transaction should not rollback following an out-of-disk-space error that they have a test case to verify that. You should perhaps talk to the direct-sqlite people to find out why that test case is there and why they are dependent upon that (undocumented) behavior.

(6) By Guanpeng Xu (herberteuler) on 2022-03-10 13:14:53 in reply to 5 [link] [source]

Roger. Thank you very much!

(7) By Guanpeng Xu (herberteuler) on 2022-03-10 13:34:34 in reply to 5 [link] [source]

I am sorry but on the second pass of careful reading, I am confused by the explanation.

The CREATE TABLE statement hits a serious error (out of disk space - due to the artificial limit created by PRAGMA max_page_count) and so the transaction rolled back to protect the integrity of the database. It used to not roll back in this case.

Does this indicate that autocommit is not restored to enabled (1), in 3.38.0, after the transaction is rolled back? Because that seems to be the only conclusion deduced from the above statement. Does the user need to turn it on manually?

(8) By Richard Hipp (drh) on 2022-03-10 13:51:41 in reply to 7 [link] [source]

I was confused too, which is why my explanation was not clear. I think in terms of "is there a transaction active" not in terms of "is autocommit mode on" and those two states are opposite of each other, which got me turned around.

In 3.28.0, when a disk-full error occurs, the outer transaction rolls back to protect the integrity of the database.

In 3.38.0, the CREATE TABLE has a separate nested "statement" transaction that was not present in 3.28.0. Because that statement transaction is available, the statement transaction is rolled back rather than the outer transaction, and the outer transaction remains active.

I think the official policy should be something like this:

"SQLite reserves the right to rollback a transaction following an error, if it feels it needs to do so in order to protect the integrity of the database. Whether or not a specific error rolls back a transaction might vary according to the details of the circumstances that caused the error, the statement that was running when the error occurs, and from one release to the next. Use the sqlite_get_autocommit() interface to determine whether or not a transaction is still active or was rolled back following an error."

(9) By Guanpeng Xu (herberteuler) on 2022-03-10 13:58:05 in reply to 8 [link] [source]

Okay, this now clarifies everything. Many thanks.

(3) By Larry Brasfield (larrybr) on 2022-03-10 01:24:19 in reply to 1 [link] [source]

Your post reminds me of an old bubble-gum wrapper cartoon where a patient asks his doctor how to keep his head from hurting after hitting himself with a hammer.

Rather than just repeating the doctor's advice, I suggest you look at what PRAGMA max_page_count does, then ask yourself: "Do I really want to keep doing that?"