Cannot merge two v247 databases together. Multiple errors.

[darrklogik]

I attempted merging two databases, both v247, using a freshly compiled source version#20240925, commit#cbdecd9, on ubuntu v19.04.

The operation failed with alot of errors:

$ ./signalbackup-tools signal-2023.backup "passwordnotshown" --importthreads ALL --source signal-2024.backup --sourcepassphrase "passwordnotshown" --output signal-merged.backup > output.txt

.
.
.
.
 , 5[Error]: During sqlite3_prepare_v2(): no such table: PRAGMA_TABLE_XINFO
         -> Query: "SELECT 1 FROM PRAGMA_TABLE_XINFO('thread') WHERE name == 'active'"
, 129[Error]: During sqlite3_prepare_v2(): no such table: PRAGMA_TABLE_XINFO
         -> Query: "SELECT 1 FROM PRAGMA_TABLE_XINFO('thread') WHERE name == 'active'"
, 131[Error]: During sqlite3_prepare_v2(): no such table: PRAGMA_TABLE_XINFO
         -> Query: "SELECT 1 FROM PRAGMA_TABLE_XINFO('thread') WHERE name == 'active'"
, 132[Error]: During sqlite3_prepare_v2(): no such table: PRAGMA_TABLE_XINFO
         -> Query: "SELECT 1 FROM PRAGMA_TABLE_XINFO('thread') WHERE name == 'active'"

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 |
-----------------------------
| call     | message | 1    |
| reaction | message | 1    |
-----------------------------
[Error]: A fatal error occurred while trying to import thread 2. Aborting
.
.
.

see full stdout output in attached file: output.txt

Thankyou.

Also, your program is very useful. Thankyou.

[bepaald]

Hi, I'm not immediately sure what's happening here, but there are many bad errors in that output log. Those, together with

on ubuntu v19.04.

Make me think you may be using a (much) too old sqlite3 version. What version do you have installed? any chance of updating that?

[bepaald]

I've tried to test this on Ubuntu 19.04 by running it in a virtual machine, but I can't seem to install sqlite since the servers for this Ubuntu version appear to be no longer running. Just looking at the release-dates, I'm not sure if the version is the problem though. If you just run sqlite3 in a terminal and type the following commands, what do you see?

[~] $ sqlite3 
SQLite version 3.46.1 2024-08-13 09:16:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE message (_id INTEGER PRIMARY KEY AUTOINCREMENT, thread_id INTEGER, body TEXT);
sqlite> CREATE VIRTUAL TABLE message_fts USING fts5(body, thread_id UNINDEXED, content=message, content_rowid=_id, tokenize = "unicode61 categories 'L* N* Co Sc So'");
sqlite> SELECT * FROM PRAGMA_TABLE_XINFO('message') WHERE false;
sqlite> .quit
[~] $ 

When things are working, there shouldn't be any output from the SQL statements (like above).

Thanks!

[darrklogik]

I think its v3.22 I've got installed.

I just downloaded the latest trunk-checkin of sqlite3 v.3.46 from https://www.sqlite.org/src/tarball/sqlite.tar.gz and built it.

I did a make install, and i will test signalbackup-tool tomorrow.

[darrklogik]

SQLite version 3.47.0 2024-09-26 22:25:13 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .version SQLite 3.47.0 2024-09-26 22:25:13 27ef1909bb0c4d9470c6074b40500632c68341127a079a3eb3b6a19dbfb2aeac zlib version 1.2.11 gcc-9.4.0 (64-bit)

[darrklogik]

I recompiled signalbakup-tools, and tried merging the databases again, and got a different result, with alot less errors:

$ ~/Downloads/signaltemp$ ./signalbackup-tools signal-fixed-2024.backup  "password-not-shown" --importthreads ALL --source signal-fixed-2023.backup --sourcepassphrase "password-not-shown" --output ~/Downloads/signaltemp/signal-merged.backup
 *** Starting log: 2024-09-28 15:26:01 *** 
signalbackup-tools (./signalbackup-tools) source version 20240925.172224
BACKUPFILE VERSION: 1
BACKUPFILE SIZE: 5742139557
COUNTER: 2322302098
Reading backup file: 000.0%...
[Error]: After sqlite3_step(): no such module: fts5
         -> Query: "CREATE VIRTUAL TABLE message_fts USING fts5(body, thread_id UNINDEXED, content=message, content_rowid=_id, tokenize = "unicode61 categories 'L* N* Co Sc So'")"
[Warning]: Failed to execute statement: CREATE VIRTUAL TABLE message_fts USING fts5(body, thread_id UNINDEXED, content=message, content_rowid=_id, tokenize = "unicode61 categories 'L* N* Co Sc So'")
Reading backup file: 100.0%... done!
Database version: 247
[Warning]: Foreign key constraint violated.
-----------------------------
| table    | parent  | fkid |
-----------------------------
| call     | message | 1    |
| reaction | message | 1    |
-----------------------------
BACKUPFILE VERSION: 1
BACKUPFILE SIZE: 3489561322
COUNTER: 1834625976
Reading backup file: 0.000%...
[Error]: After sqlite3_step(): no such module: fts5
         -> Query: "CREATE VIRTUAL TABLE message_fts USING fts5(body, thread_id UNINDEXED, content=message, content_rowid=_id, tokenize = "unicode61 categories 'L* N* Co Sc So'")"
[Warning]: Failed to execute statement: CREATE VIRTUAL TABLE message_fts USING fts5(body, thread_id UNINDEXED, content=message, content_rowid=_id, tokenize = "unicode61 categories 'L* N* Co Sc So'")
Reading backup file: 100.0%... done!
Database version: 247
[Warning]: Foreign key constraint violated.
-----------------------------
| table    | parent  | fkid |
-----------------------------
| call     | message | 1    |
| reaction | message | 1    |
-----------------------------
Requested ALL threads, reading source to get thread list
Getting list of thread id's...
Got: 1,2,3,4,5,6,7,8,9,10,12,14,18,19,24,25,31,32,33,34,37,38,39,43,44,46,48,49,50,52,56,61,62,63,64,67,69,72,73,74,75,79,97,99,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133

Importing thread 1 (1/75) from source file: signal-fixed-2023.backup
importThread (1)
Deleted releasechannel recipient from source database (_id: 384)
cropToThread
  Deleting messages not belonging to requested thread(s) from 'mms'
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "DELETE FROM message WHERE thread_id != ?"
cleanDatabaseByMessages
  Deleting attachment entries from 'attachment' not belonging to remaining message entries
  Deleting other threads from 'thread'...
updateThreadsEntries

  Deleting entries from 'mention' not belonging to remaining mms entries
  Deleting removed groups... (7)
  Deleting unneeded MessageSendLog entries... (0, 0, 0)
  Deleting reactions to non-existing messages... (911)
  Deleting call details from non-existing messages... (387)
  Deleting unreferenced recipient entries...
  Deleted 560 unreferenced recipients
  Deleting unneeded notification profiles entries...
  Deleting pending_pni_signature_messages not belonging to existing recipients...
  Deleting unused avatars...
  Deleting unused attachments...
  Delete others from 'identities'
  Deleting group_receipts entries from deleted messages...
  Deleting group_receipts from non-existing recipients
  Deleting drafts from deleted threads...
  Deleting remapped recipients for non existing recipients
  Vacuuming database
  Deleted 307 existing cds's
makeIdsUnique
  Adjusting indexes in tables...
setMinimumId recipient
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "UPDATE message SET from_recipient_id = from_recipient_id + ? "
  Adjusted 'message.from_recipient_id' to match changes in 'recipient' : 1
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "UPDATE message SET to_recipient_id = to_recipient_id + ? "
  Adjusted 'message.to_recipient_id' to match changes in 'recipient' : 1
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "UPDATE message SET quote_author = quote_author + ? "
  Adjusted 'message.quote_author' to match changes in 'recipient' : 1
  Adjusted 'distribution_list.recipient_id' to match changes in 'recipient' : 1
setMinimumId sticker
  Compacting table: sticker (_id)
setMinimumId distribution_list
  Adjusted 'recipient.distribution_list_id' to match changes in 'distribution_list' : 1
  Compacting table: distribution_list (_id)
setMinimumId cds
  Compacting table: cds (_id)
setMinimumId remote_megaphone
  Compacting table: remote_megaphone (_id)
setMinimumId kyber_prekey
  Compacting table: kyber_prekey (_id)
  Found existing thread for this recipient in target database, merging into thread 23
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "UPDATE message SET thread_id = ?"
  updateRecipientIds
  Mapping 363 -> 1 (d)
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "UPDATE message SET from_recipient_id = ? WHERE from_recipient_id = ?"
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "UPDATE message SET to_recipient_id = ? WHERE to_recipient_id = ?"
[Error]: During sqlite3_prepare_v2(): no such table: main.message_fts
         -> Query: "UPDATE message SET quote_author = ? WHERE quote_author = ?"
Dropped 1 existing recipients from source database
  Deleted 5 existing stickers
[Error]: During sqlite3_prepare_v2(): no such column: devide_id
         -> Query: "DELETE FROM pending_pni_signature_message WHERE recipient_id = ? AND sent_timestamp = ? AND devide_id = ?"
[Error]: During sqlite3_prepare_v2(): no such column: devide_id
         -> Query: "DELETE FROM pending_pni_signature_message WHERE recipient_id = ? AND sent_timestamp = ? AND devide_id = ?"
  Deleted 2 existing pending_pni_signature_messages
  Deleted 1 existing distribution lists
Importing statements from source table 'cds'... (165 entries) ...done
Importing statements from source table 'kyber_prekey'... (504 entries) ...done
Importing statements from source table 'remote_megaphone'... (5 entries) ...done
Importing statements from source table 'sticker'... (25 entries) ...done
updateThreadsEntries
  Dealing with thread id: 1, 17, 23, 8, 9, 19, 22, 21, 10, 15, 18, 20, 14, 11, 12, 13, 2, 16, 6, 3, 4, 5, 24, 25, 27, 7, 26, 28, 29
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 |
-----------------------------
| call     | message | 1    |
| reaction | message | 1    |
-----------------------------
[Error]: A fatal error occurred while trying to import thread 1. Aborting

[darrklogik]

I think the new sqlite3 v2.47 makes a difference. However, its still not working. Which version SQLite3 is recommended to use with signalbackup-tools?

[bepaald]

I think the new sqlite3 v2.47 makes a difference. However, its still not working. Which version SQLite3 is recommended to use with signalbackup-tools?

There is no recommended version really, anything is supposed to work really, unless it's really multiple years out of date. It's also dependent on what Signal Android is using.

I recompiled signalbakup-tools, and tried merging the databases again, and got a different result, with alot less errors:

Ok, looking at this I have some questions.

First a remark, it does indeed look like the previous version of sqlite was too old for the PRAGMA_TABLE_XINFO, since just updating has made that error disappear. Even though these functions have been supported since SQLite version 3.16.0 (2017-01-02), which seems old even compared to Ubuntu 19.04.

However, you still have an error about the CREATE VIRTUAL TABLE message_fts.... Note this statement is not one originating from this tool, it is present in the Android backup, it's read from there and then executed and not something I can work around.

While in your first output, the error indicated to me perhaps SQlite was outdated, in your most recent output the error has changed: [Error]: After sqlite3_step(): no such module: fts5. This appears to me that you may have compiled sqlite without this module. I'm afraid you need to recompile sqlite again, this time with the module present (see https://www.sqlite.org/fts5.html#building_fts5_as_part_of_sqlite). Apologies for that, I never imagined this module was disabled by default.

What I also notice now is that there are foreign key constraint violations in both input files before any edits are made (directly after opening). This is not expected from a valid Signal backup. Also, I see both files are named signal-fixed-202X.backup... What was broken exactly in these backup, and how were they 'fixed'? Note, the foreign key constraint errors tell me they are not fixed and neither of these backups would actually restore on a phone in their current state. These errors would need to be fixed before attempting to merge.

So, the plan: first recompile sqlite, then fix the foreign key constraints, then attempt the merge again. I'm late for work now, so I'll be back later with instructions for fixing the foreign key constraint if needed.

Thanks!

[bepaald]

To fix the foreign key constraint errors, run the following on each backup file (after rebuilding sqlite with the fts5 module):

./signalbackup-tools [input] [passphrase] --runsqlquery "DELETE FROM reaction WHERE message_id NOT IN (SELECT _id FROM message)" --runsqlquery "DELETE FROM call WHERE message_id NOT IN (SELECT _id FROM message)" -o [OUTPUT]

Look at the output from the above for any errors or warnings. When done, opening each backup, without any arguments, should produce no errors and no warnings, for example:

 $ ./signalbackup-tools ~/PHONE/signal-2024-09-26-03-49-10.backup [passphrase] 
 *** Starting log: 2024-09-28 16:11:00 *** 
signalbackup-tools (./signalbackup-tools) source version 20240926.210714 (SQlite: 3.46.1, OpenSSL: OpenSSL 3.3.2 3 Sep 2024)
BACKUPFILE VERSION: 1
BACKUPFILE SIZE: 7594253851
COUNTER: 4254865059
Reading backup file: 100.0%... done!
Database version: 250
 $

Let me know if you still have errors showing at this point, otherwise you could attempt to merge again. I think things should work then (though it's hard to be sure, there could be other problems with those backups which aren't showing themselves (yet)).

Thanks!

[bepaald]

No response. Let me know if you want to get back to this eventually. This could be reopened.