cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How do I force MobiLink to work in single row mode?

Go to solution
Breck_Carter
Participant

on ‎2011 Jul 10 3:07 PM

3,413

It is vastly confusing, not to mention inefficient, to see a trigger fired N*2 times when only N rows have been uploaded. How do I stop this from happening?

W. 2011-07-10 14:00:06. <1> [10039] Error detected during multi-row operation, performing rollback before retrying in single row mode

SQL Anywhere 11.0.1.2587 SQL Server 2008 ODBC driver 2009.100.1600.01

(OK, that ODBC driver is listed for 12.0.1 with MSS 2008, not 11.0.1 with MSS 2008, but it's the one that ships with 2008 now... sigh... I'll shut up now... George Bush ate my homework!)

Update: As requested by Philippe Bertrand, the partial MobiLink server diagnostic log originally posted here has been replaced with the following...

SQL Server 2008

ODBC driver 2009.100.1600.01

SQL Anywhere 11.0.1.2587

Consolidated setup...

--------------------------------------------------------------------- 
-- Create separate database "mltest" (change the paths as desired).

USE master
GO

CREATE DATABASE mltest
ON 
( NAME = mltest,
   FILENAME = 'D:\\data\\mltest\\mltest.mdf',
   SIZE = 100MB )
LOG ON
( NAME = mltest_log,
   FILENAME = 'D:\\data\\mltest\\mltest.ldf',
   SIZE = 100MB )
GO

--------------------------------------------------------------------- 
USE mltest
GO

SET QUOTED_IDENTIFIER ON
GO

--------------------------------------------------------------------- 
CREATE TABLE account_balance (
   account_number      VARCHAR ( 10 )    NOT NULL,
   amount              DECIMAL ( 15, 2 ) NOT NULL DEFAULT 0,
   limit_exceeded      VARCHAR ( 1 )     NOT NULL DEFAULT 'N' CHECK ( limit_exceeded IN ( 'Y', 'N' ) ),
   PRIMARY KEY ( account_number ) )
GO

---------------------------------------------------------------------
BEGIN TRANSACTION
INSERT account_balance ( account_number ) VALUES ( '100' ) 
INSERT account_balance ( account_number ) VALUES ( '200' ) 
INSERT account_balance ( account_number ) VALUES ( '300' ) 
INSERT account_balance ( account_number ) VALUES ( '400' ) 
INSERT account_balance ( account_number ) VALUES ( '500' ) 
INSERT account_balance ( account_number ) VALUES ( '600' ) 
INSERT account_balance ( account_number ) VALUES ( '700' ) 
INSERT account_balance ( account_number ) VALUES ( '800' ) 
INSERT account_balance ( account_number ) VALUES ( '900' ) 
COMMIT TRANSACTION
GO

---------------------------------------------------------------------
CREATE TRIGGER tru_account_balance ON account_balance AFTER INSERT, UPDATE 
AS
BEGIN

-- Flag the row as soon as it exceeds the limit.

UPDATE account_balance
      SET account_balance.limit_exceeded = 'Y'
     FROM inserted
    WHERE account_balance.account_number = inserted.account_number
      AND account_balance.limit_exceeded = 'N'
      AND account_balance.amount         > 5000.00

END
GO

-- Example 1: One local transaction, one operation.

BEGIN TRANSACTION
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '100'
COMMIT TRANSACTION
GO

-- Example 2: One local transaction, two operations.

BEGIN TRANSACTION
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '200'
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '200'
COMMIT TRANSACTION
GO

-- Example 3: Two local transactions, one operation each.

BEGIN TRANSACTION
UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '300'
COMMIT TRANSACTION
GO

BEGIN TRANSACTION
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '300'
COMMIT TRANSACTION
GO

USE mltest
GO

SET QUOTED_IDENTIFIER ON
GO

--------------------------------------------------------------------- 
EXECUTE ml_add_column  'v1', 'account_balance', NULL, NULL
GO

EXECUTE ml_add_column  'v1', 'account_balance', 'account_number', NULL
GO

EXECUTE ml_add_column  'v1', 'account_balance', 'amount', NULL
GO

--------------------------------------------------------------------- 
EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_insert', NULL
GO

EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_insert', '
INSERT account_balance
       ( account_number,
         amount )
VALUES ( {ml r.account_number},
         {ml r.amount} )' 
GO

--------------------------------------------------------------------- 
EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_update', NULL 
GO

EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_update', '
UPDATE account_balance
   SET amount = {ml r.amount}
 WHERE account_number = {ml r.account_number}' 
GO

--------------------------------------------------------------------- 
EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_delete', NULL 
GO

EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_delete', '
DELETE account_balance
 WHERE account_number = {ml r.account_number}' 
GO

Consolidated table as it existed before synchronization...

SELECT * FROM account_balance ORDER BY account_number
 account_number amount            limit_exceeded
 -------------- ----------------- --------------
 100                      6000.00 Y
 200                      5000.00 Y
 300                      5000.00 Y
 400                          .00 N
 500                          .00 N
 600                          .00 N
 700                          .00 N
 800                          .00 N
 900                          .00 N

(9 rows affected)
1>

Remote setup...

CREATE TABLE account_balance (
   account_number      VARCHAR ( 10 )    NOT NULL,
   amount              DECIMAL ( 15, 2 ) NOT NULL DEFAULT 0,
   PRIMARY KEY ( account_number ) )
GO

---------------------------------------------------------------------
INSERT account_balance ( account_number ) VALUES ( '400' ); 
INSERT account_balance ( account_number ) VALUES ( '500' ); 
INSERT account_balance ( account_number ) VALUES ( '600' ); 
INSERT account_balance ( account_number ) VALUES ( '700' ); 
INSERT account_balance ( account_number ) VALUES ( '800' ); 
INSERT account_balance ( account_number ) VALUES ( '900' ); 
COMMIT;

---------------------------------------------------------------------
CREATE PUBLICATION p1 (
   TABLE account_balance ( account_number,
                           amount ) );

CREATE SYNCHRONIZATION USER "1" TYPE tcpip;

CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1";

-- Example 4: One synchronization without -tu, one transaction, one operation.

UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '400';
COMMIT;

-- Example 5: One synchronization without -tu, one transaction, two operations.

UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '500';
UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '500';
COMMIT;

-- Example 6: One synchronization without -tu, two transactions, one operation each.

UPDATE account_balance SET amount = amount + 6000.00 WHERE account_number = '600';
COMMIT;

UPDATE account_balance SET amount = amount - 1000.00 WHERE account_number = '600';
COMMIT;

Remote table as it existed before synchronization...

SELECT * FROM account_balance ORDER BY account_number;

account_number,amount
'400',6000.00
'500',5000.00
'600',5000.00
'700',0.00
'800',0.00
'900',0.00

MobiLink command lines...

Note that dbmlsync -uo (upload only) is in effect, but NOT dbmlsync -tu (transactional upload).

"%SQLANY11%\\bin32\\mlsrv11.exe"^
  -c "DSN=mltest;UID=sa;PWD=j68Fje9#fyu489"^
  -fr^
  -o mlsrv11_log_cons.txt^
  -os 10M^
  -ppv 60^
  -vcefhimnopqrstu^
  -zu+

"%SQLANY11%\\bin32\\dbmlsync.exe"^
  -c "ENG=remo;DBN=remo;UID=dba;PWD=sql"^
  -e "adr='host=localhost';sv=v1"^
  -o dbmlsync_log_remo_from_step_9.txt^
  -os 10M^
  -uo^
  -vnorsu^
  -x

MobiLink server diagnostic log...

I. 2011-07-11 05:13:56. SQL Anywhere MobiLink Server Version 11.0.1.2587
I. 2011-07-11 05:13:56. 
I. 2011-07-11 05:13:56. Copyright © 2001-2009, iAnywhere Solutions, Inc.
I. 2011-07-11 05:13:56. Portions copyright © 1988-2009, Sybase, Inc. All rights reserved.
I. 2011-07-11 05:13:56. Use of this software is governed by the Sybase License Agreement. Refer to http://www.sybase.com/softwarelicenses
I. 2011-07-11 05:13:56. 
I. 2011-07-11 05:13:56. This server is licensed to:
I. 2011-07-11 05:13:56.     Breck
I. 2011-07-11 05:13:56.     RisingRoad
I. 2011-07-11 05:13:56. Running Windows 7 Build 7601 Service Pack 1 on X86_64
I. 2011-07-11 05:13:56. Server built for X86 processor architecture
I. 2011-07-11 05:13:56. <main> Option 1: -c
I. 2011-07-11 05:13:56. <main> Option 2: DSN=mltest;UID=sa;pwd=********
I. 2011-07-11 05:13:56. <main> Option 3: -fr
I. 2011-07-11 05:13:56. <main> Option 4: -o
I. 2011-07-11 05:13:56. <main> Option 5: mlsrv11_log_cons.txt
I. 2011-07-11 05:13:56. <main> Option 6: -os
I. 2011-07-11 05:13:56. <main> Option 7: 10M
I. 2011-07-11 05:13:56. <main> Option 8: -ppv
I. 2011-07-11 05:13:56. <main> Option 9: 60
I. 2011-07-11 05:13:56. <main> Option 10: -vcefhimnopqrstu
I. 2011-07-11 05:13:56. <main> Option 11: -zu+
I. 2011-07-11 05:13:56. <main> Verbose logging: show upload row values
I. 2011-07-11 05:13:56. <main> Verbose logging: show download row values
I. 2011-07-11 05:13:56. <main> Verbose logging: show script names when invoked
I. 2011-07-11 05:13:56. <main> Verbose logging: show script contents when invoked
I. 2011-07-11 05:13:56. <main> Verbose logging: show schema for each table
I. 2011-07-11 05:13:56. <main> Verbose logging: show an error when the first read of a synchronization fails
I. 2011-07-11 05:13:56. <main> Verbose logging: show translated SQL for prepared statements
I. 2011-07-11 05:13:56. <main> Verbose logging: show rowcount values
I. 2011-07-11 05:13:56. <main> Cache size: 52428800 bytes
I. 2011-07-11 05:13:56. <main> Download cache directory size: 10485760 bytes
I. 2011-07-11 05:13:56. <main> Local file for remote synchronization logs: 'mlsrv.mle'
I. 2011-07-11 05:13:56. <main> Individual database connections will be closed after synchronization errors
I. 2011-07-11 05:13:56. <main> Maximum number of BLOB bytes to compare: 4294967295
I. 2011-07-11 05:13:56. <main> Maximum number of database connections: 6
I. 2011-07-11 05:13:56. <main> Maximum number of deadlock retries: 10
I. 2011-07-11 05:13:56. <main> Timeout for inactive database connections: 60 minutes
I. 2011-07-11 05:13:56. <main> Maximum delay between retries after deadlock: 30 seconds
I. 2011-07-11 05:13:56. <main> Rowset size: 10
I. 2011-07-11 05:13:56. <main> Number of database worker threads: 5
I. 2011-07-11 05:13:56. <main> Maximum number of threads uploading concurrently: 5
W. 2011-07-11 05:13:56. <main> [10064] Unknown users will be added automatically (when there is no authenticate_user script)
I. 2011-07-11 05:13:56. <main> ODBC DBMS Name: Microsoft SQL Server
I. 2011-07-11 05:13:56. <main> ODBC DBMS Version: 10.00.4000
I. 2011-07-11 05:13:56. <main> ODBC DBMS Driver Version: 10.50.1600
I. 2011-07-11 05:13:56. <main> ODBC Version supported by the driver: 3.52
I. 2011-07-11 05:13:56. <main> Collation sequence of the consolidated database is 'ISO 8859-1'
I. 2011-07-11 05:13:56. <main> System event:
                        set xact_abort off
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        set xact_abort off
I. 2011-07-11 05:13:56. <main> ODBC isolation set to: Read Committed
I. 2011-07-11 05:13:56. <main> Connected
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT user_id, name, hashed_password FROM ml_user WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT user_id, name, hashed_password FROM ml_user WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT rid, remote_id, script_ldt, description FROM ml_database WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT rid, remote_id, script_ldt, description FROM ml_database WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT rid, subscription_id, user_id, progress, publication_name, last_upload_time, last_download_time FROM ml_subscription WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT rid, subscription_id, user_id, progress, publication_name, last_upload_time, last_download_time FROM ml_subscription WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT table_id, name FROM ml_table WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT table_id, name FROM ml_table WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT component_name, property_set_name, property_name, property_value FROM ml_property WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT component_name, property_set_name, property_name, property_value FROM ml_property WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT script_id, script, script_language FROM ml_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT script_id, script, script_language FROM ml_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT version_id, name, description FROM ml_script_version WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT version_id, name, description FROM ml_script_version WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT version_id, event, script_id FROM ml_connection_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT version_id, event, script_id FROM ml_connection_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT version_id, table_id, event, script_id FROM ml_table_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT version_id, table_id, event, script_id FROM ml_table_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT last_modified FROM ml_scripts_modified WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT last_modified FROM ml_scripts_modified WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT version_id, table_id, idx, name, type FROM ml_column WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT version_id, table_id, idx, name, type FROM ml_column WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT script_id, script_name, flags, affected_pubs,    script, description FROM ml_passthrough_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT script_id, script_name, flags, affected_pubs,    script, description FROM ml_passthrough_script WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT remote_id, run_order, script_id, last_modified FROM ml_passthrough WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT remote_id, run_order, script_id, last_modified FROM ml_passthrough WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT status_id, remote_id, run_order, script_id, script_status, error_code, error_text, remote_run_time FROM ml_passthrough_status WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT status_id, remote_id, run_order, script_id, script_status, error_code, error_text, remote_run_time FROM ml_passthrough_status WHERE 1=2
I. 2011-07-11 05:13:56. <main> System event:
                        SELECT failed_script_id, error_code, new_script_id, action FROM ml_passthrough_repair WHERE 1=2
I. 2011-07-11 05:13:56. <main> Translated SQL:
                        SELECT failed_script_id, error_code, new_script_id, action FROM ml_passthrough_repair WHERE 1=2
I. 2011-07-11 05:13:57. <main> System event:
                        SELECT snapshot_isolation_state FROM sys.databases WHERE database_id=DB_ID()
I. 2011-07-11 05:13:57. <main> Translated SQL:
                        SELECT snapshot_isolation_state FROM sys.databases WHERE database_id=DB_ID()
I. 2011-07-11 05:13:57. <main> System event:
                        SELECT count(*) FROM ml_scripts_modified
I. 2011-07-11 05:13:57. <main> Translated SQL:
                        SELECT count(*) FROM ml_scripts_modified
I. 2011-07-11 05:13:57. <main> This software is using security technology from Certicom Corp.
I. 2011-07-11 05:13:57. <main> MobiLink server started
I. 2011-07-11 05:13:58. <main> System event:
                        SELECT count(*) FROM ml_passthrough_script
I. 2011-07-11 05:13:58. <main> Translated SQL:
                        SELECT count(*) FROM ml_passthrough_script
I. 2011-07-11 05:14:00. <main> System event:
                        SELECT count(*) FROM ml_passthrough_script
I. 2011-07-11 05:14:00. <main> Translated SQL:
                        SELECT count(*) FROM ml_passthrough_script
I. 2011-07-11 05:14:19. <1> Request from "Dbmlsync Version 11.0.1.2587" for: remote ID: ddbafee1-1d87-49be-8ef0-4feab955c657, user name: 1, version: v1
I. 2011-07-11 05:14:19. <1> Table #1: account_balance, 2 columns
I. 2011-07-11 05:14:19. <1> Column #1: varchar(10) NOT NULL PRIMARY KEY
I. 2011-07-11 05:14:19. <1> Column #2: decimal(15,2) NOT NULL
I. 2011-07-11 05:14:19. <1> Table 'account_balance' is referenced by publication 'p1'
I. 2011-07-11 05:14:19. <1> System event:
                        SELECT last_modified FROM ml_scripts_modified
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        SELECT last_modified FROM ml_scripts_modified
I. 2011-07-11 05:14:19. <1> ml_scripts_modified last modified at: 2011-07-11 05:08:38.760
I. 2011-07-11 05:14:19. <1> System event:
                        SELECT version_id FROM ml_script_version WHERE name = ?
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        SELECT version_id FROM ml_script_version WHERE name = ?
I. 2011-07-11 05:14:19. <1> System event:
                        SELECT version_id FROM ml_script_version WHERE name = ?
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        SELECT version_id FROM ml_script_version WHERE name = ?
I. 2011-07-11 05:14:19. <1> System event:
                        set xact_abort off
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        set xact_abort off
I. 2011-07-11 05:14:19. <1> ODBC isolation set to: Read Committed
I. 2011-07-11 05:14:19. <1> System event:
                        SELECT @@spid
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        SELECT @@spid
I. 2011-07-11 05:14:19. <1> System event:
                        SELECT c.event, s.script_language, s.script FROM ml_connection_script c, ml_script s WHERE c.version_id = ? AND c.script_id = s.script_id
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        SELECT c.event, s.script_language, s.script FROM ml_connection_script c, ml_script s WHERE c.version_id = ? AND c.script_id = s.script_id
I. 2011-07-11 05:14:19. <1> begin_connection <connection> (no script)
I. 2011-07-11 05:14:19. <1> COMMIT Transaction: begin_connection
I. 2011-07-11 05:14:19. <1> The current synchronization is using a connection with SPID '52'
I. 2011-07-11 05:14:19. <1> System event:
                        SELECT user_id, hashed_password FROM ml_user WHERE name = ?
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        SELECT user_id, hashed_password FROM ml_user WHERE name = ?
I. 2011-07-11 05:14:19. <1> authenticate_user <connection> (no script)
I. 2011-07-11 05:14:19. <1> authenticate_user_hashed <connection> (no script)
I. 2011-07-11 05:14:19. <1> authenticate_parameters <connection> (no script)
I. 2011-07-11 05:14:19. <1> modify_user <connection> (no script)
I. 2011-07-11 05:14:19. <1> User name '1' not found in the ml_user table, inserting a new entry
I. 2011-07-11 05:14:19. <1> System event:
                        INSERT INTO ml_user ( name, hashed_password ) VALUES( ?, ? )
I. 2011-07-11 05:14:19. <1> Translated SQL:
                        INSERT INTO ml_user ( name, hashed_password ) VALUES( ?, ? )
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT user_id FROM ml_user WHERE name = ?
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT user_id FROM ml_user WHERE name = ?
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT rid FROM ml_database WHERE remote_id = ?
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT rid FROM ml_database WHERE remote_id = ?
I. 2011-07-11 05:14:20. <1> System event:
                        INSERT INTO ml_database ( remote_id, description ) VALUES( ?, ? )
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        INSERT INTO ml_database ( remote_id, description ) VALUES( ?, ? )
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT rid FROM ml_database WHERE remote_id = ?
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT rid FROM ml_database WHERE remote_id = ?
I. 2011-07-11 05:14:20. <1> Publication #1: p1, subscription id: 2, last download time: 1900-01-01 00:00:00.000000
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT progress FROM ml_subscription WHERE rid = ? AND subscription_id = ?
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT progress FROM ml_subscription WHERE rid = ? AND subscription_id = ?
I. 2011-07-11 05:14:20. <1> Progress offsets for the publications that are explicitly involved in the current synchronization
I. 2011-07-11 05:14:20. <1> Subscription id 2: consolidated progress 0 and remote progress 0
W. 2011-07-11 05:14:20. <1> [10017] The consolidated and remote databases have different timestamp precisions.  Consolidated database timestamps are precise to 2 digit(s) in the fractional second while the remote database timestamps are precise to 6 digit(s)
W. 2011-07-11 05:14:20. <1> [10018] Resolve the timestamp precision mismatch by setting the SQL Anywhere DEFAULT_TIMESTAMP_INCREMENT option on the remote database to %1!d! and TRUNCATE_TIMESTAMP_VALUES to 'On'. UltraLite remotes must set the TIMESTAMP_INCREMENT option to 10000
W. 2011-07-11 05:14:20. <1> [10020] The timestamp precision mismatch may affect upload conflict detection.  Use the -zp option to cause the MobiLink server to use the lowest timestamp precision for conflict detection purposes
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT table_id FROM ml_table WHERE name = ?
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT table_id FROM ml_table WHERE name = ?
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT t.event, s.script_language, s.script FROM ml_table_script t, ml_script s WHERE t.table_id = ? AND t.version_id = ? AND t.script_id = s.script_id
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT t.event, s.script_language, s.script FROM ml_table_script t, ml_script s WHERE t.table_id = ? AND t.version_id = ? AND t.script_id = s.script_id
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT idx, name, type FROM ml_column WHERE version_id = ? and table_id = ? ORDER BY idx DESC
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT idx, name, type FROM ml_column WHERE version_id = ? and table_id = ? ORDER BY idx DESC
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT last_upload_time FROM ml_subscription WHERE rid = ? AND subscription_id = ?
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT last_upload_time FROM ml_subscription WHERE rid = ? AND subscription_id = ?
I. 2011-07-11 05:14:20. <1> Last upload time for subscription id 2: 1900-01-01 00:00:00.000000
I. 2011-07-11 05:14:20. <1> begin_synchronization <connection> (no script)
I. 2011-07-11 05:14:20. <1> begin_synchronization account_balance (no script)
I. 2011-07-11 05:14:20. <1> begin_publication <connection> (no script)
I. 2011-07-11 05:14:20. <1> COMMIT Transaction: begin_synchronization
I. 2011-07-11 05:14:20. <1> begin_upload <connection> (no script)
I. 2011-07-11 05:14:20. <1> begin_upload account_balance (no script)
I. 2011-07-11 05:14:20. <1> handle_UploadData <connection> (no script)
I. 2011-07-11 05:14:20. <1> begin_upload_rows account_balance (no script)
I. 2011-07-11 05:14:20. <1> System event:
                        save tran  it0 
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        save tran  it0 
I. 2011-07-11 05:14:20. <1> upload_update account_balance
                                                 UPDATE account_balance                            SET amount = {ml r.amount}                          WHERE account_number = {ml r.account_number}
I. 2011-07-11 05:14:20. <1> Translated SQL:
                                                 UPDATE account_balance                            SET amount =  ?                          WHERE account_number =  ?
I. 2011-07-11 05:14:20. <1> upload_fetch account_balance (no script)
I. 2011-07-11 05:14:20. <1> upload_fetch_column_conflict account_balance (no script)
I. 2011-07-11 05:14:20. <1> Update row (new remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   400
I. 2011-07-11 05:14:20. <1>   6000
I. 2011-07-11 05:14:20. <1> Update row (new remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   500
I. 2011-07-11 05:14:20. <1>   5000
I. 2011-07-11 05:14:20. <1> Update row (new remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   600
I. 2011-07-11 05:14:20. <1>   5000
I. 2011-07-11 05:14:20. <1> System event:
                        rollback tran  it0
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        rollback tran  it0
W. 2011-07-11 05:14:20. <1> [10039] Error detected during multi-row operation, performing rollback before retrying in single row mode
I. 2011-07-11 05:14:20. <1> upload_update account_balance
                                                 UPDATE account_balance                            SET amount = {ml r.amount}                          WHERE account_number = {ml r.account_number}
I. 2011-07-11 05:14:20. <1> Translated SQL:
                                                 UPDATE account_balance                            SET amount =  ?                          WHERE account_number =  ?
I. 2011-07-11 05:14:20. <1> upload_fetch account_balance (no script)
I. 2011-07-11 05:14:20. <1> upload_fetch_column_conflict account_balance (no script)
I. 2011-07-11 05:14:20. <1> Update row (new remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   400
I. 2011-07-11 05:14:20. <1>   6000
I. 2011-07-11 05:14:20. <1> Update row (new remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   500
I. 2011-07-11 05:14:20. <1>   5000
I. 2011-07-11 05:14:20. <1> Update row (old remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   500
I. 2011-07-11 05:14:20. <1>   0
I. 2011-07-11 05:14:20. <1> The row will be processed as in-conflict because the consolidated row no longer exists
I. 2011-07-11 05:14:20. <1> upload_new_row_insert account_balance (no script)
I. 2011-07-11 05:14:20. <1> upload_old_row_insert account_balance (no script)
W. 2011-07-11 05:14:20. <1> [10037] Ignoring updated row (new values)
W. 2011-07-11 05:14:20. <1> [10038] Ignoring updated row (old values)
I. 2011-07-11 05:14:20. <1> resolve_conflict account_balance (no script)
W. 2011-07-11 05:14:20. <1> [10072] The update row for table 'account_balance' is a conflict update and this row is ignored
I. 2011-07-11 05:14:20. <1> Update row (new remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   600
I. 2011-07-11 05:14:20. <1>   5000
I. 2011-07-11 05:14:20. <1> Update row (old remote values) [account_balance]:
I. 2011-07-11 05:14:20. <1>   600
I. 2011-07-11 05:14:20. <1>   0
I. 2011-07-11 05:14:20. <1> The row will be processed as in-conflict because the consolidated row no longer exists
W. 2011-07-11 05:14:20. <1> [10037] Ignoring updated row (new values)
W. 2011-07-11 05:14:20. <1> [10038] Ignoring updated row (old values)
W. 2011-07-11 05:14:20. <1> [10072] The update row for table 'account_balance' is a conflict update and this row is ignored
I. 2011-07-11 05:14:20. <1> end_upload_rows account_balance (no script)
I. 2011-07-11 05:14:20. <1> end_upload account_balance (no script)
I. 2011-07-11 05:14:20. <1> end_upload <connection> (no script)
W. 2011-07-11 05:14:20. <1> [10040] 2 row(s) were ignored in uploading data into table account_balance
I. 2011-07-11 05:14:20. <1> # rows uploaded into table account_balance : 3
I. 2011-07-11 05:14:20. <1> # rows inserted into table account_balance : 0
I. 2011-07-11 05:14:20. <1> # rows deleted in table account_balance : 0
I. 2011-07-11 05:14:20. <1> # rows updated into table account_balance : 1
I. 2011-07-11 05:14:20. <1> # rows conflicted in table account_balance : 0
I. 2011-07-11 05:14:20. <1> # rows ignored in table account_balance : 2
I. 2011-07-11 05:14:20. <1> upload_statistics account_balance (no script)
I. 2011-07-11 05:14:20. <1> upload_statistics <connection> (no script)
I. 2011-07-11 05:14:20. <1> System event:
                        SELECT {FN NOW()}
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        SELECT {FN NOW()}
I. 2011-07-11 05:14:20. <1> System event:
                        INSERT INTO ml_subscription ( rid, subscription_id, user_id, publication_name ) VALUES( ?, ?, ?, ? )
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        INSERT INTO ml_subscription ( rid, subscription_id, user_id, publication_name ) VALUES( ?, ?, ?, ? )
I. 2011-07-11 05:14:20. <1> System event:
                        UPDATE ml_subscription SET user_id = ?, progress = ?, last_upload_time = ? WHERE rid = ? AND subscription_id = ?
I. 2011-07-11 05:14:20. <1> Translated SQL:
                        UPDATE ml_subscription SET user_id = ?, progress = ?, last_upload_time = ? WHERE rid = ? AND subscription_id = ?
I. 2011-07-11 05:14:20. <1> COMMIT Transaction: end_upload
I. 2011-07-11 05:14:20. <1> end_publication <connection> (no script)
I. 2011-07-11 05:14:20. <1> end_synchronization account_balance (no script)
I. 2011-07-11 05:14:20. <1> end_synchronization <connection> (no script)
I. 2011-07-11 05:14:20. <1> synchronization_statistics account_balance (no script)
I. 2011-07-11 05:14:20. <1> synchronization_statistics <connection> (no script)
I. 2011-07-11 05:14:20. <1> time_statistics account_balance (no script)
I. 2011-07-11 05:14:20. <1> time_statistics <connection> (no script)
I. 2011-07-11 05:14:20. <1> COMMIT Transaction: end_synchronization
I. 2011-07-11 05:14:20. <1> Synchronization complete
I. 2011-07-11 05:14:20. <1> PHASE: start_time: 2011-07-11 05:14:19.938
I. 2011-07-11 05:14:20. <1> PHASE: duration: 139
I. 2011-07-11 05:14:20. <1> PHASE: sync_request: 0
I. 2011-07-11 05:14:20. <1> PHASE: receive_upload: 38
I. 2011-07-11 05:14:20. <1> PHASE: get_db_worker: 1
I. 2011-07-11 05:14:20. <1> PHASE: connect: 17
I. 2011-07-11 05:14:20. <1> PHASE: authenticate_user: 20
I. 2011-07-11 05:14:20. <1> PHASE: begin_sync: 17
I. 2011-07-11 05:14:20. <1> PHASE: apply_upload: 30
I. 2011-07-11 05:14:20. <1> PHASE: prepare_for_download: 0
I. 2011-07-11 05:14:20. <1> PHASE: fetch_download: 0
I. 2011-07-11 05:14:20. <1> PHASE: wait_for_download_ack: 0
I. 2011-07-11 05:14:20. <1> PHASE: end_sync: 16
I. 2011-07-11 05:14:20. <1> PHASE: send_download: 0
I. 2011-07-11 05:14:20. <1> PHASE: get_db_worker_for_download_ack: 0
I. 2011-07-11 05:14:20. <1> PHASE: connect_for_download_ack: 0
I. 2011-07-11 05:14:20. <1> PHASE: nonblocking_download_ack: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: TCP_CONNECTIONS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: PAGES_USED: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: PAGES_LOCKED: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: PAGES_LOCKED_MAX: 12768
I. 2011-07-11 05:14:57. <main> PERIODIC: TCP_CONNECTIONS_OPENED: 1
I. 2011-07-11 05:14:57. <main> PERIODIC: TCP_CONNECTIONS_CLOSED: 1
I. 2011-07-11 05:14:57. <main> PERIODIC: TCP_CONNECTIONS_REJECTED: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: TCP_BYTES_READ: 421
I. 2011-07-11 05:14:57. <main> PERIODIC: TCP_BYTES_WRITTEN: 272
I. 2011-07-11 05:14:57. <main> PERIODIC: ML_NUM_CONNECTED_CLIENTS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: PAGES_SWAPPED_OUT: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: PAGES_SWAPPED_IN: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: PAGES_IN_STREAMSTACK: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: CPU_USAGE: 312002
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_COMMITS: 4
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_ROLLBACKS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_SUCCESS_SYNCS: 1
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_FAILED_SYNCS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_ERRORS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_WARNINGS: 12
I. 2011-07-11 05:14:57. <main> PERIODIC: DB_CONNECTIONS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: RAW_TCP_STAGE_LEN: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: STREAM_STAGE_LEN: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: HEARTBEAT_STAGE_LEN: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: CMD_PROCESSOR_STAGE_LEN: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_ROWS_DOWNLOADED: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_ROWS_UPLOADED: 3
I. 2011-07-11 05:14:57. <main> PERIODIC: FREE_DISK_SPACE: 117667745792
I. 2011-07-11 05:14:57. <main> PERIODIC: LONGEST_DB_WAIT: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: LONGEST_SYNC: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: MEMORY_USED: 69218304
I. 2011-07-11 05:14:57. <main> PERIODIC: SERVER_IS_PRIMARY: 1
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_CONNECTED_SYNCS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_CONNECTED_PINGS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_CONNECTED_FILE_XFERS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_CONNECTED_MONITORS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_CONNECTED_LISTENERS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_WAITING_CONS: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_SYNC_REQUEST: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_RECVING_UPLOAD: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_CONNECT: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_AUTH_USER: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_BEGIN_SYNC: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_APPLY_UPLOAD: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_PREP_FOR_DNLD: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_FETCH_DNLD: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_END_SYNC: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_SEND_DNLD: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_WAIT_FOR_DNLD_ACK: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_GET_DB_WORKER_FOR_ACK: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_CONNECT_FOR_ACK: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_IN_NON_BLOCKING_ACK: 0
I. 2011-07-11 05:14:57. <main> PERIODIC: NUM_UPLOAD_CONNS_IN_USE: 0
I. 2011-07-11 05:15:00. <main> System event:
                        SELECT count(*) FROM ml_passthrough_script
I. 2011-07-11 05:15:00. <main> Translated SQL:
                        SELECT count(*) FROM ml_passthrough_script
I. 2011-07-11 05:15:00. <main> MobiLink server shutting down
I. 2011-07-11 05:15:00. <main> MobiLink server undergoing hard shutdown
I. 2011-07-11 05:15:00. <main> All connections and synchronizations being terminated
I. 2011-07-11 05:15:03. <main> end_connection <connection> (no script)
I. 2011-07-11 05:15:03. <main> COMMIT Transaction: end_connection
I. 2011-07-11 05:15:03. <main> Disconnected from consolidated database
I. 2011-07-11 05:15:03. <main> Disconnected

MobiLink client diagnostic log...

I. 2011-07-11 05:14:18. SQL Anywhere MobiLink Client Version 11.0.1.2587
I. 2011-07-11 05:14:18. 
I. 2011-07-11 05:14:18. Copyright © 2001-2009, iAnywhere Solutions, Inc.
I. 2011-07-11 05:14:18. Portions copyright © 1988-2009, Sybase, Inc. All rights reserved.
I. 2011-07-11 05:14:18. Use of this software is governed by the Sybase License Agreement. Refer to http://www.sybase.com/softwarelicenses
I. 2011-07-11 05:14:18. 
I. 2011-07-11 05:14:18. 1: -c
I. 2011-07-11 05:14:18. 2: *********************************
I. 2011-07-11 05:14:18. 3: -e
I. 2011-07-11 05:14:18. 4: adr='host=localhost';sv=v1
I. 2011-07-11 05:14:18. 5: -o
I. 2011-07-11 05:14:18. 6: dbmlsync_log_remo_from_step_9.txt
I. 2011-07-11 05:14:18. 7: -os
I. 2011-07-11 05:14:18. 8: 10M
I. 2011-07-11 05:14:18. 9: -uo
I. 2011-07-11 05:14:18. 10: -vnorsu
I. 2011-07-11 05:14:18. 11: -x
I. 2011-07-11 05:14:18. Connecting to remote database
I. 2011-07-11 05:14:18. Inserting initial SQL Passthrough state into database.
I. 2011-07-11 05:14:18. Loading synchronization information
I. 2011-07-11 05:14:18. Begin synchronizing 'p1' for MobiLink user '1'
I. 2011-07-11 05:14:18. Options for this synchronization:
I. 2011-07-11 05:14:18.         SV=v1,CTP=TCPIP,ADR='host=localhost'
I. 2011-07-11 05:14:18. Log scan starting at offset 0000474959
I. 2011-07-11 05:14:18. Processing transaction logs from directory "C:\\projects\\$SA_templates\\MobiLink\\MobiLink_demo_11_MSS_SA_preserve_transaction_order\\"
I. 2011-07-11 05:14:18. Processing transactions from active transaction log
I. 2011-07-11 05:14:19. Transaction log renamed to: 110711AA.LOG
I. 2011-07-11 05:14:19. Processing transaction logs from directory "C:\\projects\\$SA_templates\\MobiLink\\MobiLink_demo_11_MSS_SA_preserve_transaction_order\\"
I. 2011-07-11 05:14:19. Transaction log "C:\\projects\\$SA_templates\\MobiLink\\MobiLink_demo_11_MSS_SA_preserve_transaction_order\\110711AA.LOG" starts at offset 0000470263
I. 2011-07-11 05:14:19. Processing transactions from transaction log "C:\\projects\\$SA_templates\\MobiLink\\MobiLink_demo_11_MSS_SA_preserve_transaction_order\\110711AA.LOG"
I. 2011-07-11 05:14:19. Transaction log ends at offset 0000475596
I. 2011-07-11 05:14:19. Processing transactions from active transaction log
I. 2011-07-11 05:14:19. Hovering at end of active log
I. 2011-07-11 05:14:19. Log scan ended at offset 0000475556
I. 2011-07-11 05:14:19. Connecting to MobiLink server at 'host=localhost' using 'TCPIP'
I. 2011-07-11 05:14:19. Begin upload
I. 2011-07-11 05:14:19. Character set: windows-1252
I. 2011-07-11 05:14:19. MobiLink user name: 1
I. 2011-07-11 05:14:19. Script version: v1
I. 2011-07-11 05:14:19. Remote ID: ddbafee1-1d87-49be-8ef0-4feab955c657
I. 2011-07-11 05:14:19. Publication 'p1' - Synchronizing - Log offset 0000474959 - Last download time 1900-01-01 00:00:00.0.
I. 2011-07-11 05:14:19. Publication 'p1' has never been synchronized. Progress offsets will not be checked.
I. 2011-07-11 05:14:19. Article table: account_balance
I. 2011-07-11 05:14:19. Article column: varchar          account_number(10) NOT NULL PRIMARY KEY
I. 2011-07-11 05:14:19. Article column: decimal          amount(15,2) NOT NULL
I. 2011-07-11 05:14:19. Table Upload Order: account_balance
I. 2011-07-11 05:14:19. Processor is little-endian
I. 2011-07-11 05:14:19. Uploading table operations
I. 2011-07-11 05:14:19. Upload operations on table 'account_balance'
I. 2011-07-11 05:14:19. Update row:
I. 2011-07-11 05:14:19.  Preimage:
I. 2011-07-11 05:14:19.   <account_number>: 400
I. 2011-07-11 05:14:19.   <amount>: 0
I. 2011-07-11 05:14:19.  Postimage:
I. 2011-07-11 05:14:19.   <account_number>: 400
I. 2011-07-11 05:14:19.   <amount>: 6000
I. 2011-07-11 05:14:19. Update row:
I. 2011-07-11 05:14:19.  Preimage:
I. 2011-07-11 05:14:19.   <account_number>: 500
I. 2011-07-11 05:14:19.   <amount>: 0
I. 2011-07-11 05:14:19.  Postimage:
I. 2011-07-11 05:14:19.   <account_number>: 500
I. 2011-07-11 05:14:19.   <amount>: 5000
I. 2011-07-11 05:14:19. Update row:
I. 2011-07-11 05:14:19.  Preimage:
I. 2011-07-11 05:14:19.   <account_number>: 600
I. 2011-07-11 05:14:19.   <amount>: 0
I. 2011-07-11 05:14:19.  Postimage:
I. 2011-07-11 05:14:19.   <account_number>: 600
I. 2011-07-11 05:14:19.   <amount>: 5000
I. 2011-07-11 05:14:19. # rows inserted in table account_balance : 0
I. 2011-07-11 05:14:19. # rows deleted in table account_balance : 0
I. 2011-07-11 05:14:19. # rows updated in table account_balance : 3
I. 2011-07-11 05:14:19. Waiting for MobiLink to apply upload
I. 2011-07-11 05:14:19. Sending schema information to MobiLink server.
I. 2011-07-11 05:14:19. Article table: account_balance
I. 2011-07-11 05:14:19. Article column: varchar          account_number(10) NOT NULL PRIMARY KEY
I. 2011-07-11 05:14:19. Article column: decimal          amount(15,2) NOT NULL
I. 2011-07-11 05:14:19. Table Upload Order: account_balance
I. 2011-07-11 05:14:19. Processor is little-endian
I. 2011-07-11 05:14:20. The user authentication value is 1000.
I. 2011-07-11 05:14:20. Setting last upload time to 2011-07-11 05:14:20.05.
I. 2011-07-11 05:14:20. Setting generation number for publication p1 to 1.
I. 2011-07-11 05:14:20. COMMIT
I. 2011-07-11 05:14:20. Passthrough action is none
I. 2011-07-11 05:14:20. Disconnecting from MobiLink server
I. 2011-07-11 05:14:20. Complete log scan required.
I. 2011-07-11 05:14:20. Synchronization completed
I. 2011-07-11 05:14:20. Disconnecting from remote database

Consolidated table as it existed after synchronization...

Note that all three row updates to 400, 500 and 600 were successfully uploaded.

SELECT * FROM account_balance ORDER BY account_number
 account_number amount            limit_exceeded
 -------------- ----------------- --------------
 100                      6000.00 Y
 200                      5000.00 Y
 300                      5000.00 Y
 400                      6000.00 Y
 500                      5000.00 N
 600                      5000.00 N
 700                          .00 N
 800                          .00 N
 900                          .00 N

(9 rows affected)
1>

Remote table as it existed after synchronization...

account_number,amount
'400',6000.00
'500',5000.00
'600',5000.00
'700',0.00
'800',0.00
'900',0.00
Show replies
Show replies
Breck_Carter
Participant
‎2011 Jul 10 6:08 PM
0 Kudos

The entire reproducible will be on my blog tomorrow ...as a demonstration, not a bug report 🙂

BTW, it's transactional upload, so it starts off being funky.

philippefbertrand
Participant
‎2011 Jul 10 8:54 PM
0 Kudos

Hey! If you post a question, you have to post all the information here so it can be answered here and become part of the community.

former_SQLA_member1694874
Participant
‎2011 Jul 11 5:59 AM

Lecturing Breck Carter on how to be part of the community, LMAO, him and @Volker practically are the community. I understand your point but maybe you need to be more careful how you come across next time.

Answer

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
‎2011 Jul 11 12:57 PM

To answer the question in the subject, use -s 1 on the mlsrv11 command line.

I don't see SET NOCOUNT ON in your log output, but it can cause what you are seeing with MSS. If you have that set MobiLink 11 will think every uploaded update is a conflict.

Before version 12, even if you implemented Resolving conflicts with upload_update scripts (or didn't need conflict detection or handling) the MobiLink server would still try to do Detecting conflicts with upload_update scripts: it would still check that the count of changed rows matches the number of rows ML was trying to update.

If you have SET NOCOUNT ON in your upload_update script, the changed-row count never matches, so ML incorrectly detects a conflict (after incorrectly retrying in single row mode) for every update row. It then tries to call the ML conflict resolution scripts, but none of those are defined, so the ML server issues warnings that the update row was ignored (since it doesn't know that the upload_update actually worked).

Similarly, if you have any AFTER triggers the MobiLink server will get the row count from the trigger instead of from your upload_update script, though apparently using SET NOCOUNT ON in all such triggers is a workaround. That seems to be happening for you, since the MobiLink server log reports the trigger row counts: 1 row updated and 2 rows ignored.

As of version 12, the row counting to detect conflicts is only done if:

  • No upload_fetch or upload_fetch_column_conflict script is defined.

  • One or both of the upload_new_row_insert or upload_old_row_insert scripts is defined.

So with version 12 you would not be getting any of the warnings or unneeded extra processing that you are seeing with version 11. For version 11, to get rid of the warnings and extra processing I think you need NOCOUNT OFF for your upload_update script and NOCOUNT ON in all your triggers (as well as in all stored procedures and SQL batches executed by MobiLink

Show replies
Show replies
Breck_Carter
Participant
‎2011 Jul 12 4:07 PM
0 Kudos

Arrgh... I have seen (and written) SET NOCOUNT ON enough times to know better... however, knowing why is another thing.

Yes, SET NOCOUNT ON appeared nowhere in this code, until now.

Adding SET NOCOUNT ON to the trigger, and nowhere else, made it all work ok.

I am confused, however... you seem to imply that I should NOT just load up my shotgun with SET NOCOUNT ON statements and blast them into every script... that I "need NOCOUNT OFF for your upload_update script". It works when that is true (by default) but will it not also work with SET NOCOUNT ON in the upload_update???

I will experiment...

Breck_Carter
Participant
‎2011 Jul 12 4:54 PM
0 Kudos

It appears that a simple upload_update script like the following does not need (and should not have) SET NOCOUNT ON added to it: 

EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_update', '
UPDATE account_balance
   SET amount = {ml r.amount}
 WHERE account_number = {ml r.account_number}' 
GO
Here is an attempt to add it: 
EXECUTE ml_add_table_script  'v1', 'account_balance', 'upload_update', '
BEGIN
SET NOCOUNT ON
UPDATE account_balance
   SET amount = {ml r.amount}
 WHERE account_number = {ml r.account_number}
END' 
GO
Here is what happens when you do that; it throws a different warning, reverts to single-row, throws the warning again, and ends up doing the right thing: 
W. 2011-07-12 15:54:46. <1> [10081] Unable to retrieve the column data types for table 'account_balance' from the consolidated database
...
W. 2011-07-12 15:54:46. <1> [10039] Error detected during multi-row operation, performing rollback before retrying in single row mode
...
I. 2011-07-12 15:54:46. <1> The row will be processed as in-conflict because the consolidated row no longer exists
...
W. 2011-07-12 15:54:46. <1> [10072] The update row for table 'account_balance' is a conflict update and this row is ignored
I. 2011-07-12 15:54:46. <1> Update row (new remote values) [account_balance]:
I. 2011-07-12 15:54:46. <1>   500
I. 2011-07-12 15:54:46. <1>   5000
I. 2011-07-12 15:54:46. <1> Update row (new remote values) [account_balance]:
I. 2011-07-12 15:54:46. <1>   600
I. 2011-07-12 15:54:46. <1>   5000
I. 2011-07-12 15:54:46. <1> end_upload_rows account_balance (no script)
I. 2011-07-12 15:54:46. <1> end_upload account_balance (no script)
I. 2011-07-12 15:54:46. <1> end_upload <connection> (no script)
W. 2011-07-12 15:54:46. <1> [10040] 1 row(s) were ignored in uploading data into table account_balance
I. 2011-07-12 15:54:46. <1> # rows uploaded into table account_balance : 3
I. 2011-07-12 15:54:46. <1> # rows inserted into table account_balance : 0
I. 2011-07-12 15:54:46. <1> # rows deleted in table account_balance : 0
I. 2011-07-12 15:54:46. <1> # rows updated into table account_balance : 2
I. 2011-07-12 15:54:46. <1> # rows conflicted in table account_balance : 0
I. 2011-07-12 15:54:46. <1> # rows ignored in table account_balance : 1

Breck_Carter
Participant
‎2011 Jul 12 4:56 PM

Question: If the upload_update script EXECs a stored procedure to do the actual work, should that stored procedure contain SET NOCOUNT ON?

I am happy to wave dead chickens over the keyboard, but if there are rules about when and when NOT to do that, that's nice to know too 🙂

Breck_Carter
Participant
‎2011 Jul 12 5:00 PM
0 Kudos

Question: None of this SET NOCOUNT ON carp applies to ASE, correct? (the hilarious part is that in this case MSS is standing in for ASE which I haven't installed yet 🙂

Former Member
‎2011 Jul 12 5:02 PM
0 Kudos

Right. That's why our doc says to use SET NOCOUNT ON for stored procedures or SQL batches. It's okay to not have it for a single SQL statement. As you've found, before version 12 of MobiLink you need NOCOUNT OFF for just the UPDATE statement in your upload_update script to avoid false detections of conflicts. (I'm not sure if it works to just turn it off for the UPDATE statement when you are using a stored procedure.)

Former Member
‎2011 Jul 12 5:12 PM
0 Kudos

That is mentioned at the end of the doc topic I referenced, but its advice to do both the conflict detection and resolution in the stored procedure doesn't prevent the false detection until version 12.

The only way to avoid the retrying in single-row mode is to have the MSS @@ROWCOUNT value after an upload_update batch match the number of rows ML tried to update. For that to work when multiple updates are uploaded, you might have to use -s 1.

A strong reason to upgrade to version 12 if you insist on using an MSS consolidated and don't want a noisy log or reduced performance for uploaded updates.

Former Member
‎2011 Jul 12 5:15 PM

No it doesn't apply to ASE. My recollection is that MS added that ill-advised "feature" to support a GUI tool they created.

Answers (1)

Answers (1)

jeff_albion
Advisor
Advisor
‎2011 Jul 11 12:05 PM
0 Kudos

How do I force MobiLink to work in single row mode?

Use the "mlsrv11 -s 1" switch.

It is vastly confusing, not to mention inefficient, to see a trigger fired N*2 times when only N rows have been uploaded. How do I stop this from happening?

This situation is occurring because of the way the MobiLink server is currently processing conflicts on this table:

I. 2011-07-11 05:14:20. <1> upload_fetch account_balance (no script)
I. 2011-07-11 05:14:20. <1> upload_fetch_column_conflict account_balance (no script)
...
W. 2011-07-10 14:00:06. <1> [10039] Error detected during multi-row operation, performing rollback before retrying in single row mode
...
W. 2011-07-11 05:14:20. <1> [10072] The update row for table 'account_balance' is a conflict update and this row is ignored
...
W. 2011-07-11 05:14:20. <1> [10040] 2 row(s) were ignored in uploading data into table account_balance

Since you have not defined an error-handling mechanism for this table, the default operation is to try the multi-row operation again in single-row mode (which again fails due to the missing conflict resolution scripts).

You will need to define a conflict-resolution mechanism, which will avoid the logic error, which will avoid the "duplicate" insert/firing of the triggers.

Show replies
Show replies
Ask a Question