Contact Sync and Database.sqlite3 corruption bring Mail.app and my fast iMac to its knees

Sometime over the past two weeks my relatively new iMac seemed to lose steam. It was slow to respond, lots of beachballs. Felt like it was occupied, but Activity Monitor didn't show anything obvious.

I think OS X was caught in a sync loop involving Spanning Sync, OS X iSync and MobileMe sync all synchronizing between OS X Address Book, my iPhone, MobileMe Address Book, and a subsection of Google Contacts. That setup has worked well for about a year, but it's fallen apart now.

Looking at the Spanning Sync logs a few contacts were being updated constantly. Nothing special about them I can see.

I'm afraid my Contacts Unification program has died. With MobileMe on the way out, and with Google frantically tweaking Contacts for their social/G+ initiatives, and Apple doing its own Lion and iCloud contact management I don't think this is going to come back. I'll need another approach.

For the moment I've set MobileMe sync to manual and disabled Spanning Sync. I may do a manual sync every few days. My iMac is working again.

Synchronization is Hell.

Update 7/22/11: This morning I received a warning from Carbon Copy Cloner about a physical read error with Library/PubSub/Database/Database.sqlite3. Interesting ...

07/22 08:25:58 rsync: read errors mapping "/Users/jfaughnan 1/Library/PubSub/Database/Database.sqlite3": Input/output error (5)

07/22 08:26:22 ERROR: Users/jfaughnan 1/Library/PubSub/Database/Database.sqlite3 failed verification -- update discarded. (51)

Disk Utility didn't find any physical errors, so I assume Database.sqlite3 has been corrupted.

I put Database.sqlite3 in the trash, logged out and back in, then deleted it. Mail.app recreated it, went from 7MB to 2MB. Safari and Mail.app both felt much faster. I then downloaded the most recent version of Onyx, restarted as Admin, then ran all the cleanup and automation scripts. At the moment the machine feels about 10 times faster.

Even though sqlite3 is a core part of OS X Data Management, but it's hard to find much about how it's actually used in most apps. I found one page that listed apps that use SQLite databases, but those apps are listed as storing data in different locations. I found a couple of references [1] that suggest this is where Mail.app and Safari store their RSS feeds.

One article mentioned using this command to fix this sqlite database:

/usr/bin/sqlite3 ~/Library/PubSub/Database/Database.sqlite3 vacuum

Using that search string brought up more interesting articles:

• 10.5: Set per-feed update/expire intervals in Safari RSS - Mac OS X Hints
• One possible fix for Safari freezes and slowdowns - Mac OS X Hints

The second link is from 2009, and it sounds very much like what has been happening to me in OS X 10.6 ...

I am syncing, on several Macs and several user accounts, information such as Calender, Mail Rules, Bookmarks, etc. After recently updating to Safari 4.01 and OS X 10.5.7, all of a sudden I had all very bad Safari performance -- including constant freeze situations. After being ready to delete the impacted user, I realized that there were some issues with the database.sqlite3 file in the Library » PubSub » Database folder. The PubSub folder is used for tracking RSS feeds, and it seems my problem resided there.

After deleting the database.sqlite file, and resetting the Mobile Me information, the freezes and crashes stopped...

Deleting this file did not actually remove the feeds from Mail.app and Safari.

Looking at my feeds I see I've been tracking my Google Reader shared item feed. That is an enormous feed; I think I've been pushing the limits of what Mail.app RSS subscription can handle. I deleted all of my Feed subscriptions from Safari and Mail.app, then I delted all the data in ~/Library/PubSub/Feeds and I deleted Database.sqlite3 (again). On logout/login it was recreated with a size of 74kb (empty). Mail.app is now blindingly fast and my system is healthy again.

I won't be using Mail.app's feed reader features any more. They don't scale. I use Google Reader, but to archive some of my feeds I'm going to buy a dedicated standalone feed reader.

Update 7/24/11: I created an archive of my Address Book, cleared the iSync database then restarted MobileMe. It told me it was going to add 843 records. Coincidentally, that's how many I have. I went ahead anyway, since it's surprisingly easy to save and restore Address Book archives (I have about fifty versions saved). In fact I ended up with 843 that look correct on spot check. Curiously, both MobileMe and my laptop say I have 842 contacts. So there's something broken somewhere in iSync. It's easy to see why Apple dropped iSync (so far) from Lion. Synchronization is, honestly, and truly, Hell. This has underappreciated implications for health care interoperability incidentally.

The massive security hole in Google two factor authentication

I've been using Google's two factor authentication for a few months. It works reasonably well for the core Google App suite (gmail, calendar, contacts, etc) from a web UI if I use Chrome.

Even there, however, there are bugs. Even on machines I don't authorize for '30 day use' I sometimes connect without a request for an authenticator token. I think this is improving, but there's still no way to de-authenticate a '30 day' machine from the Google Account.

Beyond the core services though, there are lots of problems. The worst of these is Google's "Application Specific" password framework. It's the software equivalent of medical malpractice.

The problems start with the misleading name. There is nothing Application Specific about these passwords. If you write one down, or if one is captured by a keystroke logger, it works with most (all?) Google services. The same password can be used with an IMAP client to download email or with Google Chrome to sync passwords. If you know one has been lost it can be revoked, but of course by then it's too late.

The only sense in which these "additional passwords" are "application specific" is that Google has us label them by application. This is worse than worthless, it's misleading.

I find I have to use these "additional passwords" very frequently. Today, when I tried enabling Google Sync in the very latest Chrome release, I was asked for one. That was on a less-trusted machine, if a keystroke logger were running it would have been lost.

Obviously, I'm disappointed. Actually, I'm kind of appalled. This smells like a marketing maneuver. Somewhere in Google there are security people contemplating honorable seppuku.

1Password fails to sync - they have problems

Synchronization is Hell.

I know that, so I wasn't surprised when 1Password became increasingly messy. It had multiple logins with different passwords and I don't think it was synchronizing correctly from desktop to iPhone. I decided to start over.

Fortunately I've never trusted 1Password, so the "source of truth" for my passwords is a 15yo FileMaker Pro database with about 1,600 records. It's not only a credential story, it's a history of the WWW (as we used to call it.) So I deleted everything in 1Password desktop and did a sync to my iPhone. There's no UI indicator that sync is happening, but shortly thereafter there were about 3,200 records on my iPhone and 1,600 on my desktop.

Right.

So after some messing about I hit the "reset" button in 1Password/iPhone and tried to sync with the OS X app overwriting the iPhone.

Nothing happened. There was no error message, but clicking on the sync button didn't do anything.

Google eventually took me a to a very long 1Password tech support thread. It's sad reading -- clearly 1Password is in trouble. Towards the end I saw something promising ....

How to Sync 1Password on Mac to my iPad - AgileBits Forums: "defaults write ws.agile.1Password ShowWiFiSyncAuthAutomatically -bool YES"

Terminal hacks usually work. This one did. When next I started the desktop app I got the dialog for entering my 'secret codes' and I was able to sync. Everything is clean for the moment.

Obviously, 1Password doesn't meet my tests. My guess is that their too ambitious for their technical abilities. They've tried to make synchronization automatic and invisible, but they failed to provide a manual option for when things go wrong. Sync is Hell, things will go wrong.

What I really want is the old FileMaker Mobile app for the Palm. There's nothing like it now; the closest today is Bento's ability to sync desktop and iOS device. I'd go for Bento, but there's no iOS encryption option. So it doesn't work.

For now, 1Password is my least bad option. I can't recommend it for anyone else however.

Google Apps: the long list of Google products

Eight months ago I suggested waiting before moving Google Apps users onto Google's integrated identity platform.

Tonight, a few months after successfully migrating one of my Google Apps domains, I tried our family domain. Google's introduction is pretty confident ...

You've probably heard that Google is about to make more of its services available to organizations with Google Apps accounts. Well, we're ready!..

Sounds good. So I went for it and got ...

Google Apps - Server error...

... We are unable to process your request at this time, please try again later.

Right. Maybe not quite so ready.

Oh well, quality is so 20th century. Glad Google doesn't get hung up about things not working. Maybe that's why the list of Google services can get to be so very long ...

• 3D Warehouse
• Android Market Publisher Site
• Blogger
• Chrome Web Store
• DART for Publishers
• DoubleClick Customer Resource Center
• DoubleClick DART Enterprise
• DoubleClick for Advertisers
• DoubleClick for Publishers
• DoubleClick Search
• DoubleClick Studio
• FeedBurner
• Fusion Tables
• Google Ad Planner
• Google AdSense
• Google AdSense for TV
• Google Advertising Professionals
• Google AdWords
• Google Affiliate Network
• Google Analytics
• Google App Engine
• Google Base
• Google Bookmarks
• Google Books
• Google Checkout
• Google Chrome Sync
• Google Code
• Google Custom Search
• Google Desktop
• Google Finance
• Google Friend Connect
• Google Groups
• Google Hotpot
• Google in Your Language
• Google Latitude
• Google Map Maker
• Google Maps
• Google Moderator
• Google News
• Google Places
• Google Public Data
• Google Reader
• Google Sidewiki
• Google Squared
• Google Subscribed Links
• Google Translator Toolkit
• Google Voice
• Google Webmaster Tools
• iGoogle
• Knol
• Merchant Center
• New Service
• orkut
• Panoramio
• Partner Program
• Picasa Web Albums
• Shopping List
• User Managed Storage
• Web History
• Website Optimizer
• YouTube
• YouTube CMS
• YouTube Partner Syndication
• YouTube Promoted Videos

I like "New Service" myself.

I'll try again tomorrow.

Tagging in OS X - OpenMeta

I'm a metadata kind of guy, so I did a quick review of OS X OpenMeta. Links are below.

OpenMeta

• OpenMeta = Google Project Hosting
• OpenMetaApplications - Google Project Hosting

OpenMeta applications

• MailTags
• Punakea | nudge:nudge
• Ironic Software
• Fresh
• Tagit - Simple tagging and rating for OS X
• Tags - Simply Organized
• Punakea | nudge:nudge
• Yep 2

Alas, Apple hasn't played along, and Michael Tsai teaches us that OpenMeta Is a Hack. A clever hack, but too risky for me.

Email applications that let you edit received emails - esp subject lines

Outlook lets me edit subject lines (undocumented, there for 10 years, just click on subject line and type. Yes, there's no UI indication that it works. Yes, it works.) It even lets me edit sent or received email bodies.

These are killer features. Gmail doesn't do this, despite my many requests.

I didn't realize Eudora, a program I used heavily throughout the 90s, did the same thing ...

Macintouch - Applications

MailTags -Eudora lets you edit received emails, which I found handy for making notes and changing the subject line. I really missed this in Mail. MailTags doesn't let you edit the email, but you can add a note and change the subject line, and more. The interface is simple, there when you want it and out of the way when you don't.

So, what's this MailTags?! It sounds interesting, but I don't see how it changes the original subject line. I do see Google's partial IMAP implementation doesn't support MailTags metadata.

Review: Seattle Sports Dry Doc Waterproof Digi Case for my iPhone

Jen Wieczner told a story of a friend lost, perhaps but for the sake of a waterproof phone case ...

When Technology Can't Save You - Jen Wieczner - Technology - The Atlantic

... four others, including my friend Tyler Lorenzi, 23, treaded water while the river swept them downstream. Around the same time their fellow sailors were pleading at the door of a strange residence, a tugboat found their overturned vessel and called authorities. Near a ship graveyard known as the Ghost Fleet, Tyler was eventually pulled unconscious from the James; he passed away in the hospital. Another sailor, Alexander Brown, 24, drowned. Tyler, a graduate of Northwestern University, worked as a research engineer for the National Institute of Aerospace, a division of NASA; Alex was earning a doctorate in engineering there.

I didn't know Alex, but Tyler was generous, selfless and warm, and gave hugs without hesitation ... He was dashingly handsome, strong in the way of someone who got that way by going about his regular business, with perpetually tan skin and flushed cheeks, the kind that mark someone who is comfortable outdoors and spends much of his time there...

... According to the Virginia Marine Resources Commission, which investigated the accident, none of the boaters were carrying cell phones on the fatal night, or at least none that still worked. After Tyler's death, I wondered about what went through the boaters' minds -- tech-savvy young people who worked and studied at NASA programs -- when they fell into the water: Did they immediately realize the gravity of the situation? Dependent on their technology on land, did they reach automatically for their phone before reality settled in? What must it have been like to realize that their means of communication - and hopes of rescue -- were quite literally dead in the water?

... Motorola calls its Defy SmartPhone "life-proof," because it's water- and dust-resistant; its new Brute flip phone is designed to meet military standards for "extreme elements," including "blowing rain," "salt fog" and liquid immersion. RIM, which manufactures Blackberry devices, says, "While it is possible that BlackBerry could work after being submerged in water, RIM does not recommend doing it," and adds that in a recent Yahoo News water test, BlackBerry did just fine.

Needless to say, iPhones are not water resistant. They are notoriously water sensitive [1].

You can, however, buy pouches for $10-$25 that will keep the phone working underwater. You can even use the phone in the pouch.

I tested a cheap one at home. Here's my Amazon review ...

Amazon.com: John Faughnan "John G Faughnan"'s review of Seattle Sports Dry Doc Waterproof Digi 02 ...: ""

I purchased and tested the Seattle Sports Dry Doc Digi Case. The Digi Case 02 appears to be the same case with different attachment device.

I filled the case with a paper towel and then submerged it for 30 minutes in 1 foot of water. The end of the paper towel was slightly moist when I recovered. Most phones, even an iPhone, would probably still be ok at that point.

It looks to me like a new Digi Case, carefully sealed, would protect against a quick dunk. This one is fine for keeping my phone in a non-waterproof bike bag or for insurance on a hiking trip that could involve a stream crossing. I would want something more robust (and more costly) if I were going on a canoe trip.

An iPhone is useable through the case -- the touch controls work.

For an iPhone 4 to fit best, it's probably worth removing any other case. Larger pouches work with a case on the phone.

[1] We inherited a friend's iPhone 1 after it visited a white porcelain bowl. It worked well as an iTouch for my son, save for the lack of a speaker. One day, after about 1-2 years of use, the external speaker started working. I do not understand this.