Have had a MobiLink sync running successfully for some time now. Have just added 2 new tables, 2 delete shadow tables, and associated scripts and now mlsrv11.exe crashes when selecting from the first new delete shadow table encountered.

While I plan to open a support incident to submit the crash dump, I am guessing that I have just missed something simple to cause the problem in the first place and I am hoping to get the sync working.

ORACLE consolidated: I. 2011-12-20 09:58:10. <main> ODBC DBMS Name: Oracle I. 2011-12-20 09:58:10. <main> ODBC DBMS Version: 10.02.0000 I. 2011-12-20 09:58:10. <main> ODBC DBMS Driver Version: 11.00.0000

SQL Anywhere: MobiLink Server: 11.0.1.2652 MobiLink Client: Dbmlsync Version 11.0.1.2652

The last few lines of the MobiLink server log are:

>                                   SYNC_ERROR_MSG,
>                                       CREATED_DATE,
>                                       PROCESSED_DATE
>                                   FROM INTK_SYNC_LOG
>                          WHERE  to_char(INTK_SYNC_LOG.OFFICE_ORGANIZATION_ID)
> = SUBSTR( :p1, -3, 3) AND
>                           INTK_SYNC_LOG.processed_date >  :p2 I.
> 2011-12-20 10:27:52. <1> System
> variable username value: SRVAA995 I.
> 2011-12-20 10:27:52. <1> System
> variable last_table_download value:
> 2011-12-19 12:36:53.486000 I.
> 2011-12-20 10:27:52. <1>
> end_download_rows INTK_SYNC_LOG (no
> script) I. 2011-12-20 10:27:52. <1>
> begin_download_deletes
> MEMO_SYNC_HEADER (no script) I.
> 2011-12-20 10:27:52. <1>
> download_delete_cursor
> MEMO_SYNC_HEADER
>                         
>                              SELECT DELETED_MEMO_SYNC_HEADER.MEMO_SYNC_SID
>                                     FROM DELETED_MEMO_SYNC_HEADER
>                                     WHERE
> to_char(DELETED_MEMO_SYNC_HEADER.BP_ORGANIZATION_ID)
> = SUBSTR({ml s.username}, -3, 3) AND
>                           DELETED_MEMO_SYNC_HEADER.DELETED_ON >
> ? I. 2011-12-20 10:27:52. <1>
> Translated SQL:
>                         
>                              SELECT DELETED_MEMO_SYNC_HEADER.MEMO_SYNC_SID
>                                     FROM DELETED_MEMO_SYNC_HEADER
>                                     WHERE
> to_char(DELETED_MEMO_SYNC_HEADER.BP_ORGANIZATION_ID)
> = SUBSTR( :p1, -3, 3) AND
>                           DELETED_MEMO_SYNC_HEADER.DELETED_ON >
> :p2 I.    2011-12-20 10:27:52. <1> System
> variable username value: SRVAA995

The sync has 2 Publications:

  • AgencyToMCFDPublication
  • This Pub contains a number of tables, with only upload insert, upload update and upload delete scripts.
  • MCFDTOAGENCYDOWNLOADONLYPUBLICATION
  • CREATE PUBLICATION MCFDTOAGENCYDOWNLOADONLYPUBLICATION FOR DOWNLOAD ONLY (TABLE INTK_SYNC_LOG);

The script we just ran against the Oracle consolidated database is:

--CREATE DELETE SHADOW TABLE FOR MEMO_SYNC_DETAILS
CREATE TABLE DELETED_MEMO_SYNC_DETAILS (
    ACTION_TYPE     VARCHAR2 (4)    NOT NULL,
    MEMO_SYNC_SID       INTEGER     NOT NULL,
    BP_ORGANIZATION_ID  INTEGER,
    DELETED_ON      TIMESTAMP   DEFAULT SYSTIMESTAMP NOT NULL )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE &&TABLE_TS;

ALTER TABLE DELETED_MEMO_SYNC_DETAILS
    ADD CONSTRAINT DELETED_MEMO_SYNC_DETAILS_PK 
    PRIMARY KEY(
        ACTION_TYPE,
        MEMO_SYNC_SID,
                 DELETED_ON)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE &&INDEX_TS  ENABLE;

--CREATE DELETE SHADOW TABLE FOR MEMO_SYNC_HEADER
CREATE TABLE DELETED_MEMO_SYNC_HEADER (
    MEMO_SYNC_SID       INTEGER     NOT NULL,
    BP_ORGANIZATION_ID  INTEGER,
    DELETED_ON      TIMESTAMP   DEFAULT SYSTIMESTAMP NOT NULL )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE &&TABLE_TS;

ALTER TABLE DELETED_MEMO_SYNC_HEADER
    ADD CONSTRAINT DELETED_MEMO_SYNC_HEADER_PK 
    PRIMARY KEY(
        MEMO_SYNC_SID,
                 DELETED_ON)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE &&INDEX_TS  ENABLE;

--CREATE TRIGGER TO WRITE DELETED PKS TO DELETE SHADOW TABLE FOR MEMO_SYNC_DETAILS
CREATE OR REPLACE TRIGGER TD_ML_DELETE_MEMO_SYNC_DETAILS
  BEFORE DELETE ON MEMO_SYNC_DETAILS
  FOR EACH ROW
    DECLARE li_bp_organization_id   INTEGER;
BEGIN
    SELECT BP_ORGANIZATION_ID INTO li_bp_organization_id    FROM MEMO_SYNC_HEADER WHERE :old.MEMO_SYNC_SID = MEMO_SYNC_HEADER.MEMO_SYNC_SID;

    INSERT INTO DELETED_MEMO_SYNC_DETAILS
        (ACTION_TYPE,
        MEMO_SYNC_SID,
        BP_ORGANIZATION_ID)
        VALUES
            (:old.ACTION_TYPE,
            :old.MEMO_SYNC_SID,
            li_bp_organization_id);
END;

--CREATE TRIGGER TO WRITE DELETED PKS TO DELETE SHADOW TABLE FOR MEMO_SYNC_HEADER
CREATE OR REPLACE TRIGGER TD_ML_DELETE_MEMO_SYNC_HEADER 
  BEFORE DELETE ON MEMO_SYNC_HEADER
  FOR EACH ROW
BEGIN
  INSERT INTO DELETED_MEMO_SYNC_HEADER
    (MEMO_SYNC_SID,
    BP_ORGANIZATION_ID)
    VALUES
      (:old.MEMO_SYNC_SID,
    :old.BP_ORGANIZATION_ID);
END;

----------SCRIPTS FOR TABLE MEMO_SYNC_DETAILS -------------------------------------------
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', '
     INSERT INTO MEMO_SYNC_DETAILS (
          ACTION_TYPE,
          MEMO_SYNC_SID,
          PAYLOAD,
          SEND_DATE,
          PROCESSED_DATE)
          VALUES (
               {ml r.ACTION_TYPE},
               {ml r.MEMO_SYNC_SID},
               {ml r.PAYLOAD},
               {ml r.SEND_DATE},
               {ml r.PROCESSED_DATE})');

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_update', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_update', '
     UPDATE MEMO_SYNC_DETAILS SET
          PAYLOAD       = {ml r.PAYLOAD},
          SEND_DATE     = {ml r.SEND_DATE},
          PROCESSED_DATE = {ml r.PROCESSED_DATE}
           WHERE ACTION_TYPE   = {ml r.ACTION_TYPE}
             AND MEMO_SYNC_SID = {ml r.MEMO_SYNC_SID}');

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'download_delete_cursor', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'download_delete_cursor', '
     SELECT ACTION_TYPE, 
            MEMO_SYNC_SID
            FROM DELETED_MEMO_SYNC_DETAILS
        WHERE  to_char(DELETED_MEMO_SYNC_DETAILS.BP_ORGANIZATION_ID) = SUBSTR({ml s.username}, -3, 3) AND
          DELETED_MEMO_SYNC_DETAILS.DELETED_ON > ?');

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'download_cursor', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'download_cursor', '
     SELECT ACTION_TYPE, 
            MEMO_SYNC_DETAILS.MEMO_SYNC_SID, 
            PAYLOAD, 
            SEND_DATE, 
            PROCESSED_DATE
          FROM  MEMO_SYNC_DETAILS,
        MEMO_SYNC_HEADER
     WHERE  MEMO_SYNC_HEADER.MEMO_SYNC_SID = MEMO_SYNC_DETAILS.MEMO_SYNC_SID AND
        to_char(MEMO_SYNC_HEADER.BP_ORGANIZATION_ID) = SUBSTR({ml s.username}, -3, 3) AND
        MEMO_SYNC_DETAILS.LAST_UPDATE > ?');

CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', NULL,          NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'ACTION_TYPE',     NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'MEMO_SYNC_SID',   NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'PAYLOAD',     NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'SEND_DATE',       NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'PROCESSED_DATE',  NULL);
COMMIT;

----------SCRIPTS FOR TABLE MEMO_SYNC_HEADER --------------------------------------------
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'upload_insert', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'upload_insert', '
     INSERT INTO MEMO_SYNC_HEADER(
          BP_ORGANIZATION_ID,
          BP_OFFICE_ID,
          MEMO_SYNC_SID)
          VALUES(
               {ml r.BP_ORGANIZATION_ID},
               {ml r.BP_OFFICE_ID},
               {ml r.MEMO_SYNC_SID})');

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'upload_update', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'upload_update', '
     UPDATE MEMO_SYNC_HEADER SET
          BP_ORGANIZATION_ID = {ml r.BP_ORGANIZATION_ID},
          BP_OFFICE_ID       = {ml r.BP_OFFICE_ID}
           WHERE MEMO_SYNC_SID = {ml r.MEMO_SYNC_SID}');

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'download_delete_cursor', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'download_delete_cursor', '
     SELECT DELETED_MEMO_SYNC_HEADER.MEMO_SYNC_SID
            FROM DELETED_MEMO_SYNC_HEADER
            WHERE to_char(DELETED_MEMO_SYNC_HEADER.BP_ORGANIZATION_ID) = SUBSTR({ml s.username}, -3, 3) AND
        DELETED_MEMO_SYNC_HEADER.DELETED_ON > ?');

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'download_cursor', NULL);
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'download_cursor', '
     SELECT MEMO_SYNC_HEADER.MEMO_SYNC_SID, 
            MEMO_SYNC_HEADER.BP_ORGANIZATION_ID, 
            MEMO_SYNC_HEADER.BP_OFFICE_ID
          FROM  MEMO_SYNC_HEADER,
        MEMO_SYNC_DETAILS
            WHERE MEMO_SYNC_HEADER.MEMO_SYNC_SID = MEMO_SYNC_DETAILS.MEMO_SYNC_SID AND
          to_char(MEMO_SYNC_HEADER.BP_ORGANIZATION_ID) = SUBSTR({ml s.username}, -3, 3) AND
          MEMO SYNC DETAIL.LAST_UPDATE > ?');

CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', NULL, NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'MEMO_SYNC_SID', NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'BP_ORGANIZATION_ID', NULL);
CALL ml_add_column('ScriptVersion12.00.00', 'MEMO_SYNC_HEADER', 'BP_OFFICE_ID', NULL);
COMMIT;

asked 20 Dec '11, 15:24

Bill%20Aumen's gravatar image

Bill Aumen
2.0k294473
accept rate: 16%


ML server should never crash. I see you are not using the latest software. We just posted 11.0.1.2724. Please upgrade to that version and try again. If it still fails after upgrading, opening a support case is appropriate.

permanent link

answered 20 Dec '11, 16:54

RussC_FromSAP's gravatar image

RussC_FromSAP
1.3k11030
accept rate: 20%

I did already re-create the crash on our own DEV system using 2713. It takes a major amount of paperwork to upgrade the server we are testing against, so I'll open a support case next. Thanks Russ.

(20 Dec '11, 17:05) Bill Aumen

Now we know what was happening:

Problem #1: mixed named parameters with ? parameters This is what causes mlsrv11.exe to crash. Sybase engineers explained from our crash dumps exactly what was happening. I can hope in the future there will be an error message when parameters are mixed, but for now a crash is the expected outcome.

Problem #2: We have applied corrected scripts several times with ml_add_table_script, but the log files show the original script is still being executed. This is after complete service restarts. We have the Oracle guys working on that problem, not sure if it is 1) permissions?, 2) data corruption in mobilink tables??, or 3) other??? So I reported the crash still happened after correcting the script, but we now know from the log files that the script has never changed.

permanent link

answered 31 Dec '11, 22:11

Bill%20Aumen's gravatar image

Bill Aumen
2.0k294473
accept rate: 16%

You're positive the new script has the same version, right?

(01 Jan '12, 09:25) Breck Carter

99.99% (thats 100% minus an allowance for dumb mistakes). I copied-and-pasted the original script, then modified the '?' parameters. When doing selects from ml_table, ml_table_script and ml_script, I see that my new script is in the script table, but the reference to it in ml_table_script is no longer there.

(02 Jan '12, 19:01) Bill Aumen
1

Unless you are running with -zf you'll need to restart ML server when scripts change.

(03 Jan '12, 10:06) RussC_FromSAP

yes, we have done that. Checking one of our SQL Anywhere databases, I have confirmed that ml_script has a foreign key to ml_table_script (as I would expect to find). So the Oracle database obviously is missing that FK. I believe the dba "cloned" the Oracle database somehow, so it looks like some things are missing and/or corrupted.

(03 Jan '12, 15:30) Bill Aumen
1

In case anyone is still following along from home, I just wanted to make a quick comment on problem #1 from this post where the MobiLink Server was crashing when there was a mix of named parameters and questions marks.

It's my experience that the crash in the ML Server will only seems to occur in v11, AND it only happens when you mix the question mark and named parameters in the same script AND you attempt to bind the {ml s.username} system parameter to a column which is not a character column.

We're still investigating why the crash occurs, and whether we can properly return an "unable to open [script_name]" error.

(09 Jan '12, 16:30) Reg Domaratzki

It looks like your select in the download_delete_cursor is mixing named parameters and question marks. Replace the question mark with the named parameter: {ml s.last_table_download}

permanent link

answered 20 Dec '11, 15:38

RussC_FromSAP's gravatar image

RussC_FromSAP
1.3k11030
accept rate: 20%

Ah yes... Thanks Russ. I made that change, and I do remember I should not have mixed the methods, but the ML server still crashes.

(20 Dec '11, 16:41) Bill Aumen
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:

×278

question asked: 20 Dec '11, 15:24

question was seen: 1,440 times

last updated: 09 Jan '12, 16:30