What steps will reproduce the problem? - storage = "sql" - restart prosody - sqlite3 /var/lib/prosody/prosody.sqlite "pragma journal_mode=wal" - restart prosody - lsof -p `pgrep -f prosody` What is the expected output? What do you see instead? I would expect /var/lib/prosody/prosody.sqlite and /var/lib/prosody/prosody.sqlite-shm having a read lock. But I see no lock. When accessing the DB from another process (e.g. sqlite3 or prosodyctl), the *-shm and *-wal are deleted because of the missing lock, leading to data loss. What version of the product are you using? On what operating system? prosody-trunk 1nightly724-1~jessie on Debian Jessie amd64. lua-dbi is 0.5.hg5ba1dd988961-2 (official Debian package). Please provide any additional information below. I have already filed this as https://github.com/mwild1/luadbi/issues/37 but I'm not sure if it is a luadbi bug, because a small lua test script locks a DB as expected.

create the test db: $ sqlite3 example.sqlite3 sqlite> CREATE TABLE schn (eins,zwo); sqlite> INSERT INTO "schn" VALUES(1,2); sqlite> INSERT INTO "schn" VALUES(2,3); sqlite> pragma journal_mode=wal; sqlite> ^D the "small lua test script" here: local DBI = require('DBI') local dbh = assert(DBI.Connect('SQLite3','example.sqlite3')) local sth = assert(dbh:prepare('select * from schn')) print (sth:execute()) for row in sth:rows(true) do print (row.eins,row.zwo) end os.execute('lsof -p $PPID') print (sth:close()) os.execute('lsof -p $PPID') print (dbh:close())

Did you test with this script running as different users?

Zash, the missing lock can be seen without a second concurrent process. And I tested with a "sleep 20" instead of the lsof, and a concurrent sqlite3 cmdline access to the database, and the locking and access worked fine. Same UID. Different UIDs are not the problem here. The user has write access to the directory (like prosody has on /var/lib/prosody).

Illustrating with lsof output: $ lsof -p `pgrep -f prosody` | grep sody.sqlite lua5.1 32181 prosody mem REG 254,1 32768 131264 /var/lib/prosody/prosody.sqlite-shm lua5.1 32181 prosody 10u REG 254,1 3433472 131105 /var/lib/prosody/prosody.sqlite lua5.1 32181 prosody 11u REG 254,1 1532208 131118 /var/lib/prosody/prosody.sqlite-wal lua5.1 32181 prosody 12u REG 254,1 32768 131264 /var/lib/prosody/prosody.sqlite-shm $ lua test.lua | grep ample.sqli lua 26275 woffs mem-r REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm lua 26275 woffs 3ur REG 0,21 8192 101254 /home/woffs/sqlite3/example.sqlite3 lua 26275 woffs 4u REG 0,21 0 102638 /home/woffs/sqlite3/example.sqlite3-wal lua 26275 woffs 5ur REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm lua 26275 woffs mem-r REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm lua 26275 woffs 3ur REG 0,21 8192 101254 /home/woffs/sqlite3/example.sqlite3 lua 26275 woffs 4u REG 0,21 0 102638 /home/woffs/sqlite3/example.sqlite3-wal lua 26275 woffs 5ur REG 0,21 32768 102639 /home/woffs/sqlite3/example.sqlite3-shm Notice the additional r for "read lock".

Try with dbh:autocommit(false); and explicit BEGIN TRANSACTION and COMMIT, which is what Prosody does.

Looks fine to me: lua5.1 22734 zash 15ur REG 8,6 32768 1441878 /home/zash/src/prosody/trunk/data/prosody.sqlite lua5.1 22734 zash 16u REG 8,6 98912 1444683 /home/zash/src/prosody/trunk/data/prosody.sqlite-wal lua5.1 22734 zash 17ur REG 8,6 32768 1444687 /home/zash/src/prosody/trunk/data/prosody.sqlite-shm On: Debian 9.0 (stretch) libsqlite3-0 3.16.2-3 lua-dbi-sqlite3 0.5.hg5ba1dd988961-4

• tags Status-CantReproduce

I suspect a mod which is interfering here in my case. I remember testing this with and without some modules and I noticed different behaviour and looked into the code and then gave up. This has to be repeated and documented properly.