Unable to restore backup - crashes any version of signal

[bhsane]

When using your tool, i get the following output:

WARNING Foreign key constraint violated.
-----------------------------
| table  | parent    | fkid |
-----------------------------
| thread | recipient | 0    |
-----------------------------

WARNING Foreign key constraint violated.
-----------------------------
| table  | parent    | fkid |
-----------------------------
| thread | recipient | 0    |
-----------------------------

I've tried a few things but the truth is I have no idea how to fix this.

Any ideas? Thanks

[bepaald]

Assuming you did not use my tool to edit the database and something went wrong, you seem to be another victim of https://github.com/signalapp/Signal-Android/issues/12703. Please see here: #99 (my first two replies) for instructions to solve.

Please let me know if it works or if you need more help.

[bepaald]

Seems like a fix is possibly also in Signal (beta) now, maybe you'd prefer to try that instead as it's an official solution: https://github.com/signalapp/Signal-Android/issues/12857

[bhsane]

Thanks, these were the threads i looked through before posting here.

I'm way out of my depth and i don't have the device i originally the made the backup on. Only the backup with a new device.

I'll try the beta and see if it solves the problem.

Thanks.

Edit: i've already tried that beta branch, it didnt work.

[bepaald]

Right, the fix in the beta indeed requires creating a new backup, so if you can't do that we'll have to fix the existing one.

I will talk you through it, but it will be later today, I'm not at home right now and want to test the commands before I post them here. It will just be more confusing if I give you a bunch of incorrect ones...

I'll get back to you!

[bepaald]

Ok, first run

signalbackup-tools [input] [passphrase] --runsqlquery "pragma foreign_key_check"

(Of course, if you're on windows, the command would be signalbackup-tools_win.exe) This will show which entries in the thread table are bad, I'm expecting 1 single entry. For example:

signalbackup-tools (./signalbackup-tools) source version 20230408.151446 (OpenSSL)
[...]
FRAME 97479 (100.0%)... Read entire backup file...

done!
 * Executing query: pragma foreign_key_check
table|rowid|parent|fkid
thread|62|recipient|0

This shows a bad entry with _id 62 (it will probably be a different number for you), we need to delete that thread to fix your backup. But first, we will check to make sure the recipient mentioned in this thread is indeed bad, and that there are no messages in this thread:

signalbackup-tools [input] [passphrase] --runsqlquery "SELECT _id FROM recipient WHERE _id IS (SELECT recipient_id FROM thread WHERE _id = 62)" --runsqlquery "SELECT COUNT(_id) FROM message WHERE thread_id = 62"

Note we are running two queries here, both end with the thread_id we just found (62 in my case) you need to replace this with your own. Expected output:

[...]
done!
 * Executing query: SELECT _id FROM recipient WHERE _id IS (SELECT recipient_id FROM thread WHERE _id = 62)
(no results)
 * Executing query: SELECT COUNT(_id) FROM message WHERE thread_id = 62
COUNT(_id)
0

If you see this same output, it means the bad thread entry can be safely deleted:

signalbackup-tools [input] [passphrase] --runsqlquery "DELETE FROM thread WHERE _id = 62" -o [output]

Again, note the 62 in there which should be replaced. If all goes well, you should have a fixed backup now. I think Signal needs it to have a proper name (signal-DATESTRING.backup, where DATESTRING is the most recent in the directory), so make sure you do that.

Let me know if you need more instructions, or if you get unexpected output at any of the steps mentioned.

[bhsane]

"signalbackup-tools` [input] [passphrase] --runsqlquery "pragma foreign_key_check""
done!

WARNING Foreign key constraint violated.
-----------------------------
| table  | parent    | fkid |
-----------------------------
| thread | recipient | 0    |
-----------------------------
 * Executing query: pragma foreign_key_check
table|rowid|parent|fkid
thread|335|recipient|0

"signalbackup-tools [input] [passphrase] --runsqlquery "SELECT _id FROM recipient WHERE _id IS (SELECT recipient_id FROM thread WHERE _id = 355)" --runsqlquery "SELECT COUNT(_id) FROM message WHERE thread_id = 355""

done!

WARNING Foreign key constraint violated.
-----------------------------
| table  | parent    | fkid |
-----------------------------
| thread | recipient | 0    |
-----------------------------
 * Executing query: SELECT _id FROM recipient WHERE _id IS (SELECT recipient_id FROM thread WHERE _id = 355)
_id
599
 * Executing query: SELECT COUNT(_id) FROM message WHERE thread_id = 355
COUNT(_id)
1

I don't know whether it's safe to delete. I will do it if there's no other options.

I tested it on a copy, just in case.

"signalbackup-tools [input] [passphrase] --runsqlquery "DELETE FROM thread WHERE _id = 62" -o [output]"

I removed id=355 and got the following:

WARNING Foreign key constraint violated.
------------------------------
| table   | parent    | fkid |
------------------------------
| message | thread    | 1    |
| thread  | recipient | 0    |
------------------------------

Running ""pragma foreign_key_check"" on this new backup:

done!

WARNING Foreign key constraint violated.
------------------------------
| table   | parent    | fkid |
------------------------------
| message | thread    | 1    |
| thread  | recipient | 0    |
------------------------------
 * Executing query: pragma foreign_key_check
table|rowid|parent|fkid
message|25059|thread|1
thread|335|recipient|0

What do you think?

Using signalbackup-tools [input] [passphrase] --listthreads, i found thread 355 and it's pretty much useless. Same with 10.

[bepaald]

Before responding to everything here, which I'd need to think about because of the unexpected results: in the first command output, identifying the problem thread, I see 335, but in all subsequent commands you seem to use 355 (except once where I see a left-over 62). Is there just some bad copy-pasting going on or did you use the wrong thread id? I'm really hoping the latter 🤞

[bhsane]

Right.

I wrote 355 instead of 335.

So i ran --runsqlquery "SELECT _id FROM recipient WHERE _id IS (SELECT recipient_id FROM thread WHERE _id = 335)" --runsqlquery "SELECT COUNT(_id) FROM message WHERE thread_id = 335"

Results:

WARNING Foreign key constraint violated.
-----------------------------
| table  | parent    | fkid |
-----------------------------
| thread | recipient | 0    |
-----------------------------
 * Executing query: SELECT _id FROM recipient WHERE _id IS (SELECT recipient_id FROM thread WHERE _id = 335)
(no results)
 * Executing query: SELECT COUNT(_id) FROM message WHERE thread_id = 335
COUNT(_id)
2

then: --runsqlquery "DELETE FROM thread WHERE _id = 335" -o [output]

Results:

Reading backup file...
FRAME 34323 (100.0%)... Read entire backup file...

done!

WARNING Foreign key constraint violated.
-----------------------------
| table  | parent    | fkid |
-----------------------------
| thread | recipient | 0    |
-----------------------------
 * Executing query: DELETE FROM thread WHERE _id = 335
Modified 1 rows

WARNING Foreign key constraint violated.
---------------------------
| table   | parent | fkid |
---------------------------
| message | thread | 1    |
---------------------------

Exporting backup to '[output]'
Writing HeaderFrame...

I ran --runsqlquery "pragma foreign_key_check" on the new backup created.

Results:

done!

WARNING Foreign key constraint violated.
---------------------------
| table   | parent | fkid |
---------------------------
| message | thread | 1    |
---------------------------
 * Executing query: pragma foreign_key_check
table|rowid|parent|fkid
message|2326|thread|1
message|2327|thread|1

Anyway tried this new backup file and the restore reached 3000 messages before crashing.

So progress!

[bepaald]

Ok, that's a bit better indeed. The original problem was that there was a thread without a recipient. So, that thread needed to be deleted. Now, however, there are messages without a thread, which is also a problem. (I have not seen this before by the way, all other cases of this foreign_key violation had the violation on an empty thread)

So, to fix the backup we now also need to delete the two messages. But, you might first want to show the message content, so you now what you are deleting:

signalbackup-tools [input] [passphrase] --runsqlquery "SELECT _id,DATE((date_received / 1000), 'unixepoch', 'localtime'),type,body FROM message WHERE _id = 2326 OR _id = 2327" --runsqlquery "SELECT mid,ct,name,_id,unique_id FROM part WHERE mid = 2326 OR mid = 2327"

This will show the date of the messages, the message body and the type (which is probably not useful for you). Then, it will show some info on any media attachments these messages had (might be none, of course). If you feel you want to extract and save these attachments, let me know and we'll figure something out.

Assuming you are ok with deleting these messages, you can now run

signalbackup-tools [input] [passphrase] --runsqlquery "DELETE FROM message WHERE _id = 2326 OR _id = 2327" --runsqlquery "DELETE FROM part WHERE mid = 2326 OR mid = 2327" --runsqlquery "DELETE FROM thread WHERE _id = 335" -o [output]

(note, this command also deletes the thread again, so it works on the original backup file)

It is not entirely impossible that deleting these messages will cause yet another foreign_key constraint to fail (possibly in the msl_message table), but Signal may be tolerant of this error. Otherwise, we'll need to delete some more data...

[bhsane]

Right, so that backup restored on my test device without issues.

* Executing query: pragma foreign_key_check
(no results)

This came back without issues as well.

So the final thing i should check is what those deleted messages are. Like is there an easy way to do it? Maybe by dumping the database to the disk?

Thank you very much for your help

[bepaald]

Right, so that backup restored on my test device without issues.

Looks good!

So the final thing i should check is what those deleted messages are. Like is there an easy way to do it? Maybe by dumping the database to the disk?

As said, the first command should show most useful data in those messages:

signalbackup-tools [input] [passphrase] --runsqlquery "SELECT _id,DATE((date_received / 1000), 'unixepoch', 'localtime'),type,body FROM message WHERE _id = 2326 OR _id = 2327" --runsqlquery "SELECT mid,ct,name,_id,unique_id FROM part WHERE mid = 2326 OR mid = 2327"

Dumping the database would also work, though the actual messages would still be in the sqlite database (on which you could run the same query to get the same output). If the messages had attachments, they would be in that same dump, named Attachment_[_id]_[unique_id].bin, where _id and unique_id are listed by the above command.

Example (note, messages 2326 and 2327 are just random messages from my own backup, all output will be different in your case):

[~/programming/signalbackup-tools] $ ./signalbackup-tools [input] [passphrase] --runsqlquery "SELECT _id,DATE((date_received / 1000), 'unixepoch', 'localtime'),type,body FROM message WHERE _id = 2326 OR _id = 2327" --runsqlquery "SELECT mid,ct,name,_id,unique_id FROM part WHERE mid = 2326 OR mid = 2327" -o TEMPOUTPUT/
signalbackup-tools (signalbackup-tools) source version 20230322.145000 (OpenSSL)
[...]
Reading backup file...
FRAME 97479 (100.0%)... Read entire backup file...

done!
 * Executing query: SELECT _id,DATE((date_received / 1000), 'unixepoch', 'localtime'),type,body FROM message WHERE _id = 2326 OR _id = 2327
_id|DATE((date_received / 1000), 'unixepoch', 'localtime')|type|body
2326|2019-06-30|10485783|What?
2327|2019-07-01|10485783|Hey! What's up?
 * Executing query: SELECT mid,ct,name,_id,unique_id FROM part WHERE mid = 2326 OR mid = 2327
mid|ct|name|_id|unique_id
2326|image/jpeg|(NULL)|1914|1561905506345
2327|image/jpeg|(NULL)|1915|1561979719075

Exporting backup into 'TEMPOUTPUT//'
Writing HeaderFrame...
Writing DatabaseVersionFrame...
Writing Attachments...
Writing Avatars...
Writing SharedPrefFrame(s)...
Writing KeyValueFrame(s)...
Writing StickerFrames...
Writing EndFrame...
Writing database...
Done!
[~/programming/signalbackup-tools] $ ls -l TEMPOUTPUT/Attachment_1914_1561905506345.* TEMPOUTPUT/Attachment_1915_1561979719075.*
-rw-r--r-- 1 bepaald bepaald 186948 apr 11 11:45 TEMPOUTPUT/Attachment_1914_1561905506345.bin
-rw-r--r-- 1 bepaald bepaald     73 apr 11 11:45 TEMPOUTPUT/Attachment_1914_1561905506345.sbf
-rw-r--r-- 1 bepaald bepaald 405599 apr 11 11:45 TEMPOUTPUT/Attachment_1915_1561979719075.bin
-rw-r--r-- 1 bepaald bepaald     73 apr 11 11:45 TEMPOUTPUT/Attachment_1915_1561979719075.sbf
[~/programming/signalbackup-tools] $

In the above I am combining the two sqlqueries with the -o TEMPOUTPUT/ option to dump the backup to disk. Note the argument to -o needs an existing directory or a path ending in / so it knows to unpack the backup (otherwise you'll just get a new backup file as output).

In the output of the first query, you can see the messages date and body. From the type you could deduce if the message was incoming or outgoing, though it's a bit difficult to explain. Just for the most common types, 10485783 would be outgoing, and 10485780 would be incoming. Obviously, the most useful info would be to/by whom this message was sent, but that would be the recipient_id which was invalid so that information is not there.

In the output of the second query, we see what attachments belong to these messages, first the _id of the message the attachment belongs to (here called mid), then the filetype and a filename (which is often not set). The last two numbers, _id and unique_id are used to identify the actual attachment data. You could use these numbers to find the attachments in the unpacked backup.

I hope that makes some sense.

Thank you very much for your help

No worries, happy to help. Let me know if it works, or if you need more help.

[bhsane]

I did use those but they didnt make sense

 --runsqlquery "SELECT mid,ct,name,_id,unique_id FROM part WHERE mid = 2326 OR mid = 2327"

done!

WARNING Foreign key constraint violated.
-----------------------------
| table  | parent    | fkid |
-----------------------------
| thread | recipient | 0    |
-----------------------------
 * Executing query: SELECT _id,DATE((date_received / 1000), 'unixepoch', 'localtime'),type,body FROM message WHERE _id = 2326 OR _id = 2327
_id|DATE((date_received / 1000), 'unixepoch', 'localtime')|type|body
2326|2022-07-09|10485783|
2327|2022-07-09|10485783|
 * Executing query: SELECT mid,ct,name,_id,unique_id FROM part WHERE mid = 2326 OR mid = 2327
mid|ct|name|_id|unique_id
2326|audio/aac|(NULL)|1752|1657373304723
2327|audio/aac|(NULL)|1753|1657373369075

So both are outgoing and audio messages. And yet, when looking at the dump, attachment 2326 and 2327 are images. Not important ones either.

So i'm going to skip those and try the restore on my actual device.

[bepaald]

So both are outgoing and audio messages. And yet, when looking at the dump, attachment 2326 and 2327 are images. Not important ones either.

Yes, this is mostly correct, but you should be looking for attachments named Attachment_1752_1657373304723.bin and Attachment_1753_1657373369075.bin.

[bhsane]

Had a look at those you mentioned and they're both audio files and pretty useless. So not going t bother keeping them.

The new backup restored on my actual device.

Thanks you very much for your help.

P.s. If you want me to donate to a project in your name or if you want a beer donation. Let me know!

[bepaald]

Happy to hear it!

If you feel you want to give something, there are some donation options at the bottom of the readme, but it's absolutely not necessary, just glad to be able to help someone.

[bhsane]

Happy to hear it!

If you feel you want to give something, there are some donation options at the bottom of the readme, but it's absolutely not necessary, just glad to be able to help someone.

I feel you should join me in merriment.

Donated.