Foreign key constraint violated.

[Huntingtor]

The tool processed my backup file about 90 seconds and exited with errors.

My command: C:\Users\Hunter\Desktop\Signal Backups>signalbackup-tools_win.exe "signal-2023-11-08-09-42-38.backup" 270475549372890 --importfromdesktop --exporthtml "exported signal chats" --append --addincompletedataforhtmlexport

Result:

signalbackup-tools (signalbackup-tools_win.exe) source version 20231107.231353 (Win)
BACKUPFILE VERSION: 1
BACKUPFILE SIZE: 1167486282
COUNTER: 1363271753
Reading backup file...
FRAME 87030 (100.0%)... Read entire backup file...

done!
Database version: 210

WARNING Foreign key constraint violated.
-----------------------------
| table    | parent  | fkid     |
-----------------------------
| reaction | message | 1    |
-----------------------------
reorderMmsSmsIds
updateThreadsEntries
  Dealing with thread id: 6, 1, 2, 4, 8, 3, 7, 5
Checking foreign key constraints...
ERROR Foreign key constraint violated. This will not end well, aborting.
      Please report this error to the program author.
-----------------------------
| table    | parent  | fkid     |
-----------------------------
| reaction | message | 1    |
-----------------------------

[bepaald]

Hi, thanks for reporting. I'll look in to this.

But, first one question: You have obviously removed some of the output — and rightly so, it was probably a lot — so it is not immediately clear where during the process the two foreign key warnings are printed. Can I assume the first one was right after Database version 210, and then a lot of output is omitted until the reorderMmsSmsIds line?

Could you please run C:\Users\Hunter\Desktop\Signal Backups>signalbackup-tools_win.exe "signal-2023-11-08-09-42-38.backup" [passphrase]? So the program just opens and reads the backup file, without doing anything with it (so no arguments). What's the output, does it still show the (first) warning?

Thanks!

[Huntingtor]

Thanks for your quick reply. I didn't remove anything from the output. While processing, the tool shows indeed a lot of output under "Read entire backup file...", but only in one flickering line. No linefeeds. Yes, it still shows the 1st warning. I'm running the tool as admin.

BACKUPFILE VERSION: 1
BACKUPFILE SIZE: 1167486282
COUNTER: 1363271753
Reading backup file...
FRAME 87030 (100.0%)... Read entire backup file...

done!
Database version: 210

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

[Huntingtor]

I redirected the output to a file and there is indeed much more than in consolw window. You may download it here: https://www.swisstransfer.com/d/07bec634-5974-4c6b-ad05-58e7dbaf90b2

[bepaald]

Thanks for your quick reply. I didn't remove anything from the output. While processing, the tool shows indeed a lot of output under "Read entire backup file...", but only in one flickering line. No linefeeds.

Ok, I thought the --importfromdesktop would show a lot of output, on my (very small) testing files I get for example:

$ ./signalbackup-tools ~/PHONE/DEVsignal-2023-10-02-11-28-34.backup 000000000000000000000000000000 --importfromdesktop
signalbackup-tools (./signalbackup-tools) source version 20231107.231353
BACKUPFILE VERSION: 1
BACKUPFILE SIZE: 65455705
COUNTER: 3011343483
Reading backup file...
FRAME 1804 (100.0%)... Read entire backup file...

done!
Database version: 209
Trying to match conversation (1/9) (type: private)
 - Importing 12 messages into thread._id 8
Warning: Skipping 0 byte attachment. Not supported in Signal Android.
Trying to match conversation (2/9) (type: private)
 - Importing 177 messages into thread._id 6
Warning: Unsupported message type 'timer-notification (fromSync=true)'. Skipping...
Warning: Failed to get number of attachments in quoted message. Skipping
Trying to match conversation (3/9) (type: group)
 - Importing 106 messages into thread._id 5
Warning: Unsupported message type 'group-v2-change'. Skipping... (this warning will be shown only once)
Warning: Unsupported message type 'timer-notification (in group)'. Skipping... (this warning will be shown only once)
Trying to match conversation (4/9) (type: private)
 - Importing 2 messages into thread._id 7
Trying to match conversation (5/9) (type: private)
 - Importing 95 messages into thread._id 9
Trying to match conversation (6/9) (type: group)
 - Importing 25 messages into thread._id 10
Trying to match conversation (7/9) (type: group)
 - Importing 8 messages into thread._id 13
Trying to match conversation (8/9) (type: group)
 - Importing 10 messages into thread._id 14
Trying to match conversation (9/9) (type: group)
 - Importing 38 messages into thread._id 16
reorderMmsSmsIds
updateThreadsEntries
  Dealing with thread id: 8, 1, 6, 5, 7, 9, 10, 13, 14, 15, 16
Checking foreign key constraints... ok
Checking database integrity (full)... ok

But that's ok.

Yes, it still shows the 1st warning.

Ok, so this tells me the actual error is already in your input backup file. Is that file an official, completely unmodified (by this tool or any other) backup file as created by Signal Android? If so, I think this qualifies for a bug report over at Signal. I think it will not be possible to restore this backup, which I think is a serious bug.

You could try to remove the error by running signalbackup-tools [input] [passphrase] --runsqlquery "DELETE FROM reaction WHERE message_id NOT IN (SELECT _id FROM message)" -o [newbackupfile]. If successful, you can then try the previous commands on the new file.

[bepaald]

I redirected the output to a file and there is indeed much more than in consolw window

Yeah, that's actually the same output (but when redirecting, it can't overwrite the percentage indicator so all of them are included). I was expecting some output during the actual importfromdesktop-operation. Thanks anyway.

[Huntingtor]

-- Oops, i missed your suggestion to deal with the output file. Will try that. --

Newest output. It gererated a file, but it seems to be only a copy of the backup file, no HTML output.

BACKUPFILE VERSION: 1
BACKUPFILE SIZE: 1167486282
COUNTER: 1363271753
Reading backup file...
FRAME 87030 (100.0%)... Read entire backup file...

done!
Database version: 210

WARNING Foreign key constraint violated.
-----------------------------
| table    | parent  | fkid |
-----------------------------
| reaction | message | 1    |
-----------------------------
 * Executing query: DELETE FROM reaction WHERE message_id NOT IN (SELECT _id FROM message)
Modified 1 rows

Exporting backup to 'exported signal chats1'
Writing HeaderFrame...
Writing DatabaseVersionFrame...
Writing SqlStatementFrame(s)...
  Dealing with table 'avatar_picker'... 0/0 entries...
  Dealing with table 'recipient'... 23/23 entries...done
  Dealing with table 'thread'... 8/8 entries...done
  Dealing with table 'message'... 13632/13632 entries...done
  Dealing with table 'call'... 0/0 entries...
  Dealing with table 'call_link'... 0/0 entries...
  Dealing with table 'cds'... 22/22 entries...done
  Dealing with table 'chat_colors'... 0/0 entries...
  Dealing with table 'distribution_list'... 1/1 entries...done
  Dealing with table 'distribution_list_member'... 0/0 entries...
  Dealing with table 'donation_receipt'... 0/0 entries...
  Dealing with table 'drafts'... 0/0 entries...
  Dealing with table 'emoji_search'... 0/0 entries...
  Dealing with table 'groups'... 1/1 entries...done
  Dealing with table 'group_membership'... 20/20 entries...done
  Dealing with table 'group_receipts'... 5035/5035 entries...done
  Dealing with table 'identities'... 22/22 entries...done
  Dealing with table 'kyber_prekey'... 362/362 entries...done
  Dealing with table 'mention'... 12/12 entries...done
  Dealing with table 'msl_payload'... 12455/12455 entries...done
  Dealing with table 'msl_message'... 15963/15963 entries...done
  Dealing with table 'msl_recipient'... 20886/20886 entries...done
  Dealing with table 'notification_profile'... 0/0 entries...
  Dealing with table 'notification_profile_allowed_members'... 0/0 entries...
  Dealing with table 'notification_profile_schedule'... 0/0 entries...
  Dealing with table 'part'... 129/3698 entries...Warning: attachment data not found (rowid: 130, uniqueid: 1693349250753)
  Dealing with table 'part'... 3698/3698 entries...done
  Dealing with table 'payments'... 0/0 entries...
  Dealing with table 'pending_pni_signature_message'... 0/0 entries...
  Dealing with table 'pending_retry_receipts'... 0/0 entries...
  Dealing with table 'reaction'... 8331/8331 entries...done
  Dealing with table 'remapped_recipients'... 0/0 entries...
  Dealing with table 'remapped_threads'... 0/0 entries...
  Dealing with table 'remote_megaphone'... 3/3 entries...done
  Dealing with table 'sender_key_shared'... 0/0 entries...
  Dealing with table 'sender_keys'... 0/0 entries...
  Dealing with table 'sticker'... 1357/1357 entries...done
  Dealing with table 'storage_key'... 0/0 entries...
  Dealing with table 'story_sends'... 0/0 entries...
Writing SharedPrefFrame(s)...
Writing KeyValueFrame(s)...
Writing Avatars...
Writing EndFrame...
Done! Wrote 1167486179 bytes.

[bepaald]

Newest output. It gererated a file, but it seems to be only a copy of the backup file, no HTML output.

Yes, it is almost a copy, it should be identical but without the foreign key constraint. You can now use this new file as the input (probably a good idea to give it another name, like "signal-2023-11-08-09-42-38.backup.fixed" or something). Try --importfromdesktop and --exporthtml with this newly created file. Thanks!

[bepaald]

-- Oops, i missed your suggestion to deal with the output file. Will try that. --

Ah, ok you noticed... 👍

[Huntingtor]

It worked flawlessly! Your 2nd command did the trick. Thank you very much for your quick support and have a nice day :)

[bepaald]

No problem, happy to help. Thanks for your kind words.