SQL Anywhere Mobile - unique constraint errors on upload

Author

unique constraint errors on upload

Brent Williams

2005-06-01, 8:24 pm

I'm running version 9.0.2.3044.

I have a unique constraint on a table but I get the following during upload:

Error: [-10002] ODBC: [Sybase][ODBC Driver][Adaptive Server
Anywhere]Index 'RxPreferences UNIQUE (IndividualID,Catego
ry,OrderNum)'
for table 'RxPreferences' would not be unique (ODBC State = 23000,
Native error code = -196)
Warning: [10039] Error detected while using multi-row cursor -- retrying
with single row cursor
.....

I think the data is fine so how do I tell it not to check the unique
constraint until all the rows have been uploaded?

Breck Carter [TeamSybase]

2005-06-02, 7:24 am

The upload stream is constructed using the final version of each
updated row. For example, if you run a series of three UPDATE
statements to swap unique values in two rows on the remote database,
the upload will only contain two UPDATE entries, and the first one
will cause an error event though the second UPDATE will fix the
problem.

There is no "wait for commit" feature for indexes or unique
constraints. You can use a handle_error script to skip the first row
but I'm not sure how you would repair the error.

You might try uploading into a temporary table and handling the issue
in the end_upload script.

A brute force solution is to run syncs more often, for each row
change.

Breck

On 1 Jun 2005 17:24:07 -0700, Brent Williams
< brentwilliams@bcbsal
.org> wrote:

>I'm running version 9.0.2.3044.
>
>I have a unique constraint on a table but I get the following during upload:
>
>Error: [-10002] ODBC: [Sybase][ODBC Driver][Adaptive Server
>Anywhere]Index 'RxPreferences UNIQUE (IndividualID,Catego
ry,OrderNum)'
>for table 'RxPreferences' would not be unique (ODBC State = 23000,
>Native error code = -196)
>Warning: [10039] Error detected while using multi-row cursor -- retrying
>with single row cursor
>....
>
>I think the data is fine so how do I tell it not to check the unique
>constraint until all the rows have been uploaded?

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Graham Hurst

2005-06-02, 8:25 pm

For ASA remotes in version 9.0.1 and up you can use the -tu dbmlsync
switch to upload each transaction separately. Expect a big performance
drop though.

Cheers,

Graham

Breck Carter [TeamSybase]

2005-06-03, 7:24 am

A question about -tu: what happens on the next synchronization if the
current upload process commits a few transactions in dbmlsrv9 and then
collapses in a heap? Does dbmlsync know what worked and what didn't,
or does it re-upload everything as it does without -tu?

Breck

On 2 Jun 2005 12:14:23 -0700, Graham Hurst
< spam_guard_hurst@ian
ywhere.com> wrote:

>For ASA remotes in version 9.0.1 and up you can use the -tu dbmlsync
>switch to upload each transaction separately. Expect a big performance
>drop though.
>
>Cheers,
>
>Graham

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Graham Hurst

2005-06-08, 9:24 am

Breck Carter [TeamSybase] wrote:
> A question about -tu: what happens on the next synchronization if the
> current upload process commits a few transactions in dbmlsrv9 and then
> collapses in a heap? Does dbmlsync know what worked and what didn't,
> or does it re-upload everything as it does without -tu?

Dbmlsync doesn't know until the next time it syncs. Usually it will not
resend, but there are some special cases where it will. Here is the full
story from a dbmlsync developer:

If a transactional upload is interrupted, any transactions completely
received by the MobiLink Server will be applied to the consolidated
database.

In most cases, on the next synchronization attempt dbmlsync will
automatically perform an extra exchange with the MobiLink Server to
determine which transactions were successfully applied. It will then
begin sending the transactions from that point so the transactions that
were previously applied will not be resent.

There are a few cases where the extra exchange described above does not
occur. In these cases, dbmlsync will reupload the received
transactions. When it receives the upload, the MobiLink server will
identify this as a progress offset mismatch and will request the
dbmlsync send a new upload (built by scanning from a different log
offset) that does not contain the transactions that have already been
applied. The most common case where this occurs is when the upload
failure occurs during the first synchronization of a subscription.

Cheers,

Graham

Sponsored Links