Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Pro2 synchronization has stopped unexpectedly

Information

 
Article Number000096840
EnvironmentProduct: OpenEdge
Version: All supported versions
Product: Pro2SQL
Version: 5.x
OS: Windows
Other: Replication
Question/Problem Description
Pro2 synchronization has stopped unexpectedly.

Specific replication thread will not restart.

Replproc.log reports the following error messages:
SYSTEM ERROR: Assignment error. (6177)
22005: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification


Other replication threads in the instance start and run as expected.

No changes were made to the Pro2 configuration.

No changes were made to the SQL Server database.

Re-pulling the schema definitions from Microsoft SQL Server has no effect.

During a re-baseline of the data, a bulkload thread terminates unexpectedly

Per bulk-runner_C_ErrorsYYYYMMDD.log, 

MM/DD/YYYY hh:mm:ss.####+01:00:ERROR: An Error Occurred For The Thread8 Table:db.problemTable: Running bulkload 
This table did not load properly
Running bprepl\repl_mproc\mrdb.problemTable.p Token: "C_Thread-8_db.problemTable
SYSTEM ERROR: Assignment error. (6177)
22005: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
MM/DD/YYYY hh:mm:ss.####+01:00 Tables Failed so far: 1


Checking the first record in the ReplQueue table shows that it is from the problem table.

Attempting to replicate this specific record to the foreign database fails with the same errors.
 
Steps to Reproduce
Clarifying Information
No discrepancies found in the GenSQLDiff.log.
Error MessageSYSTEM ERROR: Assignment error. (6177)
22005: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Defect/Enhancement Number
Cause
Problem is related to the corrupt or invalid data in the database. In the scenario presented above, a decimal field in the problematic table contained an invalid value. For example,

1234?<56

The value cannot be converted to a decimal value so the replication process fails with a SYSTEM ERROR that terminated the replication thread process. 
The problem appeared to be limited to a specific field in the table. However, it is unknown how the invalid value made it into the field.
 
Resolution
Correct the invalid data.

If needed, scan the data to identify other instances of the invalid decimal value.
OUTPUT TO prgs_problemTable_decimal_corruptvalue.txt.

DEFINE VARIABLE cDecimal        AS CHARACTER    NO-UNDO.
DEFINE VARIABLE deDecimalTest   AS DECIMAL      NO-UNDO.

FOR EACH problemTable FIELDS (id problemDecimalField) NO-LOCK :
    
    cDecimal = STRING(problemTable.problemDecimalField).
    deDecimalTest = DECIMAL(cDecimal) NO-ERROR.
        
    // If the conversion from STRING to DECIMAL fails then output record info
    IF ERROR-STATUS:ERROR AND ERROR-STATUS:NUM-MESSAGES > 0 THEN DO:
        PUT UNFORMATTED problemTable.id "     " problemTable.problemDecimalField "     " STRING(ROWID(problemTable)) SKIP.
    END.

END.

OUTPUT CLOSE.

Once the corrupt or invalid data has been corrected or removed then Pro2 replication will resume as normal. 
No changes to the Pro2 configuration are required.
Workaround
Notes
Sample code to test replicating a specific record in the Replqueue table to the foreign database.
Replace values in <> with values from the affected environment.
 
&GLOBAL-DEFINE ReplDB <logical DB name>. 
&GLOBAL-DEFINE ReplDB2 <logical DB name>

SESSION:DEBUG-ALERT = TRUE.

LOG-MANAGER:LOGFILE-NAME = "testRepl.log".
LOG-MANAGER:LOGGING-LEVEL = 4.
LOG-MANAGER:LOG-ENTRY-TYPES = "4gltrace,4glmessages".

DSLOG-MANAGER:DB-CONTEXT = "<target schema image>".
DSLOG-MANAGER:LOGGING-LEVEL = 4.
DSLOG-MANAGER:LOG-ENTRY-TYPES = "debug".

DEFINE VARIABLE hProc     AS HANDLE    NO-UNDO.
DEFINE VARIABLE strRowid  AS CHARACTER NO-UNDO.
DEFINE VARIABLE strStatus AS CHARACTER NO-UNDO.
DEFINE VARIABLE strErrMsg AS CHARACTER NO-UNDO.

/* Setup ROWID - obtained from Replqueue.SrcRecord */
strRowid = "<ROWID of the Record>".

FIND FIRST {&ReplDB}ReplQueue NO-LOCK WHERE {&ReplDB}ReplQueue.Applied EQ FALSE and
                                            {&ReplDB}ReplQueue.SrcRecord = strRowid
                                            NO-ERROR.

IF AVAILABLE {&ReplDB}ReplQueue THEN 
DO:
    /* run replication procedure persistently */
    RUN value("bprepl\repl_proc\r{&ReplDB2}_" + {&ReplDB}ReplQueue.SrcTable + ".p") PERSISTENT SET hProc.

    /* replicate the record */
    RUN VALUE({&ReplDB}ReplQueue.SrcDB + "_" + {&ReplDB}ReplQueue.SrcTable + "_R") in hproc
            (BUFFER {&ReplDB}ReplQueue, 
             INPUT {&ReplDB}ReplQueue.EventType, 
             INPUT TO-ROWID({&ReplDB}ReplQueue.SrcRecord), 
             OUTPUT strStatus, 
             OUTPUT strErrMsg)
        .

    MESSAGE strStatus SKIP 
        strErrMsg
        VIEW-AS ALERT-BOX.

    DELETE OBJECT hProc.
END.
ELSE
    MESSAGE "Record not found" VIEW-AS ALERT-BOX.

Review testRepl.log and dataserv.lg log files for errors even if the code reports that the record was successfully replicated.
 
Attachment 
Last Modified Date6/14/2019 2:46 PM