Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

No such table: messages (Edit: Support for WhatsApp databases with "message" table) #9

Closed
Takepy opened this issue Feb 18, 2022 · 74 comments
Assignees
Labels
enhancement New feature or request

Comments

@Takepy
Copy link

Takepy commented Feb 18, 2022

When running the script I get following error message

Gathering contacts...(97)
Traceback (most recent call last):
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\Scripts\wtsexporter.exe\__main__.py", line 7, in <module>
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\site-packages\Whatsapp_Chat_Exporter\__main__.py", line 147, in main
    messages(db, data)
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\site-packages\Whatsapp_Chat_Exporter\extract.py", line 88, in messages
    c.execute("""SELECT count() FROM messages""")
sqlite3.OperationalError: no such table: messages

My database doesn't contain the table messages, I only have one called message which contains the data but the schema of the table looks different from what it's supposed to look like.
2022-02-14_22-28-04
What could be the issue here?

@KnugiHK
Copy link
Owner

KnugiHK commented Feb 18, 2022

What is the WhatsApp version and which platform (Android/iOS) are you using?

@Takepy
Copy link
Author

Takepy commented Feb 18, 2022

I'm using Android.
WhatsApp versions that have been used are 2.22.3.77 and 2.22.5.7 (Beta)
2.22.3.77 was running on an emulator so that I could pull the /com.whatsapp/ directory.
On the 2.22.5.7 version I used the previously pulled key to decrypt the db.

@KnugiHK KnugiHK added bug Something isn't working help wanted Extra attention is needed labels Feb 18, 2022
@KnugiHK
Copy link
Owner

KnugiHK commented Feb 18, 2022

Are both versions and environments result in same database schema?

@Takepy
Copy link
Author

Takepy commented Feb 18, 2022

Yes, they both resulted in the same database schema

@KnugiHK
Copy link
Owner

KnugiHK commented Feb 18, 2022

Interesting. I will try to reproduce it with 2.22.3.77 on emulator.

@KnugiHK
Copy link
Owner

KnugiHK commented Feb 19, 2022

I tried to login WhatsApp with 2.22.3.77 on emulator. However, the database includes the "messages" table. Do you remember when you installed WhatsApp on the emulator for the first time (i.e. creation date of the database)?

@Takepy
Copy link
Author

Takepy commented Feb 19, 2022

I installed WhatsApp on the emulator on the 9th of this month and then imported my chat history using the chat backup in WhatsApp.
On my actual phone, WhatsApp has been installed like 6 months ago.

@KnugiHK
Copy link
Owner

KnugiHK commented Feb 21, 2022

I checked some similar projects and suspect that "message" table is a legacy table for storing messages. On your phone, is the WhatsApp conversation restored from old backup after installation?

@Takepy
Copy link
Author

Takepy commented Feb 21, 2022

I restored the WhatsApp conversations from Google Drive (doing weekly backups).
Never deleted my WhatsApp chat history though so the messages go all the way back to 2013

@KnugiHK
Copy link
Owner

KnugiHK commented Feb 21, 2022

As far as I observed, it is possible that "message" was used in the early years of WhatsApp. Could you check if new installation without backup will result the same situation?

@Takepy
Copy link
Author

Takepy commented Feb 21, 2022

I'll give it a try later and report back.

@Takepy
Copy link
Author

Takepy commented Feb 22, 2022

Sorry, it took a little bit longer until I had time to try it out.
Setup a new emulator, installed WhatsApp and didn't import my backup.
The table messages exists now.

So I'm guessing my backup is just too old for it to work then? :(

@KnugiHK
Copy link
Owner

KnugiHK commented Feb 23, 2022

Sorry, it took a little bit longer until I had time to try it out.

No problem!

So I'm guessing my backup is just too old for it to work then? :(

I think so.
I would like to add support for older version. However, I don't have such database, hence, I am not able to do it. If you have time and you want the script to work with your database, you can take a look inside the database and fine tune the script.
Anyway, I will keep my eyes on the opportunity for supporting old databases. Thanks for your report!

@KnugiHK KnugiHK added enhancement New feature or request and removed bug Something isn't working labels Feb 23, 2022
@KnugiHK KnugiHK changed the title no such table: messages No such table: messages Feb 23, 2022
@KnugiHK KnugiHK changed the title No such table: messages No such table: messages (Old WhatsApp database not supported) Feb 23, 2022
@KnugiHK KnugiHK changed the title No such table: messages (Old WhatsApp database not supported) No such table: messages (Old WhatsApp databases are not supported) Feb 23, 2022
@KnugiHK KnugiHK changed the title No such table: messages (Old WhatsApp databases are not supported) No such table: messages (Edit: old WhatsApp databases are not supported) Feb 23, 2022
@KnugiHK KnugiHK removed the help wanted Extra attention is needed label Feb 24, 2022
@Unbrick
Copy link

Unbrick commented Apr 6, 2022

Hey everyone! I just stumbled upon the same issue with WhatsApp version 2.22.7.73 on Android on two different devices using two different phone numbers without restoring any backup.

I'll try to reinstall WhatsApp and try it again, but it seems like the database scheme is changing. The new messages table has the following schema:

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, 
  message_add_on_flags INTEGER, sort_id INTEGER NOT NULL DEFAULT 0
);

Edit: Tried a second time, database scheme is still the same.

Edit2: In WhatsApp version 2.21.21.17 both tables messages and message do seem to exist.

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 6, 2022

I'll try to reinstall WhatsApp and try it again, but it seems like the database scheme is changing. The new messages table has the following schema:

I can produce the same result on 2.22.7.73 and "messages" table no longer exist.

@Takepy As I can reproduce your situation now, I will work on supporting "message" table.

@KnugiHK KnugiHK self-assigned this Apr 6, 2022
@KnugiHK
Copy link
Owner

KnugiHK commented Apr 6, 2022

I am curious about how WhatsApp decides which table to store messages 🤨. Is "message" a legacy table? Or "messages" is the legacy one?

@Unbrick
Copy link

Unbrick commented Apr 6, 2022

I am curious about how WhatsApp decides which table to store messages 🤨. Is "message" a legacy table? Or "messages" is the legacy one?

I'm not certain, using the outdated WhatsApp version both tables exist, in the more recent version (downloaded from whatsapp.com yesterday) only the message table existed.

Edit: Just checked, in my current setup (outdated version) messages are only stored in the messages table (which works awesome with your script!) and the message table appears to be empty.

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 6, 2022

WhatsApp using "message" table spread different types of message to many other tables. Good chance for me to rewrite the project :)

@KnugiHK KnugiHK pinned this issue Apr 25, 2022
@KnugiHK KnugiHK changed the title No such table: messages (Edit: old WhatsApp databases are not supported) No such table: messages (Edit: WhatsApp databases with "message" table are not supported) Apr 25, 2022
@nahoj
Copy link
Contributor

nahoj commented Mar 17, 2023

I had to cherry-pick the fix for #32 and then it worked perfectly. Thank you for making this tool!

➜  Whatsapp-Chat-Exporter git:(message_table)$ git cherry-pick 2ba5571
➜  Whatsapp-Chat-Exporter git:(message_table)$ pip install '.[android_backup]'

@KnugiHK
Copy link
Owner

KnugiHK commented Mar 25, 2023

Hello everyone! We are finally moving to the last step before releasing this update.

I merged the logic for processing old and new schema in extract.py in commit fb88124. As it is not a commit specifically for the message table, it will not be merged into the message_table branch, and the branch will not be updated from now on. And changes will be committed to the dev branch as usual.

Feel free to try it, especially for those with databases with both message and messages tables, to see if there is any compatibility issue.

@umjediqualquer
Copy link

umjediqualquer commented Apr 8, 2023

Hey there! Not sure if it's related, but I was having the same issue related to this thread, downloaded both the commit and the dev branch versions and now I'm getting a different error:

Traceback (most recent call last):
  File "/home/user/.local/bin/wtsexporter", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/__main__.py", line 229, in main
    messages(db, data)
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/extract.py", line 249, in messages
    data[content["key_remote_jid"]].add_message(content["_id"], Message(
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'dict' object has no attribute 'add_message'

Again, not sure if it's related, but what should I do in this case, please?

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 8, 2023

Hey there! Not sure if it's related, but I was having the same issue related to this thread, downloaded both the commit and the dev branch versions and now I'm getting a different error:

Traceback (most recent call last):
  File "/home/user/.local/bin/wtsexporter", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/__main__.py", line 229, in main
    messages(db, data)
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/extract.py", line 249, in messages
    data[content["key_remote_jid"]].add_message(content["_id"], Message(
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'dict' object has no attribute 'add_message'

Again, not sure if it's related, but what should I do in this case, please?

@umjediqualquer
What's the command you issued and does wa.db exists?

@umjediqualquer
Copy link

Hiya there @KnugiHK, I ran wtsexporter -a. I have msgstore.db, wa.db and the WhatsApp folder (containing the Backups, Databases and Media folders) in the directory I tried to run the command.

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 8, 2023

Hiya there @KnugiHK, I ran wtsexporter -a. I have msgstore.db, wa.db and the WhatsApp folder (containing the Backups, Databases and Media folders) in the directory I tried to run the command.

@umjediqualquer I did a quick fix on it (b371587) in the dev branch. Would you please check if the fix works?

@umjediqualquer
Copy link

@umjediqualquer I did a quick fix on it (b371587) in the dev branch. Would you please check if the fix works?

Managed to make some progress, getting a different error message now:

Gathering messages...(694764/694764)
Gathering media...(43618/43618)
Traceback (most recent call last):
  File "/home/user/.local/bin/wtsexporter", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/__main__.py", line 231, in main
    vcard(db, data)
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/extract.py", line 491, in vcard
    file_name = "".join(x for x in media_name if x.isalnum())
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: 'NoneType' object is not iterable```

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 8, 2023

@umjediqualquer I did a quick fix on it (b371587) in the dev branch. Would you please check if the fix works?

Managed to make some progress, getting a different error message now:

Gathering messages...(694764/694764)
Gathering media...(43618/43618)
Traceback (most recent call last):
  File "/home/user/.local/bin/wtsexporter", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/__main__.py", line 231, in main
    vcard(db, data)
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/extract.py", line 491, in vcard
    file_name = "".join(x for x in media_name if x.isalnum())
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: 'NoneType' object is not iterable```

Do you know if your msgstore.db use message or messages table?

@umjediqualquer
Copy link

Do you know if your msgstore.db use message or messages table?

I'm assuming it's message because when running the release version, I get the sqlite3.OperationalError: no such table: messages error, but I can open this with a db viewer to double check.

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 8, 2023

Do you know if your msgstore.db use message or messages table?

I'm assuming it's message because when running the release version, I get the sqlite3.OperationalError: no such table: messages error, but I can open this with a db viewer to double check.

Attempt on fixing it is pushed (06a1d34). See if that works (the error may disappear but it is not a good fix IMO).

@umjediqualquer
Copy link

umjediqualquer commented Apr 8, 2023

Attempt on fixing it is pushed (06a1d34). See if that works (the error may disappear but it is not a good fix IMO).

The previous error has been fixed, but it threw a new error a few lines after:

Gathering messages...(694764/694764)
Gathering media...(43618/43618)
Traceback (most recent call last):
  File "/home/user/.local/bin/wtsexporter", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/__main__.py", line 231, in main
    vcard(db, data)
  File "/home/user/.local/lib/python3.11/site-packages/Whatsapp_Chat_Exporter/extract.py", line 496, in vcard
    data[row["key_remote_jid"]].messages[row["message_row_id"]].data = media_name + \
                                                                       ^^^^^^^^^^^^^^
TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'

Is there a way to extract only the chat without the media? It's weekend and I don't to keep bothering you with this haha

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 8, 2023

Is there a way to extract only the chat without the media?

There is no way to extract chat only. Maybe it is a good option to add.

It's weekend and I don't to keep bothering you with this haha

That's fine. The bug has to be fixed anyway. And I reply only when I am available.

As for the bug, I committed another attempt to fix it (7c0b90d). This time I modified my database to test the problem myself and it works for me. Also, maybe the file name should rely on the information inside the vcards instead of the message data (the current implementation).

@umjediqualquer
Copy link

@KnugiHK it worked! Seems to have generated everything correctly and didn't throw any errors :D

Thank you very much for your help, especially on a weekend! May I buy you a cup of coffee (or tea, it that's your cup, heh) as a token of gratitude?

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 9, 2023

@KnugiHK it worked! Seems to have generated everything correctly and didn't throw any errors :D

Thank you very much for your help, especially on a weekend! May I buy you a cup of coffee (or tea, it that's your cup, heh) as a token of gratitude?

Glad that works for you too! And thanks for your report! The point for not releasing this update yet is to let everyone test it and spot problems.

Also, I appreciate your offer to buy me a cup of coffee or tea, but it's not necessary. If you'd like to show your support, consider staring this repo instead. That meant a lot to me😆. Thanks again.

@Signum21
Copy link

Signum21 commented Apr 17, 2023

Hi, I cloned the "message_table" branch and run the tool with this command:
python3 __main__.py -a --db ./msgstore.db

The messages are recovered correctly but i noticed that two identical "WhatsApp" folders are created.
One outside and one inside the "result" folder.
This might be a bug.

The two folders structure are:

./WhatsApp/
./WhatsApp/vCards/

./result/
./result/messages.html
./result/WhatsApp/
./result/WhatsApp/vCards/

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 18, 2023

Hi, I cloned the "message_table" branch and run the tool with this command: python3 main.py -a --db ./msgstore.db

The messages are recovered correctly but i noticed that two identical "WhatsApp" folders are created. One outside and one inside the "result" folder. This might be a bug.

The two folders structure are:

./WhatsApp/ ./WhatsApp/vCards/

./result/ ./result/messages.html ./result/WhatsApp/ ./result/WhatsApp/vCards/

@Signum21
Did "Copying media directory..." printed in your terminal? I guess your problem is related to #25.

Also, "message_table" branch is no longer maintained. Try dev branch instead.

@Signum21
Copy link

Signum21 commented Apr 18, 2023

I switched to dev branch.
"Copying media directory..." printed in the terminal.
I think the problem is not related to #25 cause I don't have the media folder so it's not a problem of moving instead of copying, it's creating two copies of the same folder from zero.
I'm working only with the msgstore.db file.

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 18, 2023

I switched to dev branch. "Copying media directory..." printed in the terminal. I think the problem is not related to #25 cause I don't have the media folder so it's not a problem of moving instead of copying, it's creating two copies of the same folder from zero. I'm working only with the msgstore.db file.

vCards will be exported to ./WhatsApp/vCards and copied to the output directory by default. If you don't want two identical folders, try -c (--move-media) flag.

@KnugiHK
Copy link
Owner

KnugiHK commented Apr 25, 2023

The output treats deleted message as not supported message in the new schema. It is fixed in c7a01bb.

@UsamaAshfaq
Copy link

WhatsApp using "message" table spread different types of message to many other tables. Good chance for me to rewrite the project :)

Kindly do it, I am also getting same error : sqlite3.OperationalError: no such table: messages
In my case Messages are stored in "message" table.

@KnugiHK
Copy link
Owner

KnugiHK commented May 8, 2023

WhatsApp using "message" table spread different types of message to many other tables. Good chance for me to rewrite the project :)

Kindly do it, I am also getting same error : sqlite3.OperationalError: no such table: messages In my case Messages are stored in "message" table.

The support of the message table is completed and will be released soon.

@KnugiHK
Copy link
Owner

KnugiHK commented May 16, 2023

Support with new schema has been released in 0.9.0. I am closing this issue. Further problems should be reported on a dedicated issue. Thanks to everyone who contributed to the support of the new schema!

@KnugiHK KnugiHK closed this as completed May 16, 2023
@KnugiHK KnugiHK removed the priority Priority to be fixed or implemented label May 16, 2023
Goblincomet added a commit to Goblincomet/WhatsApp-Chat-Exporter that referenced this issue Sep 15, 2023
@KnugiHK KnugiHK unpinned this issue Jun 26, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests