STOP SYNCHRONIZATION DELETE not working

[VolkerBarth]

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.

[VolkerBarth]

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.

[VolkerBarth]

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...

[Former Member]

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.

[VolkerBarth]

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..

[Breck_Carter]

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.

[Breck_Carter]

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'