Many export errors

[jbaker6953]

A lot of errors.

1. 17 SMS messages were not exported because local variable 'phone' referenced before assignment
2. 56 MMS/Signal messages were not exported because No such file or directory: 'bits/Attachment_8161_1670890804128.bin' or similar filenames. The files actually do not exist.

There were no errors listed in creating the files in the bits directory.

Exporting backup into 'bits//'
Writing HeaderFrame...
Writing DatabaseVersionFrame...
Writing Attachments...
Writing Avatars...
Writing SharedPrefFrame(s)...
Writing KeyValueFrame(s)...
Writing StickerFrames...
Writing EndFrame...
Writing database...
Done!

2022-12-30 19:16:30 - INFO - Finished signalbackup-tools
2022-12-30 19:16:30 - INFO - Parsing the sqlite database bits/database.sqlite
2022-12-30 19:16:30 - INFO - Unable to find a contact on your phone with ID: 461
2022-12-30 19:16:30 - INFO - Unable to find a contact on your phone with ID: 572
2022-12-30 19:16:30 - INFO - Total num SMS messages: 22310
2022-12-30 19:16:30 - INFO - Total number Signal messages: 88869
2022-12-30 19:16:30 - INFO - Total num MMS messages: 19671
2022-12-30 19:16:30 - INFO - Total number Signal media messages: 5233
2022-12-30 19:16:30 - INFO - Starting SMS and Signal text message export

The only thing that catches my eye is the double slash

[jbaker6953]

I ran the process again and verified that those 56 attachments are NOT created during export.

[alexlance]

Thank you for the useful report. Signal's database format was updated, and this script hadn't been updated.

If you run this:

cd signal-message-exporter git checkout -t -b signal-6-5-6 origin/signal-6-5-6

It'll checkout some changes that I've just pushed up.

Then run the thing again, maybe you'll get some different / more exciting errors.

[jbaker6953]

I don't have git on my system. Is there an easier way than setting up that whole environment in windows?

[alexlance]

You could download the updated file directly:

https://raw.githubusercontent.com/alexlance/signal-message-exporter/signal-6-5-6/signal-message-exporter.py

[jbaker6953]

Reading backup file...
Read entire backup file...

done!

Exporting backup into 'bits//'
Writing HeaderFrame...
Writing DatabaseVersionFrame...
Writing Attachments...
Writing Avatars...
Writing SharedPrefFrame(s)...
Writing KeyValueFrame(s)...
Writing StickerFrames...
Writing EndFrame...
Writing database...
Done!

2022-12-30 23:46:15 - INFO - Finished signalbackup-tools
2022-12-30 23:46:15 - INFO - Parsing the sqlite database bits/database.sqlite
2022-12-30 23:46:15 - INFO - Unable to find a contact on your phone with ID: 461
2022-12-30 23:46:15 - INFO - Unable to find a contact on your phone with ID: 572
2022-12-30 23:46:15 - INFO - Total num SMS messages: 22310
2022-12-30 23:46:15 - INFO - Total number Signal messages: 88869
Traceback (most recent call last):
  File "/root/signal-message-exporter.py", line 254, in <module>
    print_num_mms()
  File "/root/signal-message-exporter.py", line 37, in print_num_mms
    cursor.execute(q)
sqlite3.OperationalError: no such column: type
make: *** [Makefile:6: run] Error 1

[alexlance]

Could you please dive into Signal settings and see what version of Signal you're running.

It'd be useful if I could detect the Signal version from the Signal backup file, but I don't see that information in there - so we need to retrieve it manually.

[jbaker6953]

It's already uninstalled, but it was updated just this morning because Signal refused to let me delete my account until I updated. Prior to that I was still on 5.59 or something thereabouts.

[jbaker6953]

The version was 6.6.8.

[alexlance]

So would this backup file have been generated from the older version of Signal or from the newest version?

[jbaker6953]

The older version.

[jbaker6953]

Is the sqllite structure pretty simple? Can I look in there easily to see if those attachments are really missing?

[alexlance]

Ah ok - then the newer version I just patched isn't going to be much good for you. Revert back to the original one here: https://raw.githubusercontent.com/alexlance/signal-message-exporter/master/signal-message-exporter.py

Feel free to post exact copy-paste of the error that's occurring.

The sqlite schema is a little hairy, but there is a table named mms and a related table named part that contains the mms files.

And some info in this thread about examining the sqlite database: https://github.com/alexlance/signal-sms-mms-importer/issues/7

[jbaker6953]

All 56 of the errors are substantially the same, just different message IDs and attachments.

2022-12-30 19:38:06 - ERROR - File not found for media message: bits/Attachment_752_1508789814077.bin for part: {'_id': 752, 'mid': 1883, 'seq': 0, 'ct': 'image/jpeg', 'name': '', 'chset': None, 'cd': 'DOlLha6Ip4UglfXidOc4wjZZsj4VsDoIXioyC8Zu4+mp9oiZJhMcJwDse8sNy1EzjDvf4uJFZPUnZeZILujTbA==', 'fn': None, 'cid': None, 'cl': '7080573386167961144', 'ctt_s': None, 'ctt_t': None, 'encrypted': None, 'pending_push': 1, '_data': None, 'data_size': 4292872, 'file_name': None, 'thumbnail': None, 'aspect_ratio': None, 'unique_id': 1508789814077, 'digest': b'\xfd\xd2\x81\xc6c\xd3\xea\x89\x9d\x9b9r\xc1kq(\xb9\t\xc9\xb4H1\x9c\x01n\x8e\x13\xde\xa1\xcf^\xa1', 'fast_preflight_id': None, 'voice_note': 0, 'data_random': None, 'thumbnail_random': None, 'width': 0, 'height': 0, 'quote': 0, 'caption': None, 'sticker_pack_id': None, 'sticker_pack_key': None, 'sticker_id': -1, 'data_hash': None, 'blur_hash': None, 'transform_properties': None, 'transfer_file': None, 'display_order': 0, 'upload_timestamp': 0, 'cdn_number': 0, 'borderless': 0, 'sticker_emoji': None, 'video_gif': 0}
2022-12-30 19:38:06 - ERROR - Failed to export this media message: {'_id': 1883, 'thread_id': 1, 'date': 1508789805684, 'date_received': 1508789814000, 'date_server': -1, 'msg_box': 10485780, 'read': 1, 'body': "It was Dean's day to bring home Bitey Eater the spider from school.", 'part_count': 1, 'ct_l': '', 'address': 31, 'address_device_id': None, 'exp': None, 'm_type': 132, 'm_size': None, 'st': 1, 'tr_id': None, 'delivery_receipt_count': 0, 'mismatched_identities': None, 'network_failures': None, 'subscription_id': -1, 'expires_in': 0, 'expire_started': 0, 'notified': 0, 'read_receipt_count': 0, 'quote_id': 0, 'quote_author': None, 'quote_body': None, 'quote_attachment': -1, 'quote_missing': 0, 'quote_mentions': None, 'shared_contacts': None, 'unidentified': 0, 'previews': None, 'reveal_duration': 0, 'reactions': None, 'reactions_unread': 0, 'reactions_last_seen': -1, 'remote_deleted': 0, 'mentions_self': 0, 'notified_timestamp': 0, 'viewed_receipt_count': 0, 'server_guid': None, 'receipt_timestamp': -1, 'ranges': None, 'is_story': 0, 'parent_story_id': 0, 'quote_type': 0, 'export_state': None, 'exported': 0} because [Errno 2] No such file or directory: 'bits/Attachment_752_1508789814077.bin'

[alexlance]

I think maybe the parent application signalbackup-tools didn't extract those attachments into files for some reason.

[alexlance]

signalbackup-tools pulls out all the file attachments (MMSes) and dumps them as files into the bits folder. It sounds like it skipped over those 56 files for some reason...?

[jbaker6953]

Right. I'm trying to figure out a way to manually see if those attachments are there, if they're a specific file type, corrupted, or something. I notice the "name" attribute is null for all of them. Not sure if that's true for the ones that are successfully extracted.

[jbaker6953]

I also noticed that it took about 3 hours to import the XML file and it appears that MMS groups are not maintained. The messages are stored under each individual sender.

[alexlance]

Unfortunately I'm not sure how groups are modelled in MMSes - it's possible that someone could perform a standard sms/mms export via SMS Backup and Restore, then find some group messages in that XML file, and then document that format somewhere, and then I might be able to adjust this script to create that same format.

(the lack of groups thing wasn't much of a problem for me, as I didn't have any Signal groups)

[jbaker6953]

I figured out how to look into it and those 56 messages have a part_count=1 in the mms table, implying they should have a corresponding entry in the parts table, but there is no corresponding row in the parts table. It looks to be a Signal issue.

[jbaker6953]

Looks like group MMS look like this in the XML:

  <mms date="1518477381000" spam_report="0" ct_t="application/vnd.wap.multipart.related" msg_box="2" address="+12025551234~+12025551235~+12025551236" sub_cs="null" re_type="0" retr_st="null" re_original_body="null" d_tm="null" exp="null" locked="0" msg_id="0" app_id="0" from_address="null" m_id="null" retr_txt="null" date_sent="1518477381" read="1" rpt_a="null" ct_cls="null" bin_info="0" pri="129" sub_id="-1" re_content_type="null" object_id="null" resp_txt="null" re_content_uri="null" ct_l="null" re_original_key="null" d_rpt="null" reserved="0" using_mode="0" _id="1433" rr_st="0" m_type="128" favorite="0" rr="null" sub="null" hidden="0" deletable="0" read_status="null" d_rpt_st="0" callback_set="0" seen="1" re_recipient_address="null" device_name="null" cmc_prop="null" resp_st="128" text_only="1" sim_slot="0" st="null" retr_txt_cs="null" creator="org.thoughtcrime.securesms" m_size="null" sim_imsi="null" correlation_tag="null" re_body="null" safe_message="0" tr_id="signal:TMessageId(id=3270, mms=true)" m_cls="personal" v="19" secret_mode="0" re_file_name="null" re_count_info="null" readable_date="Feb 12, 2018 3:16:21 PM" contact_name="Bob Smith, Joe Smith">
    <parts>
      <part seq="0" ct="text/plain" name="null" chset="null" cd="null" fn="null" cid="&lt;signal:text&gt;" cl="null" ctt_s="null" ctt_t="null" text="First message text" sef_type="0" />
    </parts>
    <addrs>
      <addr address="+12025551234" type="151" charset="106" />
      <addr address="+12025551235" type="151" charset="106" />
      <addr address="+12025551236" type="137" charset="106" />
    </addrs>
  </mms>
  <mms date="1518477436000" spam_report="0" ct_t="application/vnd.wap.multipart.related" msg_box="1" address="+12025551234~+12025551235~+12025551236" sub_cs="null" re_type="0" retr_st="null" re_original_body="null" d_tm="null" exp="null" locked="0" msg_id="0" app_id="0" from_address="null" m_id="null" retr_txt="null" date_sent="1518477433" read="1" rpt_a="null" ct_cls="null" bin_info="0" pri="129" sub_id="-1" re_content_type="null" object_id="null" resp_txt="null" re_content_uri="null" ct_l="null" re_original_key="null" d_rpt="null" reserved="0" using_mode="0" _id="1434" rr_st="0" m_type="128" favorite="0" rr="null" sub="null" hidden="0" deletable="0" read_status="null" d_rpt_st="0" callback_set="0" seen="1" re_recipient_address="null" device_name="null" cmc_prop="null" resp_st="128" text_only="1" sim_slot="0" st="null" retr_txt_cs="null" creator="org.thoughtcrime.securesms" m_size="null" sim_imsi="null" correlation_tag="null" re_body="null" safe_message="0" tr_id="signal:TMessageId(id=3272, mms=true)" m_cls="personal" v="19" secret_mode="0" re_file_name="null" re_count_info="null" readable_date="Feb 12, 2018 3:17:16 PM" contact_name="Bob Smith, Joe Smith">
    <parts>
      <part seq="0" ct="text/plain" name="null" chset="null" cd="null" fn="null" cid="&lt;signal:text&gt;" cl="null" ctt_s="null" ctt_t="null" text="Second message text" sef_type="0" />
    </parts>
    <addrs>
      <addr address="+12025551234" type="137" charset="106" />
      <addr address="+12025551235" type="151" charset="106" />
      <addr address="+12025551236" type="151" charset="106" />
    </addrs>
  </mms>

Hope that helps. The 12025551236 number is my phone. I think the "type="137" indicates the originator of the message.

[alexlance]

Perfect thank you! I've pushed a change which should make the address field a tilda-separated list of phone numbers and the contact_name field a comma-separated list of names.

No guarantee it'll import the groups correctly - but unless I've missed something the format now looks very similar to what you've posted. You'll need to fetch the latest copy of the script again.

If it still doesn't create the groups correctly, then I guess there's something else going on.

[alexlance]

This was the change I added: https://github.com/alexlance/signal-message-exporter/blob/90d5ca5598fd427ad704b8c11584ca8184ac7e50/signal-message-exporter.py#L142

[jbaker6953]

It's doing some weird stuff on group messages. Here are some I found in the output XML:

<mms date="1672277748272" ct_t="application/vnd.wap.multipart.related" msg_box="2" rr="null" sub="null" read_status="1"
 address="+12025551212~+12025551213~+12025551214~+12025551215" contact_name ="Bob Smith, Joe Smith, Roger
 Smith" m_id="null" read="1" m_size="None" m_type="128" sim_slot="0">
  <addr address="+12025551212" type="137" charset="UTF-8"/>
  <addr address="+12025551213" type="137" charset="UTF-8"/>
  <addr address="+12025551214" type="137" charset="UTF-8"/>
  <addr address="+12025551215" type="137" charset="UTF-8"/>
</mms>
<mms date="1672112568807" ct_t="application/vnd.wap.multipart.related" msg_box="2" rr="null" sub="null" read_status="1" 
address="+12025551212~+12025551213~+12025551214~+12025551215" contact_name ="Bob Smith, Joe Smith, Roger 
Smith" m_id="null" read="1" m_size="None" m_type="128" sim_slot="0">
    <addr address="+12025551212" type="137" charset="UTF-8"/>
    <addr address="+12025551213" type="137" charset="UTF-8"/>
    <addr address="+12025551214" type="137" charset="UTF-8"/>
    <addr address="+12025551215" type="137" charset="UTF-8"/>
</mms>
<mms date="1672112817790" ct_t="application/vnd.wap.multipart.related" msg_box="2" rr="null" sub="null" read_status="1" 
address="+12025551212~+12025551213~+12025551214~+12025551215" contact_name ="Bob Smith, Joe Smith, Roger 
Smith" m_id="null" read="1" m_size="None" m_type="128" sim_slot="0">
  <addr address="+12025551212" type="137" charset="UTF-8"/>
  <addr address="+12025551213" type="137" charset="UTF-8"/>
  <addr address="+12025551214" type="137" charset="UTF-8"/>
  <addr address="+12025551215" type="137" charset="UTF-8"/>
</mms>

Two things I see:

1. The messages are missing the \<parts> and \<part> structure that carries the actual message content.
2. All addresses are set to type 137.

[alexlance]

Re 1. I've just pushed some code which removes a bit of error suppression - if you download the latest version and run it again - an error will occur and you can let me know what it is (the longer the copy-paste the more helpful).

2. I've never been 100% on what to set these, but it looks like I've left myself a comment suggesting 137 or 151, I've added a branch in the code to try and set it.

[jbaker6953]

It seems to me that the addr structure should be that the sender's address has type=137 and the other members of the group addresses are type=151.

Let me run the latest

[jbaker6953]

I didn't experience any new errors. Just the same errors with attachments as before.

[alexlance]

There's something a bit weird with determining who the sender is when it comes to group messages.

There's a field on each MMS and it states who receives the message. The field is named address and it's on the mms table.

This address field is actually the primary key from the recipient table. The recipient table can contain a single person, or a link off to a group of people.

There's no field I can see that specifies this person was the sender, and that person was the receiver though...

I wonder if it's just the first of the recipients. I.e. if there are 4 recipients to an MMS, just assume that the first number was the sender, and everyone else is the receiver. I can add a modification to the code if you think that's a goer.

[alexlance]

Just the same errors with attachments as before.

This is definitely the same "older" backup file that we were working with earlier?

No file attachments at all? Quite sure? Or just particular messages don't have parts?

[jbaker6953]

1. In my XML exported by the app, the order of the \<addr> varies with each message. Sometimes the sender is first, sometimes last, sometimes in the middle. Faced with this situation my first inclination is to label the group member that is not in the list of recipients. I have to investigate the tables some to say definitively.
2. I get the same 56 messages failed to export because the parts don't exist in '/bits'. This appears to be a Signal issue since these parts aren't in the part table even though the MMS table has part_count=1.

[jbaker6953]

I'm still trying to figure out why there is no

<parts>
      <part />
</parts>

structure in the output.

[jbaker6953]

I think I solved the mystery of how you tell who sent a group message. You have to look up the mms_id in group_receipts. The sender is the one with no matching record in group_receipts for a given mms_id. I'm still doing more research to confirm.

[jbaker6953]

It's even simpler than I thought. The sender of a group message is the person in the mms.address column, except when the message is a sent message instead of a received message. A sent message (one originating from the user) has mms.address set to thread.thread_recipient_id where thread._id = mms.thread_id.

[jbaker6953]

Was finally able to make a small test case database that contains nothing but messages from one MMS group. I tried running the latest script and importing the XML. It didn't go well. Some messages appeared in the group, and some appear under the individual who sent them. None of the text (mms.body) appears in any of the messages. I went back and tried the original script and it's slightly worse. Each message appears under the sender rather than in a group and none of the messages have any text.

[alexlance]

Ok so one thing at a time, I've just pushed a patch to master which sets the "sub" field in the XML file to be the mms.body field. Could you please run that against your test database and see if the mmses are titled properly now?

[jbaker6953]

I'm having a bit of trouble because several of the messages contain carriage returns and those are returned raw, so messages look like this:

sub="Line 1

Line 2"

[jbaker6953]

In the XML generated by the app the message is in the 'text' attribute and multiline text looks like this (same message):

text="Line 1????

Line 2"

[jbaker6953]

For comparison here's the whole \<mms> generated by the 'SMS Backup and Restore" app for this particular message:

<mms date="1614149334000" spam_report="0" ct_t="application/vnd.wap.multipart.related" msg_box="1" address="+12025551212~+12025551213~+12025551214~+12025551215" sub_cs="null" re_type="0" retr_st="null" re_original_body="null" d_tm="null" exp="null" locked="0" msg_id="0" app_id="0" from_address="null" m_id="null" retr_txt="null" date_sent="1614149329" read="1" rpt_a="null" ct_cls="null" bin_info="0" pri="129" sub_id="-1" re_content_type="null" object_id="null" resp_txt="null" re_content_uri="null" ct_l="null" re_original_key="null" d_rpt="null" reserved="0" using_mode="0" _id="11528" rr_st="0" m_type="128" favorite="0" rr="null" sub="null" hidden="0" deletable="0" read_status="null" d_rpt_st="0" callback_set="0" seen="1" re_recipient_address="null" device_name="null" cmc_prop="null" resp_st="128" text_only="1" sim_slot="0" st="null" retr_txt_cs="null" creator="org.thoughtcrime.securesms" m_size="null" sim_imsi="null" correlation_tag="null" re_body="null" safe_message="0" tr_id="signal:TMessageId(id=22748, mms=true)" m_cls="personal" v="19" secret_mode="0" re_file_name="null" re_count_info="null" readable_date="Feb 23, 2021 10:48:54 PM" contact_name="Bob, Joe, Frank">
    <parts>
      <part seq="0" ct="text/plain" name="null" chset="null" cd="null" fn="null" cid="&lt;signal:text&gt;" cl="null" ctt_s="null" ctt_t="null" text="Line 1&#10;&#10;Line 2" sef_type="0" />
    </parts>
    <addrs>
      <addr address="+12025551213" type="137" charset="106" />
      <addr address="+12025551212" type="151" charset="106" />
      <addr address="+12025551214" type="151" charset="106" />
      <addr address="+12025551215" type="151" charset="106" />
    </addrs>
  </mms>

And this is what is generated by signal-message-exporter.py operating on the same message:

<mms date="1614149329000" ct_t="application/vnd.wap.multipart.related" msg_box="1" rr="null" sub="Line 1

Line 2" read_status="1" address="+12025551213" contact_name ="Bob" m_id="null" read="1" m_size="None" m_type="132" sim_slot="0"><addr address="+12025551213" type="151" charset="UTF-8"/></mms>

[alexlance]

Thanks - ignoring the newline thing for a second - are you saying that the value in mms.body is being plugged into the parts -> text attribute?

Like where in the sqlite database is the string Line 1

Line 2 being stored?

[alexlance]

I've pushed another commit to try and tackle the authorship problem. The logic I'm using is a mix of msg_box and the recipient information. I might not have it 100% yet, but getting closer!

[jbaker6953]

Thanks - ignoring the newline thing for a second - are you saying that the value in mms.body is being plugged into the parts -> text attribute?

It appears that way. And when an MMS has both a message and an attachment (such as an image), it creates two \<part> elements: 1 for the message and 1 for the attachment.

Like where in the sqlite database is the string Line 1 Line 2 being stored?

That's coming from mms.body.

[alexlance]

Looks like my previous changes didn't get sent up

[alexlance]

Ok have sent up some changes again, this has the authorship fixes I mentioned previously, as well as having a tweak to set the part -> text attribute to the mms.body, but only if the parts.caption field is empty.

I'm not sure if I actually need to fix the newline problem or not - as the xml writing library would surely not be able to create malformed XML...

[jbaker6953]

I believe placing bare newline characters in XML is a bug. It's acceptable in text/html, but not application/xml.

[jbaker6953]

I can't see that the output has changed from the previous version.

[alexlance]

Looks like this a bug that has been open for over a decade! https://github.com/python/cpython/issues/50002

I just tested manually applying some escaping to the particular attribute named "text" but the actual messages all end up with the encoded characters displayed on the phone.

[alexlance]

Okay pushed some more changes, where I join the thread table onto the sms + mms tables and use the thread.thread_recipient_id to determine where the messages go. Did a full import with my message store and it's looking pretty good!

Will close this issue now - feel free to create a new one if there's still things to fix - but I'll probably put my keyboard down for a little while now!

Thanks for such excellent reporting btw - the full payloads that you pasted made all the difference.

[alexlance]

Just noticed that although all my messages are in the right message thread, the authors of each message can't be determined.

This is insane.

If you set the address field to a single person's phone number, then the message correctly appears to be authored by them, BUT that message appears in its own unique 1-person thread.

Or if you set the address field to the group's tilda-separated list of phone numbers, then the message correctly ends up in the right thread, BUT the identity of the person who authored that message is lost.

Stuck :(

[jbaker6953]

I suspect the authorship is determined by putting the type=137 on the right \<address> element and type=151 on the others.

[jbaker6953]

It's still doing lots of weird stuff with attachments and not setting up the proper XML structure. I suspect it's in the way the code is looping through the rows.

For example, one group message with an attached picture looks like this:

<mms date="1661268745000" ct_t="application/vnd.wap.multipart.related" msg_box="1" rr="null" sub="
Message text." read_status="1" address="+12025551212~+12025551213~+12025551214~+12025551215" contact_name ="Joe, Frank, Bob" m_id="null" read="1" m_size="None" m_type="132" sim_slot="0">
<part seq="0" ct="image/jpeg" name="None" chset="None" cl="None" text="
Message text." data="[base64]"/>
<part seq="0" ct="image/jpeg" name="None" chset="None" cl="None" text="
Message text." data="[base64]"/>
<addr address="+12025551212" type="151" charset="UTF-8"/>
<addr address="+12025551213" type="151" charset="UTF-8"/>
<addr address="+12025551214" type="151" charset="UTF-8"/>
<addr address="+12025551215" type="151" charset="UTF-8"/>
</mms>

There are a number of things wrong here:

1. The XML \<part> and \<addr> elements need to have a proper parent element - \<parts> and \<addrs> - which are not being created.
2. The attachment is repeated twice.
3. The message text is added to each copy of the attachment.
4. There are no \<part> nodes elements for the message text.

I'm going to try my hand at refactoring this code, but Python is new to me so it'll take some time. If I'm successful I'll post back here.

[jbaker6953]

OK. I have fixed the node issue and made it write more readable XML. Here's the raw file. signal-message-exporter.txt

Working on getting the message text into a \<part> and not duplicating parts.