iOS 6 and un-syncable Safari bookmarks

Source

Shortly after iOS 6 was released I upgraded my iPhone. About the same time I set up iClouds backups of Safari bookmarks. Within days (hours? It's all a blur) I discovered that I could no longer sync Safari bookmarks via iTunes. Worse, I could no longer edit them on my iPhone.

The problem proved to be a corrupt schema in Mobile Safari's bookmarks database.

Here's the quick repair procedure. It does not require you to restore your phone to factory settings. (It should go without saying, but: do this at your own risk!)

  1. Buy iBackupBot — it will save a lot of hassle in editing your phone backups
  2. Export Library/Safari/Bookmarks.db
  3. Using sqlite3 /path/to/exported/Bookmarks.db, run the following commands on your exported bookmarks database to re-create missing tables and indices 
    CREATE TABLE bookmark_title_words (id INTEGER PRIMARY KEY, bookmark_id INTEGER NOT NULL CONSTRAINT fk_bookmark_id REFERENCES bookmarks(id) ON DELETE CASCADE, word TEXT, word_index INTEGER);
CREATE INDEX title_bookmark_id_index ON bookmark_title_words(bookmark_id);
CREATE INDEX title_word_index ON bookmark_title_words(word);
  4. Re-import the modified Library/Safari/Bookmarks.db
  5. Use iTunes to "Restore from backup…"

Note that the last step should read "Use iBackupBot to restore the bookmarks file to your iPhone." I haven't yet tried doing that. I didn't even know iBackupBot could restore individual files until after my problem had been solved. Ah, ignorance :)

Also note that the exact set of required schema restoration commands may vary from one phone to another. These were the bits which were missing on my phone.

I learned a lot of interesting trivia while bumbling along toward this solution. Writer's block permitting, I'll try to summarize it in subsequent posts. Meanwhile, here are some links which helped me find a solution. Thanks to all for sharing!