Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
OS X Yosemite Messages database merger

OS X Yosemite Messages database merger

Early betas of OS X Yosemite had a bug where the library of old messages wasn't migrated. Instead a new library was created and all old messages were gone.

The bug has been fixed, but if you were affected, you still have two separate libraries. This script merged them for me. Use it at your own risk! You might end up worse than before. Backup everything beforehand. Twice.

This is what your ~/Library/Messages folder will look like if you're affected:

  • chat.db
  • chat.db-shm
  • chat.db-wal
  • chat.db.incompatible.v7006.sqlitedb
  • chat.db.incompatible.v7006.sqlitedb-shm
  • chat.db.incompatible.v7006.sqlitedb-wal

After you backed these files up, rename the first three to new.db, new.db-shm and new.db-wal, and rename the other three to look like the first three did. You have new restored your original library. Reboot!

When you start Messages, it will try to migrate the old messages again. Hopefully it will be successful this time. (If not I can't help you!). If you open Messages, you should see all (and only) the messages you saw before upgrading to Yosemite. Both databases now have the new format.

Next, run the merge.py migration script in that folder. It should merge the newer messages into your original database. Reboot and then delete new.*.

Again, this comes with no guarantees and might make things worse. Even if it appears to work, it might cause damage to your database. I have no idea, but the result looked good to me.

#!/bin/env python
import sqlite3, os, sys
if os.popen("uname -sr").read() != "Darwin 14.0.0\n":
print("This is not for your OS X version")
sys.exit(1)
def verify_format(cursor, name):
tables = old.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()
if tables != [(u'_SqliteDatabaseProperties',), (u'chat',), (u'sqlite_sequence',), (u'attachment',), (u'handle',), (u'chat_handle_join',), (u'message',), (u'chat_message_join',), (u'message_attachment_join',)]:
print("Unexpected database format (%s). Didn't iMessage convert it?" % name)
sys.exit(1)
def fetch(cursor, table):
cursor.execute("SELECT * FROM " + table + " ORDER BY ROWID")
return cursor.fetchall()
def max_row_id(cursor, table):
cursor.execute("SELECT Max(ROWID) FROM " + table)
return cursor.fetchone()[0]
def set_tuple_index(tup, index, value):
return tuple(tup[0:index]) + (value,) + tuple(tup[index+1:])
def tuple_where(tuples, id_func, o):
matches = filter(lambda x: id_func(x) == id_func(o), tuples)
return matches[0][0] if len(matches) > 0 else None
def merge(old_cursor, new_cursor, table, id_func):
old = fetch(old_cursor, table)
new = fetch(new_cursor, table)
additional = []
row_map = {}
next_row = max_row_id(old_cursor, table) + 1
for row in new:
rowid = row[0]
mapped_row = tuple_where(old, id_func, row)
if not mapped_row:
mapped_row = next_row
next_row += 1
additional = additional + [set_tuple_index(row, 0, mapped_row)]
print("Adding %s row %d" % (table, mapped_row))
else:
print("Mapping %s row %d->%d" % (table, rowid, mapped_row))
row_map[rowid] = mapped_row
return (row_map, additional)
def apply_map_to_tuple_index(tuples, index, m):
return map(lambda t: set_tuple_index(t, index, m[t[index]]), tuples)
def merge_joins(old, new, table, map1, map2):
print(table + ":")
new_rows = fetch(new, table)
old_rows = fetch(old, table)
mapped = apply_map_to_join(new_rows, map1, map2)
return filter(lambda x: x not in old_rows, mapped)
def apply_map_to_join(tuples, map1, map2):
for t in tuples:
print("Mapping %d/%d->%d/%d" % (t[0], t[1], map1[t[0]], map2[t[1]]))
return map(lambda t: (map1[t[0]], map2[t[1]]), tuples)
def insert_values(cursor, values, db):
for row in values:
placeholder = ", ".join(map(lambda x: "?", list(row)))
statement = "INSERT INTO %s VALUES (%s)" % (db, placeholder)
cursor.execute(statement, row)
with sqlite3.connect("chat.db") as old_db, sqlite3.connect("new.db") as new_db:
old = old_db.cursor()
new = new_db.cursor()
verify_format(old, "chat.db")
verify_format(new, "new.db")
(attachment_map, attachments) = merge(old, new, "attachment", lambda x: x[1])
(chat_map, chats) = merge(old, new, "chat", lambda x: x[1])
(handle_map, handles) = merge(old, new, "handle", lambda x: (x[1], x[3]))
(message_map, messages) = merge(old, new, "message", lambda x: x[1])
messages = apply_map_to_tuple_index(messages, 5, handle_map)
chat_handle_joins = merge_joins(old, new, "chat_handle_join", chat_map, handle_map)
chat_message_joins = merge_joins(old, new, "chat_message_join", chat_map, message_map)
message_attachment_joins = merge_joins(old, new, "message_attachment_join", message_map, attachment_map)
insert_values(old, attachments, "attachment")
insert_values(old, chats, "chat")
insert_values(old, handles, "handle")
insert_values(old, messages, "message")
insert_values(old, chat_handle_joins, "chat_handle_join")
insert_values(old, chat_message_joins, "chat_message_join")
insert_values(old, message_attachment_joins, "message_attachment_join")
old_db.commit()
print("OK")
@fluidof

This comment has been minimized.

Copy link

@fluidof fluidof commented Sep 23, 2014

Unfortunately it doesn't work for me. I've followed istruction, but after all it displays only old messages and not new ones. Anyway, thanks to try.

@glaurent

This comment has been minimized.

Copy link

@glaurent glaurent commented Oct 6, 2014

I tried it and it eventually failed with the following stack trace :

Traceback (most recent call last):
File "/Users/glaurent/Downloads/message app history merge/merge.py", line 84, in
messages = apply_map_to_tuple_index(messages, 5, handle_map)
File "/Users/glaurent/Downloads/message app history merge/merge.py", line 53, in apply_map_to_tuple_index
return map(lambda t: set_tuple_index(t, index, m[t[index]]), tuples)
File "/Users/glaurent/Downloads/message app history merge/merge.py", line 53, in
return map(lambda t: set_tuple_index(t, index, m[t[index]]), tuples)
KeyError: 0

@glaurent

This comment has been minimized.

Copy link

@glaurent glaurent commented Oct 7, 2014

The problem was a few messages with a handle_id set to 0, I fixed it in the DB and then your tool worked perfectly. Thank you very much :)

@sHaDoW8185

This comment has been minimized.

Copy link

@sHaDoW8185 sHaDoW8185 commented Oct 25, 2014

I'm wondering if you can write a script that will let me merge two current Yosemite databases. I didn't import my Messages right away on my new format and now I have two databases to deal with.

@glaurent

This comment has been minimized.

Copy link

@glaurent glaurent commented Oct 28, 2014

If someone has the same problem as me : for messages which have a handle_id set to 0, find the handle_id for your own account (can be 1 but not always) and use that value as the handle_id of those messages. I'm sorry, my expertise on the topic doesn't go any further.

@sHaDoW8185

This comment has been minimized.

Copy link

@sHaDoW8185 sHaDoW8185 commented Nov 2, 2014

My problem is that when I run the script I get the error "Didn't iMessage already convert these?" I've checked the two db files and the newer .db files have 1 new piece of information per entry which is not in the old .db files. I'd really like to find a way to be able to merge my two files together, but I don't quite fit in the same boat as those who had OS X yosemite beta and it didn't convert the db files correctly. I just wiped my mavericks and came back into yosemite and reinstalled all my programs and copied over my photo library, but forgot to reimport my old messages. So I've just started a who new chat.db (not due to an incorrect conversion done by yosemite). Is there any way to force the merge even if the file type is not as expected?

@Tiggar

This comment has been minimized.

Copy link

@Tiggar Tiggar commented Nov 23, 2014

Amazing nschum!

Your script worked for me after I fixed the 0-handles thanks to @glaurent-intellicore.

Some hints...
...which may help the one or the other user during the process

  1. This script merges two Yosemite database of the same version/structure. Make sure your database come from the same OS X version. In my case both databases were from 10.10.1
  2. Copy the both databases in a separate folder. Do not operate under ~/Library/Messages
  3. Copy the merge.py script in the same directory as both databases.
  4. Launch merge process in terminal with python merge.py
  5. After successful merge copy back the chat.db-files to ~/Library/Messages
  6. To see the changes in the Messages database you need a reboot

I also encountered the error described by glaurent-intellicore, also it looked slightly different. Important is the last line of the error trace. If it says

KeyError: 0

you'll probably have orphaned messages in both of your message databases. To correct the issue, please open both *.db files with a SQLite browser, eg. from http://sqlitebrowser.org and updated all rows in the messages table where handle_id = 0.

To do this I first checked the handle table, to find an appropriate handle_id. Once finding one I executed the following sql command:

UPDATE message
SET handle_id = 5 # Replace with your desired id
WHERE handle_id = 0;

After updating the message table write changes to the database and your good to go.

@chriscombshawaii

This comment has been minimized.

Copy link

@chriscombshawaii chriscombshawaii commented Feb 18, 2015

Hi Guys,

Great to finally see something that comes close to addressing how to merge 2 chat databases. I have been trying to find a way to do this for a while. Here is my issue, hoping someone more knowledgeable might be able to help with :

Using time machine, I backed up Mavericks and then did a clean install of Yosemite, only manually bringing over items and things that I wanted to from the backup files. However I put off bringing over all my chat convos. With that said, I have a couple months worth of new chat history (therefore a new chat.db from Yosemite). I am trying to merge my Mavericks DB with the new Yosemite DB. I have tried the above with failed results. Steps below:

  • Copied Chat DB from Mavericks and Yosemite into separate folder
  • Renamed the Yosemite chat.db files to "new" as listed above, kept the Mavericks chat.db as was
  • Copied the above script into text edit, saved as RTF and renamed to merge.py
  • Placed merge.py into same folder as the copied databases old and new
  • launched terminal from folder and used the python merge.py
  • received this error: File "merge.py", line 1
    {\rtf1\ansi\ansicpg1252\cocoartf1343\cocoasubrtf160
    ^
    SyntaxError: unexpected character after line continuation character

Anyone have ideas or thoughts of how I might be able to make this work?

@CNG

This comment has been minimized.

Copy link

@CNG CNG commented Mar 2, 2015

Thank you for the script! I used it to merge two histories on Yosemite, though my need wasn't related to the bug you created this for. Anyway, worked great!

@tombunton

This comment has been minimized.

Copy link

@tombunton tombunton commented Mar 26, 2015

CNG - can you give a step by step how you achieved the merging of two yosemite histories please?

I'm getting the error - "This is not for your OS X version"

@davidov81

This comment has been minimized.

Copy link

@davidov81 davidov81 commented May 11, 2015

Hi! I can not do this because of my version of OSX.
Could you tell me how to fix it in 10.10.3?
Actually I get message "This is not for your OS X version", so I can not do anything. Thank you by advance from desperate user.

@janineanne-walmart

This comment has been minimized.

Copy link

@janineanne-walmart janineanne-walmart commented May 11, 2015

@tombunton, @davidov81, just edit the script and change the string it's looking for from "Darwin 14.0.0" to "Darwin 14.3.0" (or whatever 'name -sr' returns for you). I just had to do that, and I also changed "#!/usr/env python" to "#!/usr/bin/python", and the script worked flawlessly for me after that. The version of python that ships with OS X is very old, but this script still worked with it.

@davidov81

This comment has been minimized.

Copy link

@davidov81 davidov81 commented Jul 7, 2015

Thanks janineanne-walmart! It worked. But partially. i.e. I was able to get back my old messages. But now I can not get the recent ones :) Archived & old messages are now accessible, but not the last messages. I tried to swap db files and launch again the script, but no help. Is there a tool to merge any messages database, so I can recover access to my complete database?

@kopurando

This comment has been minimized.

Copy link

@kopurando kopurando commented Jul 18, 2015

Thank you for such a great fix! I had my chat.db ruined during Mavericks -> El Capitan upgrade, and while trying to fix that, I discovered that my chat.db had been ruined once again by dropbox 4 months ago. Thanks to dropbox (what an irony), I could recover previous version of my chat.db, and used my wife's Yosemite to migrate both DBs to new sqlite format (7006 -> 9004, which is the same in El capitan as I understood).
After that I used your script to merge both databases together, and now I've recovered back four years worth of precious hearty chats!

Two notes:

  1. handle_id = 0; means group chats, not orphaned messages. So change it to whichever contact's id you chat with most (in my case it's my mom, heh). On the other side, why is 0 value unacceptable by the script? Maybe it should be updated (I dont do python unfort to fix that ;(

  2. El Capitan's migration added extra column order_date in message table, as a result, merge.py failed with an error message similar to "52 values but 51 columns". To fix:

ALTER TABLE message ADD COLUMN order_date INTEGER DEFAULT 0;
UPDATE message SET order_date=date;

Lesson: do not keep /LIbrary/Messages synced in Dropbox! During re-indexing rush so called "conflicted-copy" situation can render chat.db corrupted. Subsequently, Messages will fail to open it and will rename it to chat.db.incompatible.unknownversion.sqlitedb. To fix that I used:
echo ".dump" | sqlite3 chat.incompatible.db | sqlite3 chat.compatible.db

Now the only question is why Yosemite migration worked, but El Capitan did not?? Same beta-version bug was re-introduced again? Apple, Apple, Apple...

@wtrst111

This comment has been minimized.

Copy link

@wtrst111 wtrst111 commented Jul 20, 2015

I am trying to revert from El Capitan to Yosemite. When I copied the messages folder back and open Messages the 3 db files get renamed chat.db.incompatible.v9004.sqlitedb, chat.db.incompatible.v9004.sqlitedb-shm, chat.db.incompatible.v9004.sqlitedb-wal.
I followed the steps above and ran the script to return 'Unexpected database format (chat.db). Didn't iMessage convert it?'
Please help convert my messages db from El Capitan to Yosemite

@mp607

This comment has been minimized.

Copy link

@mp607 mp607 commented Aug 8, 2015

Thanks for your help!
It works fine with Darwin 14.4.0

@jkeen

This comment has been minimized.

Copy link

@jkeen jkeen commented Nov 29, 2015

After doing some digging in trying to understand the chat.db format, @kopurando is right that handle_id = 0 means group chats, but from what I can see on El Capitan, it more specifically means you within the group chat. I didn't particularly like the solution before of just assigning that handle_id to something else and not maintaining group history, so I'm trying to continue trying to understand all this until I can modify the script to handle those cases properly.

So far the following query has made things pretty readable within the chat.db, so hopefully it helps some future traveler to this thread.

SELECT
    m.rowid as message_id,
    (SELECT chat_id FROM chat_message_join WHERE chat_message_join.message_id = m.rowid) as message_group,
    CASE p.participant_count
        WHEN 0 THEN "???"
        WHEN 1 THEN "Individual"
        ELSE "Group"
    END AS chat_type,
    DATETIME(date +978307200, 'unixepoch', 'localtime') AS date,
    CASE is_from_me
        WHEN 0 THEN "Received"
        WHEN 1 THEN "Sent"
        ELSE is_from_me
    END AS type,
    id AS address,
    text,
    CASE cache_has_attachments
        WHEN 0 THEN Null
        WHEN 1 THEN filename
    END AS attachment,
    m.service
FROM message AS m
LEFT JOIN message_attachment_join AS maj ON message_id = m.rowid
LEFT JOIN attachment AS a ON a.rowid = maj.attachment_id
LEFT JOIN handle AS h ON h.rowid = m.handle_id
LEFT JOIN (SELECT count(*) as participant_count, cmj.chat_id, cmj.message_id as mid FROM 
    chat_handle_join as chj
    INNER JOIN chat_message_join as cmj on cmj.chat_id = chj.chat_id
    GROUP BY cmj.message_id, cmj.chat_id) as p on p.mid = m.rowid

ORDER BY date DESC

Now to break apart what's causing this script to fail, and then to fix it.

@AlexS376

This comment has been minimized.

Copy link

@AlexS376 AlexS376 commented Dec 30, 2015

Hello,

I’ve just downgraded back to Mavericks from Yosemite and I want to restore my iMessage history. Is it possible to do this with this script or an altered version of it?
If so, could anybody please explain how do I run this from Terminal and what parts of the script I have to change?

Thank you very much!

@bostonlady233

This comment has been minimized.

Copy link

@bostonlady233 bostonlady233 commented May 29, 2016

Hello everyone...I know this is an oldish thread, but I am brand new to this forum and am having this exact problem - after a recent clean install of OS X El Capitan and a total Time Machine fail, I realized the only way to get my Messages database history was to extract the sms.db from an iPhone backup. My Messages threads are super important to me and I never delete them - mine actually go back to my first iPhone in 2008! So, I figured this could at least be a good opportunity to get my Mac's Messages database to match up with my iPhone's, since it only had histories going back to the introduction of the Mac Messages app (whenever that was) and was missing all SMS conversations until last summer, when it SMS was introduced there.

Anyway, I changed the iPhone's sms.db file to chat.db and added that, along with the Attachments folder, to ~/Library/Messages, and deleted the com.Apple.iChat folder from ~/Library/Containers, and restarted. My Messages app remained empty, and now I have files in ~/Library/Messages named chat.db, chat.db-wal and chat.db-shm, along with chat.db.incompatible.v9006.sqlitedb, chat.db.incompatible.v9006.sqlitedb-shm, and chat.db.incompatible.v9006.sqlitedb-wal. The "incompatible" files are the larger ones of the bunch, so I think that's where my actual old Messages archive is located. After doing a lot of research over the course of a few days it appears as though what you guys have described above is exactly what could fix the issue - merging the "incompatible" files into the other ones to get Messages to read them.

However, please forgive me for this...the steps you've described above is a bit over my head! I'm generally pretty tech savvy and am sure I could figure it out if someone could be so kind as to spell it out for me a bit more. I would be very, very grateful if one of you guys could! I got a bit lost at the "running the script out of the folder" stuff, and would be so grateful for a little extra help, as I really do want my messages back!

Thanks everyone. :)

@pchhetri

This comment has been minimized.

Copy link

@pchhetri pchhetri commented Jul 16, 2016

Thanks @nschum for the script! I was able to run the script properly but the new mapping caused some the older messages for a chat to appear as the latest message.

Anyways, I decided to take a crack at merging two databases into a brand new database and the results were satisfactory for my needs.

If anyone wants to try and give me feedback here it is: https://gist.github.com/pchhetri/120a10cb33e0d462dc5a5776bd70f50d

Thanks again @nschum for providing the initial insight into this!!

@eplt

This comment has been minimized.

Copy link

@eplt eplt commented Jul 23, 2017

Good news is that Messages on iCloud will help to merge these. Just need to "load up" the right chat.db file you want to sync, remember to restart each time you switch to another chat.db, wait for it to sync all to iCloud, and the resultiing DB will be all merged properly, with no handle_id or table structure issue. I plan to dig out all the iOS backups I have in the past 10 years and restore with a spare device and upload to iCloud to have a full merge. (Old iOS didn't use to store all SMS, I remember having to jailbreak the device and added trigger to sqlite to keep auto-deleted messages.)

@calawrence

This comment has been minimized.

Copy link

@calawrence calawrence commented Nov 16, 2017

eplt - this is great news!

Question: when you say "restart", are you referring to the device or the application?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment