SQLite Forum

Timeline
Login

50 most recent forum posts

2021-10-27
15:03 (Awaiting Moderator Approval) Post: database creation getting hung up on a NFS mount path (artifact: 218bf4ac5d user: anonymous (unpublished))
2021-10-26
23:12 Reply: cksumvfs producing invalid checksums (artifact: 6a47db65a2 user: achamb)

Thanks for confirming.

21:46 Reply: Database on android SD CARD (artifact: 605fa14ffe user: larrybr)

On my SD card the permissions were RWXRWX--- but on my emulated storage its rw rw ---

That's a decent clue that Stephan's "noexec" tip is pertinent.

I selected the 20 FE phone specifically for the SD card so I could do this project without rooting my phone.

The "cannot root phone" constraint is not one likely to be catered to.

I'm on the verge of giving up on SQLite even though I really really want it to work!!

If wishes were fishes, ...

What makes you think this has anything to do with SQLite, other than that it is ultimately executed (like anything else running) and your environment is blocking execution?

You should go to an Android development forum to get advice on your problem. You have a platform issue rather than a SQLite issue.

21:32 Edit reply: Configuring SQLite to wait indefinitely (artifact: fbe2f75316 user: casaderobison)

Based on the phrasing, it seems what is wanted is a "wait forever" that semantically says what is being sought vs "wait for a really long but finite time". Something that in code would be immediately obvious as the difference between "wait until something is available" and "wait until something is available or the timer elapses".

To that end, defining a custom busy handler would be the way to do that as described.

21:30 Reply: Configuring SQLite to wait indefinitely (artifact: 5eab27608c user: casaderobison)

Based on the phrasing, it seems all that is a "wait forever" that semantically says what is being sought vs "wait for a really long but finite time". Something that in code would be immediately obvious as the difference between "wait until something is available" and "wait until something is available or the timer elapses".

To that end, defining a custom busy handler would be the way to do that as described.

21:22 Edit reply: Database on android SD CARD (artifact: be9094781c user: Webalchemist)

It says generic hierarchical.

What if I format the SD card?

I tried navigating to

cd /tmp but I got invalid directory.

I have no idea of any other "nonFAT" places. How can I solve it?

---- update

I tried reformatting but android didn't offer me any alternative formats.

As an aside,

I moved my files to the storage/emulated/0/test directory and there was a difference.

On my SD card the permissions were RWXRWX--- but on my emulated storage its rw rw ---

I selected the 20 FE phone specifically for the SD card so I could do this project without rooting my phone.

I'm on the verge of giving up on SQLite even though I really really want it to work!!

Any last ideas?

21:00 Reply: Database on android SD CARD (artifact: 178f0672cb user: Webalchemist)

It says generic hierarchical.

What if I format the SD card?

I tried navigating to

cd /tmp but I got invalid directory.

I have no idea of any other "nonFAT" places. How can I solve it?

20:28 Reply: Configuring SQLite to wait indefinitely (artifact: 961ea2698f user: slavin)

Define your own busy handler which does whatever you want:

https://sqlite.org/c3ref/busy_handler.html

You can, of course, copy the default one and modify it for your own purposes.

However, along with Ivan, I suspect something weird is going on if a pause of 24 days is not enough for your purposes. It might be to your advantage to tell us what you're really trying to do.

19:41 Reply: Database on android SD CARD (artifact: 65a6731d78 user: stephan)

/storage/.../sqlite3 can't execute: Permission denied

That SD card is very likely formatted with the FAT filesystem and linux distributions will mount such filesystems with the "noexec" option, making it impossible to run executables. Copy the sqlite3 binary to /tmp (or some other non-FAT place) and run it from there.

19:30 Reply: Configuring SQLite to wait indefinitely (artifact: e90db2a100 user: itroot)

Curious, why do you need that? It is possible to get an example of an actual use-case?

19:18 Reply: Database on android SD CARD (artifact: 7661094626 user: Webalchemist)

thank you so much for this excellent reply!

Some specifics of my project: I'm not using andriod studio. I'm working with Macrodroid and shell command.

So, instead of the andriod binaries, I downloaded Precompiled Binaries for Linux and was able to move the sqlite file to my sdcard

I then called it in my shell like:

/storage/2EA5-2D26/Xenno/sqlite /storage/2EA5-2D26/Xenno/zztest.db

the result I got was:

/system/bin/sh: <stdin>[25] /storage/.../sqlite3 can't execute: Permission denied

I then used the LS command and the sqlite3 file is set to rwxrwx---

I tried using chmod o=rwx /storage/2EA5-2D26/Xenno/sqlite but I get no result

I've spent so many hours on this. Any help is much appreciated. If only I could make sqlqueries!? it'd be a whole other world!

18:17 Reply: Get SQLITE_PROTOCOL error when opening database (artifact: 4bda3c9b0e user: anonymous)

I am doing nothing special for the compilation. I just added the 3 files that were the wrapper for SQLite to my project as normal. Then I added winsqlite3.dll and .lib files to the project also. Everything compiles with no errors. I did not no about the MACRO, so I will check that out. While waiting for an answer I was messing around with the project and I did something (don't know what) but now the open function works. I can only guess that I fixed the project scope with the database file because that was the only thing I was changing. Strange!!! I am happy now.

18:06 Reply: Get SQLITE_PROTOCOL error when opening database (artifact: 2cece78f9d user: anonymous)

The file is in my home folder for the project hence the lack of any path on the file. SQLite says the full path is not necessary so I did not use it. The reason I dumped the code that it was not working. I have used the wrapper previously in another project that was written in MFC and it worked flawlessly. I always try to use things that work, but in this case I was following the example on sqlite.org first, and it did not work. I moved on because this has been so frustratingly hard to get to work.

17:57 Reply: Get SQLITE_PROTOCOL error when opening database (artifact: f22d79300e user: RandomCoder)

On WinRT, it's fairly unlikely that the processes current directory is writable. You'll need to explicitly specify one of the writable directories.

Also, how are you compiling the SQLite library?

I don't see any mention of setting the SQLITE_OS_WINRT macro that's necessary for SQLite to function on WinRT.

17:37 Post: Configuring SQLite to wait indefinitely (artifact: 2744b798ff user: nchammas)

Is there any way to configure busy_timeout so that SQLite waits indefinitely?

I know I can set the timeout to INT32_MAX milliseconds, which is roughly equal to 24.5 days of waiting. But I'm wondering if there is a more semantically direct way of saying "wait forever".

17:22 Reply: Get SQLITE_PROTOCOL error when opening database (artifact: 0de0b94877 user: larrybr)

It seems odd to me that you abandon code with simple, identified errors in favor of courting new ones. Leaving that aside ...

Also leaving aside the wisdom of throwing an exception for failure to .open() ...

What is the absolute path which your "db.sqlite3" filename resolves to? Or, what is the process current directory when the code you now have is run? Is the directory in which you try to create a database file one which you can modify without unusual permissions?

As part of diagnosing your trouble, I suggest setting m_szFile to name a file in a a directory you can create files in. It should contain the whole path, starting from a filesystem root. That way, the process current directory will not matter.

16:37 Reply: Get SQLITE_PROTOCOL error when opening database (artifact: 61d6aa44a2 user: anonymous)

Update - I dumped all the code above and tried a different scheme. I used a wrapper for SQLite3 from https://github.com/neosmart/CppSQLite and used the same .lib and .dll files. Everything compiles and I get a different error, which is 14 or SQLITE_CANTOPEN. I suspected that was the problem. Why it was one off? Here is the code.

CppSQLite3DB m_dB; const char* m_szFile = "db.sqlite3"; const char* m_szFilesTable = "Files";

void UI_Database::InitializeDatabase(void) { bool fileTableExists = false; const char* ver; int verNumb = 0;

ver = CppSQLite3DB::SQLiteHeaderVersion();
ver = CppSQLite3DB::SQLiteLibraryVersion();
verNumb = CppSQLite3DB::SQLiteLibraryVersionNumber();

try
{
    // Open the database.
    m_dB.open(m_szFile);

    // Check if database contains the 'Files' table.
    fileTableExists = m_dB.tableExists(m_szFilesTable);

    if (fileTableExists == false)
    {
    }
}
catch (CppSQLite3Exception& e)
{
    // Show the exception.
    ver = e.errorMessage();
}

}

Everything works except for getting an exception when open on the database is called. It returns SQLITE_CANTOPEN. I think it is getting blocked from opening by WinRT, but I don't know how to give it access. I just want to confirm my suspicions. Thanks.

16:24 Edit: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: c68cb87012 user: Minoru)

Hello!

Documentation for SQLITE_LOCKED says that it can happen either because of a conflict inside the current connection, or because of someone who uses the same shared cache. Similarly, documentation for transactions states (emphasis mine):

If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with SQLITE_BUSY.

However, I managed to run into a situation where an attempt to upgrade to a write transaction results in SQLITE_LOCKED rather than SQLITE_BUSY.

To reproduce this, we'll need two SQLite CLI tools running in the same directory (I changed their prompts to clarify which is which).

First, we initialize a database in WAL mode:

first> .open test.db
first> pragma journal_mode='wal';
wal
first> create table test (id integer primary key, note text);
first> insert into test(note) values ('hello'), ('world');

We open the same database in the second shell:

second> .open test.db

Back in the first shell, we start a transaction which obtains a read lock:

first> begin transaction;
first> select * from test;
1|hello
2|world

In the second shell, we modify the table:

second> insert into test(note) values ('foo');

Now in the first one, we try to modify the table as well, but get SQLITE_LOCKED:

first> update test set note = 'bar' where note = 'hello';
Error: database is locked

This was a bit surprising, and the docs didn't clarify the situation because they insist that SQLITE_LOCKED can only happen because of shared cache or something on the same connection ­— neither of which applies to the scenario described above.

SQLite's behaviour is correct though. SQLITE_BUSY would imply that I can fix the first shell by waiting a bit, but this clearly won't work; I have to restart the transaction to get an up-to-date view of the database, hence SQLITE_LOCKED.

It'd be nice if docs for SQLITE_LOCKED and transactions mentioned this possibility.

(As usual with FLOSS, I only get a chance to talk to developers when something isn't perfect, but: thank you very much for creating, maintaining, and sharing SQLite with the rest of the world!)

16:18 Edit reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: bf94df725a user: larrybr)

Perhaps the CLI application can be modified to display the extended error code as well as the text. ...

This seems like a good idea to me, and fairly simple to implement.
(Edit resulting from discussion with dev team:)
Although it's a feature change, it is trivial enough to be included in v3.37 (unless my perception of triviality is way off.)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

(Correction via edit regarding silence upon stepping error:)

At present, any error returned from sqlite3_step() is treated as the end of stepping, then reported like others. Given the potential locking issues raised in this thread, indicating this also seems worthwhile.

15:40 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 715037eaca user: anonymous)

Thanks... I will copy the db, install several database engines and try to import the db into those engines to see which one recognizes it... thanks a lot for your time and patience :-)

15:30 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 37f63aff57 user: larrybr)

You do not need "file" or me to tell you that is not the beginning of a SQLite3 database file.

15:20 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 93938f101c user: anonymous)

0 0 0 0 0 0 0 0 M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R M A S T E R 0 357 R 0 0 0 0 0 0 0 0 0 0 0 0 0 0 357 R 0 0 0 0 0 ...

14:50 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 05bded67c9 user: larrybr)

In other words, it is of type "Unrecognized". The "file" utility has known of SQLite databases for awhile now. But you need not rely on it. The first 16 bytes of a SQLite3 database are as stated earlier in this thread. What does 'od -c your.db | head' say?

14:45 Edit reply: Get SQLITE_PROTOCOL error when opening database (artifact: 629f9bdd62 user: larrybr)

The object known as "m_dB" does not have a value which can sensibly be passed to sqlite3_open(). This is a fundamental problem, unrelated to Win10 permissions.

(Added via edit:) Also, your use of sqlite3_temp_directory represents a memory leak.

14:42 Reply: Get SQLITE_PROTOCOL error when opening database (artifact: d3de6254b8 user: larrybr)

The object known as "m_dB" does not have a value which can sensibly be passed to sqlite3_open(). This is a fundamental problem, unrelated to Win10 permissions.

14:39 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 1a9f396689 user: anonymous)

Hi, thanks. It said it is of type "data"

14:32 Post: Get SQLITE_PROTOCOL error when opening database (artifact: 2cdfe4f3f4 user: anonymous)
Hi, Can anyone help with this error? I am using this code in UWP C++/WinRT

sqlite3** m_dB;
const char* m_szFile = "DataBase/db.sqlite3";
char* sqlite3_temp_directory;

void UI_Database::InitializeDatabase(void)
{
    int sqlError = SQLITE_OK;
    
    LPCWSTR zPath = ApplicationData::Current().TemporaryFolder().Path().data();
    char zPathBuf[MAX_PATH + 1];
    memset(zPathBuf, 0, sizeof(zPathBuf));
    WideCharToMultiByte(CP_UTF8, 0, zPath, -1, zPathBuf, sizeof(zPathBuf), NULL, NULL);
    sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf);

    sqlError = sqlite3_open(m_szFile, m_dB);

    if (sqlError == SQLITE_OK)
    {
    }
}

All I every get for sqlError is 15 (SQLITE_PROTOCOL). It never tries to create the file if missing. I suspect it might be a permissions thing in Win10. Thanks.
14:32 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: 92ab78f89d user: Minoru)

You're totally right. My bad, I wasn't attentive enough; I just read "database is locked" and assumed it's SQLITE_LOCKED, even though my program reported the error code of 5 (which is SQLITE_BUSY).

So sorry to waste your time with this; you even took the pain of reproducing it in yet another way, with Python — thanks for that!

13:47 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: 29f056d0c7 user: larrybr)

Perhaps the CLI application can be modified to display the extended error code as well as the text. ...

This seems like a good idea to me, and fairly simple to implement. I think it's a v3.38 feature though because of the current state of v3.37 development.

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

At present, any error returned from sqlite3_step() is silently treated as the end of stepping. Given the potential locking issues raised in this thread, that seems like a behavior worth changing. That will necessitate some modest internal restructuring which definitely makes it a post-v3.37 task.

13:20 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: e51d19f8e6 user: billwade)
That test tells you it is not a typical sqlite3 database file. So you've eliminated one possibility out of many (and mostly the only one this forum discusses).

Since you have unix utilities, you might try the "file" command, which (according to its man page) determines file types.

If I rename a Microsoft Access .mdb file to abc.def I get

$ file abc.def
abc.def: Microsoft Access Database

so it sometimes works.
12:38 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 7a5a24d36c user: anonymous)

I did so and can read text/words does make sense, but there was no "SQLite format 3" text on the head...

Does this mean that 1) the db is an sqlite db (why could I not get any .table nor .schema? it says file is not a database of it is encrypted 2) the db is not an sqlite db but its encoding could be read with "strings myfile.db | head"?

I also run "strings myfile.db more" and seen
  1. What would you recommend me to get the schema of this db and manage to read it?
  2. Can I dump somehow these strings to a kind of csv textfile?
12:20 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 79e6f32064 user: billwade)
The first several bytes in an sqlite db are ascii "SQLite format 3" (without the quotes). There are exceptions to that with some VFS implementations, but for those you'd have to do something special with sqlite3.exe anyway.

If you open the file with a binary editor, you can check for that string, or if you have unix utilities:

strings myfile.db | head

to see if that string shows up.
11:00 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 8d10f33d81 user: anonymous)

Thanks, I did so and then typed ".tables" and nothing appeared... does this mean, does the db is not an sqlite db? (because I now for certain, that the db is not empty)

10:21 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: 6733f1d20a user: drh)

noting that 3.7 is now more than 8 years old and will be missing all sorts of more current features.

Yeah. A simpler solution would be to download and use the latest 3.36.0 CLI.

10:00 Reply: No ".open" option in version 3.7.17 2013-05-20 (artifact: c7e03274d8 user: stephan)

No ".open" option in version 3.7.17 2013-05-20

...

How can I open then the db?

You pass the db name to sqlite3:

$ sqlite3 /path/to/the/db.file

noting that 3.7 is now more than 8 years old and will be missing all sorts of more current features.

09:12 Post: No ".open" option in version 3.7.17 2013-05-20 (artifact: ccc79ad508 user: anonymous)

Hi, I am trying to open a db in my server directory but the command ".open" is not recognized. When I type ".help", the ".open" is not listed... How can I open then the db? Thanks!

04:23 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: 1bf33f0ef4 user: kmedcalf)

Perhaps the CLI application can be modified to display the extended error code as well as the text. It is displayed in some places, but not in others.

Instead of printing the error message as:

Error: <message text>

could it perhaps be displayed as:

Error: <message text> (<extended error code>)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

That is instead of the CLI printing the message Error: database is locked in the above circumstance, perhaps the CLI could print the message Error during Step: database is locked (517).

This would make the CLI more useful as a debugging tool.

00:45 Edit reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: a7f2f09ebf user: kmedcalf)

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be upgraded -- which case applies is up to the programmer to determine).

** NB: I have MonkeyPatched APSW to have added methods such as .begin, .execute, and .commit to the apsw Connection class **

00:43 Edit reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: cb728a42e1 user: kmedcalf)

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done (because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be updated -- which case applies is up to the programmer to determine).

** NB: I have MonkeyPatched APSW to have added methods such as .begin, .execute, and .commit to the apsw Connection class **

00:38 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: f162d6f95b user: kmedcalf)

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done (because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be updated -- which case applies is up to the programmer to determine).

2021-10-25
23:06 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: c33881c335 user: kmedcalf)

Are you sure that the error code is not SQLITE_BUSY_SNAPSHOT, just being simplificated for display to the user by the application?

22:27 Post: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected (artifact: bc0ddb7834 user: Minoru)

Hello!

Documentation for SQLITE_LOCKED says that it can happen either because of a conflict inside the current connection, or because of someone who uses the same shared cache. Similarly, documentation for transactions states (emphasis mine):

If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with SQLITE_BUSY.

However, I managed to run into a situation where an attempt to upgrade to a write transaction results in SQLITE_LOCKED rather than SQLITE_BUSY.

To reproduce this, we'll need two SQLite CLI tools running in the same directory (I changed their prompts to clarify which is which).

First, we initialize a database in WAL mode:

first> .open test.db
first> pragma journal_mode='wal';
wal
first> create table test (id integer primary key, note text);
first> insert into test(note) values ('hello'), ('world');

We open the same database in the second shell:

second> .open test.db

Back in the first shell, we start a transaction which obtains a read lock:

first> begin transaction;
first> select * from test;
1|hello
2|world

In the second shell, we modify the table:

second> insert into test(note) values ('foo');

Now in the first one, we try to modify the table as well, but get SQLITE_LOCKED:

first> update test set note = 'bar' where note = 'hello';
Error: database is locked

This was a bit surprising, and the docs didn't clarify the situation because they insist that SQLITE_LOCKED can only happen because of shared cache or something on the same connection ­— neither of which applies to the scenario described above.

SQLite's behaviour is correct though. SQLITE_BUSY would imply that I can fix the first shell by waiting a bit, but this clearly won't work; I have to restart the transaction to get an up-to-date view of the database, hence SQLITE_LOCKED.

It'd be nice if docs for SQLITE_LOCKED and transactions mentioned this possibility.

(As usual with FLOSS, I only get a chance to talk to developers when something isn't perfect, but: thank you very much for creating, maintaining, and sharing SQLite with the rest of the world!)

12:07 Delete reply: 100x jump in query time with IN operator (artifact: 33515d88e3 user: gunter_hick)
Deleted
12:06 Reply: 100x jump in query time with IN operator (artifact: 5a04e3117b user: gunter_hick)
SQLite NGQP is a cost based query planner. The IN () operator with a list of literal values gets implemented as a kind of temporary table; sometimes SQLite decides to create an index and do lookups, other times it decides to use that table as the outermost loop of the query.

EXPLAIN QUERY PLAN should show that in a more concise manner.

If compiled in DEBUG mode mith WHERETRACE enabled, the .wheretrace command will show how SQLite NGQP reaches its plan. Essential input is the return values from the xBestIndex method of your virtual table, especially the "number of rows" and the "estimated cost". It is paramount to deliver accurate estimates. Cost should reflect processing cost relative to SQLite native tables.

Note that you can name the IN table by making it a CTE and CROSS JOIN to force the query plan that works fast.
12:06 Reply: 100x jump in query time with IN operator (artifact: a3d68ed8b4 user: gunter_hick)
SQLite NGQP is a cost based query planner. The IN () operator with a list of literal values gets implemented as a kind of temporary table; sometimes SQLite decides to create an index and do lookups, other times it decides to use that table as the outermost loop of the query.

EXPLAIN QUERY PLAN should show that in a more concise manner.

If compiled in DEBUG mode mith WHERETRACE enabled, the .wheretrace command will show how SQLite NGQP reaches its plan. Essential input is the return values from the xBestIndex method of your virtual table, especially the "number of rows" and the "estimated cost". It is paramount to deliver accurate estimates. Cost should reflect processing cost relative to SQLite native tables.

Note that you can name the IN table by making it a CTE and CROSS JOIN to force the query plan that works fast.
08:00 Post: 100x jump in query time with IN operator (artifact: e7b9a0d7db user: Prinzhorn)

I have a query that uses WHERE id IN (1,2,3,...) where the list (1,2,3,...) is dynamically generated from an array of integers (not using parameters). Now I have a particular query that takes roughly 500ms with 26623 ids but 50s (100x slower) with 26624 ids. In a different scenario the jump in query time happens between 3071 and 3072 IDs.

This happens in Node.js via better-sqlite3 (with its default compile options https://github.com/JoshuaWise/better-sqlite3/blob/23c56aa77e44689e56c56370345e3c3cc7895128/docs/compilation.md#bundled-configuration) with SQLite 3.36.0.

This query uses a custom virtual table and I'm not sure how to reproduce it without it and if it is part of the issue. This means I can't easily provide steps to reproduce it with vanilla SQLite. But I will provide as much information as I can.

The database contains normalized HTTP traffic. What the query does is get all unique URL parameters with a list of unique values. The search_params virtual table parses a query string and yields all name/value pairs.

This query takes 500ms:

SELECT params.name AS name, json_group_array(DISTINCT params.value) AS "values"
FROM view_requests AS req, search_params(search) AS params
JOIN flows ON flows.request_id = req.id
WHERE flows.id IN (1,2,3,...,26623)
GROUP BY params.name
ORDER BY json_array_length("values") DESC, params.name ASC

If I change the range of IDs up to 26624 it takes 50s. This on its own is already interesting. I must be running into some limitation and maybe it starts paging or something?

Here's what I also found, which is even more interesting. If I make search_params(search) constant, the jump happens from 3071 to 3072 (500x from 10ms to 5s).

SELECT params.name AS name, json_group_array(DISTINCT params.value) AS "values"
FROM view_requests AS req, search_params('?foo=bar') AS params
JOIN flows ON flows.request_id = req.id
WHERE flows.id IN (1,2,3,...,3071)
GROUP BY params.name
ORDER BY json_array_length("values") DESC, params.name ASC

Without the IN condition (running the query on all 31k flows) it takes roughly as long as with 26623 flows.

Now my workaround is to use json_each like this:

SELECT params.name AS name, json_group_array(DISTINCT params.value) AS "values"
FROM view_requests AS req, search_params(search) AS params
JOIN flows ON flows.request_id = req.id
WHERE flows.id IN (SELECT value FROM json_each('[1,2,3,...,26623]'))
GROUP BY params.name
ORDER BY json_array_length("values") DESC, params.name ASC

Which is just as fast but seems to scale effortlessly (and as a bonus can make use of parameters for any number of IDs).

EXPLAIN returns 80k and 9k rows respectively (that sounds like a lot). With the json_each implementation it's like 70 (not k, just 70). Maybe I'm running into problems with the generic virtual table implementation of xBestIndex or xFilter (https://github.com/JoshuaWise/better-sqlite3/blob/master/src/util/custom-table.lzz)? But why would that have any influence on how flows.id is queried?

You can download the EXPLAIN results here:

  • https://drive.protonmail.com/urls/MP56BT56F0#S0VWMC1hD29I
  • https://drive.protonmail.com/urls/QHZV7KWHV0#Yf0DvjP97B2A
  • https://drive.protonmail.com/urls/8VZEQ3JXFR#5P6EEle67dCF
  • https://drive.protonmail.com/urls/5B3D7M3WS8#5xib2GAKU03f
2021-10-24
01:48 Reply: Database on android SD CARD (artifact: 4ac3f373f2 user: slavin)

There's no problem with storing a SQLite database on an SD card. You need all privs for card access, including the privs needed to create new files, and to change file attributes.

You don't really 'install' SQLite. SQLite is an API, a programmer's library, a set of functions your program can call. You build it into your program. If you don't want to call Android's own library

https://developer.android.com/training/data-storage/sqlite

then download the amalgamation source code

https://sqlite.org/download.html

add it to your project, and call that. You can find precompiled binaries for Android on the same download page.

2021-10-23
23:19 Post: Database on android SD CARD (artifact: fec93c0fbf user: Webalchemist)

Hi I hit so Many brick walls trying to make my own database on android without using android studio.

I just want to make calls to the db using shell on my SD card.

Can I install sqlite on my SD card and put the db in the same directory and have it run there?

Please help. Burned up so many hours trying to get this working

2021-10-22
20:07 Reply: Documentation bug regarding max SQL length (artifact: 992d12f58e user: larrybr)

As you say, it is just a doc issue. The discrepancy is now corrected. Thanks.

18:48 Post: Documentation bug regarding max SQL length (artifact: ba0e448646 user: anonymous)

Hi, We recently saw SQLite error code regarding queries being too large. On checking the error code page, the docs mention limit as 1,000,000 (https://www.sqlite.org/rescode.html#toobig)

However the actual limit is 1,000,000,000 as documented here: https://www.sqlite.org/limits.html#max_sql_length, and in the code

https://sqlite.org/src/file?name=src/sqliteLimit.h&ci=trunk

/*
** The maximum length of a single SQL statement in bytes.
**
** It used to be the case that setting this value to zero would
** turn the limit off.  That is no longer true.  It is not possible
** to turn this limit off.
*/
#ifndef SQLITE_MAX_SQL_LENGTH
# define SQLITE_MAX_SQL_LENGTH 1000000000
#endif

Wanted to confirm that this indeed is just a documentation issue, and I'm not misreading the default value.

Regards, Kanishka

More ↓