[Android Software/Hacking General [Developers Only]] daoist:
Crossposted from my blog since more people read xda-devs :)

http://bellybuttonporn.com/wp/2011/05/19/titanium-backup-and-cyanogenmod7-got-a-problem/

I’ve run into this and from my searching it seems a few others have too.

Here’s the scenario: You’re running CyanogenMod 7 on your phone and you decide to try out a different ROM. You use Titanium Backup to save all your apps and data only to find that once you’re on the new ROM a lot of data doesn’t seem to stick around. Titanium reports that it’s successfully restored the data — and some of it really has been restored — but lots of data is missing. It’s restored to the default value from when you first installed the app.

Now here’s what’s really going on.

Titanium is indeed restoring your data. It just makes sure the app isn’t running and copies all of the database and prefs setting files into its data directory. No magic. That’s all it does and that’s all it needs to do. When your app starts up (if it’s coded well) it will check the integrity of its database files. If they are corrupt it will probably delete them and start over from scratch. Not exactly optimal, but at least the app can still run, right? Well that’s what’s happening here. Your app thinks its database file is corrupt.

CyanogenMod 7 uses a newer version of sqlite than other ROMs. (sqlite is the database engine underpinning android’s apps). This version introduces a new journaling method, the write-ahead-log, abbreviated wal for short. There’s a variety of technical benefits to using wal but that discussion doesn’t really impact our issue. Our issue is that the older version of sqlite on other ROMs doesn’t understand wal, so thinks your databases are corrupt. Bam, bye-bye data!

You can attempt this fix in one of two places. You can either fix the database files before you back them up, or after you restore them. In terms of having the right version of sqlite3 available, it’s probably best to try this before you back up your apps.

This fix assumes a few things. First, you have root. If you’re messing with CM7 and Titanium Backup you already have root. Not a problem. Second, you have busybox installed. Again, not a problem since Titanium requires busybox (and even installs it for you). Third, you have to have a sqlite3 binary somewhere on your phone. If your ROM doesn’t (some don’t for some reason) include it, you’re not screwed. I *think* Titanium will detect this and copy a binary to your phone when you click the “Problems?” button. If that’s not the case, there may be other copies of sqlite3 on your phone you can use. Open up a terminal, buddy, you’re goin in.

Find an sqlite3 binary:

find / -name sqlite3

This will locate sqlite3 for you, if it’s on your phone. Once you find it, (mine was in /system/xbin/, i.e. preinstalled in the ROM), make sure it’s new enough to understand wal.

/path/to/sqlite3 --version

If you get something 3.7.0 or higher, you’re in luck. If not, hopefully you found other sqlite3′s you can try. This is why it’s a good idea to do this BEFORE backing your apps up and flashing a new ROM. If your ROM has wal, its copy of sqlite3 does too.

I hesitate to call this a script, so here’s the command to run:

find /data/data/ -regex '.*databases/[^-]*' -exec /path/to/sqlite3 {} 'PRAGMA journal_mode=delete;' \;

Again, run this in a terminal. And yes, you do need to be root. What this is doing is going through all of the database files stored by your apps and setting them to the old journal_mode. You will get errors that certain files ending in -wal or -shm don’t exist. Don’t worry about those. When sqlite3 changes the journal_mode back to delete it will get rid of the -wal and -shm files automatically; that confuses find but doesn’t hurt anything. If an app is running during the process we won’t be able to open and update its database files. So make sure to kill them all from the Applications menu (or your task killer if you still have one of those around).

IMHO the best time to do this is on your old ROM (e.g. CM7) before doing the Titanium Backup. If you’ve already flashed to the new ROM and you’re stuck, then you can try running the command immediately after restoring everything in Titanium Backup. If you mess something up try not to panic; Titanium still has your data safely backed up. You can try again.

Leave a comment to tell everyone if this worked for you.