How to prevent duplicate submissions (a.k.a Expected exactly one match in phantom reconstruction! error)

Development
1

Hi there,

Using ODK 1.4.4, but this has happened even with older ODK-s.

IllegalStateException: Expected exactly one match in phantom
reconstruction! SELECT * FROM PROJECT_NAME_CORE5 WHERE _TOP_LEVEL_AURI =
uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f AND _PARENT_AURI =
uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f => expected 1 row but found 2

I correct this by removing a submission (or by changing the uuid of one of
them) in all affected tables, so the Aggregate query will retrieve only a
single row. This solution is a bit ugly since requires database
intervention.

Any idea why this issue might happen, and how to prevent it?

Best,
piqo

2

ODK 1.4.4 and 1.4.5 are better at reporting potential data corruption
issues than the earlier ODK Aggregate releases.

This error does require manual database intervention.

It should not occur frequently -- it should be a rare problem.

This is caused when a submission is not completely recorded in the
database. This only occurs on Google AppEngine and is caused by
Google AppEngine aggressively terminating the data-submission process if it
runs longer than 60 seconds. When the data-submission process is
terminated, there is no mechanism to revert and unwind any changes that are
in process, leaving the database in a corrupted state.

To avoid termination, ensure that you only submit data when you have a
good, high-bandwidth data connection to your ODK Aggregate server. The
more media attachments you expect to submit, the higher the required
bandwidth. E.g., portable satellite data connections are probably only
suitable for text-only submissions.

The 60-second timer starts when the first byte of data reaches the server.
bit the server cannot begin doing the necessary processing until the entire
submission is received. If it takes many seconds to send the media
attachments to the server, that delay reduces the time available to
construct and store the data in the database.

Forms without media attachments are much smaller and transmit much quicker,
and can often be reliably submitted on much slower data connections (e.g.,
via satellite).

By paying attention to the quality of the data connection, you should be
able to avoid this error.

With ODK Aggregate 1.4.5, there is a flag on the Site Admin / Preferences
page to skip over these bad records when publishing data to external
applications or pulling the data via ODK Briefcase. Note that checking this
checkbox can cause these data records to be ignored even after they are
manually corrected in the database.

··· ------- Mitch

On Wed, Jan 28, 2015 at 1:25 AM, piqo piqoni@gmail.com wrote:

Hi there,

Using ODK 1.4.4, but this has happened even with older ODK-s.

IllegalStateException: Expected exactly one match in phantom
reconstruction! SELECT * FROM PROJECT_NAME_CORE5 WHERE _TOP_LEVEL_AURI =
uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f AND _PARENT_AURI =
uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f => expected 1 row but found 2

I correct this by removing a submission (or by changing the uuid of one of
them) in all affected tables, so the Aggregate query will retrieve only a
single row. This solution is a bit ugly since requires database
intervention.

Any idea why this issue might happen, and how to prevent it?

Best,
piqo

--
You received this message because you are subscribed to the Google Groups
"ODK Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com

3

Dear All,

Publishing the the form to Google spreadsheet saved my life on that issue. Thanks Mitch for the explanations.

Best

4

Thank you Mitch,

I would like to add that the issue doesn't happen only with the App Engine,
since we use our own machines.

Perhaps Aggregate shouldn't touch the database unless it receives the full
submission. Anyways, I don't know ODK internals.

What you said is true, this happens rarely.

Best,
piqo

··· On Thursday, January 29, 2015 at 9:51:26 PM UTC+1, Mitch wrote: > > ODK 1.4.4 and 1.4.5 are better at reporting potential data corruption > issues than the earlier ODK Aggregate releases. > > This error does require manual database intervention. > > It should not occur frequently -- it should be a rare problem. > > This is caused when a submission is not completely recorded in the > database. This only occurs on Google AppEngine and is caused by > Google AppEngine aggressively terminating the data-submission process if > it runs longer than 60 seconds. When the data-submission process is > terminated, there is no mechanism to revert and unwind any changes that are > in process, leaving the database in a corrupted state. > > To avoid termination, ensure that you only submit data when you have a > good, high-bandwidth data connection to your ODK Aggregate server. The > more media attachments you expect to submit, the higher the required > bandwidth. E.g., portable satellite data connections are probably only > suitable for text-only submissions. > > The 60-second timer starts when the first byte of data reaches the server. > bit the server cannot begin doing the necessary processing until the entire > submission is received. If it takes many seconds to send the media > attachments to the server, that delay reduces the time available to > construct and store the data in the database. > > Forms without media attachments are much smaller and transmit much > quicker, and can often be reliably submitted on much slower data > connections (e.g., via satellite). > > By paying attention to the quality of the data connection, you should be > able to avoid this error. > > With ODK Aggregate 1.4.5, there is a flag on the Site Admin / Preferences > page to skip over these bad records when publishing data to external > applications or pulling the data via ODK Briefcase. Note that checking this > checkbox can cause these data records to be ignored even after they are > manually corrected in the database. > > ------- > Mitch > > > > > > > > On Wed, Jan 28, 2015 at 1:25 AM, piqo <piq...@gmail.com > wrote: > >> Hi there, >> >> Using ODK 1.4.4, but this has happened even with older ODK-s. >> >> *IllegalStateException: Expected exactly one match in phantom >> reconstruction! SELECT * FROM PROJECT_NAME_CORE5 WHERE _TOP_LEVEL_AURI = >> uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f AND _PARENT_AURI = >> uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f => expected 1 row but found 2* >> >> I correct this by removing a submission (or by changing the uuid of one >> of them) in all affected tables, so the Aggregate query will retrieve only >> a single row. This solution is a bit ugly since requires database >> intervention. >> >> Any idea why this issue might happen, and how to prevent it? >> >> >> Best, >> piqo >> >> -- >> You received this message because you are subscribed to the Google Groups >> "ODK Developers" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to opendatakit-developers+unsubscribe@googlegroups.com >> . >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Mitch Sundt > Software Engineer > University of Washington > mitche...@gmail.com >
5

Good to know.

The only way I can think that this would occur on Tomcat is if you are
shutting down or restarting the server at the exact moment a submission is
being processed. If you do shutdown or restart a lot, and you have a high
submission rate, this is more likely to happen.

You can eliminate this possibility by going to the Form Management / Forms
List sub-tab and unchecking the "Accept Submissions" checkboxes before
shutting down the server, then re-checking them once the server comes back
up.

You also might be able to eliminate this without doing the uncheck /
re-check by changing the executorTerminationTimeoutMillis value in the HTTP
Connector to a larger value. This would make shutdowns slower, but would
ensure that enough time is given to write everything to the database (the
default value does not do that).

Another cause might be an OutOfMemoryException. If you can correlate the
failures with that, then it would indicate that you should configure your
Tomcat with a larger JVM.

Mitch

··· On Thu, Jan 29, 2015 at 1:01 PM, piqo wrote:

Thank you Mitch,

I would like to add that the issue doesn't happen only with the App
Engine, since we use our own machines.

Perhaps Aggregate shouldn't touch the database unless it receives the full
submission. Anyways, I don't know ODK internals.

What you said is true, this happens rarely.

Best,
piqo

On Thursday, January 29, 2015 at 9:51:26 PM UTC+1, Mitch wrote:

ODK 1.4.4 and 1.4.5 are better at reporting potential data corruption
issues than the earlier ODK Aggregate releases.

This error does require manual database intervention.

It should not occur frequently -- it should be a rare problem.

This is caused when a submission is not completely recorded in the
database. This only occurs on Google AppEngine and is caused by
Google AppEngine aggressively terminating the data-submission process if
it runs longer than 60 seconds. When the data-submission process is
terminated, there is no mechanism to revert and unwind any changes that are
in process, leaving the database in a corrupted state.

To avoid termination, ensure that you only submit data when you have a
good, high-bandwidth data connection to your ODK Aggregate server. The
more media attachments you expect to submit, the higher the required
bandwidth. E.g., portable satellite data connections are probably only
suitable for text-only submissions.

The 60-second timer starts when the first byte of data reaches the
server. bit the server cannot begin doing the necessary processing until
the entire submission is received. If it takes many seconds to send the
media attachments to the server, that delay reduces the time available to
construct and store the data in the database.

Forms without media attachments are much smaller and transmit much
quicker, and can often be reliably submitted on much slower data
connections (e.g., via satellite).

By paying attention to the quality of the data connection, you should be
able to avoid this error.

With ODK Aggregate 1.4.5, there is a flag on the Site Admin / Preferences
page to skip over these bad records when publishing data to external
applications or pulling the data via ODK Briefcase. Note that checking this
checkbox can cause these data records to be ignored even after they are
manually corrected in the database.

Mitch

On Wed, Jan 28, 2015 at 1:25 AM, piqo piq...@gmail.com wrote:

Hi there,

Using ODK 1.4.4, but this has happened even with older ODK-s.

IllegalStateException: Expected exactly one match in phantom
reconstruction! SELECT * FROM PROJECT_NAME_CORE5 WHERE _TOP_LEVEL_AURI =
uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f AND _PARENT_AURI =
uuid:75cfb440-ed8e-4b92-acdb-8a7f0c80665f => expected 1 row but found 2

I correct this by removing a submission (or by changing the uuid of one
of them) in all affected tables, so the Aggregate query will retrieve only
a single row. This solution is a bit ugly since requires database
intervention.

Any idea why this issue might happen, and how to prevent it?

Best,
piqo

--
You received this message because you are subscribed to the Google
Groups "ODK Developers" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to opendatakit-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitche...@gmail.com

--
You received this message because you are subscribed to the Google Groups
"ODK Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com