Closed rangeonnicolas closed 4 months ago
bepaald
commented
4 months ago Hi!
I just had a go at this. I'm not sure it works yet, I have no backups with entries in the pending_pni_signature_message table (and nobody ever reported any before), so it's difficult to test.
Please update and let me know how far it gets now. If it makes it to the end, I'd love to hear if the merged backup restores correctly.
Thanks for your report!
rangeonnicolas
commented
4 months ago Wow, thanks for your prompt update !
I tried again, the error is lo longer here and the program went further. It finally stopped after a segmentation fault.
Here is only the new part (the beginning of the message is the same than in my previous message) :
Dealing with thread id: 27, 567, 81, 225, 503, 549, 529, 586, 241, 112, 595, 518, 607, 430, 541, 603, 601, 405, 634, 591, 484, 633, 383, 547, 514, 268, 424, 507, 557, 205, 641, 589, 128, 524, 350, 217, 381, 478, 635, 516, 460, 551, 494, 520, 447, 283, 362, 624, 399, 610, 97, 356, 485, 622, 613, 581, 361, 226, 442, 531, 185, 521, 386, 615, 564, 502, 614, 563, 523, 556, 91, 560, 227, 543, 348, 428, 246, 431, 515, 587, 553, 606, 426, 93, 413, 643, 505, 530, 360, 396, 387, 450, 492, 160, 555, 214, 528, 369, 384, 278, 596, 500, 611, 415, 432, 212, 599, 533, 631, 417, 486, 271, 639, 578, 427, 480, 550, 626, 548, 525, 479, 462, 202, 474, 598, 544, 577, 542, 490, 612, 330, 359, 483, 353, 375, 170, 538, 504, 594, 213, 532, 210, 98, 390, 604, 420, 454, 277, 425, 108, 99, 534, 95, 609, 140, 414, 497, 418, 628, 240, 419, 421, 115, 481, 573, 519, 358, 559, 627, 527, 429, 423, 316, 422, 584, 632, 575, 477, 536, 526, 618, 293, 294, 295, 282, 78, 296, 80, 297, 248, 270, 298, 299, 118, 300, 119, 209, 637, 313, 62, 301, 302, 33, 303, 545, 304, 161, 139, 239, 513, 629, 305, 583, 125, 306, 109, 351, 621, 117, 374, 88, 116, 131, 134, 319, 110, 600, 590, 284, 512, 554, 312, 129, 619, 285, 171, 329, 307, 498, 623, 310, 491, 605, 122, 111, 162, 123, 204, 475, 235, 355, 354, 229, 453, 576, 561, 572, 143, 602, 574, 201, 146, 245, 292, 275, 349, 280, 411, 445, 499, 630, 495, 473, 309, 535, 558, 224, 175, 579, 476, 218, 616, 357, 496, 482, 215, 608, 338, 207, 336, 196, 195, 446, 352, 444, 274, 279, 254, 487, 452, 597, 510, 409, 441, 344, 273, 593, 337, 269, 290, 286, 368, 346, 291, 347, 410, 363, 569, 588, 539, 645, 317, 580, 570, 562, 582, 488, 565, 522, 540, 471, 472, 517, 571, 625, 323, 511, 566, 638, 617, 592, 320, 324, 408, 552, 509, 493, 325, 546, 506, 568, 443, 345, 640, 636, 585, 470, 449, 501, 377, 451, 378, 467, 455, 402, 437, 459, 379, 400, 404, 620, 391, 537, 644, 489, 508, 398, 416, 407, 465, 469, 401, 389, 382, 457, 385, 397, 388, 439, 412, 458, 406, 642, 448, 456, 461, 463, 464, 466, 468
Checking foreign key constraints... ok
Checking database integrity (full)... ok
Importing thread 2 (2/342) from source file: [...]
importThread (2)
1 : 2227 -> 26
Old id:
(no results)
New id:
_id|type|e164|aci|pni|username|email|group_id|distribution_list_id|call_link_room_id|registered|unregistered_timestamp|blocked|hidden|profile_key|profile_key_credential|profile_sharing|profile_given_name|profile_family_name|profile_joined_name|profile_avatar|last_profile_fetch|system_given_name|system_family_name|system_joined_name|system_nickname|system_photo_uri|system_phone_label|system_phone_type|system_contact_uri|system_info_pending|notification_channel|message_ringtone|message_vibrate|call_ringtone|call_vibrate|mute_until|message_expiration_time|sealed_sender_mode|storage_service_id|storage_service_proto|mention_setting|capabilities|last_session_reset|wallpaper|wallpaper_uri|about|about_emoji|extras|groups_in_common|avatar_color|chat_colors|custom_chat_colors_id|badges|needs_pni_signature|reporting_token|phone_number_sharing|phone_number_discoverable|pni_signature_verified|nickname_given_name|nickname_family_name|nickname_joined_name|note
[...]
2 : 2228 -> 1978
Old id:
(no results)
New id:
_id|type|e164|aci|pni|username|email|group_id|distribution_list_id|call_link_room_id|registered|unregistered_timestamp|blocked|hidden|profile_key|profile_key_credential|profile_sharing|profile_given_name|profile_family_name|profile_joined_name|profile_avatar|last_profile_fetch|system_given_name|system_family_name|system_joined_name|system_nickname|system_photo_uri|system_phone_label|system_phone_type|system_contact_uri|system_info_pending|notification_channel|message_ringtone|message_vibrate|call_ringtone|call_vibrate|mute_until|message_expiration_time|sealed_sender_mode|storage_service_id|storage_service_proto|mention_setting|capabilities|last_session_reset|wallpaper|wallpaper_uri|about|about_emoji|extras|groups_in_common|avatar_color|chat_colors|custom_chat_colors_id|badges|needs_pni_signature|reporting_token|phone_number_sharing|phone_number_discoverable|pni_signature_verified|nickname_given_name|nickname_family_name|nickname_joined_name|note
[...]
3 : 2197 -> 552
Old id:
(no results)
New id:
_id|type|e164|aci|pni|username|email|group_id|distribution_list_id|call_link_room_id|registered|unregistered_timestamp|blocked|hidden|profile_key|profile_key_credential|profile_sharing|profile_given_name|profile_family_name|profile_joined_name|profile_avatar|last_profile_fetch|system_given_name|system_family_name|system_joined_name|system_nickname|system_photo_uri|system_phone_label|system_phone_type|system_contact_uri|system_info_pending|notification_channel|message_ringtone|message_vibrate|call_ringtone|call_vibrate|mute_until|message_expiration_time|sealed_sender_mode|storage_service_id|storage_service_proto|mention_setting|capabilities|last_session_reset|wallpaper|wallpaper_uri|about|about_emoji|extras|groups_in_common|avatar_color|chat_colors|custom_chat_colors_id|badges|needs_pni_signature|reporting_token|phone_number_sharing|phone_number_discoverable|pni_signature_verified|nickname_given_name|nickname_family_name|nickname_joined_name|note
[...]
4 : 2218 -> 2018
Old id:
(no results)
New id:
_id|type|e164|aci|pni|username|email|group_id|distribution_list_id|call_link_room_id|registered|unregistered_timestamp|blocked|hidden|profile_key|profile_key_credential|profile_sharing|profile_given_name|profile_family_name|profile_joined_name|profile_avatar|last_profile_fetch|system_given_name|system_family_name|system_joined_name|system_nickname|system_photo_uri|system_phone_label|system_phone_type|system_contact_uri|system_info_pending|notification_channel|message_ringtone|message_vibrate|call_ringtone|call_vibrate|mute_until|message_expiration_time|sealed_sender_mode|storage_service_id|storage_service_proto|mention_setting|capabilities|last_session_reset|wallpaper|wallpaper_uri|about|about_emoji|extras|groups_in_common|avatar_color|chat_colors|custom_chat_colors_id|badges|needs_pni_signature|reporting_token|phone_number_sharing|phone_number_discoverable|pni_signature_verified|nickname_given_name|nickname_family_name|nickname_joined_name|note
[...]
5 : 2199 -> 1966
Old id:
(no results)
New id:
_id|type|e164|aci|pni|username|email|group_id|distribution_list_id|call_link_room_id|registered|unregistered_timestamp|blocked|hidden|profile_key|profile_key_credential|profile_sharing|profile_given_name|profile_family_name|profile_joined_name|profile_avatar|last_profile_fetch|system_given_name|system_family_name|system_joined_name|system_nickname|system_photo_uri|system_phone_label|system_phone_type|system_contact_uri|system_info_pending|notification_channel|message_ringtone|message_vibrate|call_ringtone|call_vibrate|mute_until|message_expiration_time|sealed_sender_mode|storage_service_id|storage_service_proto|mention_setting|capabilities|last_session_reset|wallpaper|wallpaper_uri|about|about_emoji|extras|groups_in_common|avatar_color|chat_colors|custom_chat_colors_id|badges|needs_pni_signature|reporting_token|phone_number_sharing|phone_number_discoverable|pni_signature_verified|nickname_given_name|nickname_family_name|nickname_joined_name|note
[...]
REMAP RECIPIENTS!
Mapping 2227 -> 26 (d)
Mapping 2228 -> 1978 (d)
Mapping 2197 -> 552 (d)
Mapping 2218 -> 2018 (d)
Mapping 2199 -> 1966 (d)
Deleted releasechannel recipient from source database (_id: 1278)
cropToThread
Deleting messages not belonging to requested thread(s) from 'mms'
cleanDatabaseByMessages
Deleting attachment entries from 'attachment' not belonging to remaining message entries
Deleting other threads from 'thread'...
updateThreadsEntries
Dealing with thread id: 2
Deleting entries from 'mention' not belonging to remaining mms entries
Deleting removed groups...
Deleting unneeded MessageSendLog entries... (1306, 4, 1759)
Deleting reactions to non-existing messages... (2068)
Deleting call details from non-existing messages... (15)
Deleting unreferenced recipient entries...
Deleted 2268 unreferenced recipients
Deleted 1 unneeded distribution_lists
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 10 existing remote_megaphone's
Deleted 1962 existing cds's
Deleted 1677 existing kyber_prekeys
makeIdsUnique
Adjusting indexes in tables...
setMinimumId thread
Adjusting 'message.thread_id' to match changes in 'thread'
Adjusting 'drafts.thread_id' to match changes in 'thread'
Adjusting 'mention.thread_id' to match changes in 'thread'
setMinimumId message
Adjusting 'attachment.message_id' to match changes in 'message'
Adjusting 'group_receipts.mms_id' to match changes in 'message'
Adjusting 'mention.message_id' to match changes in 'message'
Adjusting 'msl_message.message_id' to match changes in 'message'
Adjusting 'reaction.message_id' to match changes in 'message'
Adjusting 'story_sends.message_id' to match changes in 'message'
Adjusting 'call.message_id' to match changes in 'message'
Adjusting 'message.latest_revision_id' to match changes in 'message'
Adjusting 'message.original_message_id' to match changes in 'message'
compactIds
Compacting table: message (_id)
setMinimumId attachment
Adjusting 'message.link_previews' to match changes in 'attachment'
compactIds
Compacting table: attachment (_id)
setMinimumId recipient
Adjusting 'message.from_recipient_id' to match changes in 'recipient'
Adjusting 'message.to_recipient_id' to match changes in 'recipient'
Adjusting 'message.quote_author' to match changes in 'recipient'
Adjusting 'sessions.address' to match changes in 'recipient'
Adjusting 'group_receipts.address' to match changes in 'recipient'
Adjusting 'thread.recipient_id' to match changes in 'recipient'
Adjusting 'groups.recipient_id' to match changes in 'recipient'
Adjusting 'remapped_recipients.old_id' to match changes in 'recipient'
Adjusting 'remapped_recipients.new_id' to match changes in 'recipient'
Adjusting 'mention.recipient_id' to match changes in 'recipient'
Adjusting 'msl_recipient.recipient_id' to match changes in 'recipient'
Adjusting 'reaction.author_id' to match changes in 'recipient'
Adjusting 'notification_profile_allowed_members.recipient_id' to match changes in 'recipient'
Adjusting 'payments.recipient' to match changes in 'recipient'
Adjusting 'identities.address' to match changes in 'recipient'
Adjusting 'distribution_list.recipient_id' to match changes in 'recipient'
Adjusting 'distribution_list_member.recipient_id' to match changes in 'recipient'
Adjusting 'story_sends.recipient_id' to match changes in 'recipient'
Adjusting 'pending_pni_signature_message.recipient_id' to match changes in 'recipient'
Adjusting 'call.peer' to match changes in 'recipient'
Adjusting 'group_membership.recipient_id' to match changes in 'recipient'
setMinimumId groups
compactIds
Compacting table: groups (_id)
setMinimumId identities
compactIds
Compacting table: identities (_id)
setMinimumId group_receipts
compactIds
Compacting table: group_receipts (_id)
setMinimumId drafts
compactIds
Compacting table: drafts (_id)
setMinimumId sticker
compactIds
Compacting table: sticker (_id)
setMinimumId msl_payload
Adjusting 'msl_recipient.payload_id' to match changes in 'msl_payload'
Adjusting 'msl_message.payload_id' to match changes in 'msl_payload'
compactIds
Compacting table: msl_payload (_id)
setMinimumId msl_recipient
compactIds
Compacting table: msl_recipient (_id)
setMinimumId msl_message
compactIds
Compacting table: msl_message (_id)
setMinimumId remapped_recipients
compactIds
Compacting table: remapped_recipients (_id)
setMinimumId remapped_threads
compactIds
Compacting table: remapped_threads (_id)
setMinimumId mention
compactIds
Compacting table: mention (_id)
setMinimumId reaction
compactIds
Compacting table: reaction (_id)
setMinimumId notification_profile
Adjusting 'notification_profile_allowed_members.notification_profile_id' to match changes in 'notification_profile'
Adjusting 'notification_profile_schedule.notification_profile_id' to match changes in 'notification_profile'
compactIds
Compacting table: notification_profile (_id)
setMinimumId notification_profile_allowed_members
compactIds
Compacting table: notification_profile_allowed_members (_id)
setMinimumId notification_profile_schedule
compactIds
Compacting table: notification_profile_schedule (_id)
setMinimumId payments
compactIds
Compacting table: payments (_id)
setMinimumId storage_key
compactIds
Compacting table: storage_key (_id)
setMinimumId sender_key_shared
compactIds
Compacting table: sender_key_shared (_id)
setMinimumId sender_keys
compactIds
Compacting table: sender_keys (_id)
setMinimumId pending_retry_receipts
compactIds
Compacting table: pending_retry_receipts (_id)
setMinimumId avatar_picker
compactIds
Compacting table: avatar_picker (_id)
setMinimumId distribution_list
Adjusting 'recipient.distribution_list_id' to match changes in 'distribution_list'
Adjusting 'distribution_list_member.list_id' to match changes in 'distribution_list'
compactIds
Compacting table: distribution_list (_id)
setMinimumId distribution_list_member
compactIds
Compacting table: distribution_list_member (_id)
setMinimumId donation_receipt
compactIds
Compacting table: donation_receipt (_id)
setMinimumId story_sends
compactIds
Compacting table: story_sends (_id)
setMinimumId cds
compactIds
Compacting table: cds (_id)
setMinimumId remote_megaphone
compactIds
Compacting table: remote_megaphone (_id)
setMinimumId pending_pni_signature_message
compactIds
Compacting table: pending_pni_signature_message (_id)
setMinimumId call
compactIds
Compacting table: call (_id)
setMinimumId group_membership
compactIds
Compacting table: group_membership (_id)
setMinimumId call_link
compactIds
Compacting table: call_link (_id)
setMinimumId kyber_prekey
compactIds
Compacting table: kyber_prekey (_id)
No existing thread found in target database for this recipient, importing.
updateRecipientIds (2)
Mapping 2199 -> 1 (up)
Dropped 1 existing recipients from source database
Mapping 3178 -> 95 (up)
Dropped 1 existing recipients from source database
Mapping 2359 -> 155 (up)
Dropped 1 existing recipients from source database
Mapping 3080 -> 334 (u)
Dropped 1 existing recipients from source database
Mapping 3053 -> 613 (u)
Dropped 1 existing recipients from source database
Mapping 3013 -> 735 (u)
Dropped 1 existing recipients from source database
Mapping 2913 -> 1553 (u)
Dropped 1 existing recipients from source database
Mapping 3412 -> 1588 (u)
Dropped 1 existing recipients from source database
Mapping 2584 -> 1879 (u)
Dropped 1 existing recipients from source database
Importing statements from source table 'attachment'...0 entries...
Importing statements from source table 'avatar_picker'...0 entries...
Importing statements from source table 'recipient'...0 entries...
Importing statements from source table 'thread'...1 entries...
Importing statements from source table 'message'...8 entries...
Importing statements from source table 'call'...0 entries...
Importing statements from source table 'call_link'...0 entries...
Importing statements from source table 'cds'...0 entries...
Importing statements from source table 'chat_colors'...0 entries...
Importing statements from source table 'distribution_list'...0 entries...
Importing statements from source table 'distribution_list_member'...0 entries...
Importing statements from source table 'donation_receipt'...0 entries...
Importing statements from source table 'drafts'...0 entries...
Importing statements from source table 'emoji_search'...0 entries...
Importing statements from source table 'groups'...1 entries...
[Error]: After sqlite3_step(): UNIQUE constraint failed: groups.recipient_id
-> Query: "INSERT INTO groups (_id,group_id,recipient_id,title,avatar_id,avatar_key,avatar_content_type,avatar_digest,timestamp,active,mms,master_key,revision,decrypted_group,expected_v2_id,unmigrated_v1_members,distribution_id,show_as_story_state,last_force_update_timestamp) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Importing statements from source table 'group_membership'...0 entries...
Importing statements from source table 'group_receipts'...7 entries...
[Error]: After sqlite3_finalize(): UNIQUE constraint failed: groups.recipient_id
-> Query: "SELECT DISTINCT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'group_receipts'"
[Warning]: Skipping table 'group_receipts', as it is not present in target database. Data may be missing.
Importing statements from source table 'identities'...0 entries...
Importing statements from source table 'kyber_prekey'...0 entries...
Importing statements from source table 'mention'...0 entries...
Importing statements from source table 'msl_payload'...0 entries...
Importing statements from source table 'msl_message'...0 entries...
Importing statements from source table 'msl_recipient'...0 entries...
Importing statements from source table 'notification_profile'...0 entries...
Importing statements from source table 'notification_profile_allowed_members'...0 entries...
Importing statements from source table 'notification_profile_schedule'...0 entries...
Importing statements from source table 'payments'...0 entries...
Importing statements from source table 'pending_pni_signature_message'...0 entries...
Importing statements from source table 'pending_retry_receipts'...0 entries...
Importing statements from source table 'reaction'...0 entries...
Importing statements from source table 'remapped_recipients'...0 entries...
Importing statements from source table 'remapped_threads'...0 entries...
Importing statements from source table 'remote_megaphone'...0 entries...
Importing statements from source table 'sender_key_shared'...0 entries...
Importing statements from source table 'sender_keys'...0 entries...
Importing statements from source table 'sticker'...103 entries...
Erreur de segmentation (core dumped)
``I'm not completely sure what exactly is going on here. I did manage to reproduce a bug that looks almost exactly like yours:
[...]
No existing thread found in target database for this recipient, importing.
updateRecipientIds (2)
Mapping 138 -> 71 (up)
Mapping 136 -> 106 (up)
Mapping 142 -> 115 (up)
Mapping 146 -> 118 (g)
[...]
Importing statements from source table 'groups'... (1 entries)
[Error]: After sqlite3_step(): UNIQUE constraint failed: groups.recipient_id
-> Query: "INSERT INTO groups (_id,group_id,recipient_id,title,avatar_id,avatar_key,avatar_content_type,avatar_digest,timestamp,active,mms,master_key,revision,decrypted_group,expected_v2_id,unmigrated_v1_members,distribution_id,show_as_story_state,last_force_update_timestamp) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
...done
[Warning]: Skipping table 'group_receipts', as it is not present in target database. Data may be missing.
Segmentation fault (core dumped)This bug would occur in the rare case a group-thread was imported, where the thread did not yet exist in the target (no messages), but the group itself was already present. That case should now be fixed. However, there is a little bit of output in your post that makes me doubt you are running in to the same problem: in my case, one of the mappings has (g) at the end of it (for group). In your case this is missing, but for the bug as I fixed, I think it should have this. So your problem may be something else.
So — just to be sure — you can try the new version, but don't get your hopes up. I slightly cleaned up the output as well, so it will look a little bit different. I will have to think a little bit about this and hopefully come back to it tomorrow. Note that I'm almost sure I fixed the segmentation fault (that was the result of the earlier sqlite3 failure), so even if the program doesn't crash at the same spot: if the UNIQUE constraint failed is still there, the problem is not fixed.
If everything does appear to work correctly, you might want to --exporthtml before restoring on the phone, just to be sure all expected messages are imported and appear in the correct threads (note especially the thread with _id = 2).
Is there anything you can tell me about the thread that is causing this? You could run --listthreads on the source backup to get its title (look for the thread with _id = 2). I'm assuming it is a group? Does the group exist on both phones, and does it have a thread (with messages) on both? Are both phones members of the group? Anything you think that might be relevant might help me realize what's happening. I will not have any more time to work on this today, but I should have a bit more time tomorrow.
Thanks!
bepaald
commented
4 months ago I was just looking a bit more into this this morning, is it possible that the thread is a 'story'? Just looking at the code, I think stories may currently not properly be imported.
I think I should be able to fix it if that's the case, but you could also just exclude the story threads from importing? If I'm not mistaken, they only last 24 hours anyway (which is probably why I've never encountered them in a backup).
rangeonnicolas
commented
4 months ago Wow, it worked !! Many thanks !!
The --exporthtml worked well. I restored the merged backup on my phone, and everything is fine !
About the thread with _id=2, it's a group where there is no more messages (I don't remember if I deleted it or if the messages where programmed to be auto-deleted after some days by signal).
Sometimes I delete my group conversations by long-clicking on it and then clicking on "delete", instead on clicking on "leave the group". I think this is what happend for thread with _id=2. When I do this, all the messages are deleted from my phone, and the conversation doesn't appear any more in Signal. But I'm still considered to be part on this group.
When I uninstall Signal and install it again, all the groups that I havn't leaved (including maybe id=2) are displayed again in Signal, because I'm still considered to be part of it. So I sometimes have groups with no messages. I think that's what happened with group id=2. Does it make sense ?
bepaald
commented
4 months ago Excellent, glad to hear it!
Yes, your explanation does make sense. I'll try to reproduce the same situation when I have some time tomorrow, just to be sure and maybe investigate any other issues that could show up in this situation. But considering
I restored the merged backup on my phone, and everything is fine !
I think this issue is solved!
Thanks for reporting back!
Hello ! I'm trying to merge 2 backups, which are both on version 223 (I both loaded them into the last version of Signal and exported them, that's why they have the same version) I'm working on Ubuntu
Here is my command line :
./signalbackup-tools signal-2023-08-20-12-15-34.backup 342[...]293 --importthreads ALL --source signal-2024-04-02-21-00-42.backup --sourcepassphrase 298[...]328 --output test.backupAnd here is what I got :
Thanks !