Can't sync from client to server when the table have a GUID column as PK

Jan 6, 2014 at 10:02 AM
My sync project setup have about twenty tables, during testing, some of them can be sync from client to server, some can't. but there is no issue from server to client. In the end, I found all the tables with GUID data type as the PK can't sync from client to server.
Is this a bug? or there is something I have not done correctly?
Coordinator
Jan 6, 2014 at 11:06 AM
Hi Sunny

Strange Behavior.

Can you send me a copy of your database (at least the schema)
I will make some test.

In my point of view, GUID shouldn't be responsable, but, hey it may be a bug :)

Sebastien
Jan 6, 2014 at 7:37 PM
Hi,

here is the sample database schema.

For example, the hospital table won't be able to sync from client to server, but codeCountry could.
CREATE TABLE [dbo].[Hospital](
[Id] [uniqueidentifier] NOT NULL,
[Name] [varchar](200) NOT NULL,
CONSTRAINT [PK_Hospital] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)

CREATE TABLE [dbo].[CodeCountry](
[ItemCode] [varchar](36) NOT NULL,
[ItemDesc] [varchar](255) NULL,
[Deleted] [bit] NULL,
CONSTRAINT [PK_CodeCountry] PRIMARY KEY CLUSTERED
(
[ItemCode] ASC
)
)
Thanks

Coordinator
Jan 7, 2014 at 8:22 AM
Hi Sunny, I have made the test with your schema and it works fine for me ..
That's pretty strange.

Here is my solution zip, based on your database Schema. Can you make a comparaison between your solution and my solution ?

sunnysolution.zip

By the way, I have only tested on Windows Store application, do you have this problem on W8 app or Windows Phone 8 app ??

Sebastien
Jan 7, 2014 at 10:07 AM
Thanks for the help, Sebastien.
yes, your solution works and I am using winRT App as well.

I tested my original solution again, and found that the sync from client to server will work, if I insert data manually (using SQLite manager etc).
The table "codeLanguage" works because the data is prepopulated, and in all my prepopulated tables, the primary key is not GUID. but all the other tables, I use GUID as PK. That is why I thought GUID column type is the cause of the issue.

the table "Hospital" is empty initially, and the test data is being added to Hospital table from application code when the app started, then I trigger the Sync, and the client side data just added won't be able to sync to server, but the server side data is downloaded(or call uploaded, not sure which way is correct naming). Then I manually insert data into client side Hospital table, and sync again, the newly added data is sync to server side table successfully.
I turned on trace:
1) for the first time sync: (the two rows inserted are from server side to client, but the one row client side data is not sync to server)
INFO , w3wp, 22, 01/08/2014 04:08:22:758, ----- Table "Hospital" -----
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:051, Executing Command: [dbo].[Hospital_selectchanges]
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:051, Parameter: @sync_min_timestamp Value: 0
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:051, Parameter: @sync_scope_local_id Value: 7
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:051, Parameter: @sync_scope_restore_count Value: 0
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:051, Parameter: @sync_update_peer_key Value: 1
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:117, Insert for row with PK: Id = 21ec2020-3aea-1069-a2dd-08002b30309d on touchmeds3
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:118, UV: 0,23827 CV: 0,23824
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:126, Insert for row with PK: Id = 21ec2020-3aea-1069-a2dd-08002b303334 on touchmeds3
VERBOSE, w3wp, 22, 01/08/2014 04:08:23:126, UV: 0,23833 CV: 0,23832
INFO , w3wp, 22, 01/08/2014 04:08:23:129, Inserts: 2
INFO , w3wp, 22, 01/08/2014 04:08:23:129, Deletes: 0
INFO , w3wp, 22, 01/08/2014 04:08:23:129, Updates: 0
INFO , w3wp, 22, 01/08/2014 04:08:23:129, Changes Enumerated: 2
INFO , w3wp, 22, 01/08/2014 04:08:23:129, --- End Table "Hospital" ---

2) second time sync (manually added row sync from client to server, as the highlighted pk)
INFO , w3wp, 20, 01/08/2014 04:16:45:673, ----- Inserts for Table "Hospital" -----
VERBOSE, w3wp, 20, 01/08/2014 04:16:45:938, Executing Command: [dbo].[Hospital_bulkinsert]
VERBOSE, w3wp, 20, 01/08/2014 04:16:45:939, Parameter: @sync_min_timestamp Value: 23841
VERBOSE, w3wp, 20, 01/08/2014 04:16:45:939, Parameter: @sync_scope_local_id Value: 8
VERBOSE, w3wp, 20, 01/08/2014 04:16:45:940, Parameter: @changeTable Value: Hospital
INFO , w3wp, 20, 01/08/2014 04:16:49:115, Applied 1 of 1 rows with bulk command BulkInsertCommand
VERBOSE, w3wp, 20, 01/08/2014 04:16:49:128, Inserted row with PK using bulk apply: Id="4096f9fd-9a03-41ec-a5d7-7f9dfdcb5766" on touchmeds3
VERBOSE, w3wp, 20, 01/08/2014 04:16:49:128,
INFO , w3wp, 20, 01/08/2014 04:16:49:128, 1 Inserts Applied
INFO , w3wp, 20, 01/08/2014 04:16:49:128, --- End Inserts for Table "Hospital" ---



I compared the manually added data row with the data row added from app code, can't find any difference which could cause the sync working/not working.... stuck again..

Any thoughts.


Coordinator
Jan 7, 2014 at 12:50 PM
The prepopulated data in the SQLite table are populated BEFORE the sync process create all the triggers and metadatas ?
(ie : you have called the sync method at least one time)

If it's the case, then you have rows in your database where the metadatas doesn't exist...
Here is a solution you can test :
           // Launch a first Synchronization to get all tables configured and retrieve good scope informations
            // The existing rows in the SQLite database
            // won't been sent because they don't have any metadatas in
            // the tracking tables
            var stats = await ctx.SynchronizeAsync();

            // Update the tracking table to be sure that they will be synced next time
            using (var db = new SQLiteWinRT.Database(ApplicationData.Current.LocalFolder, "Sunny.db"))
            {
                // Open a connection to the SQLite database – creates it if it does not exist
                await db.OpenAsync();

                using (var readstmt = await db.PrepareStatementAsync(
                    "INSERT INTO [Hospital_tracking] Select Id, null, 0 , 1 , null, null, null, DATETIME ( 'now' ) from [Hospital] "))
                {
                    await readstmt.StepAsync();
                }

            }

            // Launch a new sync. This times, all new existing rows from the client will be sent to the server
            stats = await ctx.SynchronizeAsync();
I think I will make a better solution in a next release. Maybe something automatic to hide this complex approch :)

Sébastien
Jan 8, 2014 at 10:53 AM
Finally figure out what was happened :)

the prepopulated data are populated after the sync process create all the triggers, actually the sqlite was created automatically from the sync(very first time).
The tricky part is that the code deleted all the data before prepopulating the data again.
Take the Hospital as example, when the sqlite database was created, it sync a few records from sql server. But these few recrods was deleted and repoulated with a few other recrods, But the Hospital_tracking table still contains the old meta data.
I remove the "delete" part from the code, everything works fine!

Now the question is when deleting data from client sqlite database, should the code explictly delete the meta data as well from "tracking" table? or should the sync framework handle it? the later option seems make more sense. Thoughts?