Issues with accessing exported browser file

[sylasabdullahnguyen]

Hi, just wanted to say thanks for this tool, it's so much simpler than others I've looked at.

I might be missing something really simple here but I'm trying to access the exported db file using DB Browser for SQLite and I am getting this error:

Reason: malformed database schema (messages_on_insert_insert_mentions) - near ">>": syntax error

I made sure Signal was not running when I exported the DB. I am on MacBook Pro, Apple M1 Pro, Ventura 13.4. Here are the outputs for what you mentioned in #26:

sigtop db signal.db (returns nothing, file is created)

sigtop check (returns nothing)

sigtop query 'pragma user_version'
87

sigtop query 'select name, type from sqlite_schema where tbl_name = "messages"'
messages|table
sqlite_autoindex_messages_1|index
messages_id|index
messages_receipt|index
messages_schemaVersion|index
messages_view_once|index
messages_sourceUuid|index
messages_searchOrder|index
messages_on_view_once_update|trigger
messages_unexpectedly_missing_expiration_start_timestamp|index
messages_hasAttachments|index
messages_hasFileAttachments|index
messages_on_insert|trigger
messages_on_update|trigger
messages_on_delete|trigger
messages_conversation|index
messages_unread|index
messages_conversation_no_story_id|index
messages_unread_no_story_id|index
messages_unseen_no_story|index
messages_unseen_with_story|index
expiring_message_by_conversation_and_received_at|index
messages_by_distribution_list|index
messages_by_storyId|index
messages_call|index
messages_expires_at|index
messages_hasVisualMediaAttachments|index
messages_preview|index
messages_preview_without_story|index
messages_activity|index
message_user_initiated|index
messages_unread_mentions|index
messages_unread_mentions_no_story_id|index
messages_on_insert_insert_mentions|trigger
messages_on_update_update_mentions|trigger
messages_story_replies|index

sqlite3 signal.db 'pragma integrity_check'
ok

sqlite3 signal.db 'select name, type from sqlite_schema where tbl_name = "messages"'
messages|table
sqlite_autoindex_messages_1|index
messages_id|index
messages_receipt|index
messages_schemaVersion|index
messages_view_once|index
messages_sourceUuid|index
messages_searchOrder|index
messages_unexpectedly_missing_expiration_start_timestamp|index
messages_hasAttachments|index
messages_hasFileAttachments|index
messages_conversation|index
messages_unread|index
messages_conversation_no_story_id|index
messages_unread_no_story_id|index
messages_unseen_no_story|index
messages_unseen_with_story|index
expiring_message_by_conversation_and_received_at|index
messages_by_distribution_list|index
messages_by_storyId|index
messages_call|index
messages_expires_at|index
messages_hasVisualMediaAttachments|index
messages_preview|index
messages_preview_without_story|index
messages_activity|index
message_user_initiated|index
messages_unread_mentions|index
messages_unread_mentions_no_story_id|index
messages_story_replies|index
messages_on_view_once_update|trigger
messages_on_insert|trigger
messages_on_update|trigger
messages_on_delete|trigger
messages_on_insert_insert_mentions|trigger
messages_on_update_update_mentions|trigger

sqlite3 'select count(*) from messages' (starts sqlite3 command line utility but does nothing else)

sqlite3 -version
3.39.5 2022-10-14 20:58:05 554764a6e721fab307c63a4f98cd958c8428a5d9d8edfde951858d6fd02daapl

DB Browser for SQLite (version from GUI)
Version 3.12.2

Everything you mentioned seems to come back clean but the SQLite browser still won't open it for some reason. Would you happen to have any ideas?

[sylasabdullahnguyen]

I upgraded sqlite3 to: SQLite version 3.43.0 2023-08-24 12:36:59.

The only difference in results seems to be

sqlite3 signal.db 'select name, type from sqlite_schema where tbl_name = "messages"'
Error: in prepare, no such column: messages
  ct name, type from sqlite_schema where tbl_name = "messages"
                                      error here ---^

But when I switch the single/double quotes, it works fine:

sqlite3 signal.db "select name, type from sqlite_schema where tbl_name = 'messages'"
messages|table
sqlite_autoindex_messages_1|index
messages_id|index
messages_receipt|index
messages_schemaVersion|index
messages_view_once|index
messages_sourceUuid|index
messages_searchOrder|index
messages_unexpectedly_missing_expiration_start_timestamp|index
messages_hasAttachments|index
messages_hasFileAttachments|index
messages_conversation|index
messages_unread|index
messages_conversation_no_story_id|index
messages_unread_no_story_id|index
messages_unseen_no_story|index
messages_unseen_with_story|index
expiring_message_by_conversation_and_received_at|index
messages_by_distribution_list|index
messages_by_storyId|index
messages_call|index
messages_expires_at|index
messages_hasVisualMediaAttachments|index
messages_preview|index
messages_preview_without_story|index
messages_activity|index
message_user_initiated|index
messages_unread_mentions|index
messages_unread_mentions_no_story_id|index
messages_story_replies|index
messages_on_view_once_update|trigger
messages_on_insert|trigger
messages_on_update|trigger
messages_on_delete|trigger
messages_on_insert_insert_mentions|trigger
messages_on_update_update_mentions|trigger

The message count query when directed to signal.db returns:

sqlite3 signal.db "select count(*) from messages"
162646

[sylasabdullahnguyen]

Some more information, the .db file opens just fine with SQLiteStudio (3.4.4) but nothing else seems to be able to get it to work.

[tbvdm]

I'm trying to access the exported db file using DB Browser for SQLite and I am getting this error:

Could not open database file.
Reason: malformed database schema (messages_on_insert_insert_mentions) - near ">>": syntax error

Please show the output of sqlitebrowser -v.

But when I switch the single/double quotes, it works fine:

My mistake. sqlite3 no longer accepts double-quoted strings.

[sylasabdullahnguyen]

This command does not seem to work:

sqlitebrowser -v
zsh: command not found: sqlitebrowser

I was trying to use the GUI for DB Browser for SQLite, and this is the information I can get from it:

DB Browser for SQLite Version 3.12.2

Built for arm64-little_endian-lp64, running on arm64
Qt Version 5.15.6
SQLCipher Version 4.5.1 community (based on SQLite 3.37.2).

[tbvdm]

Thanks, that's the information I was after.

I'm afraid your version of sqlitebrowser is too old. It has SQLite 3.37.2, but the Signal Desktop database uses a feature that was introduced in SQLite 3.38.0.

You could try with a nightly build of sqlitebrowser. Maybe it has a newer SQLite version.

[sylasabdullahnguyen]

Yep, that was it. Not sure why I didn't catch that bit in the version information. Thanks for the help!