Can you help with this issue? about watoi HOT 5 CLOSED

CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL, key_from_me INTEGER, key_id TEXT NOT NULL, status INTEGER, needs_push INTEGER, data TEXT, timestamp INTEGER, media_url TEXT, media_mime_type TEXT, media_wa_type TEXT, media_size INTEGER, media_name TEXT, media_caption TEXT, media_hash TEXT, media_duration INTEGER, origin INTEGER, latitude REAL, longitude REAL, thumb_image TEXT, remote_resource TEXT, received_timestamp INTEGER, send_timestamp INTEGER, receipt_server_timestamp INTEGER, receipt_device_timestamp INTEGER, read_device_timestamp INTEGER, played_device_timestamp INTEGER, raw_data BLOB, recipient_count INTEGER, participant_hash TEXT, starred INTEGER, quoted_row_id INTEGER, mentioned_jids TEXT, multicast_id TEXT, edit_version INTEGER, media_enc_hash TEXT, payment_transaction_id TEXT);
CREATE TABLE sqlite_sequence(name,seq);
CREATE UNIQUE INDEX messages_key_index on messages (key_remote_jid, key_from_me, key_id);
CREATE INDEX messages_jid_id_index on messages (key_remote_jid, _id);
CREATE INDEX media_hash_index on messages (media_hash);
CREATE INDEX media_type_index on messages (media_wa_type);
CREATE INDEX media_type_jid_index on messages (key_remote_jid, media_wa_type);
CREATE INDEX starred_index on messages (starred);
CREATE TABLE chat_list (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT UNIQUE, message_table_id INTEGER, subject TEXT, creation INTEGER, last_read_message_table_id INTEGER, last_read_receipt_sent_message_table_id INTEGER, archived INTEGER, sort_timestamp INTEGER, mod_tag INTEGER, gen REAL, my_messages INTEGER, plaintext_disabled BOOLEAN, last_message_table_id INTEGER, unseen_message_count INTEGER, unseen_missed_calls_count INTEGER, unseen_row_count INTEGER, vcard_ui_dismissed INTEGER, deleted_message_id INTEGER, deleted_starred_message_id INTEGER, deleted_message_categories TEXT, change_number_notified_message_id INTEGER, last_important_message_table_id INTEGER, show_group_description INTEGER);
CREATE TABLE props (_id INTEGER PRIMARY KEY AUTOINCREMENT, key TEXT UNIQUE, value TEXT);
CREATE VIRTUAL TABLE messages_fts USING FTS3();
CREATE TABLE IF NOT EXISTS 'messages_fts_content'(docid INTEGER PRIMARY KEY, 'c0content');
CREATE TABLE IF NOT EXISTS 'messages_fts_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'messages_fts_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
CREATE TRIGGER messages_bd_trigger BEFORE DELETE ON messages BEGIN DELETE FROM messages_fts WHERE docid=old._id; END;
CREATE TABLE messages_quotes (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL, key_from_me INTEGER, key_id TEXT NOT NULL, status INTEGER, needs_push INTEGER, data TEXT, timestamp INTEGER, media_url TEXT, media_mime_type TEXT, media_wa_type TEXT, media_size INTEGER, media_name TEXT, media_caption TEXT, media_hash TEXT, media_duration INTEGER, origin INTEGER, latitude REAL, longitude REAL, thumb_image TEXT, remote_resource TEXT, received_timestamp INTEGER, send_timestamp INTEGER, receipt_server_timestamp INTEGER, receipt_device_timestamp INTEGER, read_device_timestamp INTEGER, played_device_timestamp INTEGER, raw_data BLOB, recipient_count INTEGER, participant_hash TEXT, starred INTEGER, quoted_row_id INTEGER, mentioned_jids TEXT, multicast_id TEXT, edit_version INTEGER, media_enc_hash TEXT, payment_transaction_id TEXT);
CREATE TRIGGER messages_bd_for_quotes_trigger BEFORE DELETE ON messages BEGIN DELETE FROM messages_quotes WHERE _id=old.quoted_row_id; END;
CREATE TABLE messages_vcards (_id INTEGER PRIMARY KEY AUTOINCREMENT, message_row_id INTEGER, sender_jid TEXT, vcard TEXT, chat_jid TEXT);
CREATE TABLE messages_vcards_jids (_id INTEGER PRIMARY KEY AUTOINCREMENT, message_row_id INTEGER, vcard_jid TEXT, vcard_row_id INTEGER);
CREATE TRIGGER messages_bd_for_vcards_trigger BEFORE DELETE ON messages BEGIN DELETE FROM messages_vcards WHERE message_row_id=old._id; END;
CREATE TRIGGER messages_bd_for_vcards_jids_trigger BEFORE DELETE ON messages BEGIN DELETE FROM messages_vcards_jids WHERE message_row_id=old._id; END;
CREATE TABLE messages_edits (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL, key_from_me INTEGER, key_id TEXT NOT NULL, status INTEGER, needs_push INTEGER, data TEXT, timestamp INTEGER, media_url TEXT, media_mime_type TEXT, media_wa_type TEXT, media_size INTEGER, media_name TEXT, media_caption TEXT, media_hash TEXT, media_duration INTEGER, origin INTEGER, latitude REAL, longitude REAL, thumb_image TEXT, remote_resource TEXT, received_timestamp INTEGER, send_timestamp INTEGER, receipt_server_timestamp INTEGER, receipt_device_timestamp INTEGER, read_device_timestamp INTEGER, played_device_timestamp INTEGER, raw_data BLOB, recipient_count INTEGER, participant_hash TEXT, starred INTEGER, quoted_row_id INTEGER, mentioned_jids TEXT, multicast_id TEXT, edit_version INTEGER, media_enc_hash TEXT, payment_transaction_id TEXT);
CREATE TABLE messages_links (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT, message_row_id INTEGER, link_index INTEGER);
CREATE TRIGGER messages_bd_for_links_trigger BEFORE DELETE ON messages BEGIN DELETE FROM messages_links WHERE message_row_id=old._id; END;
CREATE TABLE frequents (_id INTEGER PRIMARY KEY AUTOINCREMENT, jid TEXT NOT NULL, type INTEGER NOT NULL, message_count INTEGER NOT NULL);
CREATE TABLE receipts (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL, key_id TEXT NOT NULL, remote_resource TEXT, receipt_device_timestamp INTEGER, read_device_timestamp INTEGER, played_device_timestamp INTEGER);
CREATE INDEX receipts_key_index on receipts (key_remote_jid, key_id);
CREATE TRIGGER messages_bd_for_receipts_trigger BEFORE DELETE ON messages BEGIN DELETE FROM receipts WHERE key_remote_jid=old.key_remote_jid AND key_id=old.key_id; END;
CREATE TABLE group_participants (_id INTEGER PRIMARY KEY AUTOINCREMENT, gjid TEXT NOT NULL, jid TEXT NOT NULL, admin INTEGER, pending INTEGER, sent_sender_key INTEGER);
CREATE UNIQUE INDEX group_participants_index on group_participants (gjid, jid);
CREATE TABLE group_participants_history (_id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp DATETIME NOT NULL, gjid TEXT NOT NULL, jid TEXT NOT NULL, action INTEGER NOT NULL, old_phash TEXT NOT NULL, new_phash TEXT NOT NULL);
CREATE INDEX group_participants_history_index on group_participants_history (gjid);
CREATE TABLE media_refs (_id INTEGER PRIMARY KEY AUTOINCREMENT, path TEXT UNIQUE, ref_count INTEGER);
CREATE TABLE media_streaming_sidecar (_id INTEGER PRIMARY KEY AUTOINCREMENT, sidecar BLOB, timestamp DATETIME, key_remote_jid TEXT NOT NULL, key_from_me INTEGER, key_id TEXT NOT NULL);
CREATE TABLE message_thumbnails (thumbnail BLOB, timestamp DATETIME, key_remote_jid TEXT NOT NULL, key_from_me INTEGER, key_id TEXT NOT NULL);
CREATE UNIQUE INDEX messages_thumbnail_key_index on message_thumbnails (key_remote_jid, key_from_me, key_id);
CREATE TABLE status_list (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT UNIQUE, message_table_id INTEGER, last_read_message_table_id INTEGER, last_read_receipt_sent_message_table_id INTEGER, first_unread_message_table_id INTEGER, autodownload_limit_message_table_id INTEGER, timestamp INTEGER, unseen_count INTEGER, total_count INTEGER);
CREATE TABLE conversion_tuples (key_remote_jid TEXT PRIMARY KEY, data TEXT, source TEXT, last_interaction INTEGER, first_interaction INTEGER);
CREATE TABLE labels (_id INTEGER PRIMARY KEY AUTOINCREMENT, label_name TEXT, predefined_id INTEGER);
CREATE TABLE labeled_messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, label_id INTEGER NOT NULL, message_row_id INTEGER NOT NULL);
CREATE UNIQUE INDEX labeled_messages_index on labeled_messages (label_id, message_row_id);
CREATE TRIGGER messages_bd_for_labeled_messages_trigger BEFORE DELETE ON messages BEGIN DELETE FROM labeled_messages WHERE message_row_id=old._id; END;
CREATE TRIGGER labels_bd_for_labeled_messages_trigger BEFORE DELETE ON labels BEGIN DELETE FROM labeled_messages WHERE label_id=old._id; END;
CREATE TABLE labeled_jids (_id INTEGER PRIMARY KEY AUTOINCREMENT, label_id INTEGER NOT NULL, jid TEXT);
CREATE UNIQUE INDEX labeled_jids_index on labeled_jids (label_id, jid);
CREATE TRIGGER labels_bd_for_labeled_jids_trigger BEFORE DELETE ON labels BEGIN DELETE FROM labeled_jids WHERE label_id=old._id; END;
CREATE TABLE deleted_chat_jobs (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL, block_size INTEGER, deleted_message_id INTEGER, deleted_starred_message_id INTEGER, deleted_message_categories TEXT, delete_files BOOLEAN);
CREATE INDEX deleted_chat_jobs_index ON deleted_chat_jobs (key_remote_jid, _id);
CREATE UNIQUE INDEX labels_index ON labels (label_name);
CREATE TABLE pay_transactions (key_remote_jid TEXT, key_from_me INTEGER, key_id TEXT, id TEXT, timestamp INTEGER, status INTEGER, error_code TEXT, sender TEXT, receiver TEXT, type INTEGER, currency TEXT, amount_1000, credential_id TEXT, methods TEXT, bank_transaction_id TEXT, metadata TEXT, init_timestamp INTEGER);
CREATE UNIQUE INDEX message_payment_transactions_index ON pay_transactions (key_id);
CREATE UNIQUE INDEX message_payment_transactions_id_index ON pay_transactions (id);
CREATE TABLE messages_dehydrated_hsm (_id INTEGER PRIMARY KEY AUTOINCREMENT, message_row_id INTEGER UNIQUE, message_elementname TEXT, message_namespace TEXT, message_lg TEXT);
CREATE TRIGGER messages_bd_for_dehydrated_hsms_trigger BEFORE DELETE ON messages BEGIN DELETE FROM messages_dehydrated_hsm WHERE message_row_id=old._id; END;

from watoi.

Hmmm, looks like it encountered a member with null JID. I'm not sure if it's some kind of broken contact or storage format has changed (previously that field can't be null). Could you please dump a schema of your msgstore.db? It can be done like this:

sqlite3 /Users/lucasazevedo/Dev/_projects/msgstore.db '.schema'

from watoi.

Hi, FYI same on my side. Indeed it might be linked to a new version ? (2.18.31 here)
And thanks A LOT for this it's really nice to share this method !

from watoi.

As it turns out, changes in Android DB ain't the cause of this (group_participants.jid field is still not nullable). The reason is that watoi somehow can't determine the JID of the DB owner (our JID, in other words), but I'm not sure why...

I haven't found a place where this info is stored neither in Android backup, nor in iOS, so I guess it by walking all the chats in iOS backup to count the number of rooms each JID is participating in. The one with the most groups should be ours. It was a fragile method and now it's failed to do its job.

After taking a closer look at the import log, this line seemed suspicious:

CoreData: error: Attempt to add read-only file at path ChatStorage.sqlite -- file:///Users/lucasazevedo/Dev/_projects/whatsss/ read/write. Adding it read-only instead. This will be a hard error in the future; you must specify the NSReadOnlyPersistentStoreOption.

It may not be the cause of current error, but if interpret the message correctly, it could mean that changes that we've made to iOS db won't be saved.

And then this lines:

2018-02-27 20:30:00.478 watoi[40149:5054170] [email protected]: not found
2018-02-27 20:30:00.480 watoi[40149:5054170] is group chat

It seemed strange that group chat was not found in iOS backup. While sometimes it's a valid situation (e.g. rooms that you no longer member of), it led me to the hypothesis that iOS backup db wasn't in the proper state. If no group chats were present in the ChatStorage.sqlite, it would explain null JID and the mentioned log line.

@lucasazevedonet, @suhailsheikh7, @Tharkhun did you do the first step of the migration process?

Check that Whatsapp is activated on iDevice. You should see the list of group chats when you open the app. Most likely, there won't be any messages prior to moving to iOS. You can even send/receive a message or two to be sure that there is something to back up.

from watoi.

Most likely the issue is caused by empty ChatStorage.sqlite. Try to repeat the backup process and follow the steps again. If there we're no file at all, it will be caught by checks I've added some time ago.

from watoi.