No "messages" table in newer database

[bernardgut]

When I tried to run your script on the new db it gave me an error on line 135.

After inspection it seems the newer DB does not contain a messages table. What is your suggestion ?

[SenCodeMaker]

sorry i had'nt much time these days but a solution whould be simply to delete that line

[r0b0pp1]

Many thanks to @SenCodeMaker for making available these scripts!

Had the same issue. Skipping the execution of the SQL commands involving the message table and its variants resolved the issue.

However, at the risk of sounding off-topic, I was wondering @bernardgut, if you are attempting to merge whataspp DBs with the intent of restoring the merged DB back into whatsapp? If yes, did it restore successfully into Whatsapp? (merging the new messages into the message table and leaving the old messages in messages --> the outcome of this tool).

I am embarking on a similar task as per instructions in this forum: https://forum.xda-developers.com/t/guide-merge-multiple-whatsapp-backup-file-into-one-no-root-required.3142974/page-9

However, like you have noticed as well, I realised that the newer DB on my new phone has only a message table, whereas my old DB has both the messages and an empty message table. Now I am on my last straws thinking how to merge the 2 backups with different database schema.

I tried some experiments as follows:

1. Attempted sending new messages on the old backup file in Official Whatsapp to observe which tables the new text messages get appended to in the latest version of the app
   Result: New messages are still appended to the old messages table. The new message table remains empty.
   (Behaviour of the latest version of Whatsapp to date, if you have restored from an older backup)

2. Attempted to load the decrypted, merged msgstore.db file into Fouad's WA Mod for restoration
   Result: Restoration completed successfully, but all the new messages were missing from the chats, meaning that WA only managed to read from the messages table, and somehow reading from the message table went awry.

• I could tell there was some attempt to read from the message table though, as all the most recent message previews (those shown as a preview under each contact in the Chats tab) were shown as blank.
• Restoring just the old DB into Fouad WA did not feature this issue with the missing message previews.
  I'm sorry I can't attach a screenshot as I have since uninstalled the mod and didn't take one at the time

3. [Planning to attempt] Loading the re-encrypted merged msgstore.db.crypt14 from the modded WA into Official Whatsapp
   I'm currently locked out of all Whatsapps for 13 hours or so due to consecutive reinstallation attempts.

• Will re-attempt this again when able, but just thought to ask a fellow facing a similar situation to see if I should cut my losses early
• I'm clinging onto the faint hope that Official Whatsapp is able to process both the messages and message tables having contents more gracefully than the modded app, but I am getting rather disillusioned.

Kinda desperate, so any insights/observations from your end would be greatly appreciated! >.<

[SenCodeMaker]

hi n0bl3ss30bl1g3 i see you're working hard there. I can put myself in your shoes i had too also.

When i read you i think the database your obtain is valid but FMMods need help to read it correctly, try and see the solution i exposed in this post

[r0b0pp1]

Hey I wasn't expecting such a quick response, or even a response at all, so my thanks to you! Thank you for the kind acknowledgement as well. I have had no shortage of people telling me to give up over the past week where I am, so your words are endearing to hear. I have no doubt that you have spared no efforts in writing this utility yourself :)

I think I have come across your post before, but I took a quick read through again. I am not entirely sure if the situation is the same.

While I did get a "chat history is corrupted" error once, I found a way to resolve it.

Apparently it has to do with the .db-shm and .db-wal files that are created alongside the original msgstore.db in the /databases directory of FMMods.

• I recognised those as some temp files/editing files that are created whenever we open the sqlite db.
• For me at first, I just replaced the msgstore.db with my merged copy, and because mine doesnt match with the .db-shm and .db-wal, I think there's where the issues come

So here is what I tried:

1. First I tried to force close the whatsapp mod, but it didn't get rid of the temp files.
2. Since they wouldn't go away, I deleted the .db-shm and .db-wal files when I copied in my modified msgstore.db (I figured maybe FMMods will re-create them when connecting to the DB)

(2) works and I didn't even get the chat history corrupted error this time. There were no errors, nothing. Chats were loading in the Chats tab, including all the messages of the old DB when I expand each chat. But the new DB's messages were nowhere to be found. And the message previews glitch I mentioned in my last comment.

Maybe I am misunderstanding and you actually are referring to the same scenario, but I had some other thoughts...

Because I have these 2 tables:
(A) messages (old DB messages only),

and (B) message (new DB messages only)

I wonder if any keys/ids need to be running across both tables...
But I shudder to think about having to modify the keys in all the sub-tables too...

OR I have to find a way to put all messages in either (A) or (B) and not both. (also a scary thought when I have 500,000 msgs in the old DB and 5,000 msgs in the new DB, and counting...)

My SQL isn't that strong to be able to do either :,(

The quicker answer would be if anyone has had the merged DB with chat messages in both message and messages table to confirm they were able to load back, if not, I will just wait for my Whatsapp to be unlocked and try again at a later date.

I'll share any future findings I have on this thread too, but may not be that soon >.<

[DarkXonline]

Hey I wasn't expecting such a quick response, or even a response at all, so my thanks to you! Thank you for the kind acknowledgement as well. I have had no shortage of people telling me to give up over the past week where I am, so your words are endearing to hear. I have no doubt that you have spared no efforts in writing this utility yourself :)

I think I have come across your post before, but I took a quick read through again. I am not entirely sure if the situation is the same.

While I did get a "chat history is corrupted" error once, I found a way to resolve it.

Apparently it has to do with the .db-shm and .db-wal files that are created alongside the original msgstore.db in the /databases directory of FMMods.

• I recognised those as some temp files/editing files that are created whenever we open the sqlite db.
• For me at first, I just replaced the msgstore.db with my merged copy, and because mine doesnt match with the .db-shm and .db-wal, I think there's where the issues come

So here is what I tried:

1. First I tried to force close the whatsapp mod, but it didn't get rid of the temp files.
2. Since they wouldn't go away, I deleted the .db-shm and .db-wal files when I copied in my modified msgstore.db (I figured maybe FMMods will re-create them when connecting to the DB)

(2) works and I didn't even get the chat history corrupted error this time. There were no errors, nothing. Chats were loading in the Chats tab, including all the messages of the old DB when I expand each chat. But the new DB's messages were nowhere to be found. And the message previews glitch I mentioned in my last comment.

Maybe I am misunderstanding and you actually are referring to the same scenario, but I had some other thoughts...

Because I have these 2 tables: (A) messages (old DB messages only),

and (B) message (new DB messages only)

I wonder if any keys/ids need to be running across both tables... But I shudder to think about having to modify the keys in all the sub-tables too...

OR I have to find a way to put all messages in either (A) or (B) and not both. (also a scary thought when I have 500,000 msgs in the old DB and 5,000 msgs in the new DB, and counting...)

My SQL isn't that strong to be able to do either :,(

The quicker answer would be if anyone has had the merged DB with chat messages in both message and messages table to confirm they were able to load back, if not, I will just wait for my Whatsapp to be unlocked and try again at a later date.

I'll share any future findings I have on this thread too, but may not be that soon >.<

HOLY BAZANGGOS THANK YOU SO MUCH FOR THE TIPS
Deleting the msgstore.db-shm and msgstore.db-wal works well, though it is not solving the issue of merging the database, at the very least I can see my 2 years worth of old messages again that have been lost because of my stupidity

Though if I can merge it with new database it will be more great. if the creator or you find any ways to make that happen it will be appreciated

as for right now, all I can see is too many errors when doing the script

[SenCodeMaker]

it seems that you've differents whatapps database version than what i saw so far, what i would advised is simply to try to map all to the new database design.
In what I see it seems it would consist to move old from messages to message. You can try to do that by modifying the script to select from messages and insert into message.

only try it on copy of your db so in case you end up with a corrupt database you can go back to previous state

[MrBarbie]

Could you maybe update both databases to the current format?

The way I think this could be done would be to ask it to do a backup and then restore.
One potential way I can think of given it looks like you have root access is to copy your old msgstore.db into the right whatsapp location (always take a backup of current state first), then tell it to backup. You can then copy the local encrypted backup and store it aside. Here you can either reset and restore or I think you can use the decryption tools to unencrypt the backup with your key, both should give you the unencrypted msgstore.db that hopefully is now in the new format. If required repeat with your 'newer' db too so that they are both the same format. I havent tried this myself but in theory i think it should work?

[MrBarbie]

@SenCodeMaker if I have both databases in the new format do you think it would work if I just change the "messages" to "message" in the code section below?

update messages set _id = -(_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="messages")) where _id > 0; update messages set _id = -_id where _id < 0;

and also where there are
(SELECT seq FROM oldest.sqlite_sequence WHERE name="messages")

[MrBarbie]

The above worked in addition to changing all the messages_* tables to non plural form. Ie messages_links to message_link, messages_vcards_jids to message_vcard_jid and messages_quotes to message_quoted.
It now passes through all of those but gets stuck on update message_quoted set _id = -(_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where _id > 0; as the message_quoted table does not have a _id column. I have attached a picture of the table below, any suggestions on what we need to change this to? Do we change it to message_row_id as is for _link, _vcard tables?

After this section it also gets stuck on
update message_quoted set quoted_row_id = -(quoted_row_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where quoted_row_id > 0; update message_quoted set quoted_row_id = -quoted_row_id where quoted_row_id < 0; update message set quoted_row_id = -(quoted_row_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where quoted_row_id > 0; update message set quoted_row_id = -quoted_row_id where quoted_row_id < 0;
because there is no quoted_row_id column in the refered tables

[SenCodeMaker]

hello again. congrats for your advancements I see you're making progress. I would say that it's because whtasapps datastructure evolved since the inception of the script. the solution might simply be to adapt the column name to the new ID column name. the database has always the right. this don't succeeding is only problematic in the sense that link between message and quotations would get broken but all the messages would at least be present in conversations Le mer. 12 oct. 2022 à 07:45, MrBarbie ***@***.***> a écrit :

…

The above worked in addition to changing all the messages_* tables to non plural form. Ie messages_links to message_link, messages_vcards_jids to message_vcard_jid and messages_quotes to message_quoted. It now passes through all of those but gets stuck on update message_quoted set _id = -(_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where _id > 0; as the message_quoted table does not have a _id column. I have attached a picture of the table below, any suggestions on what we need to change this to? Do we change it to message_row_id as is for _link, _vcard tables? [image: image] <https://user-images.githubusercontent.com/88872912/195267989-b969234d-12d4-4d49-bd55-df50fddd7ae2.png> After this section it also gets stuck on update message_quoted set quoted_row_id = -(quoted_row_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where quoted_row_id > 0; update message_quoted set quoted_row_id = -quoted_row_id where quoted_row_id < 0; update message set quoted_row_id = -(quoted_row_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where quoted_row_id > 0; update message set quoted_row_id = -quoted_row_id where quoted_row_id < 0; because there is no quoted_row_id column in the refered tables — Reply to this email directly, view it on GitHub <#1 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKJU2L2EIWMBXRTVOE6S7BDWCZNATANCNFSM5XOHXZ5A> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[MrBarbie]

hello again. congrats for your advancements I see you're making progress. I would say that it's because whtasapps datastructure evolved since the inception of the script. the solution might simply be to adapt the column name to the new ID column name. the database has always the right. this don't succeeding is only problematic in the sense that link between message and quotations would get broken but all the messages would at least be present in conversations Le mer. 12 oct. 2022 à 07:45, MrBarbie @.> a écrit :
…
The above worked in addition to changing all the messages_ tables to non plural form. Ie messages_links to message_link, messages_vcards_jids to message_vcard_jid and messages_quotes to message_quoted. It now passes through all of those but gets stuck on update message_quoted set _id = -(_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where _id > 0; as the message_quoted table does not have a _id column. I have attached a picture of the table below, any suggestions on what we need to change this to? Do we change it to message_row_id as is for _link, _vcard tables? [image: image] https://user-images.githubusercontent.com/88872912/195267989-b969234d-12d4-4d49-bd55-df50fddd7ae2.png After this section it also gets stuck on update message_quoted set quoted_row_id = -(quoted_row_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where quoted_row_id > 0; update message_quoted set quoted_row_id = -quoted_row_id where quoted_row_id < 0; update message set quoted_row_id = -(quoted_row_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="message_quoted")) where quoted_row_id > 0; update message set quoted_row_id = -quoted_row_id where quoted_row_id < 0; because there is no quoted_row_id column in the refered tables — Reply to this email directly, view it on GitHub <#1 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AKJU2L2EIWMBXRTVOE6S7BDWCZNATANCNFSM5XOHXZ5A . You are receiving this because you were mentioned.Message ID: @.*>

Thanks, I think I figured it out. In my case both my databases are on the new version so merely changing it as I said works because the structure is identical. But as you said the link may be broken with different versions of the database and I have no idea how to account for this as I don't even have the older version to see how it does it. Nor am I skilled enough in SQL to make it work imo, first attempt working with SQL.

I have made the changes that works for me and atm it combines it ok, will make a pull request when it doesnt do weird things after I test it.

Could you help me a little by answer 2 questions?
Why do you have a - in front of -(_id + (SELECT seq FROM oldest.sqlite_sequence WHERE name="messages")) where _id > 0. To me wont that make the number negative? But it doesnt, it is positive when done like that

Related to the above, I added a new section to fix sort_id too. But looks like here the - is working as I thought and the number is correct but is negative (should be 8357 but is -8357). Do you know what is happening?

I noticed as well it was doing a weird thing where it swapped the last 2 columns (message_add_on_flags and sort_id) as well
Original with sort_id as the last column

After merging with sort_id as second last column, but new db is added in in original format giving the below

[MrBarbie]

I noticed as well it was doing a weird thing where it swapped the last 2 columns (message_add_on_flags and sort_id) as well Original with sort_id as the last column After merging with sort_id as second last column, but new db is added in in original format giving the below

I have realised this is due to the order of the columns in my dbs to be different from each other. The solution would be to select the order of the columns when adding the entries. Example in commit 8bc0e6e

[ToufiqSayed]

I noticed as well it was doing a weird thing where it swapped the last 2 columns (message_add_on_flags and sort_id) as well Original with sort_id as the last column After merging with sort_id as second last column, but new db is added in in original format giving the below

I have realised this is due to the order of the columns in my dbs to be different from each other. The solution would be to select the order of the columns when adding the entries. Example in commit 8bc0e6e

Hi there, I do not possess enough technical knowledge to help contribute to this. However, I appreciate the work y'all are putting in to solve this problem.
As someone who wants to merge two WhatsApp backups, I'd appreciate it if I can get a little assistance if you have a working solution.
I used FMWhatsApp to get the decrypted backup file. (I ensured both backups are created from the same version of WhatsApp)
I tried using the scripts from your pull request and merge the DBs by following your instructions in ReadMe- it seemed to merge fine but upon restoration (using the same FMWhatsapp), I was only able to view messages of the old db.
I have access to a rooted android emulator and tried to put the merged decrypted DB in "/data/data/com.XXwhatsapp/databases" but WhatsApp did not seem to recognize any backup

[MrBarbie]

@ToufiqSayed If you are restoring to "/data/data/com.XXwhatsapp/databases" it should just load the msgs from the new databse. Make sure you delete all 3 msgstore files with different extensions or WhatsApp will think it is corrupted.
When you open the merged db file in a Sql browser do you see the new db msgs in there? If you do then it may be due to #4 . Otherwise it didnt actually merge them.

At the moment your contact and chat list needs to be identical for this to work, unless you can somehow convert your databases to a version before the issue here (ie still using messages table instead of message).
I have managed to get a fork of https://github.com/natario1/whatsapp-database-merger fully working as far as I can tell (may very well be missing some less used tables), but it is a very different tool. However the pull request to the main repo is not approved yet.