DBD::SQLite::db prepare failed: no such table: message at iOSMessages.pm line 86.

[aryairani]

I receive this failure message if I accidentally leave off the trailing slash on the --directory_path argument.

Note that the trailing slash isn't visible in the GitHub readme, unless you scroll to the far right of the code block. What do you think about making this more visible, or changing the script to detect/correct this issue, and/or emit a clearer error message?

Thanks for this great script!

[holene]

On macOS Sierra, I get the error even if I do include the trailing slash...

[justinfinkle]

I got the error as well. It looks like the location of the database files in the script has changed.

The messages db was "3d0d7e5fb2ce288813306e4d4636395e047a3d28", and the contacts db was "31bb7ba8914766d4ba40d6dfb6113c8b614be442", both of which were in the root folder.

These seem to have moved to subdirectories "3d" and "31", respectively. When I prepend the folder names to the paths in iOSMessages.pm and iOSContacts.pm the script runs, but attachments aren't pulled correctly. I'll try looking into it, but I'm a perl novice.

[justinfinkle]

The path error seems to be the case for the attachments as well.

I added this at below line 120 and everything seems to run appropriately now. my $sha1_filename = substr($sha1_filename, 0, 2) . '/' . $sha1_filename;

Will update a relevant fork when I can. Though someone with more expertise can suggest better fixes

[taifatang]

After reading Justinfinkle comment I did the following and it worked for me

In IOSContacts.pm Replace line 13
_sms_db_filename => '3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28',

In IOSMessages.pm Replace line 16 _sms_db_filename => '3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28',

[basnijholt]

Running into the same problem, the solution @taifatang gave doesn't work.

Edit: I realise that what @taifatang should have written is:

In iOSContacts.pm on line 16: _contacts_db_filename => '31/31bb7ba8914766d4ba40d6dfb6113c8b614be442',

in iOSMessages.pm on line 13: _sms_db_filename => '3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28', add newline under 120: my $sha1_filename = substr($sha1_filename, 0, 2) . '/' . $sha1_filename;

then it works :)

[blakemattos]

@basnijholt this works for me (running MacOS 10.11) - :)

[cadmiumores]

@basnijholt Um, I tried to do that but I think I did something wrong because it did not work. Where does the "newline under 120" go and how do I make sure it got there? (To clarify, I have absolutely no programming experience of any kind and made this account specifically to post this comment.)

[basnijholt]

@cadmiumores I have put my version of the code here and opened a PR #16 where you can see the changes, I hope it works 😄

[cadmiumores]

Yes! I've got it now! Thank you so much.

On Tue, Jan 10, 2017 at 3:32 PM, Bas Nijholt [email protected] wrote:

@cadmiumores https://github.com/cadmiumores I have put my version of the code here https://github.com/basnijholt/iOSMessageExport/ and opened a PR #16 https://github.com/chelseybaker/iOSMessageExport/pull/16 where you can see the changes, I hope it works 😄

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/chelseybaker/iOSMessageExport/issues/13#issuecomment-271689549, or mute the thread https://github.com/notifications/unsubscribe-auth/AX4ZUAdPZ0YZhBwoCJeeOXx846HTEIuCks5rQ-rTgaJpZM4EqafT .

[dieggsy]

@basnijholt with your code i get the following:

DBD::SQLite::db prepare failed: file is encrypted or is not a database at iOSMessages.pm line 86, <DATA> line 1.
DBD::SQLite::db prepare failed: file is encrypted or is not a database at iOSMessages.pm line 86, <DATA> line 1.

[justinfinkle]

@therockmandolinist It's indicating that you're trying to open a non SQL file. Can you confirm what the value of your _sms_db_filename variable is? It should be:

_sms_db_filename => '3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28'

[dieggsy]

@justinfinkle I think the issue was just that I had the option for encrypted backups checked. As soon as I unchecked that, it worked as expected.

[justinfinkle]

Good, that makes sense. I forgot they had the option to encrypt the backups. Glad you figured it out.

[youseeme]

@justinfinkle I am still getting the same error message. I checked the _sms_db_filename variable name and it what you have listed. My backup wasn't encrypted, so I don't know what to try. FYI, be kind, I am new at this. I need to completely restore my iphone (including deleting all the messages off my phone) and I want to be able to save my messages!

[justinfinkle]

Can you link the exact error message you receive?

Also, what version of iOS and iTunes do you have? I'm not sure when the database organization change was made relative to the original iOSMessageExport file. Have you tried both the old and new values for _sms_db_filename and contacts_db_filename?

From what I understand these variables refer to where the iOS backup keeps the SQL database files. These database files are used to match up contacts, text messages, and images into a human readable form.

[youseeme]

DBD::SQLite::db prepare failed: no such table: message at iOSMessages.pm line 86. DBD::SQLite::db prepare failed: no such table: message at iOSMessages.pm line 86.

I tried both values. I have OS X Yosemite and iTunes version 12.5.4.42.

[justinfinkle]

@youseeme Can you verify that there is a file in your backup folder that matches _sms_db_filename? For example if my iOS backup directory is XXX then there should be a file 3d0d7e5fb2ce288813306e4d4636395e047a3d28 in the folder 3d. It should have the full path XXX/3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28. Any luck finding that file?

[youseeme]

@justinfinkle that file is actually in 3e and 3d folder. should I move i delete the version in the 3e folder?

[justinfinkle]

EDIT: @youseeme Sorry I misunderstood your comment earlier. You should change _sms_db_filename to match the one in the 3e folder. Can you post the file path for the sms database so I can confirm?

Old: Yes that seems like it should work. We'll need to figure out a more flexible way to find the SMS db file so that it works for everyone.

[FadaSarducci]

Running OSX 10.12.4 and it worked

[pudgyturtle]

Just made these changes to the original script on OSX 10.12.6 (Sierra) and it worked!

[andrewsohrabi]

When I run this command: perl iOSMessageExport/backup.pl --directory_path 4a39c9a820e14b7f7cf093f86f479da4cf4bce64

I get an error that says: DBI connect('dbname=4a39c9a820e14b7f7cf093f86f479da4cf4bce643d/3d0d7e5fb2ce288813306e4d4636395e047a3d28','',...) failed: unable to open database file at iOSMessageExport/iOSMessages.pm line 45.

Line 45 in iOSMessages.pm is

$self->{_sms_db} = DBI->connect( "dbi:SQLite:dbname=".$self->{_backup_directory}.$self->{_sms_db_filename}, "", "", { RaiseError => 1 }, )

Any ideas?

[justinfinkle]

It seems like the script can't find the database file to parse. Try adding a trailing slash when you give the directory path: 4a39c9a820e14b7f7cf093f86f479da4cf4bce64/. I don't know where you're running the command from, but you should make sure the relative path is correct, or give the absolute path.

[bleepnetworks]

In iOSContacts.pm on line 16: _contacts_db_filename => '31/31bb7ba8914766d4ba40d6dfb6113c8b614be442',

in iOSMessages.pm on line 13: _sms_db_filename => '3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28', add newline under 120: my $sha1_filename = substr($sha1_filename, 0, 2) . '/' . $sha1_filename;

then it works :)

@basnijholt fix above works on macos 10.13.6 / ios 12.0

[mbrna]

Hello! So I have MacOS 10.15.7 and iOS 13.7 I made the changes above, but now get these two errors (same error?): I've never made this work, this is the first time I am ever trying. I want to save my text messages off my phone. Can I debug this in Xcode? I use Xcode but am not a perl coder by any means (ObjC, Swift, Python (newly), sure but not perl).

thanks!

DBD::SQLite::db prepare failed: malformed database schema (message_idx_undelivered_one_to_one_imessage) - near "where": syntax error at iOSMessages.pm line 86. DBD::SQLite::db prepare failed: malformed database schema (message_idx_undelivered_one_to_one_imessage) - near "where": syntax error at iOSMessages.pm line 86.

[marnixava]

After fixing the problems as mentioned in earlier comments, I still have the same trouble as described by "mbrna" in a comment from 2020-10-27. This is also my first time trying. I get that "malformed database schema (message_idx_undelivered_one_to_one_imessage)" error. I know some perl, a little sqlite. Would love to be able to extract messages and other data from my iPhone backups. Possibly something has changed in the schema for the iMessage database? The item mentioned in the error looks like this in the schema:

CREATE INDEX message_idx_undelivered_one_to_one_imessage ON message(cache_roomnames,service,is_sent,is_delivered,was_downgraded,item_type) where cache_roomnames IS NULL AND service = 'iMessage' AND is_sent = 1 AND is_delivered = 0 AND was_downgraded = 0 AND item_type == 0`

Possibly someone reading here knows what's wrong and how to fix it. Maybe it's the "==" near the end? How does one fix that or get around it in a query?

[mbrna]

For what it is worth, I have been able to use Python to extract my text message from an iPhone backup similar to the way the above perl program/script does it. I can't yet figure out how to attach the sender's name to the text, though.

I also found an existing python script that parses a lot of the files in the backup: https://github.com/Maxmad68/ibake I do not yet know if that one pulls out the text messages, but it grabs a lot of stuff from the backup!