SQLite Forum

Timeline
Login

50 most recent forum posts by user dan

2021-11-30
14:15 Reply: SQLIte 3.37.0 breaks SQLITE_OPEN_NOMUTEX - TSAN failure (artifact: cc6668e7b3 user: dan)

The line numbers in this report don't seem to make much sense:

Read of size 4 at 0x55c2f79772f8 by thread T4 (mutexes: write M509514):#0 sqlite3VdbeExec /home/lewis/Sandbox/Stroika-Build-Dir-Ubuntu1804_x86_64/IntermediateFiles/g++-debug-sanitize_thread/ThirdPartyComponents/sqlite/sqlite3.c:88607 (Test36+0x492c1b1)

withPrevious write of size 4 at 0x55c2f79772f8 by thread T3 (mutexes: write M508861):#0 sqlite3WritableSchema /home/lewis/Sandbox/Stroika-Build-Dir-Ubuntu1804_x86_64/IntermediateFiles/g++-debug-sanitize_thread/ThirdPartyComponents/sqlite/sqlite3.c:114046 (Test36+0x4a111a5)

The conflict appears as follows:testcase( pIn1->flags & MEM_Int ); // READ IN ONE THREAD

Fair enough - but *pIn1 is part of the VM being run. No other thread should be able to get near it, with or without a mutex.

after write:testcase( (db->flags&(SQLITE_WriteSchema SQLITE_Defensive))==0 ); IN ANOTHER THREAD

And this isn't actually a write. Or anywhere near anything that should be touching anything accessed by the other thread.

Do any of the other valgrind modules show any complaints with this test?

Can you try to get the same stack traces etc. from a non-optimized binary?

Thanks,

Dan.

10:58 Edit reply: unixFcntlExternalReader not found if using SQLITE_OMIT_WAL (artifact: 6b5933cd04 user: dan)

Should be fixed here, for 3.37.0:

https://sqlite.org/src/info/948c2cb2a2f44ba0

Dan.

10:58 Reply: unixFcntlExternalReader not found if using SQLITE_OMIT_WAL (artifact: b5acf19435 user: dan)

Should be fixed here, for 3.37.0:

https://sqlite.org/src/info/948c2cb2a2f44ba0

Dan.

10:42 Reply: Error while executing query, no column with such name exists (artifact: 21e6024666 user: dan)

No "new" row for delete triggers. Maybe it should be "old.ID".

Dan.

07:14 Reply: compiler warning on Version 3.37.0 (2021-11-27). (artifact: fccec89e7d user: dan)

Is that the only instance of this warning, or are there many others?

Dan.

2021-10-19
10:59 Reply: SQLite Page Cache Subsystem as Library (artifact: 540c87005c user: dan)

I think it's possible, but far from straightforward. The proprietary ZipVFS extension does just that - uses the pager layer to store data in a different file format with ACID properties.

2021-10-13
15:26 Reply: Different time spent in Windows and Linux (artifact: d8db57418b user: dan)

It may be that the file-system on Windows is not syncing to disk when asked to, whereas Linux is.

Grouping your write operations into a transaction or transactions should help.

2021-09-28
11:24 Reply: Unable to search an FTS5 trigram table when using external content (artifact: 413819ed72 user: dan)

An external content table is like a contentless table in that the user has to arrange to update the FTS index using INSERT statements to keep it in sync with the external table.

    sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
    sqlite> INSERT INTO test(data) VALUES('testing foo bar');
    sqlite> CREATE VIRTUAL TABLE fts USING fts5(data, content=test, content_rowid=id, tokenize='trigram');
    sqlite> INSERT INTO fts(rowid, data) SELECT id, data FROM test;
    sqlite> SELECT * FROM fts('foo');
    testing foo bar

One way to do this is with triggers, another is to have your app update both the external content table and the fts index at the same time. See documentation here:

https://sqlite.org/fts5.html#external_content_and_contentless_tables

For small tables, the 'rebuild' command can be useful:

https://sqlite.org/fts5.html#the_rebuild_command

2021-09-27
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?

2021-09-18
18:13 Reply: sqlite-android-3360000.aar / SQLiteOpenHelper / getWritableDatabase() / unknown error (code 14): Could not open database (artifact: 00d636e060 user: dan)

I think it's a long-standing bug introduced along with support for file:// style filenames. Can you try with this version?

https://sqlite.org/android/info/97dc797d2f1c589d

Dan.

2021-09-17
11:25 Reply: Memory-mapped IO used for temp files even though memory-mapped I/O is not used (artifact: 04b2595910 user: dan)

Current thinking is that memory mapping isn't all that much of an advantage when sorting data. Not on modern Linux (and presumably other modern OS versions as well) anyway. So it will be turned off by default for 3.37.0:

https://sqlite.org/src/info/306694dfb462f9d1

Dan.

11:18 Reply: FTS5: use = or MATCH to compare IDs? (artifact: 1782f9097f user: dan)

Are the values in column post_fts.id a set of distinct integers? If so, you could just use the "rowid" field for the id.

Otherwise, assuming they are integer values (with no embedded spaces) in the post_fts.id column, I think MATCH should work quite well. The trouble comes if you have a text value like:

'123 456 789'

then the constraints "id = ?" and "id MATCH ?" may match different sets of rows. At that point I think you have to use an "external content table" that you can create an index on:

https://www.sqlite.org/fts5.html#external_content_and_contentless_tables

Dan.

2021-09-16
19:43 Edit reply: Javascript enforcement (artifact: cbfeca1fc7 user: dan)

It leads to a litterling of the world with useless Javascript.

The correct way to achieve the objective is to use plain links and a robots.txt file in the root. If some crawler disregards the robots.txt file, you nuke that sucker from orbit.

Javascript is evil. Javascript is the root of all evil. Badly written javascript (and there has never existed goodly-written-javascript in the entire history of the universe) is responsible for 99.999% of all safety and security issues since it was barfed-up by a moron.

2021-09-14
14:03 Reply: DB with Fts4 passes integrity check, but throws SQLITE_CORRUPT_VTAB (database disk malformed) whenever column is queried (artifact: c8a686f349 user: dan)

Corrupt or missing records in the shadow tables used by fts4.

You might be able to fix things by rebuilding the index:

    INSERT INTO fts3_tbl_name(fts3_tbl_name) VALUES('rebuild');

If you're doing new development, fts5 is much better than fts4.

Dan.

2021-09-10
11:14 Edit reply: solved disk I/O error on F2FS (artifact: 6b076fdf63 user: dan)

Can't reproduce this. The only options you have that I'm not using are alloc_mode=default and fsync_mode=posix (kernel is too old here), but they're the default values anyway.

So, if you start with no database, create it, then query it, you get the error? i.e. the third command here reports an IO error, correct?

rm a.db
sqlite3 a.db "CREATE TABLE t1(x, y)"
sqlite3 a.db "SELECT * FROM sqlite_schema"

If so, can you try running:

rm a.db
sqlite3 a.db "CREATE TABLE t1(x, y)"
strace -o strace_error.log sqlite3 a.db "SELECT * FROM sqlite_schema"

and then post the contents of file "strace_error.log" for us?

Thanks,

Dan.

11:13 Reply: solved disk I/O error on F2FS (artifact: edf23e3c3e user: dan)

Can't reproduce this. The only options you have that I'm not using are alloc_mode=default and fsync_mode=posix (kernel is too old here), but they're the default values anyway.

So, if you start with no database, create it, then query it, you get the error, correct? i.e. the third command here reports an IO error, correct?

rm a.db
sqlite3 a.db "CREATE TABLE t1(x, y)"
sqlite3 a.db "SELECT * FROM sqlite_schema"

If so, can you try running:

rm a.db
sqlite3 a.db "CREATE TABLE t1(x, y)"
strace -o strace_error.log sqlite3 a.db "SELECT * FROM sqlite_schema"

and then post the contents of file "strace_error.log" for us?

Thanks,

Dan.

2021-09-03
11:32 Reply: Virtual table ORDER BY and GT/LT (GE/LE) constraints expected behavior - full table scan when not needed? (artifact: 5efafa1334 user: dan)

My assumption would be that SQLite would know then to stop iteration when the virtual table reaches 10, since the BestIndex indicates that the results are sorted by this field.

Instead, the behavior I've observed is that SQLite does a "full scan" regardless, iterating down to 1, even though it ultimatelt discards those results less than 10 when returning results.

I'm wondering if this is expected behavior and I would need to implement a "stop" on the virtual table iteration to avoid the full scan, myself. If not, it may indicate something I'm doing incorrectly in my BestIndex/Filter implementation.

SQLite isn't quite smart enough to do that. It will take all the rows you can give it and test each one for (field>10), returning those that match to the user.

You'll need to handle the constraint in the usual manner - by setting the argvIndex member so that the RHS value is passed to the xFilter callback and so on.

Dan.

2021-09-02
06:32 Reply: SEGV in online backup API (artifact: 0707e14052 user: dan)

Thanks for reporting this. An assert() fails in debug mode too.

I think this test case is expected to malfunction. See the second paragraph under "Concurrent Usage of Database Handles" here:

https://www.sqlite.org/c3ref/backup_finish.html

As you say, we should be able to avoid the segfault though.

Dan.

2021-08-30
18:34 Reply: View with CTE no longer works (artifact: f78dcaf99e user: dan)

The behaviour of earlier versions was deemed a bug and was fixed here:

https://sqlite.org/src/info/f7dcc4b5

Dan.

17:11 Reply: Unexpected result in a equivalence transfer query (artifact: 6d97ad7383 user: dan)

Thanks for this. It was as you suggest - the equivalence transfer problem was still lurking for "INTEGER PRIMARY KEY" indexes. Now fixed here:

https://sqlite.org/src/ci/46e28cbc

Cheers,

Dan.

2021-08-20
10:51 Reply: SIGBUS in sqlite (artifact: c051e19e55 user: dan)

It might if you delete a db file while SQLite has it open. Something like:

  1. Open handle A,
  2. Read from handle A,
  3. Delete db file with java interface,
  4. Open handle B,
  5. Read from handle B,
  6. Close handle A.
  7. Read from handle B.

Will produce the crash you're seeing on Linux in the final step. There are probably other recipes.

2021-08-18
11:19 Reply: Page-level locking? (artifact: 95dadfa1a8 user: dan)
11:16 Reply: SIGBUS in sqlite (artifact: 133dd4d0f9 user: dan)

I think the post you're answering quite reasonably suspects this problem:

https://www.sqlite.org/howtocorrupt.html#_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_

Are you accessing any files directly, bypassing the SQLite library? Or is it possible that there are two copies of the SQLite library linked into the application and being used to access this database?

2021-08-09
11:05 Reply: WAL2 branch build error (artifact: 6c0c979f17 user: dan)

Quite correct I think. Merging the latest trunk changes included this identical patch:

https://www.sqlite.org/src/info/5150d2da06ab3c7e

The tip of wal2 should build now.

Dan.

2021-07-29
11:08 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: b080c1d935 user: dan)

That's not supposed to happen. Although I suppose you already knew that.

Can you try to compose an example of the problem that we can use to reproduce it?

Dan.

2021-07-28
18:54 Reply: Entries seen in WAL missing when reading DB (artifact: dcf5ba42db user: dan)

Tricky to really say anything about this.

"showwal" is a tool for examining wal files. You can build it from the full source tree with "./configure && make showwal".

Dan.

2021-07-21
16:12 Reply: SQLite diff tool doesn't work with CRLF new line characters (artifact: 08f18484f9 user: dan)

If you pipe the output of that sqldiff.exe command into a file and then inspect the results with a hex editor or similar, does it look like well-formed utf-8?

Or, if you run:

    SELECT hex(tbl."Test Text") FROM "Test Table" AS tbl WHERE rowid=3

does it look like well-formed utf-8 or utf-16 text?

Dan.

2021-07-19
14:19 Reply: Very obscure bug involving FTS5, triggers, RETURNING, and DBCONFIG_DEFENSIVE (artifact: bdeee26d9a user: dan)

Thanks for reporting this. Should now be fixed here:

https://sqlite.org/src/info/c8601d83fbecf84c

Dan.

2021-07-17
14:25 Reply: (Deleted) (artifact: 4004ba3b47 user: dan)

"IN" is a keyword. I think you'll need to call the column something else.

Can you see the error message from SQLite? It should have said as much.

2021-07-09
11:34 Reply: Custom error messages for required table-valued function parameter (artifact: 8f25aef825 user: dan)

If returning SQLITE_ERROR for a missing required parameter is expected to work in all cases, let me know, and I can repro the problem I was having with that approach.

I think that's the right approach. Code would be great, but we probably only need the virtual table schema (whatever is passed to sqlite3_declare_vtab()), the SQL query, and the details of which parameters are required to look into the problem.

I think it had to do with left joins when this function might have no input available or something like that.

That the tricky cases have to do with LEFT JOIN does sound quite plausible..

Dan.

10:55 Reply: Error message from table-valued function xConnect is ignored (artifact: 124db88419 user: dan)

Thanks for reporting this. Should now be fixed here:

https://sqlite.org/src/info/bbbbeb59a6a14b94

Dan.

2021-07-08
18:25 Edit reply: Custom error messages for required table-valued function parameter (artifact: a152ff34b0 user: dan)

The SQLITE_CONSTRAINT return is useful for table-valued functions that have required parameters. If the aConstraint[].usable field is false for one of the required parameter, then the xBestIndex method should return SQLITE_CONSTRAINT.

You should return SQLITE_CONSTRAINT if the required parameter is present in the aConstraint[] array but with usable=0. No need for an error message in this case - some other invocation of xBestIndex will have usable=1 for the required parameter and the vtable will be able to proceed. If the required parameter is not present in aConstraint[] at all, return SQLITE_ERROR and an error message.

Dan.

18:24 Reply: Custom error messages for required table-valued function parameter (artifact: c16b292c21 user: dan)

The SQLITE_CONSTRAINT return is useful for table-valued functions that have required parameters. If the aConstraint[].usable field is false for one of the required parameter, then the xBestIndex method should return SQLITE_CONSTRAINT.

You should return SQLITE_CONSTRAINT if the required parameter is present in the aConstraint[] array but with usable=0. No need for an error message in this case - some other invocation of xBestIndex will have usable=1 for the required parameter and the vtable will be able to proceed. If the required parameter is not present in aConstraint[] at all, return SQLITE_ERROR an error message.

Dan.

2021-07-02
11:10 Reply: single writer, multiple dirty readers (artifact: 6384df5b23 user: dan)

Shared cache mode generally offers less concurrency than wal mode. For starters, all queries will be serialized by a single mutex belonging to the shared cache.

I might be missing something, but I don't think looking to enhance performance by allowing dirty reads really makes much sense in SQLite's single-writer/multi-reader architecture. Each reader sees a version of the database image representing the open snapshot, not a set of rows that need to be filtered to match the open snapshot based on transaction ids and whatnot.

Dan.

11:00 Reply: fts5 rowid peculiarity in UPDATE FROM (artifact: 56143a0044 user: dan)

It is as you say.

For any "rowid" reference, SQLite first searches for an explicit user-supplied rowid column. If it finds one, all good. If it finds more than one, the reference is ambiguous. If it finds zero such columns, it searches for implicit rowid columns and handles them the results the same way - one match is good, more than one is an error.

So if you mix rowid types in a single query, "rowid" will always match the user specified column.

I'm not sure you would necessarily design things this way if you were starting over, but the thing to do now is to avoid inadvertently introducing changes that break existing queries.

2021-07-01
18:29 Reply: fts5 rowid peculiarity in UPDATE FROM (artifact: 05e4cdc1b0 user: dan)

Just for historical curiosity I wonder what was the objective of not using docid alias from the start?

Just didn't see any point to it at the time I suppose. And you have to figure out what to do about this:

    INSERT INTO fts3(rowid, docid, cols) VALUES(1, 2, ...);

The problem with SQLite not allowing "rowid" in the join query, even though the reference is not ambiguous, is fixed here, btw:

https://www.sqlite.org/src/info/d4097364c511709b

So the UPDATE FROM on the fts5 table should now work with trunk. Or 3.37.0, when it is released.

Dan.

2021-06-29
17:18 Reply: Crashed at sqlite3LeaveMutexAndCloseZombie+452 (artifact: 82aea19a0b user: dan)

3.22 is 3.5 years old. Lots of bugs fixed since then. I don't recall anything like this though.

If you don't upgrade, it might be worth running your app under valgrind or asan or similar. There shouldn't be NULL values in that hash table. Something is going awry.

Dan.

17:09 Reply: fts5 rowid peculiarity in UPDATE FROM (artifact: 3acfd1daf9 user: dan)

Internally, that statement executes:

SELECT ... FROM ftsindex5, (SELECT 1234 AS modified) WHERE rowid=modified

And it turns out that you can't use an unqualified "rowid" in any SELECT statement with more than one table in the join or you get the error.

This is an anachronism I think. There was a time when all tables/views/sub-selects in SQLite had an accessible rowid - even (SELECT 1234). So this case couldn't come up then. But now it can.

Dan.

2021-06-28
10:57 Reply: Am I doing SQL wrong? (artifact: 3d732695f5 user: dan)

Seems to be working correctly to me.

Suspect you need another 3 cases in your switch statement:

 CASE
  WHEN column1 > column2 AND column1 < column3 THEN column1
  WHEN column2 > column1 AND column2 < column3 THEN column2
  WHEN column3 > column1 AND column3 < column2 THEN column3

  WHEN column1 < column2 AND column1 > column3 THEN column1
  WHEN column2 < column1 AND column2 > column3 THEN column2
  WHEN column3 < column1 AND column3 > column2 THEN column3

 ELSE -1
2021-06-26
13:47 Reply: Use the Sessions extension from Tcl? (artifact: bb3c0e3bf4 user: dan)

That's correct, you need some sort of wrapper code to call the C API from Tcl. It's quite an extensive interface too. I don't think that any such package exists in a ready-to-use form at present.

The wrapper code that we use for testing sessions is here:

http://www.sqlite.org/src/artifact/f433f68a8a8c6

It might be useful if you decide to develop a sessions wrapper yourself. Or if you just want to extract some functions to call the parts of sessions your app needs.

Dan.

2021-06-23
18:27 Reply: SIGBUS errors in HHVM (artifact: 092dced0a6 user: dan)

sqlite3WalFindFrame() accesses the *-shm file via an mmap() mapping. So one way this could happen is if some external program or malfunctioning SQLite library is truncating the *-shm file while it is being used.

In 2012, it looks like the SIGBUS happened because there was insufficient disk space, and Linux doesn't actually allocate the page until the first time it is accessed. So the first time SQLite accessed the page - SIGBUS. This can't happen if you use fallocate() (or a series of write() calls) to preallocate the pages.

After the crash happens, is the *-shm file left on disk zero bytes in size?

Dan.

11:14 Reply: An infinite recursive exception (artifact: 39824788f1 user: dan)

Thanks for this! Now fixed here:

https://sqlite.org/src/info/710f75b98bb4ac5b

Dan.

2021-06-21
13:45 Reply: FILTER clause with window functions (artifact: 8e1ecfe02d user: dan)

They're described here:

https://www.sqlite.org/windowfunctions.html#built_in_window_functions

Like the other "built-in" window functions, they don't support the FILTER clause. I think it's a standards thing. Postgres has the same restriction.

You could use the sqlite3_create_window_function() API to create versions of first_value() and last_value() that worked like regular aggregate window functions:

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

last_value() is quite trivial, and first_value() wouldn't be all that complicated either. A user implemented first_value() would use a little extra memory I suppose, but that might not be a problem in most cases.

Dan.

2021-06-11
11:39 Reply: Vritual Tables Performing SQL Queries (artifact: 12f01b3fbf user: dan)

An (sqlite3*) handle is passed to the xConnect/xCreate method. You can save a copy in your sqlite3_vtab structure and use it from within any of the other methods.

This is what fts5, rtree and lots more do.

Dan.

2021-06-09
18:10 Reply: different query plan result when executed within transaction (artifact: 33f13a91e2 user: dan)

Not that I know of. AFAIK a query plan should depend on the schema and contents of the sqlite_stat* tables only, not whether or not a transaction is open.

Dan.

2021-06-05
14:35 Reply: Opening DB from readonly filesystem (btrfs snapshot) (artifact: 42bcda311d user: dan)

To open a wal mode database on a read-only medium, use a URI filename and add the immutable=1 argument.

https://sqlite.org/uri.html#recognized_query_parameters

2021-06-02
14:20 Reply: Bug report: a bug in VIEW (artifact: 79d53aa4ac user: dan)

I think this is a consequence of the oddity described here:

https://www.sqlite.org/datatype3.html#affcompoundview

The expression is true if "c0" is assigned INTEGER affinity, but false if it is assigned BLOB affinity. And since which affinity the column is assigned is indeterminate, so are the results of the query.

Dan.

2021-05-25
16:15 Reply: sqlite3session_changeset fails with SQLITE_NOMEM when sqlite3_session >= 1.0 GB (artifact: 71832b222c user: dan)
16:14 Reply: DUMP issue with FTS5 (artifact: 2c50cd8b6a user: dan)
2021-05-24
20:58 Reply: DUMP issue with FTS5 (artifact: 38166dd668 user: dan)

Looks like it does work if you just run plain ".dump", but if you do ".dump tblname" you don't get the shadow tables that actually contain the data. Another workaround would be ".dump tblname%". e.g.

sqlite> .dump t1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','t1','t1',0,'CREATE VIRTUAL TABLE t1 USING fts5(x)');
PRAGMA writable_schema=OFF;
COMMIT;

sqlite> .dump t1%
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','t1','t1',0,'CREATE VIRTUAL TABLE t1 USING fts5(x)');
CREATE TABLE IF NOT EXISTS 't1_data'(id INTEGER PRIMARY KEY, block BLOB);
INSERT INTO t1_data VALUES(1,X'');
INSERT INTO t1_data VALUES(10,X'00000000000000');
CREATE TABLE IF NOT EXISTS 't1_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 't1_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 't1_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 't1_config'(k PRIMARY KEY, v) WITHOUT ROWID;
INSERT INTO t1_config VALUES('version',4);
PRAGMA writable_schema=OFF;
COMMIT;

sqlite> .dump 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','t1','t1',0,'CREATE VIRTUAL TABLE t1 USING fts5(x)');
CREATE TABLE IF NOT EXISTS 't1_data'(id INTEGER PRIMARY KEY, block BLOB);
INSERT INTO t1_data VALUES(1,X'');
INSERT INTO t1_data VALUES(10,X'00000000000000');
CREATE TABLE IF NOT EXISTS 't1_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 't1_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 't1_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 't1_config'(k PRIMARY KEY, v) WITHOUT ROWID;
INSERT INTO t1_config VALUES('version',4);
PRAGMA writable_schema=OFF;
COMMIT;

More ↓