SQLite User Forum

DB(Encoding UTF-16) read by SQLite ver3.40.1,41.2 is locked.
Login
Hierarchical

DB(Encoding UTF-16) read by SQLite ver3.40.1,41.2 is locked.

(1) By anonymous on 2023-04-16 18:31:43 [link] [source] 

When "DB created with Encoding UTF16" is read by SQLite ver3.40.1or41.2,
DB is locked.
So on Shell, BD can't be vacuumed(Runtime error: database is locked (6)).
Also on DB_Browser_for_SQLite(DLL replaced),
 Database_Structure-tab shows no Tables, Browse_Data-tab shows no Views,
 but Execute_SQL-tab shows View of Query normaly.

(2) By Larry Brasfield (larrybr) on 2023-04-16 18:48:34 in reply to 1 [link] [source]

I'm too busy to play the 20 questions game. Please provide enough information that somebody can replicate your observations. Your post #1 is nowhere close to that.

I can easily create a "DB created with Encoding UTF16", then open it with any recent release of SQLite and run "VACUUM" on it. So you are doing something that creates your problem. What is that? I have no idea, yet.

(3) By brickviking on 2023-04-17 03:21:35 in reply to 1 [link] [source]

I know very little of the problems associated with this, but here's some questions I had for the OP.

  • Do you have an example of where this database file does work?
  • Do you have a backup of this database where it still works?
  • Do you have a version of sqlite3 installed where the database still works?
  • When did you first notice that you couldn't open your database?
  • Have you rebooted since? (Yes, that matters for Windows).
  • What does (DLL replaced) mean?
  • When you say "So on Shell", what shell were you using? The Sqlite3 shell? CMD.exe? Powershell.exe? pwsh.exe? bash?
  • What version of Windows are you on?

I believe that's a few of the twenty questions that larrybr doesn't have the time to ask. I'm not sure what else, but you definitely need to provide us with more information than you have. We know it's a Windows machine, and that's about it.

(Post 14)

(4) By anonymous on 2023-04-18 13:36:36 in reply to 1 [link] [source] 

I'm sorry, description"ver3.40.1" is wrong, "ver3.41.0" is correct.
Ver3.40.1 work normaly, Ver3.41.0 do not.

Sample is following.
sqlite3.exe are get from sqlite-toos-win32-3400100.zip, and 3410000, 3410200.
--------
Microsoft Windows [Version 10.0.22621.1413]
(c) Microsoft Corporation. All rights reserved.

C:\Users\***>cd desktop

C:\Users\***\Desktop>SQLite3
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma encoding=utf16;
sqlite> create table a(b integer);
sqlite> .save a.db
sqlite> .quit

C:\Users\***\Desktop>SQLite3
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma encoding=utf16;
sqlite> create table a(b integer);
sqlite> .save b.db
sqlite> .quit

C:\Users\***\Desktop>SQLite3
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma encoding=utf16;
sqlite> create table a(b integer);
sqlite> .save c.db
sqlite> .quit



C:\Users\***\Desktop>sqlite3 a.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> vacuum;
Runtime error: database table is locked (6)
sqlite> .quit

C:\Users\***\Desktop>sqlite3 b.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> vacuum;
Runtime error: database table is locked (6)
sqlite> .quit

C:\Users\***\Desktop>sqlite3 c.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> vacuum;
Runtime error: database table is locked (6)
sqlite> .quit



C:\Users\***\Desktop>sqlite3 a.db
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
sqlite> vacuum;
Runtime error: database table is locked (6)
sqlite> .quit

C:\Users\***\Desktop>sqlite3 b.db
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
sqlite> vacuum;
Runtime error: database table is locked (6)
sqlite> .quit

C:\Users\***\Desktop>sqlite3 c.db
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
sqlite> vacuum;
Runtime error: database table is locked (6)
sqlite> .quit



C:\Users\***\Desktop>sqlite3 a.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> vacuum;
sqlite> .quit

C:\Users\***\Desktop>sqlite3 b.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> vacuum;
sqlite> .quit

C:\Users\***\Desktop>sqlite3 c.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> vacuum;
sqlite> .quit

C:\Users\***\Desktop>

(5) By jose isaias cabrera (jicman) on 2023-04-18 14:26:05 in reply to 4 [link] [source]

I was able to replicate this with v3.41.2:

10:19:26.18>sqlite3 a.db
-- Loading resources from C:\Users\jcabrera/.sqliterc
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> pragma encoding=utf16;
VM-steps: 4
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> create table a(b integer);
VM-steps: 31
Run Time: real 0.159 user 0.000000 sys 0.000000
sqlite> .q

10:20:48.91>sqlite3 a.db
-- Loading resources from C:\Users\jcabrera/.sqliterc
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> vacuum;
VM-steps: 3
Run Time: real 0.000 user 0.000000 sys 0.000000
Runtime error: database table is locked (6)
sqlite>

(6) By Larry Brasfield (larrybr) on 2023-04-18 14:38:26 in reply to 4 [source]

Thanks for your repro guidance.

Below is a simplified sequence showing:
Creating the DB with 3.41.1
Trying to vacuum as first operation with 3.41.2 but failing
Succeeding with vacuum by doing something else first with 3.41.2
Trying to vacuum as first operation with 3.41.2 but failing
Running a trivial query before succeeding with vacuum with 3.41.2
Trying to vacuum as first operation with 3.41.2 but failing

[C:\\Tmp\\VacLock]
\> C:\\Tmp\\VacLock\\sqlite-tools-win32-x86-3410100/sqlite3.exe aa.db
SQLite version 3.41.1 2023-03-10 12:13:52
Enter ".help" for usage hints.
sqlite\> pragma encoding=utf16;
sqlite\> create table a(b integer);
sqlite\> .q

[C:\\Tmp\\VacLock]
\> C:\\Tmp\\VacLock\\sqlite-tools-win32-x86-3410200/sqlite3.exe aa.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite\> vacuum;
Runtime error: database table is locked (6)
sqlite\> .q

[C:\\Tmp\\VacLock]
\>C:\\Tmp\\VacLock\\sqlite-tools-win32-x86-3410200/sqlite3.exe aa.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite\> .tables
a
sqlite\> vacuum;
sqlite\> .q

[C:\\Tmp\\VacLock]
\> C:\\Tmp\\VacLock\\sqlite-tools-win32-x86-3410200/sqlite3.exe aa.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite\> vacuum;
Runtime error: database table is locked (6)
sqlite\> .q

[C:\\Tmp\\VacLock]
\> C:\\Work\\Projects\\Sqlite\\OrgDevRepo\\SqliteLib\\CliUtf8\\sqlite3.exe aa.db
SQLite version 3.42.0 2023-04-15 16:12:46
Enter ".help" for usage hints.
sqlite\> vacuum;
Runtime error: database table is locked (6)
sqlite\>

This will be sorted out in some fashion. Until then, it appears that running an innocuous query, such as "select * from sqlite_schema limit 0;", before running a VACUUM will mitigate this bug.

(7) By Larry Brasfield (larrybr) on 2023-04-18 15:31:08 in reply to 4 [link] [source]

This bug was just fixed by Richard as check-in 8b0fe63f87366103.

It is noteworthy that this was exposed by a combination of conditions that could easily not be guessed by somebody trying to reproduce the problem. Precise repro steps, even if not minimal, are virtually necessary to find and fix something like this.

(8) By anonymous on 2023-05-01 17:33:38 in reply to 7 [link] [source] 

On sqlit3.c(ver3.41.2) patched with "check-in 8b0fe63f87366103", When Encoding is UTF16, "DB Browser for SQLite" still malfunctions.
>SQLITE_PRIVATE int sqlite3InitOne(sqlite3 *db, int iDb, char **pzErrMsg, u32 mFlags){
>  int rc;
>...
>#endif
>      if( db->nVdbeActive>0 && encoding!=ENC(db)/*Patched Block Start*/&& (db->mDbFlags & DBFLAG_Vacuum)==0/*Patched Block End*/){
>        rc = SQLITE_LOCKED;
>        goto initone_error_out;
>      }else{

The cause is presumed.
When UTF16, "prepare('SELECT...')" before completeing "PRAGMA database_list (also function_list) at first" results in "result code" = 6(database table is locked).
When UTF8 or using old sqlite3.c, Doing the same results in "result code" = 100(another row available).
Sample source code and result are followings.

[soruce code: test.c ----------------------------------------------------]

#include <stdio.h>
#include "sqlite3.h"
int main(){
	sqlite3      *pDB;
	sqlite3_stmt *pStmtP, *pStmtS, *pStmt_;
	char         *zTail;
	printf("UTF8 -----------------------------------------------------------\n");
	printf(   "open: %3d / ", sqlite3_open_v2("8.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("P.dblst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA database_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	printf(   "open: %3d / ", sqlite3_open_v2("8.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("P.fclst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA function_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	//
	printf("UTF16 ----------------------------------------------------------\n");
	printf(   "open: %3d / ", sqlite3_open_v2("16.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("P.dblst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA database_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	printf(   "open: %3d / ", sqlite3_open_v2("16.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("P.fclst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA function_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	// When First PRAGMA Complete
	printf(   "open: %3d / ", sqlite3_open_v2("16.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("P.dblst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA database_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	printf(   "open: %3d / ", sqlite3_open_v2("16.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("P.fclst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA function_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	// When PRAGMA not first
	printf(   "open: %3d / ", sqlite3_open_v2("16.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("SELECT_: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmt_, &zTail)); 
	printf("P.dblst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA database_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "fin__: %3d / ", sqlite3_finalize(pStmt_));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	printf(   "open: %3d / ", sqlite3_open_v2("16.db", &pDB, SQLITE_OPEN_READWRITE, NULL));
	printf("SELECT_: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmt_, &zTail)); 
	printf("P.fclst: %3d / ", sqlite3_prepare_v2(pDB, "PRAGMA function_list"       , -1, &pStmtP, &zTail));
	printf(   "step: %3d / ", sqlite3_step(pStmtP));
	printf("SELECT*: %3d / ", sqlite3_prepare_v2(pDB, "SELECT * FROM sqlite_schema", -1, &pStmtS, &zTail)); 
	printf(   "step: %3d / ", sqlite3_step(pStmtS));
	printf(  "fin_P: %3d / ", sqlite3_finalize(pStmtP));
	printf(  "fin_S: %3d / ", sqlite3_finalize(pStmtS));
	printf(  "fin__: %3d / ", sqlite3_finalize(pStmt_));
	printf(  "close: %3d\n" , sqlite3_close_v2(pDB));
	return 0;
}

[result ---------------------------------------------------------------]

**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.2.6
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x64'

C:\Program Files\Microsoft Visual Studio\2022\Community>cd C:\Users\___\Desktop\!\sqlite-amalgamation-3410200

C:\Users\___\Desktop\!\sqlite-amalgamation-3410200>cl shell.c sqlite3.c /Fesqlite3.exe
Microsoft (R) C/C++ Optimizing Compiler Version 19.32.31332 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

shell.c
sqlite3.c
Generating Code...
Microsoft (R) Incremental Linker Version 14.32.31332.0
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:sqlite3.exe
shell.obj
sqlite3.obj

C:\Users\___\Desktop\!\sqlite-amalgamation-3410200>sqlite3.exe
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table a(b int);
sqlite> .save 8.db
sqlite> .quit

C:\Users\___\Desktop\!\sqlite-amalgamation-3410200>sqlite3
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma encoding=utf16;
sqlite> create table a(b int);
sqlite> .save 16.db
sqlite> .quit


C:\Users\___\Desktop\!\sqlite-amalgamation-3410200>cl test.c sqlite3.c
Microsoft (R) C/C++ Optimizing Compiler Version 19.32.31332 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

test.c
sqlite3.c
Generating Code...
Microsoft (R) Incremental Linker Version 14.32.31332.0
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:test.exe
test.obj
sqlite3.obj

C:\Users\___\Desktop\!\sqlite-amalgamation-3410200>test.exe
UTF8 -----------------------------------------------------------
open:   0 / P.dblst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
UTF16 ----------------------------------------------------------
open:   0 / P.dblst:   0 / step: 100 / SELECT*:   6 / step:  21 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / SELECT*:   6 / step:  21 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.dblst:   0 / step: 100 / step: 101 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / step: 100 / SELECT*:   6 / step:  21 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / SELECT_:   0 / P.dblst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / fin__:   0 / close:   0
open:   0 / SELECT_:   0 / P.fclst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / fin__:   0 / close:   0

C:\Users\___\Desktop\!\sqlite-amalgamation-3410200>cd ..\sqlite-amalgamation-3400100


C:\Users\___\Desktop\!\sqlite-amalgamation-3400100>copy ..\sqlite-amalgamation-3410200\*.db .
..\sqlite-amalgamation-3410200\16.db
..\sqlite-amalgamation-3410200\8.db
        2 file(s) copied.

C:\Users\___\Desktop\!\sqlite-amalgamation-3400100>cl ..\sqlite-amalgamation-3410200\test.c sqlite3.c
Microsoft (R) C/C++ Optimizing Compiler Version 19.32.31332 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

test.c
sqlite3.c
Generating Code...
Microsoft (R) Incremental Linker Version 14.32.31332.0
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:test.exe
test.obj
sqlite3.obj

C:\Users\___\Desktop\!\sqlite-amalgamation-3400100>test.exe
UTF8 -----------------------------------------------------------
open:   0 / P.dblst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
UTF16 ----------------------------------------------------------
open:   0 / P.dblst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.dblst:   0 / step: 100 / step: 101 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / SELECT_:   0 / P.dblst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / fin__:   0 / close:   0
open:   0 / SELECT_:   0 / P.fclst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / fin__:   0 / close:   0

C:\Users\___\Desktop\!\sqlite-amalgamation-3400100>

(9.1) By Larry Brasfield (larrybr) on 2023-05-01 18:50:21 edited from 9.0 in reply to 8 [link] [source]

After boiling your voluminous post down to "I used a later SQLite version CLI to create a DB with UTF-16 encoding", my reading of it (which is admittedly going between the literal text) is that you still see the problem in "DB Browser for SQLite".

I see nothing in your post to suggest that you have incorporated the newer SQLite code, (which I claim cures the reported problem), into that 3rd-party application. Nor do you indicate in any way that said application has been updated and you are using the updated version. Hence, I find your assertion, "'DB Browser for SQLite' still malfunctions.", to be unsurprising, even expected.

To be clear: The SQLite project does not author or publish "DB Browser for SQLite". If you want it to incorporate later versions of the SQLite library, you need to either build it yourself or find somebody who will do it for you. That somebody might be whoever is responsible for that 3rd-party application.

(Appended via edit:)

The problem that was fixed 13 days ago is manifested when a UTF-16 encoded DB is first opened, then immediately has VACUUM executed on it. The fix only affects that sequence, it has nothing to do with creating a DB. So to enjoy the fix, whatever does the open/VACUUM sequence has to incorporate that bug fix into its embedded version of the SQLite library.

(10) By anonymous on 2024-11-02 19:36:10 in reply to 9.1 [link] [source]

I'm one of the developers of "DB Browser for SQLite" (mgrojo).

Is this supposed to be fixed in the latest version (3.47.0)?

What I see is that even including this latest version, the problem described by the previous anonymous user is still present (not related to VACUUM, but just with the operations performed by DB Browser when opening the database file). SQLite 3.15.2 and 3.37.2 work with the same DB Browser version.

Affected AppImage including SQLite 3.47.0 in https://github.com/sqlitebrowser/sqlitebrowser/releases/download/continuous/DB.Browser.for.SQLite-dev-5ff8793-x86.64.AppImage

Working AppImage including SQLCipher Version 3.4.1 (based on SQLite 3.15.2) in https://github.com/sqlitebrowser/sqlitebrowser/releases/download/v3.13.0/DB.Browser.for.SQLite-v3.13.0-x86.64.AppImage

Steps to reproduce in https://github.com/sqlitebrowser/sqlitebrowser/issues/3714

Interesting is also that changes performed in this pull request seem to hide the problem: https://github.com/sqlitebrowser/sqlitebrowser/pull/3792/files

The changes consist in detecting the encoding before opening the DB file and then using sqlite3_open16() to open it if the UTF-16 encoding is detected.

(11) By Stephan Beal (stephan) on 2024-11-03 01:14:33 in reply to 10 [link] [source]

Steps to reproduce in https://github.com/sqlitebrowser/sqlitebrowser/issues/3714

After scouring those comments i'm not seeing any steps to reproduce which don't involve an AppImage of DB Browser or SQLite.

Have you got a reproduction which will work as-is in the sqlite CLI shell or with the C API?

The "stickies" db posted in one of the comments, which says it doesn't work in DB Browser 3.13, opens just fine with version 3.47:

$ sqlite3 stickies.db 
SQLite version 3.47.0 2024-09-05 12:06:45
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE [categories] ([key] INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, [name] TEXT, [flags] INTEGER, [parent] INTEGER, [next] INTEGER, [sysmod] INTEGER);
... big snip ...

sqlite> pragma encoding;
UTF-16le

.

Interesting is also that changes performed in this pull request seem to hide the problem: https://github.com/sqlitebrowser/sqlitebrowser/pull/3792/files

That change seems misled. According to the API docs, that will make no difference on existing databases (and the change is using a header byte from an existing db). Using sqlite3_open16() has two differences from sqlite3_open():

  • Its filename argument is required to be in UTF-16 encoding
  • It applies different flags than sqlite3_open() when creating a new db

(12) By anonymous on 2024-11-03 11:05:47 in reply to 11 [link] [source]

Have you got a reproduction which will work as-is in the sqlite CLI shell or with the C API?

The reproducer with the C API was already provided by (8)

After building and installing the latest SQLite version sqlite-autoconf-3470000.tar.gz, I get the same results (the return code 6 "A table in the database is locked" shows the problem):

UTF8 -----------------------------------------------------------
open:   0 / P.dblst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
UTF16 ----------------------------------------------------------
open:   0 / P.dblst:   0 / step: 100 / SELECT*:   6 / step:  21 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / SELECT*:   6 / step:  21 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.dblst:   0 / step: 100 / step: 101 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / P.fclst:   0 / step: 100 / step: 100 / SELECT*:   6 / step:  21 / fin_P:   0 / fin_S:   0 / close:   0
open:   0 / SELECT_:   0 / P.dblst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / fin__:   0 / close:   0
open:   0 / SELECT_:   0 / P.fclst:   0 / step: 100 / SELECT*:   0 / step: 100 / fin_P:   0 / fin_S:   0 / fin__:   0 / close:   0

That change seems misled. According to the API docs, that will make no difference on existing databases (and the change is using a header byte from an existing db). Using sqlite3_open16() has two differences from sqlite3_open():

I also think the change is misled, that's why I didn't approve the pull request, and investigating the problem I reached here. Everything you should need is already in (8). Nevertheless, the mere fact that those changes hide the problem seems an evidence that there is a bug somewhere. If the bug is in the C reproducer or in DB Browser, we would like to know why it works with UTF-8 and previous SQLite versions.

(13.2) By Stephan Beal (stephan) on 2024-11-05 07:10:33 edited from 13.1 in reply to 12 [link] [source]

The reproducer with the C API was already provided by (8)

This was tracked down and resolved today: src:af7173a10ec6. Edit: please disregard, that was too hasty. That fixes a related problem but apparently does not resolve the OP's specific report.

Edit again (sigh): This was tracked down and resolved today: src:af7173a10ec6. My report of it not being fixed was prompted by my testing of a stale copy of the repro binary.