Database Corruption

[Lorenz Ehrsam]

I am a long-term user of Tap Forms and think it’s the best database for Mac and iOS. However, I had a catastrophic sync incident which seems to have corrupted my database. Among the problems that have occurred:

1) Records disappeared (my Mac has 2,441 records, my iPhone 2,401, and my iPad 2,437
2) The names on 12 records are wiped out (that appears to have happened only to records that had an imported screen shot from a FaceTime interview)
3) The edited photos which should be the main image in detail view, now appear in 2nd place after the unedited photo, and I have to manually drag each photo to the right location.

Since all three devices, Mac, iPad and iPhone are affected I don’t have a perfect master database and I immediately turned off sync. However, I don’t have a backup that’s complete enough.

Here are my questions:
Is there a way to highlight the records that are not showing up on iPhone but are on the Mac so that I could manually transfer?
Is there a way to automatically sort the pictures per contact so that the edited version shows up first?

Thanks in advance for your prompt help.

[Sam Moffatt]

Do you mind expanding on what might have happened with the sync incident? Can you also share how you have your sync setup? Were you using P2P sync? Were all devices added to each other? Was one device offline for an extended period of time?

For the Mac, do you have Time Machine set up and can you restore a copy of your document from a time before you noticed the sync issues to review it’s state?

1. A difference in record count is a relatively good thing, it means you might be able to pull changes out of each of the devices.

2. The attachments might have failed to sync properly and changes on one device have overwritten changes on another device. This is a part of the sync resolution system where the most recent change should win though it seems in this case that something didn’t sync properly.

3. This feels like you’ve not lost image attachments for those records but just find them in the wrong order?

To your questions:

1. *Is there a way to diff missing records?* I don’t know of an easy way to do this, I’ve been working on a set of Terminal tools that might do something like this (and get a more complete backup) however it isn’t user friendly.

2. *Is there a way to sort edited images?* If your image field is only intended to have one image, you might be able to write a script field to count the number of images that are in the field and find the records that need intervention. You might be able to use a script field to examine the photo field and see if there is a pattern to the filenames of the edited images. I’m not sure if you could use record.setFieldValue to re-order the images within the field but that would be the way forward.

On the second one I’d be hesitant to make any changes because if you do when it comes to resolution time, those changes could cause some more damages.

If you are comfortable with a less than polished experience and using the Terminal, I can upload one of the backup tools I have that tries to pull back old revisions of documents from a Tap Forms database. That might be able to find anything that you’re missing.

[Lorenz Ehrsam]

Thanks, Sam for the prompt response. I dropped the master database from the iPhone to the Mac and iPad and had everything sync both ways via Nearby, all of them added to each other with green dots showing. I also did iCloud sync, but that didn’t seem to go well, as my database is very large (25GB) and syncing took days without success.

While all the different devices were running the database it worked well. But it’s true that the Mac database was offline for an extended period. Why should that matter? I do have an old backup from 2018. I also have a backup in zip format on iCloud, but I doubt it’s the whole version as it’s only 6.6GB.

My main concern is to make sure that I have again the full database with all records. But how can I isolate them and compare? The Mac has the most records so I would transfer the iPhone’s missing ones from there I imagine. Is there an easy way to do that?

Regarding the images, yes, they are in the wrong order only, and I changed 200 items by hand, so that issue is now solved. I just hope it will never happen again. What could have been the reason?

Many thanks,

Lorenz

[Sam Moffatt]

For peer to peer sync, I actually suggest a hub and spoke model where you have one device as master and link to each of them.

• Pick one device that is your master and is generally online, then make sure you add each of the devices you want to sync with.
• On each of other devices, only add your master node.

What happens behind the scenes is that the peer to peer sync protocol is pulling the changes from each of the devices. When you have a central hub, it pulls the changes from everyone else and handles the conflict resolution in a single place. When you have everything added to each other, they all can attempt to handle conflict resolution with each other. Most of the time that will work out just fine but some times it won’t. Adding to this is the complication that if one of your devices was offline for a while but had a number of changes made to it, when it comes back online it will trigger reconciliation of any conflicts it has and may result in it overwriting other records. I think Brendan made some changes to make sure the most latest change won but previously it was the branch that had the most changes. If you make changes to documents before everything has finished syncing, then those changes become the most recent changes and then you can lose data that might be on other devices (hence the warning earlier). The reconciliation is perhaps what triggered the re-ordering of the attachment fields.

I personally use CouchDB for everything, once I did that everything became a lot more stable. There are plenty of resources on the forum to help host your own or you can pay for IBM’s hosted solution as well. I self host my own databases on my own network and also have an instance on DigitalOcean. If you want to make sure this doesn’t happen again, I’d invest in setting up a CouchDB server somewhere and leaving it always on to sync against. It’ll be the hub in that model and will help mitigate any sync conflicts.

For the task at hand, I have a set of PHP tools I call “tftools” available on my GitHub: https://github.com/pasamio/tftools. You can use the “Download ZIP” option to get a copy of it. It has a tool to backup all possible revisions available and can help you find documents that might be missing.

The first step is to make sure you have a Tap Forms instance open with the document you are interested in open, since you’re on a Mac perhaps start up the Tap Forms instance on your Mac. You will have to repeat this process for each device to get extract it’s data.

You’re going to need to use Terminal (use Spotlight) and to get started you’re going to have to navigate to the unpacked directory. In the Terminal window that appears type in cd (including the space at the end) and then drag the tftools-master directory across into the Terminal window. It should look something like cd /Users/pasamio/Downloads/tftools-master. Then you hit enter to change the directory. You’re then going to need to type bin/tf-finder.sh and hit enter again.

Your screen should look something like this:

themac:~ pasamio$ cd /Users/pasamio/Downloads/tftools-master 
themac:tftools-master pasamio$ bin/tf-finder.sh

Scanning network, this will take ten seconds...
Processing instance db-f8365d0d8a99446db370477295e705fe@themac...
Processing instance db-babeb605dc89415388967bbe868c68f6@themac...
Processing instance db-5af742cf299940b1b9ad32e7fae7ec41@themac...
Terminated: 15
themac:tftools-master pasamio$ 

Some of that will look different like themac, pasamio and the database instances should match the ID of your document.

The next command you will need to type in is cp backups/config.ini-dist backups/config.ini. This will setup the backup configuration. We need to edit this file, so type open -e backups/config.ini. That should trigger TextEdit to appear, your Terminal window should look like this:

themac:tftools-master pasamio$ cp backups/config.ini-dist backups/config.ini
themac:tftools-master pasamio$ open -e backups/config.ini
themac:tftools-master pasamio$ 

In the TextEdit window that appears, it’ll look like this:

perHost = false
couchHosts[] = http://localhost:5984
instancePath = /tmp/tf-hosts

I need you to change the first line to say perHost = true instead (replacing false for true). This will create a per host backup that you can use to compare later. You can also delete the second line and the resulting file should look like this:

perHost = true
instancePath = /tmp/tf-hosts

Save the file in TextEdit and go back to the Terminal window. Now you’re ready to run the backup. Type in ./backup-allrevs.php and hit enter. The output for this tool is very verbose but it should start scanning your Tap Forms instance to download it’s data:

themac:tftools-master pasamio$ ./backup-allrevs.php 
Loading config.ini file
Settings:
Backup Root: /Users/pasamio/Downloads/tftools-master/backups
Copnfig INI file: /Users/pasamio/Downloads/tftools-master/backups/config.ini
Instance Path: /tmp/tf-hosts
Couch Hosts: 
Per Host: Yes
=====================

Getting documents from https://themac.local.:64700/db-f8365d0d8a99446db370477295e705fe/_all_docs

Your output should be much longer. This may take a while as it is scanning every single record in your Tap Forms document and then trying to find every available revision. It doesn’t export the attachments. Once it has finished, in the backups directory (you can type open backups to open it in Finder from the Terminal) will have a directory with your database ID (e.g. db-5af742cf299940b1b9ad32e7fae7ec41), then the hostname (e.g. themac.local.) and inside it will be a listing of every single record and any revisions that it was able to find. In some cases those files might exist but be empty (e.g. they say {"ok":true}) which means it wasn’t able to retrieve that record.

The next step is to bring online the other devices, re-run bin/tf-finder.sh and then re-run backup-allrevs.php for each of the devices. Make sure that none of them have sync enabled otherwise that might kick off the conflict resolution process. When you do the iOS devices, make sure they don’t go to sleep during the process as that will disrupt the backup process. I suggest disabling autosleep and autolock until the backup completes. If it does go to sleep, re-run the bin/tf-finder.sh command again to refresh the devices name. I noticed in my testing that my iPhone didn’t include a friendly name but a generated hash that changed over time.

Once you’re done that, there should be three folders in the backup directory, one for each of your devices. This is where we get to figuring out which one is missing documents. Run the following command: for DB in $(ls backups | grep '^db-'); do echo $DB; for HOST in $(ls backups/$DB); do echo "Creating /tmp/tfdiff-$DB-$HOST"; ls "backups/$DB/$HOST" > /tmp/tfdiff-$DB-$HOST; done; done

That one you should be able to copy and paste, it’s going to create a list of records per database and host name. It should have an output like the following:

themac:tftools-master pasamio$ for DB in $(ls backups | grep '^db-'); do echo $DB; for HOST in $(ls backups/$DB); do echo "Creating /tmp/tfdiff-$DB-$HOST"; ls "backups/$DB/$HOST" > /tmp/tfdiff-$DB-$HOST; done; done 
db-5af742cf299940b1b9ad32e7fae7ec41
Creating /tmp/tfdiff-db-5af742cf299940b1b9ad32e7fae7ec41-43352ae9-8ec1-4892-92b3-f7550640874b.local.
Creating /tmp/tfdiff-db-5af742cf299940b1b9ad32e7fae7ec41-themac.local.
db-babeb605dc89415388967bbe868c68f6
Creating /tmp/tfdiff-db-babeb605dc89415388967bbe868c68f6-themac.local.
db-f8365d0d8a99446db370477295e705fe
Creating /tmp/tfdiff-db-f8365d0d8a99446db370477295e705fe-themac.local.

The last step in this process is to figure out which records are missing. To do this we can use the Apple File Merge utility. You might need to install XCode to get this, but the following command (replace the path names with your own) will give you a listing of different documents. For example for me, I wrote opendiff /tmp/tfdiff-db-5af742cf299940b1b9ad32e7fae7ec41-43352ae9-8ec1-4892-92b3-f7550640874b.local. /tmp/tfdiff-db-5af742cf299940b1b9ad32e7fae7ec41-themac.local. matching the two instances of the db-5af prefixed document that I’m interested in.

Using File Merge, it will highlight records that are in one file but not the other. You can do this for each of your devices to figure out which ones are missing.

The last part is opening up those documents. In my case rec-19b7a5a73e9f427289c01697b8783912 was missing, so to figure out what that was, I used the Copy Record Link option in Tap Forms to get a path to the record which looks like this:

tapformz://record/view/db-babeb605dc89415388967bbe868c68f6/frm-3527dfd80f754a5fad58939ecdf91487/rec-dab19b0a51294980b6328312b40140ae

You can see at the very end there is a rec-asdf string. What you want to do is replace that with the rec ID that you found missing in File Merge. You can use open tapformz://record/view/db-babeb605dc89415388967bbe868c68f6/frm-3527dfd80f754a5fad58939ecdf91487/rec-19b84f708bdb4d809299aebac3f2178e to open up the record. From here you should use ‘Duplicate record’ to ensure that a new record is created free of any sync conflicts.

This is a little hard to follow and a very technical process, hopefully it helps.

[Lorenz Ehrsam]

Thanks for the comprehensive answer. It seems a bit daunting, to be honest, but I will try and report back. The thought of the sync having accidentally overwritten some of the records is frightening. Many thanks.

[Sam Moffatt]

I agree, it isn’t easy or straightforward to do. This is mostly stuff I built for myself to solve problems or situations I had (e.g. how do I periodically backup records so that I could restore one should I do something bad?) and I haven’t really had the time to turn it into something truly polished.

[Author]

Posts