Signal Desktop Corrupted Database and won't accept previous backed up DB

[ddshore]

I'm running signal desktop, and my computer shut off unexpectedly. That corrupted my database and I get an error saying file is not a database. Looking into it, it seems that this is a behavior that can happen sometimes (at least from reading the issues). I have a backup of my Signal folder that's about a week old, and I want to restore it. When I did the backup signal was working perfectly fine. I tried copying it to ~/.config/Signal Beta and replacing my old directory, however, I still get a corrupted database error.

I have three files in my backed up database (db.sqlite, db.sqlite-shm, and db.sqlite-wal). Whenever I move the backed up to my .config folder, two of the files are deleted (shm + wal).

If I try to open it in sqlcypher and sqlite I just get an error saying that the file is not a database (but I get the same error for a functioning database so I guess that's meaningless).

How could I restore the original database or my backed up database to my current installation? The device is already linked so I'm not linking a new device. Could this be an error with the latest beta version and not a database corruption?

ERROR:

[hiqua]

I have three files in my backed up database (db.sqlite, db.sqlite-shm, and db.sqlite-wal). Whenever I move the backed up to my .config folder, two of the files are deleted (shm + wal).

That might not be enough, usually people just backup the full directory and restore it, not specific files. You might have more luck asking there though: https://community.signalusers.org/c/support/desktop-support/12

[ddshore]

Sorry, this probably wasn't clear. I changed the whole signal beta folder but wanted to point out how those two files disappeared.

[ddshore]

I just used sigtop to run an integrity check:

https://github.com/tbvdm/sigtop

And the database seems fine, no errors were sent. So this seems to be a bug with signal beta, but I'm not sure what I can do to pinpoint it or validate it.

[indutny-signal]

@ddshore sorry about that! What OS are you running Signal on? Do you happen to run it from snap?

[ddshore]

Hi,

Running it on arch linux + KDE, installed through AUR.

I think it might be some problem in the client, but not sure how to validate it.

[ddshore]

I've actually just used signaltop and managed to decrypt my database, it worked fine. I managed to browse it with SQLite. So this seems to be a bug in Signal. I updated to the newest beta release but I still get the same error.

[indutny-signal]

Could you try starting the app with the copied wal and shm files and share the console output of it or a debug log?

[ddshore]

Sure, uploaded. It's just a regular console output, let me know if you need something else.

signal.log

[indutny-signal]

@ddshore thanks for the log. Unfortunately, I don't have much of an idea on why this happens at this point. Is sigtop able to access/export your conversation history?

[ddshore]

It had a bug which I'm currently looking at, but initially yes: it can access and export my conversation history. It managed to unencrypt my database and now I can browse it directly.

[ddshore]

@indutny-signal do you know if there is a way to get back my database before my device unlinks? The database is readable, and my convo history is exportable.

[indutny-signal]

@ddshore I don't know any easy solution, but you try exporting the database into a new file with sqlcipher_export function and putting that file back into Desktop's sql folder. Sorry, I wish I had a better idea for you!

[ddshore]

Thanks. Would you say that I should try to to encrypt the previously unencrypted database and adding it to the folder again?

And just use the same key?

[indutny-signal]

This might work! Great idea.

[alfetopito]

FYI, got the exact same issue (db corrupted for some reason).
Also on Arch, went through several signal updates and no changes.
Currently on 5.21.0-beta.2.
Finally decided to go after and fix it (thus, found this issue).

Since I do not care about msg backup, I simply renamed the previous folder and started the app.
Expectedly, no issues with DB corrupted, all is well.

[captn3m0]

Facing this again with 5.25.0beta.2-1 (after upgrading straight from 5.21.0beta.2-1).

[indutny-signal]

@captn3m0 sorry you ran into it 😭

Did it at least display the error message and offered you to erase the database? Could you paste the message here, please?

[captn3m0]

Getting this locale error (haven't figured out why they keep changing):

I also got the older file is not a database error with the prompt to erase the database a few times. Can't seem to reproduce it now.

Here's the log from the locale error launch:

signal-desktop-beta
Set Windows Application User Model ID (AUMID) { appUserModelId: 'org.whispersystems.signal-desktop-beta' }
NODE_ENV production
NODE_CONFIG_DIR /opt/Signal Beta/resources/app.asar/config
NODE_CONFIG {}
ALLOW_CONFIG_MUTATIONS undefined
HOSTNAME undefined
NODE_APP_INSTANCE undefined
SUPPRESS_NO_CONFIG_WARNING undefined
SIGNAL_ENABLE_HTTP undefined
userData: /home/nemo/.config/Signal Beta
config/get: Successfully read user config file
config/get: Successfully read ephemeral config file
making app single instance

(signal-desktop-beta:535969): Gtk-WARNING **: 15:28:33.117: Theme parsing error: gtk.css:8673:21: Not using units is deprecated. Assuming 'px'.
{"level":50,"time":"2021-11-24T09:58:33.201Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"MainSQL: SQL corruption: file is not a database in \"PRAGMA journal_mode = WAL\""}
{"level":30,"time":"2021-11-24T09:58:33.202Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"MainSQL: migrateDatabase: Migration without cipher change failed"}
{"level":50,"time":"2021-11-24T09:58:33.202Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"MainSQL: SQL corruption: statement aborts at 1: [PRAGMA user_version] file is not a database"}
{"level":50,"time":"2021-11-24T09:58:33.202Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"MainSQL: Database startup error: SqliteError: file is not a database\n    at Database.pragma ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:432:31)\n    at getUserVersion ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:27891:15)\n    at migrateSchemaVersion ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22613:51)\n    at openAndMigrateDatabase ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22645:5)\n    at openAndSetUpSQLCipher ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22661:16)\n    at Object.initialize ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22698:14)\n    at MessagePort.<anonymous> ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:25505:36)\n    at MessagePort.[nodejs.internal.kHybridDispatch] (node:internal/event_target:455:20)\n    at MessagePort.exports.emitMessage (node:internal/per_context/messageport:23:28)"}
{"level":50,"time":"2021-11-24T09:58:33.203Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"Detected sql corruption in main process. Restarting the application immediately. Error: SqliteError: file is not a database\n    at Database.pragma ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:432:31)\n    at getUserVersion ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:27891:15)\n    at migrateSchemaVersion ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22613:51)\n    at openAndMigrateDatabase ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22645:5)\n    at openAndSetUpSQLCipher ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22661:16)\n    at Object.initialize ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:22698:14)\n    at MessagePort.<anonymous> ([REDACTED].unpacked/ts/sql/mainWorker.bundle.js:25505:36)\n    at MessagePort.[nodejs.internal.kHybridDispatch] (node:internal/event_target:455:20)\n    at MessagePort.exports.emitMessage (node:internal/per_context/messageport:23:28)\nfile is not a database in \"PRAGMA journal_mode = WAL\"\nstatement aborts at 1: [PRAGMA user_version] file is not a database"}
Unhandled Promise Rejection: Error: getLocale: Locale not yet initialized!
    at getLocale (/opt/Signal Beta/resources/app.asar/app/main.js:210:15)
    at onDatabaseError (/opt/Signal Beta/resources/app.asar/app/main.js:950:13)
    at runSQLCorruptionHandler (/opt/Signal Beta/resources/app.asar/app/main.js:977:11)
{"level":50,"time":"2021-11-24T09:58:33.204Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"Unhandled Promise Rejection: Error: getLocale: Locale not yet initialized!\n    at getLocale ([REDACTED]/app/main.js:210:15)\n    at onDatabaseError ([REDACTED]/app/main.js:950:13)\n    at runSQLCorruptionHandler ([REDACTED]/app/main.js:977:11)"}
[536039:1124/152833.220794:ERROR:sandbox_linux.cc(374)] InitializeSandbox() called with multiple threads in process gpu-process.
{"level":50,"time":"2021-11-24T09:58:34.813Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"(node:535969) UnhandledPromiseRejectionWarning: Error: getLocale: Locale not yet initialized!\n    at getLocale ([REDACTED]/app/main.js:210:15)\n    at onDatabaseError ([REDACTED]/app/main.js:950:13)\n    at runSQLCorruptionHandler ([REDACTED]/app/main.js:977:11)\n(Use `signal-desktop-beta --trace-warnings ...` to show where the warning was created)"}
{"level":50,"time":"2021-11-24T09:58:34.814Z","pid":535969,"hostname":"nuc.captnemo.in","msg":"(node:535969) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)"}

[indutny-signal]

Welp, sorry for this. It is still a corruption error I'm afraid, but the getLocale() error is an unrelated race condition that we will fix in the next release.

I'm afraid that at this point there are no known fixes for corrupted database other than erasing it as app should offer. Could you try restarting the app a few times until it'll offer you an option to erase the db? Sorry you ran into it 😭

[ddshore]

I'm the original poster. Tried restarting the app and nothing happens. I'm on signal beta and again I lost all of my history on my signal beta app (I use both signal beta and regular signal so it's not such a big deal).

Here's the error.

Is this really unrecoverable even from backups? This seems like a huge huge problem, and I'm curious if this is only a beta issue or not, since I've never had it in the general release.

[Tmpod]

I just got this error again and decided to investigate further. The SQLite database that was left over from this incident is in fact, not corrupt, as I can open it just fine with sqlite3 in the terminal, or via a database explorer. However, it is completely unencrypted, and I can see all my messages, conversations, reactions, etc. What I assume, is that the database was somehow decrypted and then not encrypted again, and so, when the app starts it can't find a SQLCipher database, and says it's corrupt.

try exporting the database into a new file with sqlcipher_export function and putting that file back into Desktop's sql folder

I've tried this, but I don't know which key to use for the encryption. Is it the one in the config.json file? Using sigtop and actually booting up Signal Beta has yielded no results with that one.

[indutny-signal]

@Tmpod thanks for all these details. The key is in config.json file indeed. If I could ask, what's your OS and how did you install Signal (website, flatpak, so forth?)

[Tmpod]

Hey, thanks for the reply!

I'm running Manjaro Linux (x86 64 bits) with kernel version 5.10. I installed the signal-desktop-beta-bin package from the AUR.

[indutny-signal]

I see. Unfortunately this distribution is not officially supported. It sounds like at some point they shipped the app with sqlite3 instead of sqlcipher, and now they switched to sqlcipher. There's probably no other way forward, but to encrypt the database manually as you intended.

Sorry!

[ddshore]

I'm the original creator of the ticket, I don't understand if you're saying that they shipped the app initially with sqlite3 but at as far as I know the version is gotten directly from github, I do not think that anyone would change the database, unless I misunderstood what you're saying.

[Tmpod]

It sounds like at some point they shipped the app with sqlite3 instead of sqlcipher, and now they switched to sqlcipher.

Sorry, but I don't understand how changing the database in an update would decrypt it. If I open an sqlcipher database with sqlite3, I get an error...

Additionally, @ddshore was on the right track. This cannot be a problem, seeing as the package, under the hood, uses the .deb you release (more precisely at https://updates.signal.org/desktop/apt/pool/main/s/signal-desktop-beta/signal-desktop-beta_VERSION_amd64.deb).

And finally, this issue is not exclusive to this AUR package. A friend using Mint has also experienced the same issue, though I don't recall his system's details.

There's probably no other way forward, but to encrypt the database manually as you intended.

I've tried this, and as I've said, it did not work. I ran the following commands:

sqlcipher unencrypted_db.sqlite

ATTACH DATABASE 'db.sqlite' AS encrypted KEY 'asdfzxcv';  -- using key from config.json
SELECT sqlcipher_export('encrypted');  -- takes a couple of seconds
DETACH DATABASE encrypted;

Which goes from 129MB unencrypted to 123MB encrypted (is that normal?).¹
Please tell me if they are correct.

Footnotes

1. yes, small database, because this error has occurred very recently and I was also unable to recover from it. ↩

[indutny-signal]

I think it is very much normal. What you did is essentially equivalent to running VACUUM on the database.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to inactivity.