Question Re: STOP SYNCHRONIZATION DELETE not working in Technology Q&A

STOP SYNCHRONIZATION DELETE not working

Former Member — Sat, 05 May 2012 08:15:54 GMT

I am developing an iPad application with Client-Consolidated DB synchronization model. After synchronization I am performing a cleanup operation which deletes irrelevant records from client db. My cleanup procedure has 40 delete statements. The workflow for this cleanup is:
1. STOP SYNCHRONIZATION DELETE
2. Run all the delete queries.
3. COMMIT
4. START SYNCHRONIZATION DELETE
I have searched the documentation in which COMMIT is given after START SYNC command. I think it should not be the problem here.
I have tested this logic by executing through ULPreparedStatement::ExecuteStatement() and from the inbuilt C++ API function connection->stopSynchronizationDelete(). In both cases I am getting a true return value, but from the server log I can see that it is trying to upload those deletes to server.

Re: STOP SYNCHRONIZATION DELETE not working

VolkerBarth — Sat, 05 May 2012 15:51:15 GMT

Wild guess: AFAIK, MobiLink scripts should never do a COMMIT or ROLLBACK - cf. the warning from the docs:

Caution

There should be no implicit or explicit commit or rollback in your SQL synchronization scripts or the procedures or triggers that are called from your SQL synchronization scripts. COMMIT or ROLLBACK statements within SQL scripts alter the transactional nature of the synchronization steps. If you use them, MobiLink cannot guarantee the integrity of your data in the event of a failure.

Re: STOP SYNCHRONIZATION DELETE not working

VolkerBarth — Sat, 05 May 2012 15:55:42 GMT

Well, I may be wrong with my comment above, as the samples for STOP SYNCHRONIZATION DELETE surely do use a COMMIT, as you have stated yourself. Somewhat irritating...

Re: STOP SYNCHRONIZATION DELETE not working

Former Member — Sat, 05 May 2012 20:40:06 GMT

Stop Synchronization Delete happens on the remotes, and is not usually in the context of Sync. (At least I've never used it there, not sure, might be able to use it in some sp_hook procedures.) Anyway, that's why the COMMIT/ROLLBACK restrictions don't apply here. Sync scripts are most often used on the consolidated side.

I've been able to make this work under the normal db, but can't say I've tried it using UltraLite. I do notice that the documentation examples follow the pattern: STOP SYNCHRONIZATION DELETE; run the deletes -- no commit issued START SYNCHRONIZATION DELETE; COMMIT;

My usage has had the STOP, deletes, START in a sproc with the caller of the sproc calling the commit which matches the example patterns. I wouldn't have necessarily expected an extra commit to matter, but I could envision it being important. Might be worth a try.

Re: STOP SYNCHRONIZATION DELETE not working

VolkerBarth — Sun, 06 May 2012 07:41:24 GMT

Sorry, just forget about the non-sense I've written above - Ron has made the point clear: "Stop Synchronization Delete happens on the remotes", so my hints to the sync scripts on the cons are totally off-topic. - I won't delete my wrong comments, for the single reason that I believe one can learn something from wrong conclusions, as well..

Re: STOP SYNCHRONIZATION DELETE not working

Breck_Carter — Sun, 06 May 2012 09:55:25 GMT

Another wild guess: The COMMIT turns off the STOP SYNCHRONIZATION DELETE which then causes the deletes to be processed for upload. If so, this would fall into the category of undocumented ~~bug~~, er, ~~surprise~~, um, feature.

Here's a clue: the comment "-- including the stopping of the deletes" in the Help at http://dcx.sybase.com/index.html#1201/en/dbreference/start-synchronization-delete-statement.html implies that commit/rollback does have a direct effect on STOP SYNCHRONIZATION DELETE, and therefore should be executed after the START.

Re: STOP SYNCHRONIZATION DELETE not working

Former Member — Mon, 07 May 2012 06:23:42 GMT

Hi Guys, Thanks a lot to all for their valuable comments, no matter they are just guesses but they gave me idea to work with a different approach which i was considering worthless. I tried it with the sequence though i have to do some code rearrangement and modification.
1) STOP SYNCHRONIZATION DELETE
2) DELETE
3) START SYNCHRONIZATION DELETE
4) COMMIT
and this worked just fine. I think Volker you are right, this COMMIT is affecting the behavior of STOP SYNC command and it seems applicable to remote DB too though you mentioned it off-topic. Special thanks to you for mentioning the same. You saved me.

Re: STOP SYNCHRONIZATION DELETE not working

VolkerBarth — Mon, 07 May 2012 07:18:55 GMT

Glad you got it working - and feel free to "accept" your own answer.

For what I see, the credits w.r.t. COMMIT vs. STOP SYNC should go to Breck, not me:)

Re: STOP SYNCHRONIZATION DELETE not working

Breck_Carter — Mon, 07 May 2012 09:32:37 GMT

The credit should go to Gandalf himself, who raised the issue in the original question and then discounted it: "... I have searched the documentation in which COMMIT is given after START SYNC command. I think it should not be the problem here."

I agree that "it should not be the problem" but life isn't always agreeable 🙂

Re: STOP SYNCHRONIZATION DELETE not working

VolkerBarth — Mon, 07 May 2012 09:36:34 GMT

Well, in case it's "Gandalf the White", he won't need further credits:)

Re: STOP SYNCHRONIZATION DELETE not working

former_SQLA_member1694876 — Mon, 07 May 2012 14:09:06 GMT

Sorry Guys, but the order of 'commit' and 'start sync delete' statements doesn't make any difference. Nor does 'commit' reset or affect the stop-sync-delete status/flag. All that matters is which delete-mode is in effect when the delete statement itself is executed. (This is a little better than a wild guess, hopefully, since I've checked the code 😉

So, I'm afraid something more is going on here.

What sometimes does cause confusion with stop-sync-delete is the following sequence:

insert a row
stop sync delete
delete that row
start sync delete
synchronize

What will be uploaded on the synchronize? The delete will not of course, but the insert of the row will. This is different than the following, where nothing is uploaded on the final synchronize:

insert a row
synchronize (uploads that new row)
stop sync delete
delete that row
start sync delete
synchronize

The other normal cases are:

insert a row
delete that row (no stop-sync-delete here)
synchronize (nothing goes up)

and

insert a row
synchronize (uploads the insert)
delete that row
synchronize (uploads the delete)

Re: STOP SYNCHRONIZATION DELETE not working

Breck_Carter — Mon, 07 May 2012 14:36:23 GMT

...and we know that "reading the code" beats "testing the code" every time, right? <bg>

Re: STOP SYNCHRONIZATION DELETE not working

Breck_Carter — Mon, 07 May 2012 15:01:28 GMT

...and a test using 12.0.1.3298 does agree with Tim's reading of the code: it doesn't matter where the COMMIT goes, before or after the START SYNCHRONIZATION DELETE; either way, the row deleted on the remote database after the STOP SYNCHRONIZATION DELETE is not deleted from the consolidated.

Re: STOP SYNCHRONIZATION DELETE not working

Breck_Carter — Mon, 07 May 2012 15:03:37 GMT

Did you change more than one thing? ...because my test indicates that it doesn't matter whether the COMMIT is before or after the START SYNCHRONIZATION DELETE.

Re: STOP SYNCHRONIZATION DELETE not working

former_SQLA_member1694876 — Mon, 07 May 2012 15:13:50 GMT

🙂

(Truth be told I'm old enough that I did indeed monkey around a bit with a test as well before posting... But there's only so much time you know... 😉

Re: STOP SYNCHRONIZATION DELETE not working

Former Member — Tue, 08 May 2012 02:59:24 GMT

Hi Breck, I didn't change anything in sync model. Only the cleanup routine i rearranged. As in my solution i was performing all 40 DELETE statements required for me without committing them, then i START the sync and did the commit. this approach didn't upload any change happened due to DELETEs to consolidated database.
Can you please specify what kind of test you ran? Have you ran more than 1 deletes? If you run only one 1 delete it won't produce my scenario.

Re: STOP SYNCHRONIZATION DELETE not working

Former Member — Tue, 08 May 2012 05:17:15 GMT

Guys, i think i need to explain my whole Sync model scenario.
1. I have Client DB which can be divided in 3 set of tables lets say : 1)SET-A 2)SET-B 3) SET-C
2. SET-A - Daily transaction for my activities. INSERT, DELETE,UPDATE of new records.
3. SET-B - Lookup tables only. data will depend on my current working region(geographical) and will be downloaded from server. No insert , update is allowed in this set.
4. SET-C - Same as point 3.
5. Now if from backend my region got changed i will get new data set but previous data will also be there in remote DB, i need to delete those records. I will be performing 40 delete statements for that. But that data is relevant for other(it is common data) Users and should not be deleted from server.
6. I have 3 Sync publications lets say :- 1) Sync1 2) Sync2 3) Sync3
7. Very first time i will run all 3 publications which gives me complete DB.
8. Subsequently i will Run Sync2 or Sync3 with Sync1 based upon a logic.
9. Let say region change happened for me, i will run Sync1 & Sync2, and i need to delete unwanted records in client DB.
10. Next time i will run Sync1 with Sync3 and don't want the deletes to reflect on server.

That's it. Now when i was trying with the approach i mentioned in question, from the Mobilink server log i was able to see that sync is trying to upload those DELETEs on it(i have tested it for 3 different mlreoteids). But with the approach i used in solution it does not happen which solved my problem.
@Tim : So for a second lets say COMMIT affect the STOP sync command behavior and we have a solution, so in your case1 if you execute only one delete statement it won't produce this scenario because STOP got altered but we are not doing any DELETE after that.
But if it does not affect the STOP command, then i have no clue what's going on there as I am an XCode developer and don't know much about DB, Sync Models processing, this is my first project involved with DB.

Thanks again to all for their valuable comments & explanation

Re: STOP SYNCHRONIZATION DELETE not working

VolkerBarth — Tue, 08 May 2012 05:28:53 GMT

IMHO, the replacement of "unfitting" regional data with "fitting one" simply looks like some kínd of "territory realignment" problem, which is a common situation for mobile databases.

Here, you would not need to delete the data explicitly on the remote but would rely on a download_cursor script to get the "fresh data" and a "download_delete_cursor" script to delete the "old data".

Cf. this doc page.

Re: STOP SYNCHRONIZATION DELETE not working

Breck_Carter — Tue, 08 May 2012 08:51:29 GMT

Here's the test I just ran using SQL Anywhere 12.0.1.3298. It shows that STOP SYNCHRONIZATION DELETE prevented 40 DELETE operations from being uploaded even though the COMMIT preceded the START SYNCHRONIZATION DELETE.

001_setup_cons_remo.bat

"%SQLANY12%bin32dbinit.exe" cons.db
"%SQLANY12%bin32dbspawn.exe" -f "%SQLANY12%bin32dbeng12.exe" -o dbeng12_log_cons.txt -os 1M -x none cons.db 
"%SQLANY12%bin32dbisql.com" -c "ENG=cons;DBN=cons;UID=dba;PWD=sql" READ ENCODING Cp1252 "%SQLANY12%MobiLinksetupsyncsa.sql"
PAUSE
"%SQLANY12%bin32dbisql.com" -c "ENG=cons;DBN=cons;UID=dba;PWD=sql" READ ENCODING Cp1252 001s_script_to_setup_cons.sql
PAUSE
REM Use bin32 if running on 32-bit Windows...
"%SQLANY12%bin64dbdsn.exe" -ws cons -y -c "ENG=cons;DBN=cons;UID=dba;PWD=sql"
PAUSE
"%SQLANY12%bin32dbinit.exe" remo.db
"%SQLANY12%bin32dbspawn.exe" -f "%SQLANY12%bin32dbeng12.exe" -o dbeng12_log_remo.txt -os 1M -x none remo.db 
"%SQLANY12%bin32dbisql.com" -c "ENG=remo;DBN=remo;UID=dba;PWD=sql" READ ENCODING Cp1252 001s_script_to_setup_remo.sql
PAUSE
"%SQLANY12%bin32dbisql.com" -c "ENG=cons;DBN=cons;UID=dba;PWD=sql"
"%SQLANY12%bin32dbisql.com" -c "ENG=remo;DBN=remo;UID=dba;PWD=sql"
PAUSE All done...

001s_script_to_setup_cons.sql



-- Define GLOBAL AUTOINCREMENT partition number.
SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '0';

BEGIN
   DROP TABLE d;
   EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE d (
   key_1         UNSIGNED BIGINT NOT NULL 
                    DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
   non_key_1     VARCHAR ( 100 ) NOT NULL DEFAULT '',
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1 ) );

CALL ml_add_table_script  ( 'v1', 'd', 'upload_insert', NULL );
CALL ml_add_table_script  ( 'v1', 'd', 'upload_insert', '
INSERT d
       ( key_1,
         non_key_1 )
VALUES ( ?,
         ? )' );

CALL ml_add_table_script  ( 'v1', 'd', 'upload_update', NULL );
CALL ml_add_table_script  ( 'v1', 'd', 'upload_update', '
UPDATE d
   SET non_key_1 = ?
 WHERE key_1 = ?' );

CALL ml_add_table_script  ( 'v1', 'd', 'upload_delete', NULL );
CALL ml_add_table_script  ( 'v1', 'd', 'upload_delete', '
DELETE d
 WHERE key_1 = ?' );

CALL ml_add_table_script  ( 'v1', 'd', 'download_cursor', NULL );
CALL ml_add_table_script  ( 'v1', 'd', 'download_cursor', '
SELECT key_1,
       non_key_1
  FROM d
 WHERE last_updated >= ?' );

CALL ml_add_table_script  ( 'v1', 'd', 'download_delete_cursor', NULL );
CALL ml_add_table_script  ( 'v1', 'd', 'download_delete_cursor', '--{ml_ignore}' );

-- Insert rows to download and then delete.
BEGIN
DECLARE @rowcount INTEGER;
SET @rowcount = 1;
WHILE @rowcount <= 100 LOOP
   INSERT d VALUES ( DEFAULT, DEFAULT, DEFAULT );
   SET @rowcount = @rowcount + 1;
END LOOP;
COMMIT;
END;

001s_script_to_setup_remo.sql



-- Define GLOBAL AUTOINCREMENT partition number.
SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '1';

BEGIN
   REVOKE CONNECT FROM REMOTE_DBA;
   EXCEPTION WHEN OTHERS THEN
END;
GRANT CONNECT TO REMOTE_DBA IDENTIFIED BY SQL;
GRANT REMOTE DBA TO REMOTE_DBA;

BEGIN
   DROP SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1";
   EXCEPTION WHEN OTHERS THEN
END;
BEGIN
   DROP SYNCHRONIZATION USER "1";
   EXCEPTION WHEN OTHERS THEN
END;
BEGIN
   DROP PUBLICATION p1;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP TABLE d;
   EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE d (
   key_1         UNSIGNED BIGINT NOT NULL 
                    DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
   non_key_1     VARCHAR ( 100 ) NOT NULL DEFAULT '',
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1 ) );

CREATE PUBLICATION p1 (
   TABLE d  ( key_1,
              non_key_1 ) );
CREATE SYNCHRONIZATION USER "1" TYPE tcpip;
CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1";

002_run_mlsrv12.bat

"%SQLANY12%bin64mlsrv12.exe"^
  -c "DSN=cons;UID=dba;PWD=sql"^
  -o mlsrv12_log_cons.txt^
  -os 10M^
  -ppv 60^
  -vcefhkmnopstuU^
  -zu+

003_run_dbmlsync.bat

"%SQLANY12%bin32dbmlsync.exe"^
  -c "ENG=remo;DBN=remo;UID=REMOTE_DBA;PWD=SQL"^
  -e "adr='host=localhost';sv=v1"^
  -o dbmlsync_log_remo.txt^
  -os 10M^
  -vnosu^
  -x

004_test_STOP_SYNCHRONIZATION_DELETE.sql



-- Run initial sync.

-- Run this on remote database...
STOP SYNCHRONIZATION DELETE;
DELETE d WHERE key_1 <= 40;
COMMIT;
START SYNCHRONIZATION DELETE;

-- Run second sync.

-- Run this on consolidated database...
SELECT * FROM d;
key_1,non_key_1,last_updated
1,'','2012-05-08 08:42:21.104'
2,'','2012-05-08 08:42:21.137'
...
40,'','2012-05-08 08:42:21.140'
41,'','2012-05-08 08:42:21.140'
42,'','2012-05-08 08:42:21.140'
...
100,'','2012-05-08 08:42:21.143'

Re: STOP SYNCHRONIZATION DELETE not working

Breck_Carter — Tue, 08 May 2012 08:52:22 GMT

I reran the test with 40 deletes and posted the code as a new answer.