Database is not consistent

After my whatsapp databases are already using the latest/new schema, tried using the version updated by @MrBarbie.

But got some database consistency error, some are just as warning and stopped at RuntimeException for column message_vcard_jid.vcard_row_id. Reading from issue #2, @natario1 suggested to add the column into suggestion.

The thing is I don't know how to compile using gradle and kotlin. Want to try using sencodemaker's merger but it still not updated to account for the latest database schema.

The log is as below.

[*] Input databases:
        - input\msgstore.db
        - input\msgstore1.db
[*] Checking consistency of input\msgstore.db
        Column chat.jid_row_id is consistent! All 698 unique entries (out of 698) exist in the referenced table jid.
        Column chat.display_message_row_id is consistent! All 286 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_message_row_id is consistent! All 286 unique entries (out of 698) exist in the referenced table message.
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_message_row_id references table message, where we expected to find a list of 235 entries, but only 234 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_receipt_sent_message_row_id references table message, where we expected to find a list of 286 entries, but only 285 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Column chat.last_important_message_row_id is consistent! All 2 unique entries (out of 698) exist in the referenced table message.
        Column chat.change_number_notified_message_row_id is consistent! All 3 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_read_ephemeral_message_row_id is consistent! All 0 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_message_reaction_row_id is consistent! All 9 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_seen_message_reaction_row_id is consistent! All 9 unique entries (out of 698) exist in the referenced table message.
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_message_sort_id references table message, where we expected to find a list of 196 entries, but only 195 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Column chat.display_message_sort_id is consistent! All 217 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_message_sort_id is consistent! All 218 unique entries (out of 698) exist in the referenced table message.
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_receipt_sent_message_sort_id references table message, where we expected to find a list of 187 entries, but only 186 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Warning: Database input\msgstore.db is not consistent. Column message.chat_row_id references table chat, where we expected to find a list of 457 entries, but only 456 were found.
Total count (source): 698, total count (target): 95991
Missing=[-1]
        Column message.sender_jid_row_id is consistent! All 702 unique entries (out of 95991) exist in the referenced table jid.
        Column message.sort_id is consistent! All 95990 unique entries (out of 95991) exist in the referenced table message.
        Column message_quoted.message_row_id is consistent! All 10651 unique entries (out of 10651) exist in the referenced table message.
        Column message_quoted.chat_row_id is consistent! All 118 unique entries (out of 10651) exist in the referenced table chat.
        Column message_quoted.parent_message_chat_row_id is consistent! All 152 unique entries (out of 10651) exist in the referenced table chat.
        Column message_quoted.sender_jid_row_id is consistent! All 303 unique entries (out of 10651) exist in the referenced table jid.
        Column message_vcard.message_row_id is consistent! All 67 unique entries (out of 71) exist in the referenced table message.
        Column message_vcard_jid.message_row_id is consistent! All 57 unique entries (out of 86) exist in the referenced table message.
Exception in thread "main" java.lang.IllegalStateException: Database input\msgstore.db is not consistent. Column message_vcard_jid.vcard_row_id references table message_vcard, where we expected to find a list of 61 entries, but only 60 were found.
Total count (source): 71, total count (target): 86
Missing=[-1]
        at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21)
        at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3)
        at dev.natario.MainKt.main(Main.kt:50)

I have updated and uploaded v0.2.0b that fixes this. I saw weird behaviour in my table too but it wasnt the same -1 id yours did so I didn't include it in the previous version. Let me know if it fails at another point.

You want to add the ignoreConsistency option to the reference that it is failing at. So in this

 Exception in thread "main" java.lang.IllegalStateException: Database input\msgstore.db is not consistent. Column message_vcard_jid.vcard_row_id references table message_vcard, where we expected to find a list of 61 entries, but only 60 were found.
Total count (source): 71, total count (target): 86
Missing=[-1]
        at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21)
        at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3)
        at dev.natario.MainKt.main(Main.kt:50)

Column message_vcard_jid.vcard_row_id references table message_vcard, where we expected to find a list of 61 entries, but only 60 were found. tells you that it is failing trying to find the id value in message_vcard table for vcard_row_id defined in message_vcard_jid table.

That said be careful and look at what it is missing. I have tried to account for those that I have seen and makes sense for it to be missing, ie a message that was already deleted. In your case it seems to be the weird thing they are doing with some accessory tables where they are using the -1 id for like a default entry refering to self. So in this case probably fine that it cant find it cause -1 id wont exist anyway. But if you see some other numbers then you should investigate further, may be some entries missing in your database.

Oh yeah also with sencodemaker's version, I tried updating it to the latest schema but it was too limiting so I stopped. It will get you your messages all in the database but depending on situation, may not display some. I have described it in more detail on their repo. The crossreferences are completely unchanged there, which has also increased in the newer schema so I expect more issues.

Thanks for the reply. Didn't expect this.

I have updated and uploaded v0.2.0b that fixes this.

I'll try this later. Yeah I think I need to see my db for missing entries, and maybe delete some entries to make this work. Because in my case the db that I am trying to merge is a bit different, where the old one is already mixed with current/new entries (db that I am using now) and the somewhat new db (entries up to last week). So the entries has become mixed like a burger where

OLD NEWISH OLD

the new'ish entries is sandwiched.

Oh yeah also with sencodemaker's version, I tried updating it to the latest schema but it was too limiting so I stopped.

I tried using your modified sql from the pull request, but I think it doesn't work? I just tested once yesterday and planned to further testing later today.

Yeah I think I need to see my db for missing entries, and maybe delete some entries to make this work.

As it is now I think it should mostly work, I hope I have caught most of the ones that would fail it. But feel free to report back more that come up cause my database is a bit small.

So the entries has become mixed like a burger where

Oh yeah thats a limitation of Whatsapp that I found, it displays the message based on their order in the table rather than by time. One thing you can try to fix it a little bit is by sorting the message table by timestamp. I am guessing it will work but did not try. I am surprised with yours having overlap from what seems like a short time, that the unique constraints didn't eleminate the duplicates. This is one of the reasons I liked this tool and put the most effort into getting it working again. But yes removing duplicates before hand makes it easier for things to go well.

I tried using your modified sql from the pull request, but I think it doesn't work?

Atm I wont recommend trying that one, if I had to rate the readiness of that script vs this I would say it is at 30% at best. I also think in your case you wont be able to verify the messages are all in there using whatsapp, you will need to browse the database itself. With my small database already quite a few messages were missing and displaying incorrectly.

I am surprised with yours having overlap from what seems like a short time, that the unique constraints didn't eleminate the duplicates.

There's no duplicate. Actually what happened is the old db (2021) cant be merge as is, due to the old db is using the old db schema with messages table. Then I use Whatsapp for about a week i think with that db and after a week the db is converted automatically to the new schema. Then I want to merge this updated db which has 2021 records and the past week records, with another db (2022) I call as new.

If it can't merge then I think I will try to remove the latest entries for the past week from the old db (2021), making the entries timeline linear.

So as an example your new format 2021 db has 2021 msg and say Nov 2022 msg, then your 2022 db has Jan - Oct 2022 msg? If this is the case, try the sorting method I mentioned (on the merged db). If all references strictly look for the id which I think it does since there can be gaps, then your messages should appear in the right order. Without sorting, assuming you set 2021 as old db and 2022 as new db then the order will be 2021 msg > Nov 2022 msg > Jan - Oct 2022 msg. If it is flipped ie 2022 db is old and 2021 db is new then it will be Jan - Oct 2022 msg > 2021 msg > Nov 2022 msg.

Btw as far as I can tell, but havent had time to fully test it, the appication sorts by name and uses the first file as old and next as new. Also havent tested with 3 files.

So as an example your new format 2021 db has 2021 msg and say Nov 2022 msg, then your 2022 db has Jan - Oct 2022 msg?

Correct.

Usually how long it takes to merge? I have left it running since 10.30pm last night, until now 10.15am still not finished. Around 12 hours already. Maybe due to my db is quite large. Total records in message table is around 500k rows. Is there any like estimate how many seconds will it take for a row of records? Then maybe can estimate the merging time for message table based on that.

[*] processing table message
        - input\msgstorenew.db: 416381 rows, columns: [_id, chat_row_id, from_me, key_id, sender_jid_row_id, status, broadcast, recipient_count, participant_hash, origination_flags, origin, timestamp, received_timestamp, receipt_server_timestamp, message_type, text_data, starred, lookup_tables, sort_id, message_add_on_flags]
        - output\msgstore.db: 95991 rows, columns: [_id, chat_row_id, from_me, key_id, sender_jid_row_id, status, broadcast, recipient_count, participant_hash, origination_flags, origin, timestamp, received_timestamp, receipt_server_timestamp, message_type, text_data, starred, lookup_tables, message_add_on_flags, sort_id]

Thats all what I want to write actually, but when I want to copy the text in the terminal, I subconciously pressed ctrl+c haha. That skip the current table operation and continue the task and the next line after the output/msgstore.db: 95991 rows is as below.

        Warning: 1 rows will be skipped because of message unique constraint on [chat_row_id, from_me, key_id, sender_jid_row_id].

And it continues and now stuck at [*] message: editing column sender_jid_row

Usually how long it takes to merge? I have left it running since 10.30pm last night, until now 10.15am still not finished

Yours is probably due to the huge number of msgs, mine had ~4k in msg and 1k in jid and it probably took 2-3mins?

I subconciously pressed ctrl+c haha

Oof painful! You could keep that smaller table to do some test with. It didnt exit the entire process? Because it seems like it is still going

Warning: 1 rows will be skipped because of message unique constraint on [chat_row_id, from_me, key_id, sender_jid_row_id]. That looks like it took care of one of your duplicated msgs.

And it continues and now stuck at [*] message: editing column sender_jid_row This is where it goes and fixes the references that I briefly mentioned being a problem in the SQL script. It will take awhile given there are many msgs it needs to work on.

When you see [*] Checking consistency of output\msgstore.db that means it has finished merging and sort of doing a verify after. Should be similar results to the early consistency check which the first version failed on

Yours is probably due to the huge number of msgs, mine had ~4k in msg and 1k in jid and it probably took 2-3mins?

What? Very fast.

I tried again but this time, I deleted all the Nov 2022 msg. So it becomes linear with old db having 2021 messages only and new db having 2022 messages. Well it didn't get stuck at merging messageanymore. But I think it took quite some time as I left it to run at around 1pm, and noticed later that the output db last accessed time from file properties is at 2.30pm something. So in my case it took almost 2 hours.

And now I'm getting error at message_quoted_media.

This is the first line.

[*] processing table message_quoted_media
        - input\msgstoreold.db: 8237 rows, columns: [message_row_id, media_job_uuid, transferred, file_path, file_size, media_key, media_key_timestamp, width, height, direct_path, message_url, mime_type, file_length, media_name, file_hash, media_duration, page_count, enc_file_hash, thumbnail, media_caption]
        - output\msgstore.db: 3047 rows, columns: [message_row_id, media_job_uuid, transferred, file_path, file_size, media_key, media_key_timestamp, width, height, direct_path, message_url, mime_type, file_length, media_name, file_hash, media_duration, page_count, enc_file_hash, thumbnail, media_caption]
[*] message_quoted_media: editing column message_row_id (8237 entries), because it references table message which was previously edited (offset=112074, dupes=0)
[*] message_quoted_media: about to append 8237 rows
Exception in thread "main" java.lang.RuntimeException: INSERT failed for last batch of 500 entries:

In between the first and the last line, there's a lot of line that looks something like this.

♀☼♀
♫☼►►◄►
♀↕‼↕►‼☼►►►??'||char(0)||'C☺♥♥♥♦►♂       ♂►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►??'||char(0)||''||char(0)||'C'||char(0)||'d♥☺"'||char(0)||'☻◄☺♥◄☺??'||char(0)||'↔'||char(0)||char(0)||'☺♣☺☺☺☺'||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||'♣    ♦♥☻☺??'||char(0)||'F►'||char(0)||'☻☺☻♣☺♥        ♣       '||char(0)||char(0)||char(0)||char(0)||'☺☻♥♦♣'||char(0)||'♠◄↕‼1¶§"AU??     ▬↓2QTa#3Wq?????Sr???↑$BRb??????'||char(0)||'←☺'||char(0)||'☻♥☺☺☺'||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||'☻♥♦♣☺♠??'||char(0)||'9◄'||char(0)||'☺♥☻☻♦♥  '||char(0)||char(0)||char(0)||char(0)||char(0)||char(0)||'☺'||char(0)||'☻♥♦◄↕!♣♠1AR?‼¶§"Qa??2q??bc?35B????????'||char(0)||'♀♥☺'||char(0)||'☻◄♥◄'||char(0)||'?'||char(0)||'c|?5???2?t???+DH?B??)??LQo<]??Y???????V??j§?W??&??k????►?#?$?▲l?♠?????▼▼??Y3?¶=?ln??j"U?↕^ @.????↔=!??♫Z????????{?♥??????????8<?w??g?o?4&??Z♣?z??9r?k%0S?h?      ?b?????u??>F??Q????↕5.??☺Tx?{?◄??v?V?F????Y???k=?|py???Z?~?∟hB??q?+W/i<?i??6?Q?
?@E-??♂↕'||char(0)||'▲??aE>F??‼]?m?~??==?♥???↔?K??y,????????8<?w??g?o?4o?c?_?+w?????c?_?+w????u?8??4?^??O<]??Y????↨⌂j?{???E''?←?♦§4????jY?◄h~?)o??♀{?♀y???n??''?1?$v?/↨???↨⌂j?{??????????8???1~???$??☼??1~???$??9? ?Gh??{▲J?im???~????,⌂↓??D?pb????n??m?P>?PNd???Z?m?????'||char(0)||'?‼6?↕>%U????{§x???j?R]?*"??j8?¶?↓?y☺♀§Px???'||char(0)||'nO?!?6??Kp?U???♥w??↔Ri"?RR\◄)*??q?↔??#f:.???[MUI?(?s?,u♂R"xdP{?F,F?o? ??♦◄?U??j\?§?.?????l_?>↔z?=l??4Z?YMX4???‼↔m?K∟??♥???o◄??I?~?A?S|6▼2>?z??Q????)←???∟☼??<?|??Ic??◄?]?d??????E?Ux\??lv;☻☻?§???$M↓?i?^???/j-?0U\!?↕h??R$Fs?4Y↑????????????G?K??>+??L???P??}????♥?I??66???????.♠►???$???? ?)?cd???     c▲=??~♀U‼?sVr⌂???6d?§'||char(0)||'?Ks?I?o?↕????{????5?M???▼O>?He♥?☼G⌂???∟D\[?☼?q?????d??7?C?k?↔?k??VR?↑??AH??bF?~I?§?uf??
*X/5?X???d{lqP?{???'||char(0)||'?(G*e'||char(0)||'???∟wd?w??%??nT?♀?#IM%dqTE?m??b↓⌂_??∟?~r??)?'||char(0)||'????{?dr?▲k?TQT??a???x9#?W?K??Vd?↔Vc??♂???w??UUu?1?x↑)*☼-?♫?♂8K??s?C?e.???Td▼?/??    ?'||char(0)||'?◄?$???T?0??(7?♀p
???fS?yB`??p????C`??6??=∟P?5↑??`3♫?►?*'||char(0)||'Ib¶??⌂▼V??c??a§Y?,????§???6?Fd?R??V6?▲E☻?P?/C?Y??.??U?9???g?Ib???§f`??e?q???w?♥?→5Zy??(b???w:h??h??0?I?????c?g???}?L‼T◄;?b3??^???Nq???'||char(0)||'?♣??☺o?????.9-;↓ij?l?''xG%~??6????☻G?▬??M??l?zZ??§???Mr?U4?§b?¶¶??<C??R?v?►6?⌂??¶v??W<?T}f4???'||char(0)||'?§-??4?n???
?,q??↨??F?3?)??RL???N?? ''??+x??m??v??,ar(0)||'?J]???-M-U♫|???y%z{}D???(?5Du}?JH8???    ?~????→?e|?▬a?\h???z?Z?j''?''?<☼??????
?~?i?:?◄?n?53????K?Wy???''??zx???}▼◄??☻q[a?∟?+R?j??J?c"?"?^♠&?pj?♥??
?5??5?¶q??,?h?|~??^?4§9J??v?g,?{????P*???       ?'||char(0)||'G'||char(0)||'??X?♫?♫??↓?'||char(0)||'Q↨=g↔◄???w?RX????????}⌂???F?z?I????sD??&[?x?^???q4?~        ↓''?b(??t??:???Gb???§??#S↔?     ?▬?'||char(0)||'?K???z?♥?♦?'||char(0)||'??-v??m)??%??.c↑:7??;???♥^@q?q\\?+P?81?.??|?^?]9↑1?←♫►?bJjg?☻?;-????‼???$???T?A$?#oG????n↓''?,uu§V???↕?jJe7↑?D♀??‼?∟x?♣V<?K☻?}?????&i??.??6?:?i?h)??"??X???♣$?z☻6????d??]????↕?U(???$]?BG)^{4??????▼Ka?R??P?}?←►♫◄q?????l6+??←?P?W-c???↓?ex??* q???@p?}T5u¶?V?u4??b????K∟?F?D??☺?{??????►?dx)2??,?3↑?????o?M;?◄?§?♣J?M?7????t?e♫???)v?‼\j?$Y?2RT%<‼'||char(0)||'???+????X?Ry‼?§[‼?q4?↔?'||char(0)||'X↔??*d?C??q??x??WUQ???yEUH?{?RU♥?4♥v?bz?∟6?Z[??D???b◄?ex▬?h*?↓?!??P?#p@??rq?el?Kd????dg?RI▼6y↓???>-?I''?I8Z??h?????1s????]?G?c←?#??_d{?[?p?R:?KX????VG9♣♠0♠?'||char(0)||'?a_"vD???→
??,?t1RJ??(??⌂6♀i??}??►<A???h???????+G?_????W2W?L{⌂
?f????♠?'||char(0)||'?;???''f^?↔?Ki??bU?IUe?♥♦??m???1‼d?*??v
?>??n???D:?gHG‼??RR?x??'||char(0)||'?[▼??1▲▼?q?'||char(0)||'?↑?∟2a↔???♂?♠♀↑??◄?♠F♀↑0!↑0`??`??☻◄?♠F♀↑0?♂??', NULL)|'(?♠8?qZ>???O???????2⌂.#¶?
        - ('235517', NULL, '0', NULL, '0', 'Uu{↔←Q???d?M?U?←|4fd?W?c%1?n1', '0', '0', '0', NULL, 'https://mmg-fna.whatsapp.net/d/f/AowEDQKgtnrtKhSKUCv0fZdpjh5OP5Iz_Wf151QAaxkX.enc', 'image/jpeg', '7791', NULL, '7G66TXGkZJnhHBTtkYrlXOdeysEGeisTlCIGcdVZLGU=', '0', '0', 'OruhuKmYSwZlaQiL562a4NmSNGfLBh9N7PT2aSg2Vf8=', '????'||char(0)||'►JFIF'||char(0)||'☺☺'||char(0)||char(0)||'☺'||char(0)||'☺'||char(0)||char(0)||'??'||char(0)||'C'||char(0)||'♥☻☻☻☻☻♥☻☻☻♥♥♥♥♦♠♦♦♦♦♠♠♣♠

And this is the last.

(???????4??'||char(0)||'?E§W|J↑?????%Mi ?:?@↔1?↑??◄??↔????AK?\???4QUj?⌂?????‼♣m?U????pH?∟??z?2?ucxI)VA?E¶?;*▲??X''?*Kf???J?v?¶QT?☺?D8?](??PJ?????*?Q¶QEJ???', NULL)
        at dev.natario.MainKt.insertData(Main.kt:340)
        at dev.natario.MainKt.merge(Main.kt:297)
        at dev.natario.MainKt.main(Main.kt:67)
Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (Expression tree is too large (maximum depth 1000))
        at org.sqlite.core.DB.newSQLException(DB.java:1012)
        at org.sqlite.core.DB.newSQLException(DB.java:1024)
        at org.sqlite.core.DB.throwex(DB.java:989)
        at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
        at org.sqlite.core.NativeDB.prepare(NativeDB.java:134)
        at org.sqlite.core.DB.prepare(DB.java:257)
        at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
        at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
        at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
        at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:35)
        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:241)
        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
        at dev.natario.Database.executeWithRows(Database.kt:107)
        at dev.natario.Database.executeWithRows$default(Database.kt:101)
        at dev.natario.Database.insert(Database.kt:78)
        at dev.natario.MainKt.insertData(Main.kt:322)
        ... 2 more

I think the weird lines is the thumbnail image, when u quote like a picture. But idk in what format. Because even if the image is encoded in base64 format, there will be no such thing as char(0) in the image. I think I'll try to trim or maybe delete all 8237 entries in the message_quoted_media along with the entries in message that share same value / linked with message_quoted_media.message_row_id and message.id

Thats all what I want to write actually, but when I want to copy the text in the terminal, I subconciously pressed ctrl+c haha. That skip the current table operation and continue the task and the next line after the output/msgstore.db: 95991 rows is as below.

Just to update on the last time where i pressed ctrl+c. The last run, the same thing happened where it got stuck at merging message table. I noticed in task manager the cpu usage for the task is around 0% already after about 1 hour ++, where usually it'll be at around 15-25%. I just pressed enter on the cmd window and well it continued with next operation.

I hope by pressing enter it didn't skipped anything.

So in my case it took almost 2 hours.

Interesting that it made such a big difference. This is why my db is a bad test cause it is much smaller than a typical one, not sure if you are encountering all these extra issues because of the size or just the nature of your db.

And now I'm getting error at message_quoted_media.

This is the known issue described by the original author and an oversight by me. I did notice similar fields but couldnt find it again while going through and because my db is so small with this table (only 40) it didnt hit the issue. Looks like it may have even crashed additional libraries and such. I have uploaded a test version that copies natario's lines to deal with the original message_thumbnail table to your table that is failing. Hopefully it will help deal with it and if it doesn't will just drop the thumbnails as described.

Success.

Finally the merge is completed. I think it took around 1 and half hour?

I think the weird lines is the thumbnail image, when u quote like a picture. But idk in what format. Because even if the image is encoded in base64 format, there will be no such thing as char(0) in the image. I think I'll try to trim or maybe delete all 8237 entries in the message_quoted_media along with the entries in message that share same value / linked with message_quoted_media.message_row_id and message.id

I did just this and after that some consistency error popped back, just deleted all the missing entries again. Then I just let it run. I think the table that's taking longest is message and also receipt_device and receipt_user

Now comes another challenge, which is to import the merged db into whatsapp. I think I imported the db successfully, but the thing is, it just showing all the 2021 (old) chats, and the new 2022 chats are nowhere to be seen. I think this is due to some weird things happening during the convertion of my old db schema that's using messages into new schema using message. I attach a screenshot here showing the entries.

Screenshot (8)

I sorted the entries based on timestamp/date. As u can see in the image, the highlighted row is the last line that I can see in my chat in whatsapp. I can only see chats from the old db. The idand sort_id of the old db is bigger than the new db. This is mainly due to the schema convertion actually created a new entries based on the old one, then deleted the old entries, making the 2021 chat 'newer' than the 2022 chat.

Well I'm stuck here, and I think this problem is actually out of the scope of this project already. But I like to try getting some pointers on how to solve this.

Hurray!

I think the table that's taking longest is message

That is to be expected as messages contain the bulk of the info, EVERY single message at least appears there and is duplicated sometimes for the other tables.

Actually I think your messages are all there, try scrolling to the start, I believe you will see your new messages there. If that works you can, try saving the sorted table and reopen it. It could be exactly the same then we know WhatsApp displays msg by following the id order, if it is now the correct order then it is likely just following entry order.

Btw as far as I can tell, but havent had time to fully test it, the appication sorts by name and uses the first file as old and next as new.

It is because of this, rename your 2021 db as something like msgdb1.db and 2022 db as msgdb2.db. Should also help with the time assuming bulk of msgs in 2021 db, as that db seems to be just copied as is.

It is because of this, rename your 2021 db as something like msgdb1.db and 2022 db as msgdb2.db. Should also help with the time assuming bulk of msgs in 2021 db, as that db seems to be just copied as is.

Tried this. Still my chat is nowhere to be seen. But the id is sorted according to timestamp correctly in the table, compared to last time.

I think the problem is whatsapp doesn't really use the merged database. I imported the merged database into whatsapp, and then it throws me error as in the attached image.

Screenshot_20221127-182435-small

If I skip restore, then there will be nothing on my whatsapp. It resets all conversation and just created the group conversation that I'm in. But if I go restore, then all my chat from 2021 and this past week will be there, no chat from 2022 (new).

I think the error signify that the merged database is corrupt or something? That's why it ask to restore from another database. I think yesterday also this same problem occurs, but I didn't think much of that as I thought that the id sorting is the culprit.

Well I'm at a loss here. I'm thinking of giving up and just use the old db.

EDIT

one important note. I'm currently using a modded whatsapp named as fouad whatsapp. This app can backup/create your current db unencrypted, so the db can be opened externally. Also it can restore from a backup. This is the method I'm using to restore the chat. I tried restoring the from the 2021 backup, and also 2022 backup. Both backup still works. Just this merged db throws me the restore chat history error.

I think the problem is whatsapp doesn't really use the merged database. I imported the merged database into whatsapp, and then it throws me error as in the attached image.

When I last got that error it definitely wasnt using the database I copy into the whatsapp databases folder. If this is what you are doing, have you checked permissions and ownership? If it is not right it will not recognise it. Also make sure to delete the old -shm and -wal files as well as they go together with the .db as a set.

That option in the image restored from the most recent unencrypted backup that WhatsApp will create when you use the backup function (automated or manual). If fouad whatsapp does something different I do not know.

I'm currently using a modded whatsapp named as fouad whatsapp.

I am not super familiar how this works, do you just put your merged db into the folder where it backup the unencrypted dbs? Then you can select which file to backup from? And this is when you are getting the error in the image? I would still double check the permissions and ownership if you are able to.

Hey the great MrBarbie.

Well I'm at a loss here. I'm thinking of giving up and just use the old db.

Turns out I still have the spirit and perseverance of a SEA dota player XD. When u already called gg in all chat but still playing like it's your game 5 in the final match of TI.

Apparently the backup created using Fouad whatsapp also include logs file, so I've been on it since after I posted the last message. The first thing that I noticed is this line.

2022-11-27 19:51:57.860 LL_I W [107:SignalExecutor] msgstore/addmsg duplicate FDE7F2DFA6561EF2852C85AC4B005DD5 ; exception=android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: message.chat_row_id, message.from_me, message.key_id, message.sender_jid_row_id (code 2067 SQLITE_CONSTRAINT_UNIQUE)
### begin stack trace 2.22.17.76(390971651)
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: message.chat_row_id, message.from_me, message.key_id, message.sender_jid_row_id (code 2067 SQLITE_CONSTRAINT_UNIQUE)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:940)
    at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:89)
    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1702)
    at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1597)
    at X.0op.A03(Unknown Source:9)
    at X.0ng.A0T(Unknown Source:357)
    at X.0ng.A0B(Unknown Source:745)
    at X.0ng.A0s(Unknown Source:11)
    at X.0ng.A0r(Unknown Source:165)
    at X.0ng.A0p(Unknown Source:7)
    at X.13h.A00(Unknown Source:1238)
    at X.2DJ.AI3(Unknown Source:3408)
    at X.1aW.AI3(Unknown Source:2)
    at X.3Cg.A01(Unknown Source:682)
    at X.1Px.run(Unknown Source:553)
    at com.facebook.redex.RunnableBRunnable0Shape0S0400000_I0.run(Unknown Source:5553)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
    at com.facebook.redex.RunnableBRunnable0Shape5S0100000_I0_5.run(Unknown Source:2108)
    at java.lang.Thread.run(Thread.java:920)
### end stack trace

Then I think it says that something about unique, then message.chat_row_id, message.from_me, message.sender_jid_row_id is all for sure having duplicate entry, so I try to find if there's any duplicate in message.key_id. Referring from an answer from stackoverflow (we dont deserve to have this amazing thing, but still we're lucky to have it), I tried to find it by running sql as select key_id from main.message group by key_id having count(key_id) > 1;. Turns out I have a duplicate. There's 2 entries with key_id value of -1. I deleted one that's not there in the input databases then try again to restore the database.

And well of course it's still doesn't work. Then I try again, about 4 times using different method, where lastly I compared two logs.

1: restore not working

2022-11-27 20:41:03.596 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list msgstore.db-shm 32768 drw=011
2022-11-27 20:41:03.596 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list location.db 4096 drw=011
2022-11-27 20:41:03.597 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list commerce.db 24576 drw=011
2022-11-27 20:41:03.597 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list web_sessions.db 4096 drw=011
2022-11-27 20:41:03.597 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list companion_devices.db-wal 53592 drw=011
2022-11-27 20:41:03.597 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list wa.db-shm 32768 drw=011
2022-11-27 20:41:03.597 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list AutoMessage.db-journal 0 drw=011
2022-11-27 20:41:03.597 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list BTOR.DB-journal 0 drw=011
2022-11-27 20:41:03.598 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list stickers.db-shm 32768 drw=011
2022-11-27 20:41:03.598 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list chatsettings.db-wal 70072 drw=011
2022-11-27 20:41:03.598 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list media.db-wal 428512 drw=011
2022-11-27 20:41:03.598 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list axolotl.db-shm 32768 drw=011
2022-11-27 20:41:03.598 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list hsmpacks.db-shm 32768 drw=011
2022-11-27 20:41:03.598 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list companion_devices.db-shm 32768 drw=011
2022-11-27 20:41:03.598 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list msgstore.db 393629696 drw=011
2022-11-27 20:41:03.599 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list BTOR.DB 20480 drw=011
2022-11-27 20:41:03.599 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list hsmpacks.db-wal 37112 drw=011
2022-11-27 20:41:03.599 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list emojidictionary.db 1134592 drw=011
2022-11-27 20:41:03.599 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list payments.db-wal 82432 drw=011
2022-11-27 20:41:03.599 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list axolotl.db 1515520 drw=011
2022-11-27 20:41:03.599 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list _jobqueue-WhatsAppJobManager-journal 0 drw=011
2022-11-27 20:41:03.600 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list emojidictionary.db-shm 32768 drw=011
2022-11-27 20:41:03.600 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list DBScheduler.db 24576 drw=011
2022-11-27 20:41:03.600 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list stickers.db 4096 drw=011
2022-11-27 20:41:03.600 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list axolotl.db-wal 524288 drw=011
2022-11-27 20:41:03.600 LL_I D [50:High Pri Worker #1] msgstore/open-existing-db/list msgstore.db-wal 524288 drw=011
2022-11-27 20:41:03.600 LL_I D [2:main] statistics/init
2022-11-27 20:41:03.601 LL_I D [59:stat-save] statistics/init: Text Messages: 766 sent, 1873 received / Media Messages: 25 sent (16342102 bytes), 652 received (280029700 bytes) / Offline Messages: 2514 received (2514420 msec average delay) / Status : 5 sent (3049370 bytes), 1160 received (187995827 bytes) / Payment Messages : 0 sent, 0 received / Message Service: 5751762 bytes sent, 14587113 bytes received / Voip Calls: 28 outgoing calls, 43 incoming calls, 110406074 bytes sent, 120562816 bytes received / Google Drive: 0 bytes sent, 0 bytes received / Roaming: 0 bytes sent, 0 bytes received / Total Data: 135549308 bytes sent, 603175456 bytes received
2022-11-27 20:41:03.601 LL_I D [2:main] WaMsysSetup/bootstrap
2022-11-27 20:41:03.601 LL_I D [2:main] app-init/async/device_info/OS_BUILD_NUMBER SKQ1.211103.001
2022-11-27 20:41:03.601 LL_I D [2:main] app-init/main/done
2022-11-27 20:41:03.603 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init: header=2 bufferCount=3 maxMetadataSize=65536 maxEventBufferSize=65536 currentEventBufferIndex=1 currentEventBufferIndex=1 currentBufferSequenceNumber=626 isEventBeaconingEnabled=false dayOfLastBeaconingDecision=19322 currentEventSequenceNumber=0
2022-11-27 20:41:03.603 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/0: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.603 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/1: size=1472 timestamp=1669435657 streamId=1 bufferSequenceNumber=626 checksum=2086487876
2022-11-27 20:41:03.603 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/2: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.603 LL_I D [58:WhatsApp Worker #1] InMemorySingleEventBufferManager/initfromfile: opened existing wam file: record_count = 354, event_count = 84, attribute_count = 24, size = 1472, create_ts = 1669435657
2022-11-27 20:41:03.620 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init: header=2 bufferCount=8 maxMetadataSize=65536 maxEventBufferSize=16384 currentEventBufferIndex=0 currentEventBufferIndex=0 currentBufferSequenceNumber=0 isEventBeaconingEnabled=false dayOfLastBeaconingDecision=0 currentEventSequenceNumber=0
2022-11-27 20:41:03.620 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/0: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [2:main] com.whatsapp.youbasha.ui.YoSettings.Universal.onCreate
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/1: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/2: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/3: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/4: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/5: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/6: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/7: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.621 LL_I D [58:WhatsApp Worker #1] InMemorySingleEventBufferManager/initfromfile: opened existing wam file: record_count = 0, event_count = 0, attribute_count = 0, size = 0, create_ts = 0
2022-11-27 20:41:03.630 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init: header=2 bufferCount=2 maxMetadataSize=65536 maxEventBufferSize=65536 currentEventBufferIndex=0 currentEventBufferIndex=0 currentBufferSequenceNumber=224 isEventBeaconingEnabled=false dayOfLastBeaconingDecision=19322 currentEventSequenceNumber=0
2022-11-27 20:41:03.631 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/0: size=304 timestamp=1669435879 streamId=1 bufferSequenceNumber=224 checksum=4090968842
2022-11-27 20:41:03.631 LL_I D [58:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/1: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:41:03.631 LL_I D [58:WhatsApp Worker #1] InMemorySingleEventBufferManager/initfromfile: opened existing wam file: record_count = 82, event_count = 22, attribute_count = 16, size = 304, create_ts = 1669435879
2022-11-27 20:41:03.639 LL_I D [2:main] WhatsAppLocale/setDerivedFieldsUnderLock/fieldCreationTimer/timer/stop: 5
2022-11-27 20:41:03.643 LL_I D [2:main] com.whatsapp.youbasha.ui.YoSettings.Universal.onStart
2022-11-27 20:41:03.643 LL_I D [2:main] app-init/application foregrounded
2022-11-27 20:41:03.645 LL_I D [2:main] messageservice/startOnForeground success
2022-11-27 20:41:03.648 LL_I D [2:main] AppAuthManager/hasEnrolledBiometrics: enrolled: true
2022-11-27 20:41:03.649 LL_I D [2:main] com.whatsapp.youbasha.ui.YoSettings.Universal.onResume
2022-11-27 20:41:03.687 LL_I D [2:main] messageservice/onCreate
2022-11-27 20:41:03.688 LL_I D [2:main] OnTrimMemory/level: 15, trimming memory, app in foreground
2022-11-27 20:41:03.691 LL_I D [2:main] xmpp/handler/start
2022-11-27 20:41:03.692 LL_I D [2:main] connection_thread/logged_flag/connected initialize to false
2022-11-27 20:41:03.692 LL_I D [2:main] connection_thread/logged_flag/disconnecting initialize to false
2022-11-27 20:41:03.692 LL_I D [2:main] connection_thread/logged_flag/quit initialize to false
2022-11-27 20:41:03.692 LL_I D [2:main] message_handler/logged_flag/must_reconnect true change to true
2022-11-27 20:41:03.692 LL_I D [2:main] message_handler/logged_flag/must_ignore_network_once false change to true
2022-11-27 20:41:03.692 LL_I D [2:main] message_handler/logged_flag/must_ignore_network_once true change to false
2022-11-27 20:41:03.692 LL_I D [2:main] xmpp/handler/network/ignore (false,-1)
2022-11-27 20:41:03.695 LL_I D [72:ConnectionThread] xmpp/connection/recv/sending_channel_ready
2022-11-27 20:41:03.945 LL_W D [50:High Pri Worker #1] databasehelper/open-existing-db/nodb/sqlerror/will-retry ; exception=android.database.sqlite.SQLiteCantOpenDatabaseException: Cannot open database '/data/user/0/com.whatsapp/databases/msgstore.db': File /data/user/0/com.whatsapp/databases/msgstore.db doesn't exist
### begin stack trace 2.22.17.76(390971651)
android.database.sqlite.SQLiteCantOpenDatabaseException: Cannot open database '/data/user/0/com.whatsapp/databases/msgstore.db': File /data/user/0/com.whatsapp/databases/msgstore.db doesn't exist
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:254)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:205)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:505)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:206)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:198)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:919)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:904)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:790)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:737)
    at X.1To.AHr(Unknown Source:109)
    at X.0p7.A07(Unknown Source:169)
    at com.facebook.redex.RunnableBRunnable0Shape1S0400000_I1.run(Unknown Source:595)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
    at com.facebook.redex.RunnableBRunnable0Shape8S0200000_I0_8.run(Unknown Source:1636)
    at java.lang.Thread.run(Thread.java:920)
Caused by: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14 SQLITE_CANTOPEN): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:224)
    ... 15 more
### end stack trace

2: restore working using old backup(created from fouad)

2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list msgstore.db-shm 32768 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list location.db 4096 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list commerce.db 24576 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list web_sessions.db 4096 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list companion_devices.db-wal 37112 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list wa.db-shm 32768 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list AutoMessage.db-journal 0 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list BTOR.DB-journal 0 drw=011
2022-11-27 20:58:34.428 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list stickers.db-shm 32768 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list chatsettings.db-wal 57712 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list media.db-wal 218392 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list axolotl.db-shm 32768 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list hsmpacks.db-shm 32768 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list companion_devices.db-shm 32768 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list msgstore.db 455540736 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list BTOR.DB 20480 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list hsmpacks.db-wal 37112 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list emojidictionary.db 1134592 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list payments.db-wal 82432 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list axolotl.db 282624 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list _jobqueue-WhatsAppJobManager-journal 0 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list emojidictionary.db-shm 32768 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list DBScheduler.db 24576 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list stickers.db 4096 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list axolotl.db-wal 440872 drw=011
2022-11-27 20:58:34.429 LL_I D [48:High Pri Worker #1] msgstore/open-existing-db/list msgstore.db-wal 683952 drw=011
2022-11-27 20:58:34.429 LL_I D [2:main] statistics/init
2022-11-27 20:58:34.430 LL_I D [57:stat-save] statistics/init: Text Messages: 0 sent, 40 received / Media Messages: 0 sent (0 bytes), 91 received (1328003 bytes) / Offline Messages: 232 received (25341497 msec average delay) / Status : 0 sent (0 bytes), 106 received (609434 bytes) / Payment Messages : 0 sent, 0 received / Message Service: 178743 bytes sent, 799940 bytes received / Voip Calls: 0 outgoing calls, 1 incoming calls, 0 bytes sent, 0 bytes received / Google Drive: 0 bytes sent, 0 bytes received / Roaming: 0 bytes sent, 0 bytes received / Total Data: 178743 bytes sent, 2737377 bytes received
2022-11-27 20:58:34.430 LL_I D [2:main] WaMsysSetup/bootstrap
2022-11-27 20:58:34.432 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init: header=2 bufferCount=3 maxMetadataSize=65536 maxEventBufferSize=65536 currentEventBufferIndex=1 currentEventBufferIndex=1 currentBufferSequenceNumber=2 isEventBeaconingEnabled=true dayOfLastBeaconingDecision=19323 currentEventSequenceNumber=2
2022-11-27 20:58:34.432 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/0: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.432 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/1: size=270 timestamp=1669553910 streamId=1 bufferSequenceNumber=2 checksum=599932408
2022-11-27 20:58:34.432 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/2: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.433 LL_I D [56:WhatsApp Worker #1] InMemorySingleEventBufferManager/initfromfile: opened existing wam file: record_count = 49, event_count = 2, attribute_count = 25, size = 270, create_ts = 1669553910
2022-11-27 20:58:34.433 LL_I D [2:main] app-init/async/device_info/OS_BUILD_NUMBER SKQ1.211103.001
2022-11-27 20:58:34.433 LL_I D [2:main] app-init/main/done
2022-11-27 20:58:34.434 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init: header=2 bufferCount=8 maxMetadataSize=65536 maxEventBufferSize=16384 currentEventBufferIndex=0 currentEventBufferIndex=0 currentBufferSequenceNumber=0 isEventBeaconingEnabled=false dayOfLastBeaconingDecision=0 currentEventSequenceNumber=0
2022-11-27 20:58:34.435 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/0: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.437 LL_I D [48:High Pri Worker #1] DatabaseUtils/initDatabase/injected sql log writer/using modified LoggableSQLiteDatabase
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/1: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/2: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/3: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/4: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/5: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/6: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/7: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.438 LL_I D [56:WhatsApp Worker #1] InMemorySingleEventBufferManager/initfromfile: opened existing wam file: record_count = 0, event_count = 0, attribute_count = 0, size = 0, create_ts = 0
2022-11-27 20:58:34.438 LL_I D [48:High Pri Worker #1] databasehelper/open-existing-db/version 1
2022-11-27 20:58:34.439 LL_I D [48:High Pri Worker #1] DatabaseHelper/old tables verification, old tables are available.
2022-11-27 20:58:34.439 LL_I D [48:High Pri Worker #1] databasehelper/canQueryDb
2022-11-27 20:58:34.440 LL_I D [48:High Pri Worker #1] databasehelper/canQueryDb/timer/stop: 1
2022-11-27 20:58:34.440 LL_I D [48:High Pri Worker #1] databasehelper/canQueryDb true | time spent:1
2022-11-27 20:58:34.440 LL_I D [48:High Pri Worker #1] databasehelper/canUpdateDb
2022-11-27 20:58:34.441 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init: header=2 bufferCount=2 maxMetadataSize=65536 maxEventBufferSize=65536 currentEventBufferIndex=0 currentEventBufferIndex=0 currentBufferSequenceNumber=7 isEventBeaconingEnabled=false dayOfLastBeaconingDecision=19310 currentEventSequenceNumber=0
2022-11-27 20:58:34.441 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/0: size=355 timestamp=1668431482 streamId=1 bufferSequenceNumber=7 checksum=4193146020
2022-11-27 20:58:34.441 LL_I D [48:High Pri Worker #1] databasehelper/canUpdateDb/timer/stop: 1
2022-11-27 20:58:34.441 LL_I D [56:WhatsApp Worker #1] wambuffer/header/init/eventBufferMetadata/1: size=0 timestamp=0 streamId=1 bufferSequenceNumber=0 checksum=1
2022-11-27 20:58:34.441 LL_I D [48:High Pri Worker #1] databasehelper/canUpdateDb | time spent:1
2022-11-27 20:58:34.441 LL_I D [56:WhatsApp Worker #1] InMemorySingleEventBufferManager/initfromfile: opened existing wam file: record_count = 38, event_count = 6, attribute_count = 16, size = 355, create_ts = 1668431482
2022-11-27 20:58:34.442 LL_E D [56:WhatsApp Worker #1] queuefile/loadFromFile see locked empty mini event buffer
2022-11-27 20:58:34.446 LL_I D [48:High Pri Worker #1] databasehelper/prepareWritableDatabase/timer/stop: 5
2022-11-27 20:58:34.446 LL_I D [2:main] WhatsAppLocale/setDerivedFieldsUnderLock/fieldCreationTimer/timer/stop: 3
2022-11-27 20:58:34.447 LL_I D [48:High Pri Worker #1] app-init/main/msgstoredb/healthy
2022-11-27 20:58:34.448 LL_E D [56:WhatsApp Worker #1] queuefile/loadFromFile see locked empty mini event buffer

I arrive at conclusion that I need to replace the .shm and .wal also, as the log says it reads from those files also. Well turns out you knew that already heh.

It works now! I can view chats from 2021 and older, together with this year's chat. Can't describe how happy I am, now that this journey has come to and end.

But there is 2 things to note:

Searching on text from 2022 messages is not working - will return not found
I clicked on the thumbnail of picture where the camera icon is shown, as below and my whatsapp crashed eternally. I restored once again and don't wanna click it anymore.

If fouad whatsapp does something different I do not know.

In my use case, what fouad does is basically to decrypt the db so that i can open it with sqlite viewer. as my phone is not rooted, there's no way I can directly access the msgstore.db from file manager. Need it to backup/export my db and then import/restore the db.

Btw this is the link on xda where my journey starts.

I still have one step left, which is to export an encrypted backup using fouad, then import the backup using official whatsapp app. If it works, I'm finished. Done. If all turns out well and sound, I think this will be my last comment here. Thanks a lot @MrBarbie for all the suggestions, also for updating this fantastic thing created by the awesome @natario1. Words can't describe how grateful I feel right now. Again, thanks a lot MrBarbie. Wishing you a good, happy life.

Turns out I still have the spirit and perseverance of a SEA dota player XD. When u already called gg in all chat but still playing like it's your game 5 in the final match of TI.

Good job not giving up! Looks like you managed to get to the end of the road here! With your camera image issue, pretty sure that is the problem with any batched that were dropped due to the second issue you had with the thumbnails. Fingers crossed that the full WhatsApp will figure out something isnt right and restore them after awhile with a few backup cycles. Havent figured out exactly what triggers them to do checks or converstions.

Then I think it says that something about unique, then message.chat_row_id, message.from_me, message.sender_jid_row_id is all for sure having duplicate entry, so I try to find if there's any duplicate in message.key_id. Referring from an answer from stackoverflow (we dont deserve to have this amazing thing, but still we're lucky to have it), I tried to find it by running sql as select key_id from main.message group by key_id having count(key_id) > 1;. Turns out I have a duplicate. There's 2 entries with key_id value of -1. I deleted one that's not there in the input databases then try again to restore the database.

From playing around with my small database, this is not how I thought it worked. As far as I am aware it flags uniques through the combination of all parameters. So that command from stackoverflow will not flag the right conflicts. If you are curious the best table to see this in action is the jid table because there it evaluates the combo of user number and device or something like that. User number will often be duplicated but is fine as long as the device number is still different for the same user number. Different user numbers can also use the same device numbers.

Well turns out you knew that already heh.

hehe been playing around with restoring my own db and poking around learning about the db. Meant to do a write up covering a lot of the things we discussed but didnt have time to write it and post it somewhere nice. Or at least to xda but I dont even have an xda acc lol. So its good we have these here at least as a reference or future people following the trail.

Btw this is the link on xda where my journey starts.

I still have one step left, which is to export an encrypted backup using fouad, then import the backup using official whatsapp app. If it works, I'm finished.

I actually came from the same thread. Good luck for your final restore. Thanks for your kind words and you have a great time yourself.

Oh if you still have your logs from the merge, can I have the section between [*] processing table message_quoted_media and the start of the next table which should be [*] processing table message_quoted_mentions.

I would like to just see what it does now before I apply the final fix.

Oh if you still have your logs from the merge, can I have the section between [*] processing table message_quoted_media and the start of the next table which should be [*] processing table message_quoted_mentions.

I can reproduce the log again. Do u want the log for the working, no error of the merge or the problematic one?

I can reproduce the log again. Do u want the log for the working, no error of the merge or the problematic one?

Latest working one. Just to confirm it seems to be doing the right thing. Thanks!

Thanks!

Nope. I should be the one saying that.

[*] message_quoted_location: processed 11 entries, failed=0/11, skipped=0/11, new total is 34.
[*] processing table message_quoted_media
[*] processing table message_quoted_mentions
        - input\msgstore2.db: 257 rows, columns: [_id, message_row_id, jid_row_id]
        - output\msgstore.db: 426 rows, columns: [_id, message_row_id, jid_row_id]
[*] message_quoted_mentions: editing column message_row_id (257 entries), because it references table message which was previously edited (offset=755090, dupes=0)
[*] message_quoted_mentions: editing column jid_row_id (257 entries), because it references table jid which was previously edited (offset=9202, dupes=5254)
[*] message_quoted_mentions: mapping IDs of 257 entries (offset=426, dupes=0)
[*] message_quoted_mentions: about to append 257 rows
        processed 257/257, inserted=257/257, failed=0/257, skipped=0/257...
[*] message_quoted_mentions: processed 257 entries, failed=0/257, skipped=0/257, new total is 683.
[*] processing table message_quoted_text

Nothing is processed in table message_quoted_media cause I emptied the table. If not the merging will fail there. I do have the db where the content is still intact, if you want I can get the log using it.

Nothing is processed in table message_quoted_media cause I emptied the table. If not the merging will fail there. I do have the db where the content is still intact, if you want I can get the log using it.

Oh it fails even with the latest v0.2.0b2? Or have you not tried since the earlier errors? Yeah if you can get me the logs with those I would much appreciate it. At the least even if it still fails maybe I can get an idea how to generate some test files.

Oh it fails even with the latest v0.2.0b2?

Oh I don't know u created new version. I'll run the using this one, with the message_quoted_media intact.

Oops I thought I informed you but looks like I missed it while replying to your msg after lol. Sorry EDIT: Oh wait I did but didnt clearly reference the link or version, so if you already checked for the new version after seeing https://github.com/natario1/whatsapp-database-merger/issues/9#issuecomment-1328068656 then it would be the same

Oops I thought I informed you but looks like I missed it while replying to your msg after lol. Sorry EDIT: Oh wait I did but didnt clearly reference the link or version, so if you already checked for the new version after seeing #9 (comment) then it would be the same

I missed it.

Well here's the log. It is missing a bit at the start I think, but the part that you needed is all there.

Turns out the merging is a success. The database I'm currently using is the one where I deleted all the rows from message_quoted_media. A bit tempting to use this db but I think I'm not missing on much even using the current db. Unless if it solves the two bug I mentioned before but I think it's a no right?

But there is 2 things to note:

Searching on text from 2022 messages is not working - will return not found

I clicked on the thumbnail of picture where the camera icon is shown, as below and my whatsapp crashed eternally. I restored once again and don't wanna click it anymore.

Thanks for the log! Also fyi you basically included the entire log in there starting from about 2/3 into message_thumbnail. From this it looks like it does act as documented by natario1, ie it just leaves it out. But at least it doesnt fail like the last version anymore and doesnt dump 499 good entries with it since it goes 1 by 1 but makes the log massive like yours lol. Definitely not something I can resolve so the entries that are dumped (which didnt look like many only ~30 from the first one and ~5 in the second) will be missing. So most of it should work.

Unless if it solves the two bug I mentioned before but I think it's a no right?

It should fix bug (2) for all but the 35 entries that did not get imported. Ie for most of your msgs you should get your thumbnails back. Potentially it may also fail gracefully (if having most of the table but 1 missing entry is better for it), but I dont know because Im not sure what is causing the crash. Bug(1) should be unrelated, the text should be in message table .

They only differ with that message_quoted_media table so I suppose you can reexport the db out then copy the table over from the new merge to that new reexported db.

My pleasure. Glad I can be of help.

It should fix bug (2) for all but the 35 entries that did not get imported. Ie for most of your msgs you should get your thumbnails back. Potentially it may also fail gracefully (if having most of the table but 1 missing entry is better for it), but I dont know because Im not sure what is causing the crash.

Understood. I think I'll just let it be and be extra cautious not to click on the unavailable image. To add I think if you click on unavailable image that you send, it will just tell you that the image is not available in phone's storage. But if you click download on the image sent by others, then it will crash infinitely and you need to reinstall whatsapp.

Bug(1) should be unrelated, the text should be in message table .

I read here that it is maybe related to some other table, but I checked in the merged db everything from the mentioned table is there. So it is not the case.

I read here that it is maybe related to some other table, but I checked in the merged db everything from the mentioned table is there. So it is not the case.

Ooh this is interesting, I did not know that fts was for search, I had thought it was for attachments. Now that I looked at the fts tables again then I think yeah it is correct that those tables may be why your search for new is not working. There are some tables that are missing atm, I believe the new v2 fts broke it up into more tables, but I dont know cause I never saw old v1fts table before. Preliminary look seems like we can include it, but it will take me awhile to slowly figure it out. I dont have much time to put towards this atm. segments seem to be unique by blocks and the table listing the different blocks seem to be able to hold extras

I dont have much time to put towards this atm.

Yeah I also can't contribute but if you want to do any testing just hit me up.

Hi @MrBarbie , I am using the latest WhatsApp version, getting some consistency errors resulting in the merged database file to not even be generated. Below is a small snippet of the error:

Exception in thread "main" java.lang.IllegalStateException: Database input\old.db is not consistent. Column chat.display_message_sort_id references table message, where we expected to find a list of 131 entries, but only 130 were found. Total count (source): 77736, total count (target): 489 Missing=[-9223372036854775808] at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21) at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3) at dev.natario.MainKt.main(Main.kt:50)

I have tried with append option, but it still fails. Previously (with another WhatsApp account) when I got consistency errors, the merged database would still be outputted.

Any way I can resolve this or possibly force a merged database file despite consistency errors?

Hi @MrBarbie , I am using the latest WhatsApp version, getting some consistency errors resulting in the merged database file to not even be generated. Below is a small snippet of the error:

Exception in thread "main" java.lang.IllegalStateException: Database input\old.db is not consistent. Column chat.display_message_sort_id references table message, where we expected to find a list of 131 entries, but only 130 were found. Total count (source): 77736, total count (target): 489 Missing=[-9223372036854775808] at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21) at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3) at dev.natario.MainKt.main(Main.kt:50) I have tried with append option, but it still fails. Previously (with another WhatsApp account) when I got consistency errors, the merged database would still be outputted.

Any way I can resolve this or possibly force a merged database file despite consistency errors?

You can recompile a new version with the ignoreConsistencyChecks = true enabled for that table that is failing which seems to be display_message_sort_id under chat. That said that missing entry is weird do you have something near 9223372036854775808 msgs? Might be worth investigating that weird entry.

Apologies for the late reply! I deleted the entry 9223372036854775808 (and also all the other ones that had consistency errors) in the table using SQLite and it worked! The messages were successfully merged and I could import them into WhatsApp.

Thanks for your help and updating the tool! God bless you!

Out of curiosity was that entry 9223372036854775808 a corrupted one? Just wondering what triggered it. If you are interested there is another issue where I discussed some extra ignoreConsistencyChecks I added and why. Basically most are referencing older msgs for your last read or last sent indications etc and would probably sort itself out later and i think is caused by deleted msgs

Hi gents, Sorry for disturbance.. Can someone please support on my issue since I can see it's similar to the amazing work being done here https://github.com/jpclaudino/msgstoreRecoverySPI/issues/1 Please note I'm ready to root my phone and factory reset it if someone confirmed the files I found and recovered are enough to get the chats (or I can recover them after root).

Thanks and looking forward for your reply!

Out of curiosity was that entry 9223372036854775808 a corrupted one? Just wondering what triggered it. If you are interested there is another issue where I discussed some extra ignoreConsistencyChecks I added and why. Basically most are referencing older msgs for your last read or last sent indications etc and would probably sort itself out later and i think is caused by deleted msgs

Oh sorry @MrBarbie missed your message! Unfortunately, I didn't have the time to thoroughly check it; I just proceeded to delete that entry and also several others. So far I haven't got any reports of missing messages for the person I did it for, though you could be right that it was referencing old messages.

Hello, i don't know if i didn't compile the tool right or what but i went through and run the command ./whatsapp-database-merger /home/ghost/TEST/ from @MrBarbie's fork.. then the following error happened.

[*] Input databases:

input/11.db

input/22.db [*] Checking consistency of input/11.db Column chat.jid_row_id is consistent! All 53 unique entries (out of 53) exist in the referenced table jid. Exception in thread "main" java.lang.IllegalStateException: Database input/11.db is not consistent. Column chat.display_message_row_id references table message, where we expected to find a list of 33 entries, but only 0 were found. Total count (source): 1, total count (target): 53 Missing=[3, 2744, 11330, 9, 11, 11332, 1197, 11333, 2066, 10902, 8785, 11329, 7041, 209, 10806, 102, 2419, 8465, 9484, 5993, 1241, 1701, 10593, 9549, 2284, 8449, 2899, 3727, 5323, 7201, 7699, 7753, 7787] at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21) at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3) at dev.natario.MainKt.main(Main.kt:50)

i'm not sure i'm doing it right. but it was just a test, i have about 4 databases (but different schema) https://github.com/natario1/whatsapp-database-merger/issues/8#issuecomment-1559655953

and i've used almost every guide out there in the past and i found errors every time. this repo seems to be the last active one. and i have high hopes to be able to merge them some day.

Hello, i don't know if i didn't compile the tool right or what but i went through and run the command ./whatsapp-database-merger /home/ghost/TEST/ from @MrBarbie's fork.. then the following error happened.

[_] Input databases:

input/11.db

input/22.db [_] Checking consistency of input/11.db Column chat.jid_row_id is consistent! All 53 unique entries (out of 53) exist in the referenced table jid. Exception in thread "main" java.lang.IllegalStateException: Database input/11.db is not consistent. Column chat.display_message_row_id references table message, where we expected to find a list of 33 entries, but only 0 were found. Total count (source): 1, total count (target): 53 Missing=[3, 2744, 11330, 9, 11, 11332, 1197, 11333, 2066, 10902, 8785, 11329, 7041, 209, 10806, 102, 2419, 8465, 9484, 5993, 1241, 1701, 10593, 9549, 2284, 8449, 2899, 3727, 5323, 7201, 7699, 7753, 7787] at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21) at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3) at dev.natario.MainKt.main(Main.kt:50)

i'm not sure i'm doing it right. but it was just a test, i have about 4 databases (but different schema) #8 (comment)

and i've used almost every guide out there in the past and i found errors every time. this repo seems to be the last active one. and i have high hopes to be able to merge them some day.

I don't currently have time to look into this in detail, but briefly it seems like it compiled fine if it didnt throw errors before. The error you posted seems to be a problem with it being unable to verify that your database is "complete" so to speak. It gave you a list of msgs it could not find. There can be many reasons for this but if you think your database is fine and it can ignore those missing msgs then you can set it to ignore consistency checks for that column.

There should be enough information between this thread, other issues I have posted it and the update logs along the updates I submitted to read more on what I mentioned and their nuances.

think your database is fine and it can ignore those missing msgs

my databases are good i think, i've used them in WA viewer and they worked fine. but i think it's a schema thing that they don't get match between the tables in databases. that's why i get error. ( the databases have large time gap over 3 years of messages divided into 4. and i've compared the table in sqlite browser the schema & table increases every time)

i've used sencodemaker's tool before on 1 & 2 Databases (ignoring whatever error that might pop up) the merge was successful but there were few chats missing, also some messages within chats were missing .. also, i'm not sure if all data was merged. and even when i tried to restore it to WA -- newer messages weren't shown at all (even though they appeared in the Wa viewer).. i'm not very familiar with code & scripts, so it took me a very long time to get to the previous results.

That's why I stopped since then. & kept using my newest database (No.4) in hopes to find a solution someday.

There should be enough information between this thread, other issues I have posted it and the update logs along the updates I submitted to read more on what I mentioned and their nuances.

i've been testing things over & over but as i always have doubts that i'm doing something wrong (i don't have enough experience to tell if it's right or wrong) and i end up with 10's of folders, scripts, & zips. same overwhelming situation with no progress.

I don't currently have time to look into this in detail.

i completely understand. but i think you've enough experience (from what i've seen) to solve or even create a good tool to do merge them successfully.
Just a plea, if you ever got free time consider putting some of it for the (hopeful-mergers) out there. :) Thanks

@MrBarbie It looks like the table chat has 2 new NULLable fields of type INTEGER:

ephemeral_displayed_exemptions: the whole column is set to NULL on my db.
chat_lock: mix of NULL and 0 values on my db. I guess that NULLs may just be migrated to 0s when a row is updated as the newer conversations all have 0. On my db the older the conversation the more likely it has NULL rather than 0. But some super old conversations also have 0 so I think we can safely say that it doesn't have a special purpose just for new conversations or anything.

Are you able to reproduce this?

Here is an excerpt of the log:

[*] processing table chat
    - input/msgstore_old.db: 4165 rows, columns: [_id, jid_row_id, hidden, subject, created_timestamp, display_message_row_id, last_message_row_id, last_read_message_row_id, last_read_receipt_sent_message_row_id, last_important_message_row_id, archived, sort_timestamp, mod_tag, gen, spam_detection, unseen_earliest_message_received_time, unseen_message_count, unseen_missed_calls_count, unseen_row_count, plaintext_disabled, vcard_ui_dismissed, change_number_notified_message_row_id, show_group_description, ephemeral_expiration, last_read_ephemeral_message_row_id, ephemeral_setting_timestamp, unseen_important_message_count, ephemeral_disappearing_messages_initiator, group_type, last_message_reaction_row_id, last_seen_message_reaction_row_id, unseen_message_reaction_count, growth_lock_level, growth_lock_expiration_ts, last_read_message_sort_id, display_message_sort_id, last_message_sort_id, last_read_receipt_sent_message_sort_id, has_new_community_admin_dialog_been_acknowledged, history_sync_progress]
    - output/msgstore.db: 2088 rows, columns: [_id, jid_row_id, hidden, subject, created_timestamp, display_message_row_id, last_message_row_id, last_read_message_row_id, last_read_receipt_sent_message_row_id, last_important_message_row_id, archived, sort_timestamp, mod_tag, gen, spam_detection, unseen_earliest_message_received_time, unseen_message_count, unseen_missed_calls_count, unseen_row_count, plaintext_disabled, vcard_ui_dismissed, change_number_notified_message_row_id, show_group_description, ephemeral_expiration, last_read_ephemeral_message_row_id, ephemeral_setting_timestamp, ephemeral_disappearing_messages_initiator, unseen_important_message_count, group_type, last_message_reaction_row_id, last_seen_message_reaction_row_id, unseen_message_reaction_count, growth_lock_level, growth_lock_expiration_ts, last_read_message_sort_id, display_message_sort_id, last_message_sort_id, last_read_receipt_sent_message_sort_id, has_new_community_admin_dialog_been_acknowledged, history_sync_progress, ephemeral_displayed_exemptions, chat_lock]
Exception in thread "main" java.lang.IllegalStateException: Schema mismatch for table chat between input/msgstore_old.db and output/msgstore.db. Different WhatsApp versions?
- input/msgstore_old.db: [_id, jid_row_id, hidden, subject, created_timestamp, display_message_row_id, last_message_row_id, last_read_message_row_id, last_read_receipt_sent_message_row_id, last_important_message_row_id, archived, sort_timestamp, mod_tag, gen, spam_detection, unseen_earliest_message_received_time, unseen_message_count, unseen_missed_calls_count, unseen_row_count, plaintext_disabled, vcard_ui_dismissed, change_number_notified_message_row_id, show_group_description, ephemeral_expiration, last_read_ephemeral_message_row_id, ephemeral_setting_timestamp, unseen_important_message_count, ephemeral_disappearing_messages_initiator, group_type, last_message_reaction_row_id, last_seen_message_reaction_row_id, unseen_message_reaction_count, growth_lock_level, growth_lock_expiration_ts, last_read_message_sort_id, display_message_sort_id, last_message_sort_id, last_read_receipt_sent_message_sort_id, has_new_community_admin_dialog_been_acknowledged, history_sync_progress]
- output/msgstore.db: [_id, jid_row_id, hidden, subject, created_timestamp, display_message_row_id, last_message_row_id, last_read_message_row_id, last_read_receipt_sent_message_row_id, last_important_message_row_id, archived, sort_timestamp, mod_tag, gen, spam_detection, unseen_earliest_message_received_time, unseen_message_count, unseen_missed_calls_count, unseen_row_count, plaintext_disabled, vcard_ui_dismissed, change_number_notified_message_row_id, show_group_description, ephemeral_expiration, last_read_ephemeral_message_row_id, ephemeral_setting_timestamp, ephemeral_disappearing_messages_initiator, unseen_important_message_count, group_type, last_message_reaction_row_id, last_seen_message_reaction_row_id, unseen_message_reaction_count, growth_lock_level, growth_lock_expiration_ts, last_read_message_sort_id, display_message_sort_id, last_message_sort_id, last_read_receipt_sent_message_sort_id, has_new_community_admin_dialog_been_acknowledged, history_sync_progress, ephemeral_displayed_exemptions, chat_lock]
    at dev.natario.MainKt.merge(Main.kt:254)
    at dev.natario.MainKt.main(Main.kt:67)

Dear @MrBarbie, thank you for your effort in providing the community with this tool. I'm trying to combine two databases, but getting an error during execution of your script; I was hoping you could help me resolve this issue.

1.db is about 235MB and 2.db is about 9MB. They were both downloaded from Google Drive and decrypted using Whapa.

Here is the full log from the command line (executing .bat -t combine ./) - any ideas of how to proceed?

EDIT - I will try to create a new backup and this time Include Videos, perhaps that solves it.

EDIT2 - Creating a new backup and including videos did not resolve this issue. Please help!

[*] Input databases:
    - input\1.db
    - input\2.db
[*] Checking consistency of input\1.db
    Column chat.jid_row_id is consistent! All 1212 unique entries (out of 1212) exist in the referenced table jid.
    Column chat.display_message_row_id is consistent! All 478 unique entries (out of 1212) exist in the referenced table message.
    Column chat.last_message_row_id is consistent! All 444 unique entries (out of 1212) exist in the referenced table message.
    Warning: Database input\1.db is not consistent. Column chat.last_read_message_row_id references table message, where we expected to find a list of 427 entries, but only 426 were found.
Total count (source): 490812, total count (target): 1212
Missing=[496031]
    Warning: Database input\1.db is not consistent. Column chat.last_read_receipt_sent_message_row_id references table message, where we expected to find a list of 470 entries, but only 460 were found.
Total count (source): 490812, total count (target): 1212
Missing=[218572, 266188, 249876, 355195, 328948, 294847, 385111, 496031, 260783, 518783]
    Column chat.last_important_message_row_id is consistent! All 2 unique entries (out of 1212) exist in the referenced table message.
    Column chat.change_number_notified_message_row_id is consistent! All 9 unique entries (out of 1212) exist in the referenced table message.
    Column chat.last_read_ephemeral_message_row_id is consistent! All 0 unique entries (out of 1212) exist in the referenced table message.
    Column chat.last_message_reaction_row_id is consistent! All 39 unique entries (out of 1212) exist in the referenced table message.
    Column chat.last_seen_message_reaction_row_id is consistent! All 39 unique entries (out of 1212) exist in the referenced table message.
    Warning: Database input\1.db is not consistent. Column chat.last_read_message_sort_id references table message, where we expected to find a list of 75 entries, but only 73 were found.
Total count (source): 490812, total count (target): 1212
Missing=[496031, -9223372036854775808]
    Column chat.display_message_sort_id is consistent! All 80 unique entries (out of 1212) exist in the referenced table message.
    Column chat.last_message_sort_id is consistent! All 203 unique entries (out of 1212) exist in the referenced table message.
    Warning: Database input\1.db is not consistent. Column chat.last_read_receipt_sent_message_sort_id references table message, where we expected to find a list of 75 entries, but only 73 were found.
Total count (source): 490812, total count (target): 1212
Missing=[496031, 518783]
    Warning: Database input\1.db is not consistent. Column message.chat_row_id references table chat, where we expected to find a list of 1165 entries, but only 1164 were found.
Total count (source): 1212, total count (target): 490812
Missing=[-1]
    Column message.sender_jid_row_id is consistent! All 677 unique entries (out of 490812) exist in the referenced table jid.
    Column message.sort_id is consistent! All 490811 unique entries (out of 490812) exist in the referenced table message.
    Column message_quoted.message_row_id is consistent! All 44374 unique entries (out of 44374) exist in the referenced table message.
    Column message_quoted.chat_row_id is consistent! All 90 unique entries (out of 44374) exist in the referenced table chat.
    Column message_quoted.parent_message_chat_row_id is consistent! All 101 unique entries (out of 44374) exist in the referenced table chat.
    Column message_quoted.sender_jid_row_id is consistent! All 154 unique entries (out of 44374) exist in the referenced table jid.
    Column message_vcard.message_row_id is consistent! All 203 unique entries (out of 206) exist in the referenced table message.
    Column message_vcard_jid.message_row_id is consistent! All 150 unique entries (out of 170) exist in the referenced table message.
    Warning: Database input\1.db is not consistent. Column message_vcard_jid.vcard_row_id references table message_vcard, where we expected to find a list of 153 entries, but only 152 were found.
Total count (source): 206, total count (target): 170
Missing=[-1]
    Column message_vcard_jid.vcard_jid_row_id is consistent! All 111 unique entries (out of 170) exist in the referenced table jid.
    Column group_participant_user.group_jid_row_id is consistent! All 89 unique entries (out of 1536) exist in the referenced table jid.
    Column group_participant_user.user_jid_row_id is consistent! All 1000 unique entries (out of 1536) exist in the referenced table jid.
    Column group_participant_device.group_participant_row_id is consistent! All 1536 unique entries (out of 2486) exist in the referenced table group_participant_user.
    Column group_participant_device.device_jid_row_id is consistent! All 1478 unique entries (out of 2486) exist in the referenced table jid.
Exception in thread "main" java.lang.IllegalStateException: Database input\1.db is not consistent. Column message_thumbnail.message_row_id references table message, where we expected to find a list of 10581 entries, but only 10580 were found.
Total count (source): 490812, total count (target): 10581
Missing=[512951]
        at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21)
        at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3)
        at dev.natario.MainKt.main(Main.kt:50)

natario1 / whatsapp-database-merger

Database is not consistent #9