Hi, Is there a way to control the sequence of synchronising tables between cons. & rem on mobilink? I have two tables which have 1-to-many relationship between (connection through primary and foreign keys). I am facing a problem that the table with foreign key is uploaded before uploading the table with primary key, so that the synchronisation is stopped. Actually this problem was not existing in real field, but now I am creating test environment, and am facing this problem.

Thanks in advance

asked 06 Jun, 06:55

Sarkis's gravatar image

Sarkis
2002625
accept rate: 0%

(06 Jun, 07:27) Chris Keating

Hm, by default MobiLink should upload (and download) tables in an appropriate table order, i.e. a table holding a FK will be uploaded only when the according table(s) containing the PK has been already uploaded (for insert/update, for delete in opposite order).

Are these FK relationships declared as such and identical between cons and remotes?

For problematic cases (e.g. cyclic/self-references), you might disable the foreign key checking (dbmlsync -e "toc=OFF") or explicitly specify the table order (dbmlsync -e "tor=table1,table2,...").

permanent link

answered 06 Jun, 07:22

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668
accept rate: 32%

edited 06 Jun, 07:23

Thank you very much for the answer, everything is clear despite the problem is still existing. It is great to have such option toc/tor. Is this option saved in SYSOPTIONS or it is just an option for dblmsync? Does DBREMOTE also work with same logic? So that it can decide which table to upload/download first?

(06 Jun, 07:51) Sarkis
Replies hidden
1

These are DBMLSYNC-only options and can be stored within the database via CREATE/ALTER SYNCHRONIZATION USER and/or CREATE/ALTER SYNCHRONIZATION SUBSCRIPTION (not sure about v10 here).

AFAIK, the DBMLSync log output should warn about possible FK problems during the upload (say, when self references are contained).


They do not relate to SQL Remote at all. SQL Remote sends all operations and (the opposite database applies them) in the original order, statement by statement (even row by row) so there's no "table order" at all. - As SQL Remote assumes appropriate FK definitions at both sites, that should not lead to problems, except when you want to insert a row in a remote whose parent row has been deleted in the meantime...

(06 Jun, 09:01) Volker Barth
2
  • Both Dbmlsync and UltraLite can set the table order option in a synchronization

  • As Volker points out, if dbmlsync is choosing the wrong table order for upload, it's quite likely that the foreign key definitions at the consolidated and remote database are different.

  • Dbremote does NOT work with the same logic. Dbremote will replicate every committed operation in the exact same order and send it to the other side, so on the assumption that the foreign key relationships are the same on both sides, everything should be fine. MobiLink clients do not send every committed operation, but will send a delta of changes since the last synchronization. If you update the same row 1000 times, dbremote will send 1000 updates, but MobiLink clients will send 1 update.

Reg

(06 Jun, 09:04) Reg Domaratzki

and what exactly means the following message (on remote): Cycle of foreign key references found. Cannot guarantee referential integrity during upload. Should this message appear whenever any relationship is found between any two tables in remote? Or this message appears only when some not ordinary integrity is found in the remote?

(06 Jun, 10:02) Sarkis
Replies hidden
1

Cycle of foreign key references found. Cannot guarantee referential integrity during upload. Should this message appear whenever any relationship is found between any two tables in remote?

No, AFAIK, this warning should only appear when the declared FK relationships contain a cycle, i.e. when table A has a FK to table B and vice versa (or B has one to table C and table C to A or even more transitions...), or when table A has a self-reference.

(06 Jun, 10:18) Volker Barth

Although the MobiLink clients can order the tables, there may not be an appropriate order to avoid the problem automatically. For example, if you have cycles in your foreign key dependencies you may have upload problems.

Two approaches come to mind. I would only recommend these if the option to explicitly reorder the tables isn't sufficient, or if you want to isolate your server-side gear from arbitrary/changing client-side table ordering as the client-side application schema evolves over time:

  1. In your MobiLink upload scripts, stage the data to staging tables that don't have foreign keys, then bulk copy the upload rows to the target tables, say in the connection-level end_upload script.
  2. Use MobiLink Java or .NET direct row handling [1], and update the tables in the order you need.

[1] - https://help.sap.com/viewer/2e2850e915164089bca8ed5ba1788d0e/17.0/en-US/81bb258d6ce21014ba6de1afe59a0e01.html

  • Russ
permanent link

answered 06 Jun, 10:23

RussC_FromSAP's gravatar image

RussC_FromSAP
1.3k11030
accept rate: 18%

Do you mean that the upload_insert script should look like: insert into table1_temp (col1,col2) Values ( {ml r."col1"}, {ml r."col2"} );

and then the end_upload script like: unload select * from table1_temp to 'c:\table1_temp.txt'; load into table table1 from 'c:\table1_temp.txt'; delete table1_temp;

(06 Jun, 10:55) Sarkis
Replies hidden

The approach I'm proposing is just to insert via select. The first script you propose, inserting into table1_temp, is almost what you need. You need to distinguish the uploads of different remote databases, so you need the remote id as a column:

INSERT INTO table1_temp (remote_id, col1, col2) VALUES( {ml s.remote_id}, {ml r."col1"}, {ml r."col2"} )

The actual syntax for the end_upload script depends on your consolidated database type, but it could be something like:

INSERT INTO table1 ( col1, col2 ) SELECT col1, col2 FROM table1_temp WHERE remote_id = {ml s.remote_id}

You'll also need to delete the remote_id's table1_temp rows at the end of the end_upload script.

  • Russ
(06 Jun, 11:09) RussC_FromSAP
1

But there's more to it than that. Just replacing the existing insert scripts with inserts into temp tables and then inserting into the base table later in the synchronization will not solve the problem. Once the entire upload is available, you'll need to sort out how to insert the data into the consolidated without causing a foreign key violation.

IMHO, we are missing a lot of information here to properly answer the question and/or provide a solution.

To start with :

  1. Which RDBMS are you using as the consolidated database?
  2. Are the table definitions and foreign keys in your remote database the same as the table definition and foreign keys in the consolidated database?

Reg

(06 Jun, 14:13) Reg Domaratzki

I'd like to add that the mentioned warning about cyclic relationships should not appear in the simple 1:n FK relationship between two tables as described in the original question. So I guess we need to know more about the details of the problematic tables and the noticed RI violations.

(06 Jun, 15:34) Volker Barth

I am using SQLANYWHERE 10 on both remote and cons. Both rem and cons have the same definition of foreign keys

(06 Jun, 17:14) Sarkis
Replies hidden
1

If the tables definitions and foreign keys are all the same between the remote and consolidated, and given that the data at the remote database is a subset (or a complete copy) of the data at the consolidated, there should be no reason for a cycle of foreign keys to result in a foreign key violation when applying the upload in the MobiLink Server.

Because you have a cycle of foreign keys at the remote site, that means that the foreign keys defined must use the WAIT FOR COMMIT option, or it would be impossible to ever insert rows into the tables in questions. You've stated the foreign keys are the same at the consolidated, so those foreign keys are also defined with the WAIT FOR COMMIT option at the consolidated. It's possible that you've also set the wait_for_commit database option at both sites as opposed to using the WAIT FOR COMMIT option in the foreign key definitions. The upload is all applied in a single transaction, so no RI checking occurs until the commit, when all the data from the remote has been uploaded, and there should be no RI issues, since none existed at the remote database.

Given this information, there are three possibilities :

1) The foreign key violations are occurring simply because you have a row in a parent table that exists at the remote site that does not exist in the consolidated database. This theory would also explain why you are seeing the problem in your test environment, and not in production.

2) The tables definitions and foreign key definitions are NOT exactly the same at the consolidated and remote. If they differ, it may only be with the WAIT FOR COMMIT option on the foreign key constraint.

3) You have set the "wait_for_commit" database option at the remote site, but not at the consolidated.

I would suggest going back to your original error with this information and attempting to figure out which of the options above may be the source of your problem. If you can't figure it out, we'll probably need to see the dbmlsync output log (with -v+) and the MobiLink Server output log (also with -v+) from a failed synchronization to figure out what's going on.

Reg

(07 Jun, 08:45) Reg Domaratzki
showing 2 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×295
×10
×2

question asked: 06 Jun, 06:55

question was seen: 133 times

last updated: 07 Jun, 08:45