I was able to fix this issue and I wanted to detail what I did in this particular case so others can benefit. Much of the credit for this goes to @Mitch_S for doing a great job documenting the underlying problem and the solution.
None of the steps below are hard, but you really need to be systematic and detail-oriented. If you can't be systematic and detail-oriented, it's probably best to find someone technical you trust to make these changes.
Before attempting repairs
- Take a moment to understand that playing around with App Engine's data store is very dangerous. You can lose all your data and corrupt your install. Let the people who own the data know this.
- Get admin access to the server on Google App Engine. The person who installed the server on App Engine will have administrative access.
- Make sure the server is on unlimited billing plan. This is a good idea because you didn't want to run out of quota while making changes to the database.
- Make sure no one is sending data to the server while you are making changes to the database.
- Read through (but do not act until reading this whole post) the excellent instructions on repairing the form definition table that Mitch put together.
How I solved this particular problem
-
I looked at the server's logs on App Engine (Stackdriver -> Logging) and saw lots of errors about a form listing. This confirmed that this was likely due to a form issue.
-
Imran had noted that he had uploaded a form twice and had deleted a duplicate form from the database. The exact description he sent me was..
select * from opendatakit._form_info_submission_association where SUBMISSION_FORM_ID = “chart_review”. This query returned two rows. I deleted those rows by selecting the check boxes and deleted them. Those forms were empty.
This confirmed that the root of the problem was probably a double form upload. Imran was correct in deleting the two entries from
_form_info_submission_association, but he also needed to delete the various references to the form in the_form-*tables. -
Once I confirmed from Imran that there were no submissions/data for both forms, I proceeded with the instructions in repairing the form definition table. I deviated from the above instructions in a few ways
- I backed up all the
_form_*tables first. To do that, you'll need to create a bucket for that data (Storage -> Browser), then initiate the back up (Datastore -> Admin). - I walked through the steps without deleting anything and made sure all the various UUIDs matched per the instructions.
- For the real deletions, I confirmed the UUIDS visually and also sorted by creation date to make sure I was deleting the correct rows. Creation date is a nice confirmation because duplicate forms happen at basically the same time.
- The forms had some images associated and so I also deleted those in the
_form_manifest_*tables. I confirmed that these entries could be deleted by reading this Aggregate Database Structure document. - Once I confirmed everything worked, then I deleted the backups.
- I backed up all the
How to prevent this problem from happening
- Do regular back ups with ODK Briefcase. The command line interface makes this easier to automate.
- Make sure you are running ODK Aggregate v1.4.11 or later. These sorts of problems are less likely with later versions. Read ODK Aggregate Release Notes for how to upgrade.
- If you have a lot of forms and data, increase the size of the App Engine server to make it more performant.