Column order in Mobilink 16

[former_member329524]

Hello, all

Several of my clients reported a problem that mobilink does not send data correctly from their client DBs to the consolidated DB.

When I examined their DBs I noticed that the problematic table had different column order for some reason. This caused the Mobilink update incorrect fields.

My question - is there a way to tell the Mobilink to use column names instead of numbers? The update script is correct, however, it is useless, because it is updating according to the column ID, instead of the column name.

What can be done about it, short of destroying the table and recreating it?

[Former Member · Accepted solution]

This is being look into still.  Outside of your databases, we were not able to mock this up here so are still trying to determine what specifically is happening when it is your files that are invovled. None of the possible theories discussed seem to be contributing ... but it will be figured out I am sure.

It may help us for you to find out how and when the schemas divereged.  There may be a subtle detail there that could help to cut through a lot of effort and time here.

[former_member329524 · Accepted solution]

Hi, Nick

All you had to do was ask It is, actually, quiet simple.

Here are the exact steps to recreate the problem:

1. Create a consolidated DB (like the cons.db, which I sent). Create the table only, not the mobilink server.

2. Create a remote1.db (like a remote1.db, which I sent). Create the table only, not the publication.

3. Create a remote2.db with the same table, but a changed column order.

4. Install mobilink server on the cons.db. Select the remote1.db to be the remote schema.

5. Deploy the resulting sync model on the cons.db. Create a publication and the sync subscription on the remote1.db.

6. Run a schema comparison between the remote1 and remote2 db's. Run the resulting script (minus the column ordering, which would be in comments anyway and the remote id, which has to be null).

Now any row you add to remote1 db will sync correctly, and rows in remote2.db will sync incorrectly.

Regards,

Arcady

[regdomaratzki · Accepted solution]

I'll start by clarifying a few points :

1) Dbmlsync never initially sends it's schema during a synchronization.  It will generate an MD5 hash that represents the schema of the remote database and send that.  If the MD5 hash is not a value recognizes by the MobiLink Server, it will send a message back down to the remote database and ask the remote to send the schema of the remote database, including the column names of all the tables.  When it receives the schema, it then stores the MD5 hash and schema in memory, so that subsequent synchronizations using the same MD5 hash will not need to send their schema.  An MD5 hash is not guaranteed to be unique, but there is only a 1 in 2^128 chance that a collision will occur (that's a number with 39 digits).  I find it highly unlikely that we are running into an MD5 hash collision.

2) If remote databases have a different schema, even in the order of the columns, you should be using a different script version at the consolidated database to synchronize these different schemas.  This is how MobiLink works.  The link that Breck highlighted in the docs shows that, and when you use the Wizard in Sybase Central to create a Synchronization Model, the screen where you create the Remote Schema Name makes it clear that all databases should have the same schema.

3) Like Breck, I experimented with a different column orders at the remote database, but my testing didn't use Sybase Central and the Synchronization Model method, but runs in a DOS prompt and sets up an environment using SQL files.  My testing showed the same results as Breck's testing.  Using named parameters in the upload scripts resulted in the two remote database with different schemas that differ only in column order properly uploading their values into the desired columns in the consolidated database.  However, there is no way to write a download_cursor script that will work for both schemas.  Like Breck, I'll offer to send you the scripts I used that showed uploads working for me (downloads WILL fail!) if you provide your email address.  Mine is firstname.lastname@sap.com (but clearly use my real first and last name).

I'll now address Arcady's last post.

It's still not clear to me how you created the three databases in your environment, but it's probably my unfamiliarity with the MobiLink plug-in in Sybase Central that is issue.  I don't use the tool that often, since I find it easier to work with a set of SQL scripts and batch files that are quicker and more repeatable.  A set of scripts will do exactly the same thing each time, and does not rely on clicking the same options in a Wizard from iteration to iteration.  Could you please do one of the following :

a) Contact me to get a set of scripts you can modify to try and show me the problem you are seeing.  I will send you a reproducible that I believe describes your environment that does not exhibit the behaviour you describe, and you can modify the scripts to show the behaviour you are seeing.

b) Reproduce the issue as you described with a single table using Sybase Central, and then provide the consolidated database, as well as the two remote databases in question that exhibit the behaviour.  Please leave the DBA password unchanged from the default if you do this.  I can provide a secure link you can use to upload the databases if you choose this route.

c) Provide a more detailed set of steps to reproduce the issue using Sybase Central.  Start by defining a brand new Synchronization Model, and describe exactly what you do in each step, on the assumption that the person who is reading your instructions has never used Sybase Central before.

Thanks,

Reg Domaratzki

[regdomaratzki · Accepted solution]

It was the use of Sybase Central and Synchronization Models that was the source of the problem here.

When you create a synchronization model, the MobiLink Plug-in continues to use the ml_add_column stored procedure defined in the ML System tables to explicitly define the order of the columns as they are defined in remote database.  These calls are no longer needed in current software (we always send column names now), and if fact, the ml_add_column stored procedure has been deprecated in version 16.  They calls typically don't cause any problems unless the order of the columns at the remote databases differ from remote to remote.  When the column orders are different, this corrupts data on download, but you were doing upload only synchs, so that wouldn't happen.

You can get around your issue by deleting all the rows in the ml_column MobiLink System table in your consolidated database.    You may need to do this every time you re-deploy

If there is any fix to be made, it is with the MobiLink Plug-in in Sybase Central.  It should likely no longer be using a deprecated stored procedure when it is no longer needed, although yours is the first environment ever where it has been an issue.  I will discuss with the tools teams as to whether a fix is needed, but my guess is that no fix will be made to the plugin, since remote databases using the same synchronization script version should have the same schema.  It is only because you are not downloading data that you are not having more serious issues.

Please keep in mind that if you ever decide you want to download data to the remote databases, this environment you have with different ordering of columns from remote to remote is GUARANTEED to cause data integrity issues. 

Reg Domaratzki

[former_member329524 · Accepted solution]

Thank you, Reg

So, in order to avoid this problem in the future, all I need to do is to remove the ml_add_column commands form the model script?

And, if I remove all records from ml_column table, this would work around this problem? Because, I used to get errors when the ml_column collection for the table did not match the remote publication.

[regdomaratzki · Accepted solution]

> So, in order to avoid this problem in the future, all I need to do is to remove the ml_add_column

> commands form the model script?

> And, if I remove all records from ml_column table, this would work around this problem?

If you want to work around the issue you are seeing without adding another script version (where you can re-define the order of calls to ml_add_column for the tables with different ordering), then removing the rows from the ml_column table (however you choose to do that) should do the trick.  I was able to synchronize both remote databases to the consolidated database you gave to Nick Elson when all the rows were removed from the table, and my tests were also successful with no rows in the ml_column table using a similar schema with columns in a different order.  Both these examples only used a single table with a handful of columns, so I would test this on your full database in a test environment before putting it into production, since this isn't something we test in our automated test streams here.

> I used to get errors when the ml_column collection for the table did not match the remote publication.

I'm not quite sure I understand this comment.  Currently, the ml_column collection for the table does NOT match the remote publication for all remote databases.  It was my understanding that the only issue you were seeing was data being applied to the incorrect columns, but no actual errors were reported.

Reg

[former_member329524 · Accepted solution]

Reg Domaratzki wrote:


> I used to get errors when the ml_column collection for the table did not match the remote publication.

I'm not quite sure I understand this comment.  Currently, the ml_column collection for the table does NOT match the remote publication for all remote databases.  It was my understanding that the only issue you were seeing was data being applied to the incorrect columns, but no actual errors were reported.

Reg

Actually, the column collection itself matches perfectly. It is the order of the columns (apparently, not being checked by the Mobinlink server) that is different.

[regdomaratzki · Accepted solution]

Arcady Abramov wrote:


Reg Domaratzki wrote:


> I used to get errors when the ml_column collection for the table did not match the remote publication.

I'm not quite sure I understand this comment.  Currently, the ml_column collection for the table does NOT match the remote publication for all remote databases.  It was my understanding that the only issue you were seeing was data being applied to the incorrect columns, but no actual errors were reported.

Reg


Actually, the column collection itself matches perfectly. It is the order of the columns (apparently, not being checked by the Mobinlink server) that is different.

OK, we're just disagreeing on the definition of "match". 

If calls are made to the ml_add_column stored procedure and rows are added to the ml_column table, the MobiLink Server will assume that these are the names of the columns for that table, and the order in which they appear, since that is what it has been told.  It will ignore the names of the columns in the uploaded schema if rows exist.  If there are no rows in the ml_column table, then the MobiLink Server will use the schema definition that was uploaded by the remote to determine the order and names of columns in the remote database. 

Reg

[former_member188493 · Accepted solution]

> continues to use the ml_add_column stored procedure

Thanks for clearing that up! (when I saw those calls in a recent SQL Anywhere 16 / ASE 15.7 MobiLink project, it made me doubt my sanity : )

FWIW The appearance of ml_add_column column calls was one of the reasons I dumped the MobiLink wizard and returned to the Old School way. Not the only reason, but it may have been the last straw.

[former_member329524 · Accepted solution]

Thank you Reg

Deleting the rows from the ml_column table solved the problem.

[Former Member]

MobiLink knows the remote schema since that is uploaded by the remotes.  This happens during the first time that schema is synchronized with the MobiLink process instance. As already noted, this is how the MobiLink scripts are able to use column names, using the "{ml r." and "{ml o." MobiLink "row parameters" expansion for column values.

On subsequent syncs (relative to the same MobiLink instance) the remote may or may not upload its schema. It will do so if the signature of the remote schema does not match any of those MobiLink has in it's cache of remote schemas.  It does this because many remote designs have huge schemas and to transmit those on every sync can be very innefficient (especially in situations where it exceed the size of the data needing to be synched).

It may be possible, that in some rare cases two very similar schemas might collide on the same signature.  If that occurs the values may come up the order of the (unknown) schema but mapped positionally to known schema and thus the values may arrive at the consolidated out of position as described above.  I suspect this is what is happening here.

As rare as this scenario should be, I would expect the mis-mapping to often cause some other errors to expose the reaons (column type mismatches being a likely possibility) and not silently but incorrectly succeed. But from the description so far, that may happen since all the value are type compatible the operation incorrectly succeeds; if out of order.

Running with a different script version for the different remote schema is expected to help resolve this.  That may or may not be able to utilize the {ml row-parameterization ... I have not checked into that aspect of this yet myself ... but, if not, positional parameterization should be able to be used to address this if only to dig your way out of this lost data scenario until you can fix the schema divergence.

Hopefully that approach won't be too painful.

[former_member188493]

Please show us an example of an offending upload_update and upload_insert script, and tell us how that script was created (MobilLink 16 Sybase Central wizard, or manual coding).

MobiLink has never used numbers to identify columns. Currently it uses names in MobiLink scripts by default, previously it used positional question marks "?" (and you can still use those but probably should not).

If tables have different column ordering on the consolidated and remote databases, it is a simple matter to code the MobiLink scripts to accomodate that difference. Manual coding is highly recommended. The wizard can be used but the resulting scripts must be checked for correctness because usage of the wizard is error prone even for small schema differences.

The bottom line: MobiLink scripts are entirely the responsibility of the developer.

[former_member329524]

I love the responsibility for as long as it comes together with authority....

Anyway, this is the full log from the mobilink server when a row in the problematic table is updated.

If there is a way to make the server use the column names in the script, instead of their presumed order in the upload, I would really like to know what it is.

//-----------------------------------------------------

I. 2015-06-15 21:59:44. <37> begin_upload_rows t068_packages (no script)

I. 2015-06-15 21:59:44. <37> upload_update t068_packages

                        /* Update the row in the consolidated database. */

                        INSERT INTO "DBA"."t068_packages" ( "c_hotel_id", "c_package_id", "c_promotion_name", "c_description", "c_price_code", "c_plan", "c_start_date", "c_end_date", "c_display_from_date", "c_display_end_date", "c_valid_on_sun", "c_valid_on_mon", "c_valid_on_tue", "c_valid_on_wed", "c_valid_on_thu", "c_valid_on_fri", "c_valid_on_sat", "c_arrive_sun", "c_arrive_mon", "c_arrive_tue", "c_arrive_wed", "c_arrive_thu", "c_arrive_fri", "c_arrive_sat", "c_max_bulk", "c_open_sale", "c_display_order", "c_min_los", "c_max_los", "c_charge_1st_night", "c_old_price_single", "c_old_price_double", "c_single_only", "c_pms_cattype", "c_agent_id", "c_discount_day01", "c_discount_day02", "c_discount_day03", "c_discount_day04", "c_discount_day05", "c_discount_day06", "c_discount_day07", "c_created", "c_last_modified_date", "c_consider_agents_discount", "c_consider_home_site_discount", "c_parent_package", "c_wing", "c_currency", "c_categories", "c_local_currency", "c_price_for_show", "c_old_price_for_show", "c_allow_extra_dates", "c_permit_for_agents", "c_without_prices", "c_old_price_to_show_on_web", "c_deny_for_customer_group", "c_permit_for_homesite", "c_exclude_dates", "c_new_price_to_show_on_web" )

                        on existing update

                        VALUES ( {ml r."c_hotel_id"}, {ml r."c_package_id"}, {ml r."c_promotion_name"}, {ml r."c_description"}, {ml r."c_price_code"}, {ml r."c_plan"}, {ml r."c_start_date"}, {ml r."c_end_date"}, {ml r."c_display_from_date"}, {ml r."c_display_end_date"}, {ml r."c_valid_on_sun"}, {ml r."c_valid_on_mon"}, {ml r."c_valid_on_tue"}, {ml r."c_valid_on_wed"}, {ml r."c_valid_on_thu"}, {ml r."c_valid_on_fri"}, {ml r."c_valid_on_sat"}, {ml r."c_arrive_sun"}, {ml r."c_arrive_mon"}, {ml r."c_arrive_tue"}, {ml r."c_arrive_wed"}, {ml r."c_arrive_thu"}, {ml r."c_arrive_fri"}, {ml r."c_arrive_sat"}, {ml r."c_max_bulk"}, {ml r."c_open_sale"}, {ml r."c_display_order"}, {ml r."c_min_los"}, {ml r."c_max_los"}, {ml r."c_charge_1st_night"}, {ml r."c_old_price_single"}, {ml r."c_old_price_double"}, {ml r."c_single_only"}, {ml r."c_pms_cattype"}, {ml r."c_agent_id"}, {ml r."c_discount_day01"}, {ml r."c_discount_day02"}, {ml r."c_discount_day03"}, {ml r."c_discount_day04"}, {ml r."c_discount_day05"}, {ml r."c_discount_day06"}, {ml r."c_discount_day07"}, {ml r."c_created"}, {ml r."c_last_modified_date"}, {ml r."c_consider_agents_discount"}, {ml r."c_consider_home_site_discount"}, {ml r."c_parent_package"}, {ml r."c_wing"}, {ml r."c_currency"}, {ml r."c_categories"}, {ml r."c_local_currency"}, {ml r."c_price_for_show"}, {ml r."c_old_price_for_show"}, {ml r."c_allow_extra_dates"}, {ml r."c_permit_for_agents"}, {ml r."c_without_prices"}, {ml r."c_old_price_to_show_on_web"}, {ml r."c_deny_for_customer_group"}, {ml r."c_permit_for_homesite"}, {ml r."c_exclude_dates"}, {ml r."c_new_price_to_show_on_web"} )

I. 2015-06-15 21:59:44. <37> Translated SQL:

                        INSERT INTO "DBA"."t068_packages" ( "c_hotel_id", "c_package_id", "c_promotion_name", "c_description", "c_price_code", "c_plan", "c_start_date", "c_end_date", "c_display_from_date", "c_display_end_date", "c_valid_on_sun", "c_valid_on_mon", "c_valid_on_tue", "c_valid_on_wed", "c_valid_on_thu", "c_valid_on_fri", "c_valid_on_sat", "c_arrive_sun", "c_arrive_mon", "c_arrive_tue", "c_arrive_wed", "c_arrive_thu", "c_arrive_fri", "c_arrive_sat", "c_max_bulk", "c_open_sale", "c_display_order", "c_min_los", "c_max_los", "c_charge_1st_night", "c_old_price_single", "c_old_price_double", "c_single_only", "c_pms_cattype", "c_agent_id", "c_discount_day01", "c_discount_day02", "c_discount_day03", "c_discount_day04", "c_discount_day05", "c_discount_day06", "c_discount_day07", "c_created", "c_last_modified_date", "c_consider_agents_discount", "c_consider_home_site_discount", "c_parent_package", "c_wing", "c_currency", "c_categories", "c_local_currency", "c_price_for_show", "c_old_price_for_show", "c_allow_extra_dates", "c_permit_for_agents", "c_without_prices", "c_old_price_to_show_on_web", "c_deny_for_customer_group", "c_permit_for_homesite", "c_exclude_dates", "c_new_price_to_show_on_web" )

                        on existing update

                        VALUES (  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ? )

And now all columns arrive as numbers and values, instead of name-value pairs. So, if in some production DBs the column ordering is different than from the development DB, the wrong values are applied to wrong columns

I. 2015-06-15 21:59:44. <37> Update row (new remote values) [t068_packages]:

I. 2015-06-15 21:59:44. <37>   10010

I. 2015-06-15 21:59:44. <37>   2826

I. 2015-06-15 21:59:44. <37>   HONY6

I. 2015-06-15 21:59:44. <37>   חבילת ליל כלולות 26-5-30-6

I. 2015-06-15 21:59:44. <37>   isr fit14

I. 2015-06-15 21:59:44. <37>   B/B

I. 2015-06-15 21:59:44. <37>   2015-05-26

I. 2015-06-15 21:59:44. <37>   2015-06-30

I. 2015-06-15 21:59:44. <37>   2012-04-16

I. 2015-06-15 21:59:44. <37>   2015-06-29

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   99

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37> NULL

I. 2015-06-15 21:59:44. <37> NULL

I. 2015-06-15 21:59:44. <37>   1432.2

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   231ExecuKI

I. 2015-06-15 21:59:44. <37> NULL

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37> NULL

I. 2015-06-15 21:59:44. <37>   2015-06-15 21:58:50.464000

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37> NULL

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   NIS

I. 2015-06-15 21:59:44. <37>   [4 Characters]

I. 2015-06-15 21:59:44. <37>   0x322C3130

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1689.996

I. 2015-06-15 21:59:44. <37>   1689.996

I. 2015-06-15 21:59:44. <37>   [0 Characters]

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   1

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

I. 2015-06-15 21:59:44. <37>   0

//---------------------------------------------------

[former_member188493]

"So, if in some production DBs the column ordering is different than from the development DB, the wrong values are applied to wrong columns"

Yes, if you create a MobiLink database for a database where it works, and then use it for a database with a different schema without making any changes, it might not work properly.

If you want a synchronization solution that uploads data as name/value pairs, then use something other than MobiLink (XML perhaps). MobiLink is designed for transmission efficiency.

If you continue to use MobiLink, and different remote databases have different schemas but all use the same consolidated database, MobiLink scripts with different values in the "version" field may be of assistance. Create one set of MobiLink scripts in a single *.sql file, then copy and edit it for each different remote schema: change the 'version' strings, and change the column correspondences where they differ, and so on. The version strings are intended for large numbers of remote databases where schema changes have been made to some but not all, and they will work for you.

Note that the upload stream layout is driven by the remote schema, so handling differences in schema is left up to the MobiLink scripts on the consolidated database. This is a good thing because it centralizes administration; it is a lot easier to update MobiLink scripts on one single consolidated database (no need to stop and start synchronization, no need to worry about the transaction log, etc) than it is to change anything on ten thousand remotes.

[former_member329524]

Thank you, Breck

I have to note, that nowhere in the documentation is it mentioned the column ordering in the remote DB matters.

SAP really should address this issue, since the column names are already sent by the mobilink client, there is no transmission efficiency here. The mobilink server has to use the actual script it has and apply the data according to column names, which arrive to the server in any case.

[former_member188493]

> since the column names are already sent by the mobilink client, there is no transmission efficiency here.

The column names and data types are uploaded once per synchronization, even if there are many rows of data being uploaded, so the transmission efficiency is enormous.

Experimentation shows that an upload_insert script using named parameters WILL handle simple differences between column order in different remotes, but it's the download_cursor script you have to watch out for:

CALL ml_add_table_script  ( 'v1', 't1', 'upload_insert', '
INSERT t1
       ( key_1,
         key_2,
         non_key_1,
         non_key_2 )
VALUES ( {ml r."key_1"},
         {ml r."key_2"},
         {ml r."non_key_1"},
         {ml r."non_key_2"} )' );

CALL ml_add_table_script  ( 'v1', 't1', 'download_cursor', '
SELECT key_1,
       key_2,
       non_key_1,
       non_key_2
  FROM t1
 WHERE last_updated >= {ml s.last_table_download}' );

Here's what the table looks like on cons and remo1 (same column order) versus remo2 (different order):

CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL 
                    DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
   key_2         INTEGER NOT NULL DEFAULT 0,
   non_key_1     VARCHAR ( 10 ) NOT NULL DEFAULT '',
   non_key_2     VARCHAR ( 10 ) NOT NULL DEFAULT '',
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1, key_2 ) );

CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL 
                    DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
   key_2         INTEGER NOT NULL DEFAULT 0,
   non_key_1     VARCHAR ( 10 ) NOT NULL DEFAULT '',
   non_key_2     VARCHAR ( 10 ) NOT NULL DEFAULT '',
   PRIMARY KEY ( key_1, key_2 ) );

CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL 
                    DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
   key_2         INTEGER NOT NULL DEFAULT 0,
   non_key_2     VARCHAR ( 10 ) NOT NULL DEFAULT '',  -- different column order
   non_key_1     VARCHAR ( 10 ) NOT NULL DEFAULT '',  -- different column order
   PRIMARY KEY ( key_1, key_2 ) );

The upload_insert script correctly matched the columns by name for both remo1 and remo2, when inserting the rows on cons. I can send you an end-to-end demo if you send your email address to me: breck dot carter at gmail dot com.

However, the download_cursor script constructed an incorrect download stream for remo2. Even worse, the row that had just been uploaded correctly from remo2 was re-downloaded to remo2 in the wrong column order, in the same sync.

Rows that have just been uploaded are not supposed to be re-downloaded in the same sync, but the column value differences apparently fooled that MobiLink logic.

Bottom line: Using named parameters in upload scripts is a good idea, but it doesn't help (can't be used) in download_cursor scripts... those have to match the column order on the remote.

The documentation is clear on this issue, for the download_cursor script: "The columns must be selected in the order that the corresponding columns are defined in the remote database."

The same USED to be true for the upload scripts, when you had to use positional question "?" marks, but is no longer true with named parameters like you use.

IMO there is no issue for SAP to address. If you don't like the way MobiLink works, don't use it... but if you DO use it, use it correctly.

[regdomaratzki]

That is a fantastic and thorough answer Breck.  Thanks for your time.

[former_member329524]

Breck

Download scripts are not the problem here.

My example specifically stated the upload_update script, which put data into the wrong columns.

From your answer I understand that there is no was to control this.

I will need to recreate the tables on DBs with incorrect column ordering (luckily, there is a very limited number of them.

Thank you for your help

[former_member188493]

My apologies... I assumed the script was an upload_insert because it contained an INSERT statement.

Clearly, it is an upload_update containing a (custom-written?) INSERT ON EXISTING UPDATE.

If two different remotes contained exactly the same column names but in a different column order, the named parameters (such as {ml r."c_hotel_id"}) should take care of matching like-named columns with each other. There is nothing about the INSERT statement that depends on the order of the columns in the tables... it matches columns by name.

Question: Are you absolutely sure the upload_update script is responsible for your symptom? (if there is some other explanation, recreating the tables may not help)

Question: Why is an INSERT being used in an upload_update script?

[former_member188493]

Experimentation confirms that an SQL Anywhere 16 upload_update written like yours (INSERT ON EXISTING UPDATE VALUES with the column name pairs matching in the INSERT and VALUES lists) properly handles an uploaded row from a remote where two columns appear in a different order.

However, the row on the remote is IMMEDIATELY DAMAGED by a download_cursor that uses the column order of the consolidated database to re-send the uploaded row back down to the remote (this can be quite a shock 🙂

So... it may not be the upload_update doing the damage. If you query the data AFTER the synchronization, you may be seeing damage done on the remote rather than the consolidated.

The bottom line here: The order of columns in the download_cursor script MUST MATCH the order of columns in the remote database. You can achieve that by (a) changing the column order on the remote like you are doing, or (b) creating two sets of MobiLink scripts that differ by the order of the download_cursor SELECT list. The two sets of scripts are identified by different 'version' strings, and those version strings can be specified on the dbmlsync.exe command line when launching a sync.

The script version feature is one of MobiLink hallmark features; it allows a variety of different remote schemas to synchronize with a common consolidated database.

[former_member329524]

After recreating the table in one of the remote DBs the data has been sent successfully. So, it is a Mobilink issue somewhere.

This is an "upload-only" model, there is no download script on this table, everything is in --{ml_ignore}, so it is highly unlikely that a donwload script causes trouble.

Was your experiment on a table with a small number of columns?

May be you should try to recreate the issue on a table with 60+ columns, like mine.

[Former Member]

If you are using a single upload script version for upload_update, the way that Arcady tries to synchronize to consolidated database, will not be synchornized correctly if one of the remote database have mixed column order regardless of INSERT statement trying to insert based on column names. From Mobilink Server log(using v+ switch), it shows that the values are being synchronized in the column-order of remote database schema to the consolidated database. For example, if remoteA had a schema: remoteA(id, v1, v3, v2) and consolidated database had a schema: cTable(id, v1, v2, v3), even if your upload_update script is trying INSERT based on column names, the values are actually uploaded in the order of remoteA columns.

[former_member329524]

Hello, Hanbin

I was wondering if this issue is something that SAP intends to fix.

Because having a separate upload script (actually, a whole model) just for incorrect field order is not something that is feasible for us.

We will, probably have to recreate the problematic tables in the remote DB's in correct order unless it can be fixed on SAP end.