NotesSo, finally got some time to re-assess this iCloud Notes issue and take a deep dive. (See Part 1 of my Notes Corruption saga if you haven’t already.

TL;DR, I renamed a folder and added a / within the name, and half my notes disappeared, syncing the corruption up to iCloud as well.)

In short I found and opened up the SQLite database and found some interesting things which finally led to a successful resolution. I may even write an app in the future that would correct these types of issues automatically, as it shouldn’t be that hard. This is definitely something Apple should have taken into account and programmed around to begin with.

Lessons Learned: (yeah sorry, I hate bullets too)

  • The Notes database for OSX (Mavericks) is located at /Users/<username>/Library/Containers/com.apple.Notes/Data/Library/Notes/ and the file name is NotesV2.storedata . Quickest way to get there is Finder > Go > Go to folder > Paste the directory above subbing in your username.
  • Before doing any of this, back up that freaking folder. Find and back up the corresponding known-good folder in your Time Machine backup too, if you have one.
  • As I suspected, the notes I thought were “gone”, were not actually gone. Folders were merely tags as I’d theorized, and the rename had corrupted some references. All the note content was still there, but the folder in question was not appearing in the Notes app due to the way it deals with (or doesn’t deal with) broken database references and the resulting orphaned objects. In effect, you could say the database was “corrupted”, but only in the sense that the app was not displaying all the data within.
    • This isn’t uncommon with apps that rely on databases. Databases and syncing usually result in disaster at some point – a major pet peeve of mine – and the fix is usually relatively easy once you understand the way the monster you’re dealing with works. However in this case, understanding the database alone wasn’t enough due to the syncing aspect, as you’ll see later.
    • This would be a neat way to hide some data inside an iCloud notes database such that only anyone who’s actually looking for it would be able to find it. Forensics competitions anyone? Anyway,
  • Let’s assume you use Time Machine – most people would assume a backup would help you fix the problem easily. It doesn’t. You can disconnect from the internet, restore the database from a Time Machine backup and then open Notes, and you will have your old notes back, sort of. You’ll be missing your notes since then of course, so you’d think you could connect to iCloud, it would do some differential diagnosis and download the missing notes.. but the first time you reconnect to the internet, instead your database will be overwritten from iCloud and re-corrupt itself. Great.
    • This would be no big deal if they allowed Export and Import of Notes, buuut… they don’t.  You can’t drag notes out to the desktop as a .txt or anythign either. Your only option is a PDF export, ONE AT A TIME. Great job Apple.
    • Why on earth does it overwrite anyway? Well..
  • If you manually make changes to the Notes database or restore a Time Machine backup, iCloud will overwrite them the next time you connect to the internet.
    • But why?! Well, it has to do with how they manage changes to notes happening simultaneously on multiple devices as I mentioned in my last post. Turns out they use a cryptic incrementing “highest-value” number system to determine who has the latest update for which parts of which notes and it looks very complex. If you don’t take the time to figure out that entire system and put the right values in the right places (which I don’t recommend doing – you’re asking for trouble if you make a mistake), your changes will simply be overwritten the next time you start up Notes.
      • Again, Export / Import of notes would totally resolve this. -_-
  • Thankfully, there are some workarounds that could actually solve the iCloud Overwrite problem.
    • (A) If you had a good Time Machine backup, you could go into Settings > iCloud and uncheck Notes syncing before restoring from Time Machine, however I haven’t tested this method for a variety of reasons. Namely, it starts off by deleting all your iCloud synced notes from the machine as soon as you turn it off, and turning syncing back on after applying an old restore probably wouldn’t go well. I don’t recommend this.
    • (B) You could disconnect from the internet, do the Time Machine restore song and dance if you have one, then from a different computer visit https://www.icloud.com/#settings and use their option to essentially erase all the data on iCloud and do a one-way push from your devices back to iCloud. DO NOT quote me on this or rely on their promise of not deleting any data – this is simply how they’ve described it to work and in theory it should. However this is a race condition and other devices with a more recent corrupted database might get there before you, overwrite you later once they check in, tons of stuff wonky could happen here if you have a few Apple devices.
    • (C – and this is what you should do) Follow the manual database repair steps below.

The Fix I Employed:

  1. Close Notes, disconnect from the internet. Back up the notes database before making any modifications.
  2. Open the database in an SQLite Browser such as http://sqlitebrowser.org, find the missing folders in the Z_FOLDERS table.
  3. Locate the folders called RootFolder. There may be several RootFolders, and it’s hard to tell what is what. For me, the first (lowest Z_PK number) RootFolder in the database was the one which corresponded to the local “On My Mac” section of Notes.
  4. Fix the Z_PARENT and Z6_PARENT values for the folders you are missing or screwed up, such that they’re no longer pointed to the Z_PK and Z_ENT values of a (seemingly invisible) Trash folder. Instead, change them to the corresponding Z_PK and Z_ENT values of the local RootFolder folder you found.
  5. Save your changes, and Open notes again. Observe the location of the folders you were missing. Are they back? Are they under “On My Mac”? Then you were successful. Otherwise, you may need to close Notes, Open the database again, and change the numbers to a different RootFolder until you find the correct one.
  6. Once you’ve got your folders back and they’re located under “On My Mac”, you can reconnect to the internet.  Now, iCloud will only overwrite the portion of the database it pertains to. Your notes will stay safe in the On My Mac portion.
  7. Create a new folder in iCloud (for the love of god, avoid special characters this time), and you should be able to select all of your notes and drag them back into iCloud. Voila, you’ve recovered and your changes will sync over to your other devices :)