no such table: messages

[Takepy]

When running the script I get following error message

### WhatsApp Database Exporter ###
[+] Reading Database
Traceback (most recent call last):
  File "C:\Users\******\Downloads\WhatsApp\src\main.py", line 75, in <module>
    main()
  File "C:\Users\******\Downloads\WhatsApp\src\main.py", line 63, in main
    chats = query_all_chats(config["input"].get("msgstore_path"), contacts)
  File "C:\Users\******\Downloads\WhatsApp\src\main.py", line 33, in query_all_chats
    Chat(key_remote_jid, subject, sort_timestamp, contacts.get(key_remote_jid, None), query_messages(con, key_remote_jid, contacts))
  File "C:\Users\******\Downloads\WhatsApp\src\main.py", line 19, in query_messages
    for received_timestamp, remote_resource, key_from_me, data, media_caption, media_wa_type in cur.execute(query, {"key_remote_jid": key_remote_jid}):
sqlite3.OperationalError: no such table: messages

Since the error indicates that the table "messages" couldn't be found, I opened up the msgstore.db and manually checked. And well, the error was right and the table is missing.

Did WhatsApp change something to the database or is there something wrong on my end? I pulled the /com.whatsapp/ directory from an emulator which gave me the error. After that I instead copied the msgstore.db.crypt14 from my phone and decrypted it using the key i previously pulled. Both giving me the error.

[chrrel]

Hi @Takepy, I am not aware of any changes made by WhatsApp, but also do not have access to a database file right now. So far you are the first to stumble across this error. Can you find out the name of the table which is used to store your messages now?

[Takepy]

Hey @chrrel There is a table called "message", however it doesn't match with the "messages" table (remote_resource missing, others not matching up). Also it's happening on version 2.22.3.77 and 2.22.5.7 (Beta) of WhatsApp.

Edit: Accidentally closed the issue so I opened it again. Sorry

[chrrel]

Unfortunately, I am unable to reproduce your issue. I just checked the table message with a database belonging to an account using WhatsApp 2.22.3.77. While it has a matching schema (compared to your's), it is completely empty:

sqlite> SELECT * FROM message;
1|-1|0|-1|||||||||||||||0|

Schema:

sqlite> .schema message
CREATE TABLE message (    _id                      INTEGER PRIMARY KEY AUTOINCREMENT,    chat_row_id              INTEGER NOT NULL,    from_me                  INTEGER NOT NULL,    key_id                   TEXT    NOT NULL,    sender_jid_row_id        INTEGER,    status                   INTEGER,    broadcast                INTEGER,    recipient_count          INTEGER,    participant_hash         TEXT,    origination_flags        INTEGER,    origin                   INTEGER,    timestamp                INTEGER,    received_timestamp       INTEGER,    receipt_server_timestamp INTEGER,    message_type             INTEGER,    text_data                TEXT,    starred                  INTEGER,    lookup_tables            INTEGER, sort_id INTEGER NOT NULL DEFAULT 0, message_add_on_flags INTEGER);

I've also seen that this table already existed several months ago in an old database export. Additionally, I did not find any hints on an updated database design in other projects on GitHub.

• Does the table message have any data for you?
• Is this table where your messages are stored? (Did you receive messages already or is your chat history empty?)
• Is this a fresh install of WhatsApp or did you already use this database in the last months?

[Takepy]

• Yes, the table contains data.
• It is where my messages are stored. I have received chat messages for 3 months now.
• I have been using this installation of WhatsApp for about 3 months now (Imported old chat messages due to buying a new phone).

[chrrel]

Okay, I'll leave this issue open for now. As I am currently not able to reproduce this issue, I cannot provide a fix for this. Maybe it's worth to give it another try after waiting some weeks. Thanks for your report!

[karanrajpal14]

For what it's worth, my message table format is the same as what @Takepy reported and am unable to use this program as well. Did you have a chance to look into this? Anything we can do to make things easier to debug for you?

[chrrel]

Hi @karanrajpal14, until now I was not able to reproduce this issue myself. I've now seen that there is a similar issue at https://github.com/KnugiHK/Whatsapp-Chat-Exporter/issues/9. I'll need a working Whatsapp database (at least msgstore.db) to implement support for this new structure. Do you have simple test database containing a few messages that I could use for that?

[karanrajpal14]

I don't have any laying around other than my own. Is there a way in which I could truncate the data out for you and just give you enough for you to go on?

One thing that I did notice is that the Whatsapp Viewer project does implement this in some way. Maybe you could use that as a reference and I could perhaps pull the branch that you're working on and check for you? That's an option (albeit slow and inconvenient) as well.

[chrrel]

Sorry for the late reply. I am still not sure whether message is an old legacy table or the new format. Nevertheless, we can still at least try to get this working. For this, I'd need the following data.

a) The output of the command .schema message that you receive when opening your database manually. b) Some test data from the message table as a CSV file. Please replace your private messages and the phone numbers of your contacts with test data.

You can use the following commands for this. Feeld free to attach the results to this issue.

$ sqlite3 msgstore.db
sqlite> .schema message
sqlite> .mode csv
sqlite> .output message.csv
sqlite> SELECT * FROM message LIMIT 20;
sqlite> .output stdout

[karanrajpal14]

Hey! Thanks for looking into this. I appreicate it!

Here's everything that you require:

Schema

sqlite> .schema message
CREATE TABLE message (    _id                      INTEGER PRIMARY KEY AUTOINCREMENT,    chat_row_id              INTEGER NOT NULL,    from_me                  INTEGER NOT NULL,    key_id                   TEXT    NOT NULL,    sender_jid_row_id        INTEGER,    status                   INTEGER,    broadcast                INTEGER,    recipient_count          INTEGER,    participant_hash         TEXT,    origination_flags        INTEGER,    origin                   INTEGER,    timestamp                INTEGER,    received_timestamp       INTEGER,    receipt_server_timestamp INTEGER,    message_type             INTEGER,    text_data                TEXT,    starred                  INTEGER,    lookup_tables            INTEGER, sort_id INTEGER NOT NULL DEFAULT 0, message_add_on_flags INTEGER);
CREATE UNIQUE INDEX message_key_index on message (chat_row_id, from_me, key_id, sender_jid_row_id);
CREATE INDEX message_starred_index on message (starred);
CREATE INDEX message_type_chat_index on message (chat_row_id, message_type);
CREATE INDEX message_chat_id_index ON message (chat_row_id,_id);
CREATE INDEX message_type_index ON message(message_type);
CREATE INDEX message_sort_id_index ON message (sort_id);
CREATE TRIGGER message_bd_for_audio_data_trigger BEFORE DELETE ON message BEGIN DELETE FROM audio_data WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_labeled_messages_fts_trigger BEFORE DELETE ON message BEGIN DELETE FROM labeled_messages_fts WHERE docid=old._id; END;
CREATE TRIGGER message_bd_for_labeled_messages_trigger BEFORE DELETE ON message BEGIN DELETE FROM labeled_messages WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_add_on_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_add_on WHERE parent_message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_broadcast_ephemeral_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_broadcast_ephemeral WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_ephemeral_setting_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_ephemeral_setting WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_ephemeral_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_ephemeral WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_external_ad_content_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_external_ad_content WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_forwarded_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_forwarded WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_ftsv2_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_ftsv2 WHERE docid=old._id; END;
CREATE TRIGGER message_bd_for_message_future_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_future WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_group_invite_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_group_invite WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_link_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_link WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_location_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_location WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_media_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_media WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_mentions_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_mentions WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_order_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_order WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_payment_invite_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_payment_invite WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_privacy_state_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_privacy_state WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_product_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_product WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_quoted_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_quoted WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_rating_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_rating WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_revoked_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_revoked WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_send_count_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_send_count WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_status_psa_campaign_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_status_psa_campaign WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_streaming_sidecar_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_streaming_sidecar WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_system_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_system WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_template_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_template WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_text_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_text WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_thumbnail_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_thumbnail WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_ui_elements_reply_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_ui_elements_reply WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_ui_elements_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_ui_elements WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_vcard_jid_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_vcard_jid WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_vcard_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_vcard WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_view_once_media_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_view_once_media WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_messages_hydrated_four_row_template_trigger BEFORE DELETE ON message BEGIN DELETE FROM messages_hydrated_four_row_template WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_missed_call_logs_trigger BEFORE DELETE ON message BEGIN DELETE FROM missed_call_logs WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_mms_thumbnail_metadata_trigger BEFORE DELETE ON message BEGIN DELETE FROM mms_thumbnail_metadata WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_played_self_receipt_trigger BEFORE DELETE ON message BEGIN DELETE FROM played_self_receipt WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_receipt_device_trigger BEFORE DELETE ON message BEGIN DELETE FROM receipt_device WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_receipt_user_trigger BEFORE DELETE ON message BEGIN DELETE FROM receipt_user WHERE message_row_id=old._id; END;
CREATE INDEX message_chat_sort_id_index ON message (chat_row_id, sort_id);
CREATE INDEX message_starred_sort_id_index ON message (starred, sort_id);
CREATE TRIGGER message_bd_for_agent_message_attribution_trigger BEFORE DELETE ON message BEGIN DELETE FROM agent_message_attribution WHERE message_row_id=old._id; END;
CREATE TRIGGER message_bd_for_message_poll_trigger BEFORE DELETE ON message BEGIN DELETE FROM message_poll WHERE message_row_id=old._id; END;

Messages CSV

message.csv

From what I can tell, this table does not hold any contact info for my contacts so there's a likelihood you might need the schema of a few other tables as well. I'd be happy to provide them. Please let me know if you need anything else as well.

[chrrel]

Hi, you are right. Seems like there have been changes in other tables as well. I tried to implement this based on the data you provided but that does not seem to be sufficient. Working further on this without having a complete database makes this more complex than necessary, I guess.

I'll leave this issue open for now. Feel free to implement support for the new message table on your own if you want to. :smile:

[karanrajpal14]

Ugh, that's unfortunate. I'm not sure if I even know where to start doing something like that, unfortunately. Is there anything that I can do to? Maybe scramble the text in the messages or something like that?

[chrrel]

You can try to adjust the SQL queries in main.py according to your new tables. Otherwise, I'll simply wait some time until I get access to a whatsapp database follwoing this new design and then maybe implement it on my own.

[karanrajpal14]

I'll do my best. And also, in the meantime, I'll see if I can just mask my personal data and give you access to my db if I'm successful with that. Hopefully, we can get this resolved soon.

[josh-shaw-dev]

I came across the same problem. New phone and couldnt export the messages. I ended up doing something in C# as a bit of fun and used the below to pull the messages

Might be of use to someone else...

SELECT 
    raw_string_jid AS RawStringJid,
    mv._id AS MessageId,
    mv.sort_id AS MessageSortId,
    mv.from_me AS MessageFromMe,
    mv.chat_row_id as ChatId,
    mv.received_timestamp AS MessageRecievedTime,
    mv.text_data AS MessageText,
    mm.file_path AS MediaFilePath
FROM message_view mv
LEFT JOIN message_media mm ON mm.message_row_id = mv._id 
LEFT JOIN chat_view cv ON cv._id = mv.chat_row_id;

[chrrel]

Good news: I finally was able to obtain a message database in the new format and am working on a fix.

[chrrel]

Closed with #6.

Some insights on the formats can be found here:

• Old: https://blog.group-ib.com/whatsapp_forensic_artifacts
• New: https://thebinaryhick.blog/2022/06/09/new-msgstore-who-dis-a-look-at-an-updated-whatsapp-on-android/

[chrrel]

@karanrajpal14 @Takepy This release should also work for your database now. :)

[Takepy]

@chrrel Just tried it and it works perfectly. Thank you so much for fixing this