Database Issues Whatsapp v2.24.20.89 (and v2.24.23.78)

[ReMiOS]

After updating Whatsapp to v2.24.20.89 opening the msgstore.db database gave me the error:
"no such column: chat_view.raw_string_id"

If fixed this by dropping the view and creating a new table with the following python script:
(also see the commented issue below for previous issues)

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3

# Connect Database
dbconn = sqlite3.connect( 'msgstore.db' )
cursor = dbconn.cursor()

SQL = '''CREATE TABLE IF NOT EXISTS messages AS SELECT file_hash,message._id,message.chat_row_id,message.key_id,jid.raw_string AS key_remote_jid,message.from_me AS key_from_me,
message.status,message.text_data AS data,message.timestamp,message_media.message_url AS media_url,message.message_type AS media_wa_type,message_media.file_size 
AS media_size,message_media.mime_type AS media_mime_type,message_media.media_name,message_media.media_caption,message_media.media_duration,message_location.latitude,
message_location.longitude,message_media.file_path AS thumb_image,message_payment.remote_resource,message_thumbnail.thumbnail AS raw_data, message._id AS quoted_row_id 
FROM message LEFT JOIN message_media ON message._id = message_media.message_row_id LEFT JOIN chat ON message.chat_row_id = chat._id LEFT JOIN jid ON jid_row_id = jid._id 
LEFT JOIN message_location ON message._id = message_location.message_row_id LEFT JOIN message_payment ON message.key_id = message_payment.message_row_id LEFT JOIN message_thumbnail 
ON message._id = message_thumbnail.message_row_id'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE TABLE IF NOT EXISTS chat2 AS SELECT chat.*, jid.raw_string as raw_string_jid FROM chat LEFT JOIN jid ON chat.jid_row_id = jid._id'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''DROP VIEW IF EXISTS chat_view'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE VIEW IF NOT EXISTS chat_view AS SELECT * FROM chat2'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS chat_hidden_index ON chat2 (hidden)'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE TABLE IF NOT EXISTS messages_quotes AS SELECT message_row_id AS _id, chat_row_id, parent_message_chat_row_id, from_me, sender_jid_row_id, key_id, timestamp, 
message_type, text_data, payment_transaction_id, lookup_tables, origin FROM message_quoted'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE TABLE IF NOT EXISTS messages_links AS SELECT * FROM message_link'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS messages_chat_id_index ON messages (chat_row_id,_id)'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS messages_jid_id_index on messages (key_remote_jid, _id)'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS messages_key_index on messages ( key_remote_jid, key_from_me, key_id )'''
cursor.execute(SQL)
dbconn.commit()

dbconn.close()

Compiled windows executable:
wav_create_table.zip

Also see this issue: #151 (comment)

[vb0]

Hello @andreas-mausch , any chance we can get this fixed at some point? As far as I know massaging the tables this way and using the latest whatsapp-viewer is the only comfortable way to access your WA chat backups (after you decrypt them, which is covered by https://github.com/ElDavoo/wa-crypt-tools , and can be done sanely on Android if one enables the E2E encryption with the 64-characters long key that is directly shown to you, no need for any other shenanigans).

If not at least maybe we can include on the front page a link to @ReMiOS fixes so people can find it easily and know this still works with these database tweaks? Otherwise maybe we can have these included in wa-crypt-tools so people could get directly a db compatible with whatsapp-viewer.

[KaramMed]

Hey @vb0 @ReMiOS, i tried the wav_create_table script but the issue is still happening, however i found a quick workarround, there's a software that can import the db and vizualise the chats, its called (Backuptrans android iphone whatsapp transfer plus
)

[nuel-clet]

Hello @andreas-mausch , any chance we can get this fixed at some point? As far as I know massaging the tables this way and using the latest whatsapp-viewer is the only comfortable way to access your WA chat backups (after you decrypt them, which is covered by https://github.com/ElDavoo/wa-crypt-tools , and can be done sanely on Android if one enables the E2E encryption with the 64-characters long key that is directly shown to you, no need for any other shenanigans).

If not at least maybe we can include on the front page a link to @ReMiOS fixes so people can find it easily and know this still works with these database tweaks? Otherwise maybe we can have these included in wa-crypt-tools so people could get directly a db compatible with whatsapp-viewer.

Thank you for the hard work, is there any way to get the audio, images and deleted messages

@ReMiOS
@vb0
@andreas-mausch

[ReMiOS]

After updating from WhatsApp v2.24.22.78 to v2.24.23.78 the WAviewer gave me an error

I've fixed this by dropping the faulty index (recreating the index and performing reindex on the index didn't work)
Also i've attached the table schema's for the two database versions (maybe someone can find out why the index is failing)

wav_create_table.zip
Database_Schemas.zip

See the updated code below:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3

# Connect to Database
dbconn = sqlite3.connect( 'msgstore.db' )
cursor = dbconn.cursor()

SQL = '''CREATE TABLE IF NOT EXISTS messages AS SELECT file_hash,message._id,message.chat_row_id,message.key_id,jid.raw_string AS key_remote_jid,message.from_me AS key_from_me,
message.status,message.text_data AS data,message.timestamp,message_media.message_url AS media_url,message.message_type AS media_wa_type,message_media.file_size 
AS media_size,message_media.mime_type AS media_mime_type,message_media.media_name,message_media.media_caption,message_media.media_duration,message_location.latitude,
message_location.longitude,message_media.file_path AS thumb_image,message_payment.remote_resource,message_thumbnail.thumbnail AS raw_data, message._id AS quoted_row_id 
FROM message LEFT JOIN message_media ON message._id = message_media.message_row_id LEFT JOIN chat ON message.chat_row_id = chat._id LEFT JOIN jid ON jid_row_id = jid._id 
LEFT JOIN message_location ON message._id = message_location.message_row_id LEFT JOIN message_payment ON message.key_id = message_payment.message_row_id LEFT JOIN message_thumbnail 
ON message._id = message_thumbnail.message_row_id'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE TABLE IF NOT EXISTS chat2 AS SELECT chat.*, jid.raw_string as raw_string_jid FROM chat LEFT JOIN jid ON chat.jid_row_id = jid._id'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''DROP VIEW IF EXISTS chat_view'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE VIEW IF NOT EXISTS chat_view AS SELECT * FROM chat2'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS chat_hidden_index ON chat2 (hidden)'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE TABLE IF NOT EXISTS messages_quotes AS SELECT message_row_id AS _id, chat_row_id, parent_message_chat_row_id, from_me, sender_jid_row_id, key_id, timestamp, 
message_type, text_data, payment_transaction_id, lookup_tables, origin FROM message_quoted'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE TABLE IF NOT EXISTS messages_links AS SELECT * FROM message_link'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS messages_chat_id_index ON messages (chat_row_id,_id)'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS messages_jid_id_index on messages (key_remote_jid, _id)'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''CREATE INDEX IF NOT EXISTS messages_key_index on messages ( key_remote_jid, key_from_me, key_id )'''
cursor.execute(SQL)
dbconn.commit()

SQL = '''DROP INDEX IF EXISTS lid_display_name_upper_username_index'''
cursor.execute(SQL)
dbconn.commit()

dbconn.close()