Merger: Fail at "Sort database" step

[win98se]

Before starting, is suggested that you optimize the ORIGINAL (from your Android installation) msgstore.db database to accelerate writes.
Press Control-C if you want to skip and want to start moving calls right away

Type the path of the ORIGINAL msgstore.db file:

We start by moving calls. Press Control-C if you want to skip to fixing mentions

Type the path of the calls.db (Windows Phone DB) file:

We start fixing mentions. Press Control-C if you want to skip to adding missing messages and media

Type the path of the ORIGINAL msgstore.db (Android DB) file:

We start adding missing media and messages. Press Control-C if you want to skip to fixing replies (NOT RECOMMENDED).

Type the path of the ORIGINAL msgstore.db (Android DB) file: D:\f**k\org\msgstore.db
Type the path of msgstore.db (The file produced by my other script) file: D:\f**k\msgstore.db
Type the path of messages.db (The original Windows Phone database): D:\f**k\messages.db

100% (842 of 842) |##############################################################| Elapsed Time: 0:00:00 Time:  0:00:00

Added missing messages and media from the alternative database.

¡¡¡¡¡YOU ARE ADVISED TO FIX REPLIES AND SORT THE DATABASE AFTER THIS!!!!!


We start fixing replies. Press Control-C if you want to skip to cleaning database.
MAKE SURE THAT YOU HAVE FINISHED ADDING MISSING MESSAGES BEFORE, OTHERWISE SOME DATA MIGHT BE MISSING

Type the path of the temp.db (generated by my other script) file: D:\f**k\temp.db
Type the path of the ORIGINAL msgstore.db (Android DB) file: D:\f**k\org\msgstore.db

100% (1 of 1) |##################################################################| Elapsed Time: 0:00:00 Time:  0:00:00
Fixing replies part completed successfully


We start sorting IDs in the database. Press Control-C if you want to skip to database optimization.
THIS IS HIGHLY RECOMMENDED AFTER ADDING MISSING MESSAGES.

Type the path of the ORIGINAL msgstore.db (Android DB) file: D:\f**k\org\msgstore.db

Initializing... This might take a while...

Now backing up data into the temporary database...

100% (1153 of 1153) |############################################################| Elapsed Time: 0:00:00 Time:  0:00:00

Backed up data successfully. Deleting original data from msgstore.db and restoring...

100% (1166 of 1166) |############################################################| Elapsed Time: 0:00:00 Time:  0:00:00
Traceback (most recent call last):
  File "merger.py", line 1108, in <module>
  File "site-packages\progressbar\bar.py", line 639, in update
ValueError: Value 1167 is out of range, should be between 0 and 1166
[1820] Failed to execute script merger

I'm running it from Windows 10 Version 1909, WhatsApp for Android's version is 2.20.11.

[ferferga]

Good afternoon!

Are you running it from the self-executable file or directly from the .py file?

[win98se]

I'm running from the EXE file.

I ran the .py script file from Ubuntu 18.04.3 LTS, same result.

I also find some problems running the script, such as database locking, etc.

I'm providing all 5 .db files needed for the script for you to inspect, if you prefer.

BTW, it's 11:56 PM here in Malaysia. 😂

[ferferga]

Hahahaha good night then!

The problem is not with the database, seems that I forgot to add to the progressbar counter something and the progress bar is going beyond 100%. That's why I asked if you were running the script directly from the .py file, because I didn't know if you had enough knowledge to run the file directly from the .py file (that's why I provide also a self-executable, because it solves the hassle for non-Python guys)

Just append a * 2 (or * 4 to be sure, no matter if the progress bar ends before it reaches 100%) at the end of this line and it should go perfect. I will try to search what I miss and commit the fix today, but if you're in a rush, that will solve the issue for sure.

Database locking problems? Or table locking? They're not the same. If you got database, that means that you left another connection to the database opened by another program.

[win98se]

Appending *4 to line 592 works, thank you! (I haven't tested with *2, tho...) But some problems still exist.

1. I can't move calls and the script will halt at the "Calls" step, whether I skipped the "First Optimization" step or not.

Before starting, is suggested that you optimize the ORIGINAL (from your Android installation) msgstore.db database to accelerate writes.        
Press Control-C if you want to skip and want to start moving calls right away

Type the path of the ORIGINAL msgstore.db file: /home/lim/f**k/org/msgstore.db

Starting database optimization...
Database is healthy
The database has been vacuumed and optimized!


We start by moving calls. Press Control-C if you want to skip to fixing mentions

Type the path of the calls.db (Windows Phone DB) file: /home/lim/f**k/calls.db
Type the path of the ORIGINAL msgstore.db (Android DB) file: /home/lim/f**k/org/msgstore.db
Processing data into the temporary database...
100% (291 of 291) |############################################################################################| Elapsed Time: 0:00:00 Time:  0:00:00
Traceback (most recent call last):
  File "merger_2.py", line 102, in <module>
    jid_id = msgstore.fetchone()[0]
TypeError: 'NoneType' object is not subscriptable

2. The database locking problem I met is at the "Add missing media and messages" step, after I ran the "Fix mentions" step.

Before starting, is suggested that you optimize the ORIGINAL (from your Android installation) msgstore.db database to accelerate writes.        
Press Control-C if you want to skip and want to start moving calls right away

Type the path of the ORIGINAL msgstore.db file: /home/lim/f**k/org/msgstore.db

Starting database optimization...
Database is healthy
The database has been vacuumed and optimized!


We start by moving calls. Press Control-C if you want to skip to fixing mentions

Type the path of the calls.db (Windows Phone DB) file: ^C

We start fixing mentions. Press Control-C if you want to skip to adding missing messages and media

Type the path of the ORIGINAL msgstore.db (Android DB) file: /home/lim/f**k/org/msgstore.db


Updating normal messages...

- |#                                                                                                                        | 0 Elapsed Time: 0:00:00


Finished normal messages. Repeating procedure for 'messages_quotes' table...
- |#                                                                                                                        | 0 Elapsed Time: 0:00:00
Fixing mentions part completed successfully


We start adding missing media and messages. Press Control-C if you want to skip to fixing replies (NOT RECOMMENDED).

Type the path of the ORIGINAL msgstore.db (Android DB) file: /home/lim/f**k/org/msgstore.db
Type the path of msgstore.db (The file produced by my other script) file: /home/lim/f**k/msgstore.db
Type the path of messages.db (The original Windows Phone database): /home/lim/f**k/messages.db

Traceback (most recent call last):
  File "merger_2.py", line 261, in <module>
    orig.execute("PRAGMA journal_mode = MEMORY;")
sqlite3.OperationalError: database is locked

I've inspected what's locking the database, and I found that the same Python process running merger.py is the culprit. I can unlock the database using LockHunter (Windows only) forcibly, but subsequently, errors still occur.

Hence, the workaround of adding *4 in the code only works if I skipped the "Calls" step and the "Fix mentions" step.

[ferferga]

Don't skip step 1 because the database is locked when you obtain it from the VM. That step closes all pending transactions that WhatsApp left and will remove the two files (wal and shm) that came alongside msgstore.db (check that before continuing to moving calls) alongside further optimization.

If you are going to start a part of the script and you have three files remaining from the other part, that means that the last step executed didn't go well. So yes, run that.

About the calls part, are you sure you have WhatsApp 2.20 and that you're running the process against the correct database? Be sure of that because the error means that the table that the script is trying to query in the database doesn't exist, and that table MUST be always present in WhatsApp (afaik) in order to work correctly.
Check if the table jid and its column _id exists in your database, if that's not the case, delete all the data from it, run a VACUUM command and attach it here. Make sure to run the VACUUM command for your security, or undeleted data will be still present in the database. You can use SQLiteDatabaseViewer for that.

[win98se]

Since the error at the "Calls" step occurs at line 102, I assume that it checks in msgstore.db. I opened two msgstore.db files respectively, and both databases have the table jid and their _id columns.

I put msgstore.db-shm and msgstore.db-wal alongside with the original msgstore.db and re-ran the script (since then I never skip the first step), same results.

And yes, I'm sure I have WhatsApp 2.20.11.

[ferferga]

Okay, just noticed and (supposed) what is your problem. You're not moving a lot of messagss, so I suppose that your Android installation is brand new.

Easily explained, the problem is that the script is trying to fetch contact information from the Android database to compare it with the one of the WP and reference it correctly. As I developed the script for me, I already assumed that chats with each one of the contacts that I called in the WP database were already present in the Android one, as that's the usual thing.

You're also running this without using WinWazzapMigrator before right? It's the first time I receive reports from somebody doing this, so don't expect that the troubleshooting will end here :P

I will push a commit with the fix in a while (if I can, it's late here, so maybe it'll need to wait to tomorrow)

About the database thing, you still didn't unlock it properly. Discard the database you have now and repeat the extraction process

Run the cp msgstore* /storage/emulated/0 command again and get the three files into your desktop. Open the script, do optimization process. Check that only one msgstore.db file exists. Copy it to another directory, if another step failed afterwards you will need to use this copy you just created as it's unmodified.

Once this is done, continue with the step 3

[win98se]

1. The first Android installation of WhatsApp is on the VM, and yes, it's brand new.

2. I don't have the money to buy WinWazzapMigrator (I'm poor af), and for me, it's illogical to buy an app just for one use only. So yes, I didn't use it before.

3. I always keep original copies of all 7 files, so I can restore them if anything happens.

In fact, I used 7-Zip to extract the files directly from /data/data/com.whatsapp/databases after I shut down the VM (I'm using VirtualBox 6.1). For integrity, I've also compared the three files located at /data/data/com.whatsapp/databases and /storage/emulated/0 respectively, and they're same.

Or do I need to unfreeze WhatsApp, launch it, freeze it again and get another copy of the database files?

[win98se]

Update: I've unfreezed WhatsApp, launched it, freezed it again and got another copy of the database files.

Same results, I had to skip the "Calls" step and the "Fix mentions" step to work.

I'm still waiting for your fix. :p

[ferferga]

Check with my latest commit please @win98se

For the database thing, please run the PRAGMA journal_mode query in the SQLite database viewer of your choice and tell me what's the output

[win98se]

What file do I need to run this query on?

Edit 1: I ran the query to all 5 files. Outputs are below -

• calls.db: wal
• messages.db: wal
• msgstore.db: delete
• temp.db: delete
• Original msgstore.db: wal

Edit 2: Now the "Calls" step works, but I still have to skip the "Fix mentions" step (it still causes database locking) and append *4 to line 604 of merger.py.

[ferferga]

@win98se The original msgstore.db

[win98se]

The output of the query is wal. Outputs for another 4 files are shown in my edited comment above. ;)

[win98se]

I have a possible fix for the "Fix mentions" step causing database locking - add a line between lines 253 and 254 with the code db_msgstore.close().

[ferferga]

@win98se That means that subsequent queries would not run. The optimization step (and all the script sections) already does that for you when finished. That's why I think you're doing something wrong.

Confirm me again. Are you sure that running the optimization step in original msgstore.db file leaves you with only one original msgstore.db file and not more?

[win98se]

Yes, the first step leaves only one original msgstore.db file.

I'm considering that fix because I've noticed that there are .close() statements in all other parts of the code, except at the "Fix mentions" step (lines 189-254), so I assumed that not calling .close() causes database locking.

[ferferga]

You're completely right, I forgot that one.

Thank you very much for pointing that out. Pull the changes or add the line to your local copy

[win98se]

I don't know how to do a pull request... 😂

This tool helps me so much in migrating messages from Windows 10 Mobile to Android at no cost, thank you so much for developing it. Hope to see a new release soon!

[ferferga]

@win98se Everything worked right after re-importing the database then?

With pull I mean syncing your local files with the repository.

Thank you very much for your reports!

[win98se]

Yes, messages and call history are there.

Oh, but I didn't use git to download this repo, I downloaded ZIP file instead.

[ferferga]

@win98se Okay, good to know that WinWazzapMigrator is not mandatory then. Thank you for letting me know!

Oh, okay, thought you used git