SQL Anywhere 16 de and mobilink issue

[Former Member]

We are piloting a mobile application using SA 16 de and using SQL Server as the consolidated db.  With Mobilink we have a dozen or so tables downloading to the remote only some of which use custom download subsets.  Several complex custom download subsets are working just fine, however, a few table seem to download the correct count of rows, but only apply about a third of the data to the remote DB.  Interestingly when I look at the remote data itself, we have values in columns that don't actually exist in the consolidated DB.

Has anyone see issue with SA 16 DE 16.0.0.1948?  We'd like to move forward with acquiring SA 16, but this basic issue is stopping us.

Thanks!

[former_member329524 · Accepted solution]

Did you look at the DB log if the remote DB after the download?

This can tell you how these values appear in the DB.

You should translate the remote log including the trigger transactions and have them marked as comments.

It is highly likely that triggers on these or other tables alter these values. If this is a "before" trigger on the same table, then you will see the insert/update as if it arrived directly from Mobilink. In this case, you should check triggers on the same updated table.

If you see actual update commands with these values, that means the triggers are "after" triggers and you can see from the DB log on which tables they operate.

[Former Member · Accepted solution]

Hi Arcady, What's the best way to view the db log on the remote?

I did turn on the logging at when starting mlsrv16 and for dbmlsync when syncing and attached some snippets showing the result of the download and what came across to the remote.  The server logs show 670 rows downloaded for the problem table and the client show 670 rows inserted, however, after the sync, when I check the table in question I see only 240 rows.  Also this was the first sync and the remote was empty.

I've attached the log snippets to better illustrate.  Also, I don't see any triggers on the remote table in question or any of the other remote table triggers that might impact the data.  I actually created a entirely new consolidated DB and mobilink project with just 3 tables to reproduce the problem which I'm seeing in the larger db.  I can reproduce the problem without issue.

Thanks for your suggestions and response.

Regards, Nimesh

LogSnipets.txt.zip

[former_member329524 · Accepted solution]

Hi, Nimesh

You misunderstood me.

This what you should do.

1. Backup the remote DB and truncate the DB log.

2. Reproduce the problem.

3. Backup the log of the remote DB.

4. Translate the log (make sure to include trigger transactions as comments only) is checked.

5. Look for the sql commands which set the unexpected values you talk about. See where they come from.

[Former Member · Accepted solution]

Hi Arcady,

I did you what suggested and I do see unexpected update statements, but not from a trigger that I can tell.  They do explain why some of the values are different that expected, but I'm not sure exactly why dbmlsync would be doing an update in the first place and why it chose the specific values for update.  Another interesting item I see is an insert statement (the last one) where a value for a column is inserted but not actually in the consolidated DB at all.  I ran dbtran with -t and -it for the actual table I'm seeing an issue with.

The server does have nchar columns on with the remote using nvarchar, so I did use -b for dbint on the client.

I attached some of the translated log if you see any thing obvious.

Again, thanks for your help!

Regards, Nimesh

dbtransSnipets.txt.zip

[former_member329524 · Accepted solution]

Launch the dbmlsync with -v+ option, so all the values would be visible.

BTW, is it always the same field, which is updated to unexpected value?

Could it be that the data types of the corresponding field in the consolidated DB is different?

Important (have just noticed it in your answer): You should not have done dbtran using -it option.

The transactions use see in output could have arrived from different tables.

Please translate the log again and this time use -t and -z, and not -it.

[former_member329524 · Accepted solution]

Important (have just noticed it in your answer): You should not have done dbtran using -it option.

The transactions use see in output could have arrived from different tables.

Please translate the log again and this time use -t and -z, and not -it.

[Former Member · Accepted solution]

Hi Arcady, I traced down the issue to the float data type on the SQL Server consolidate DB, in the sense that if I change the float on the consolidated and remote DB to a type numeric (in our use case a float is required, but is just how the original source DB was structured), the issue goes away entirely. 

On consolidated SQL Server DB the original column was a float which SQL Server defaulted to float(53) and on the remote SQL Anywhere the column generated as float(53) when the schema was created from the consolidate DB.  I'm not entirely sure why the sync operation is struggling with this, but it sure seems to.

Any suggestions?

Thanks, Nimesh 

[former_member329524 · Accepted solution]

Sorry, I am not an expert on MS SQL

However, just from the look at it, why the strange float(53) data type? It looks like the column is part of a primary key, so why not just use BIGINT?