"database is locked" error upon login

[blacklight]

Upon pairing, the contacts briefly appear in my list, but then the account is immediately disconnected. Relevant error:

Error: [Client] Failed to do initial fetch of app state critical_unblock_low: failed to decode app state critical_unblock_low patches: failed to update state hash: failed to get value MAC of previous SET operation: database is locked

The problem is that the initialization isn't properly completed, so upon reconnection a QR code is prompted again to pair.

My guess is that some locking issues occur when writing data to the SQLite db, but that's as far as my analysis can currently go. The whatsmeow.db exists and I can access it over SQLite.

I am using Bitlbee on irssi, Arch Linux, and the latest main branch of the plugin.

[hoehermann]

I was afraid this would become an issue sooner or later. I heavily rely on go's multithreading mechanisms, but being implemented in C, the sqlite driver has some limitations. You may try some of the suggestions mentioned in this report.

I just noticed I have some control over the number of connections. Feel free to try the dev branch.

Other than that, I have no idea on how to overcome this reliably. I suspect you can switch to the postgres backend. I have never tested PostgreSQL since most users would prefer MySQL anyway. Unfortunately, as of today, my pull-request has not been considered.

[blacklight]

This workaround (shared cache + RWC mode) fixed things for now:

account whatsapp set database-address file:$purple_user_dir/whatsmeow.db?_foreign_keys=on&_busy_timeout=3000&cache=shared&mode=rwc

But I'm well aware of the limitations of SQLite when it comes to concurrent requests. In Platypush I eventually resorted to have a single entry point for insert/update guarded by a reentrant lock - it may impact performance, but at least it keeps the concurrency issues at bay. Maybe a similar solution could be considered for the db requests here?

I'm also happy to use an external db - I don't mind either MySQL nor Postgres. Actually, it'd work even better for me, because I could use one of my db servers to store all of my account data and reuse it across multiple machines. But I haven't found any documentation on how to set it up. After looking at go-sql-driver, I tried with something like this:

account whatsapp set database-address mysql:user:pwd@tcp(host:3306)/dbname

But it fails on connection with:

whatsmeow database driver is unable to establish connection to user:pwd@tcp(host:3306)/dbname due to failed to upgrade database: Error 4161: Unknown data type: 'bytea'

[hoehermann]

Thank you for your valuable input! It is good to know there is a workaround.

I eventually resorted to have a single entry point for insert/update guarded by a reentrant lock (…). Maybe a similar solution could be considered for the db requests here?

That is the first approach I considered. It worked perfectly in Pidgin. Unfortunately, it created problems in spectrum since the plug-in is loaded multiple times concurrently.

whatsmeow (the library I am depending on) currently supports sqlite3 and postgres. I prepared a patch adding support for MySQL, but it has not been accepted (yet): https://github.com/tulir/whatsmeow/pull/48