|
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:
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; |
|
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. 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 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} 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
|