View Issue Details

IDProjectCategoryView StatusLast Update
0003578SymmetricDSBugpublic2019-04-12 16:35
Reporterrajurudru Assigned Toelong  
Priorityhigh 
Status closedResolutionno change required 
Product Version3.9.7 
Summary0003578: Failed to create triggers for table StackTraceKey.init [SqlException:1627968287]
DescriptionSymmetricDS is failing to create trigger on a table in DB2 in IBM i (AS/400). This happens every time I start SymmetricDS. When this error occur, SymmetricDS stops with message
"AbstractSymmetricEngine - Stopping SymmetricDS externalId=000 version=3.9.7 database=DB2 UDB for AS/400"

Below is the stack trace for the error in creating the trigger.

org.jumpmind.db.sql.SqlException: [SQL0199] Keyword AND not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT. Cause . . . . . : The keyword AND was not expected here. A syntax error was detected at keyword AND. The partial list of valid tokens is < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:300)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
    at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:406)
    at org.jumpmind.db.sql.JdbcSqlTransaction.execute(JdbcSqlTransaction.java:297)
    at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createTrigger(AbstractSymmetricDialect.java:394)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:1802)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:1677)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:1552)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService$2.run(TriggerRouterService.java:1538)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword AND not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT. Cause . . . . . : The keyword AND was not expected here. A syntax error was detected at keyword AND. The partial list of valid tokens is < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.
    at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:881)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:682)
    at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1603)
    at com.ibm.as400.access.AS400JDBCStatement.execute(AS400JDBCStatement.java:1995)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:304)
    at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:297)
    at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:404)
    ... 11 more
Steps To Reproduce1. Connect to DB2 in iSeries using the JDBC driver for iSeries. (This is the master database in my case)
2. Create node_group, group_links, router, trigger on a table in DB2
3. Start SymmetricDS.
4. While Synchronizing the triggers. Symmetric DS fails while creating trigger on a table in DB2 as shown in the stack trace in the description.
Additional InformationThe Trigger query being generated by SymmetricDS

CREATE TRIGGER "HKRISHNA".SYM_ON_U_FOR_MHBSR_GLXY
                                AFTER UPDATE ON "HKRISHNA"."MHUBUSER"
                                REFERENCING OLD AS OLD NEW AS NEW
                                FOR EACH ROW MODE DB2SQL
                                BEGIN ATOMIC
                                     
                                    IF 1 and sym_trigger_disabled is null then
                                            INSERT into "HKRISHNA".sym_data
                                                (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)
                                            VALUES('mhubuser', 'U', 34,
                                                
          case when old."USER_ID" is null then '' else '"' || trim(char(old."USER_ID")) || '"' end,
                                                
          case when new."USER_ID" is null then '' else '"' || trim(char(new."USER_ID")) || '"' end||','||
          case when new."ACCNTNUM" is null then '' else '"' || trim(char(new."ACCNTNUM")) || '"' end||','||
          case when new."USERTYP" is null then '' else '"' || replace(replace(new."USERTYP",'\','\\'),'"','\"') || '"' end||','||
          case when new."USERNAME" is null then '' else '"' || replace(replace(new."USERNAME",'\','\\'),'"','\"') || '"' end||','||
          case when new."PASSWORD" is null then '' else '"' || replace(replace(new."PASSWORD",'\','\\'),'"','\"') || '"' end||','||
          case when new."SSN" is null then '' else '"' || trim(char(new."SSN")) || '"' end||','||
          case when new."ZIPCD" is null then '' else '"' || trim(char(new."ZIPCD")) || '"' end||','||
          case when new."EMAILID" is null then '' else '"' || replace(replace(new."EMAILID",'\','\\'),'"','\"') || '"' end||','||
          case when new."PASSWORD_QUES" is null then '' else '"' || replace(replace(new."PASSWORD_QUES",'\','\\'),'"','\"') || '"' end||','||
          case when new."PASSWORD_ANS" is null then '' else '"' || replace(replace(new."PASSWORD_ANS",'\','\\'),'"','\"') || '"' end||','||
          case when new."LIBRARY" is null then '' else '"' || replace(replace(new."LIBRARY",'\','\\'),'"','\"') || '"' end||','||
          case when new."COMP_LIBRARY" is null then '' else '"' || replace(replace(new."COMP_LIBRARY",'\','\\'),'"','\"') || '"' end||','||
          case when new."INSERT_ID" is null then '' else '"' || replace(replace(new."INSERT_ID",'\','\\'),'"','\"') || '"' end||','||
          case when new."INSERT_DATE" is null then '' else '"' || rtrim(char(year(timestamp_iso(new."INSERT_DATE"))))||'-'||substr(digits(month(timestamp_iso(new."INSERT_DATE"))),9)||'-'||substr(digits(day(timestamp_iso(new."INSERT_DATE"))),9)||' '||substr(digits(hour(timestamp_iso(new."INSERT_DATE"))),9)||':'||substr(digits(minute(timestamp_iso(new."INSERT_DATE"))),9)||':'||substr(digits(second(timestamp_iso(new."INSERT_DATE"))),9)||'.'||RIGHT(REPEAT('0',6)||rtrim(char(microsecond(timestamp_iso(new."INSERT_DATE")))),6) || '"' end||','||
          case when new."UPDATE_ID" is null then '' else '"' || replace(replace(new."UPDATE_ID",'\','\\'),'"','\"') || '"' end||','||
          case when new."UPDATE_DATE" is null then '' else '"' || rtrim(char(year(timestamp_iso(new."UPDATE_DATE"))))||'-'||substr(digits(month(timestamp_iso(new."UPDATE_DATE"))),9)||'-'||substr(digits(day(timestamp_iso(new."UPDATE_DATE"))),9)||' '||substr(digits(hour(timestamp_iso(new."UPDATE_DATE"))),9)||':'||substr(digits(minute(timestamp_iso(new."UPDATE_DATE"))),9)||':'||substr(digits(second(timestamp_iso(new."UPDATE_DATE"))),9)||'.'||RIGHT(REPEAT('0',6)||rtrim(char(microsecond(timestamp_iso(new."UPDATE_DATE")))),6) || '"' end||','||
          case when new."STATUSS" is null then '' else '"' || replace(replace(new."STATUSS",'\','\\'),'"','\"') || '"' end||','||
          case when new."BAD_LOGIN_COUNT" is null then '' else '"' || trim(char(new."BAD_LOGIN_COUNT")) || '"' end||','||
          case when new."LAST_LOGIN_DATE" is null then '' else '"' || rtrim(char(year(timestamp_iso(new."LAST_LOGIN_DATE"))))||'-'||substr(digits(month(timestamp_iso(new."LAST_LOGIN_DATE"))),9)||'-'||substr(digits(day(timestamp_iso(new."LAST_LOGIN_DATE"))),9)||' '||substr(digits(hour(timestamp_iso(new."LAST_LOGIN_DATE"))),9)||':'||substr(digits(minute(timestamp_iso(new."LAST_LOGIN_DATE"))),9)||':'||substr(digits(second(timestamp_iso(new."LAST_LOGIN_DATE"))),9)||'.'||RIGHT(REPEAT('0',6)||rtrim(char(microsecond(timestamp_iso(new."LAST_LOGIN_DATE")))),6) || '"' end||','||
          case when new."ACCEPTTRMS" is null then '' else '"' || replace(replace(new."ACCEPTTRMS",'\','\\'),'"','\"') || '"' end||','||
          case when new."ACCOUNT" is null then '' else '"' || replace(replace(new."ACCOUNT",'\','\\'),'"','\"') || '"' end||','||
          case when new."PAYMNTSERV" is null then '' else '"' || replace(replace(new."PAYMNTSERV",'\','\\'),'"','\"') || '"' end||','||
          case when new."RESRCECENTRE" is null then '' else '"' || replace(replace(new."RESRCECENTRE",'\','\\'),'"','\"') || '"' end||','||
          case when new."ACCSUMMARY" is null then '' else '"' || replace(replace(new."ACCSUMMARY",'\','\\'),'"','\"') || '"' end||','||
          case when new."MNTHLYSTMT" is null then '' else '"' || replace(replace(new."MNTHLYSTMT",'\','\\'),'"','\"') || '"' end||','||
          case when new."ESCRWANAL" is null then '' else '"' || replace(replace(new."ESCRWANAL",'\','\\'),'"','\"') || '"' end||','||
          case when new."YRENDSTMT" is null then '' else '"' || replace(replace(new."YRENDSTMT",'\','\\'),'"','\"') || '"' end||','||
          case when new."TRNSHST" is null then '' else '"' || replace(replace(new."TRNSHST",'\','\\'),'"','\"') || '"' end||','||
          case when new."HOMEOWNINS" is null then '' else '"' || replace(replace(new."HOMEOWNINS",'\','\\'),'"','\"') || '"' end||','||
          case when new."TAXINFO" is null then '' else '"' || replace(replace(new."TAXINFO",'\','\\'),'"','\"') || '"' end||','||
          case when new."PAYOFFREQ" is null then '' else '"' || replace(replace(new."PAYOFFREQ",'\','\\'),'"','\"') || '"' end||','||
          case when new."CHNGEPWD" is null then '' else '"' || replace(replace(new."CHNGEPWD",'\','\\'),'"','\"') || '"' end||','||
          case when new."CONTACTDET" is null then '' else '"' || replace(replace(new."CONTACTDET",'\','\\'),'"','\"') || '"' end||','||
          case when new."PYMTRIGHT" is null then '' else '"' || replace(replace(new."PYMTRIGHT",'\','\\'),'"','\"') || '"' end||','||
          case when new."PWD_EXP_DATE" is null then '' else '"' || trim(char(new."PWD_EXP_DATE")) || '"' end||','||
          case when new."PWD_EXPIRE_DAYS" is null then '' else '"' || trim(char(new."PWD_EXPIRE_DAYS")) || '"' end||','||
          case when new."ENABLE_ACH" is null then '' else '"' || replace(replace(new."ENABLE_ACH",'\','\\'),'"','\"') || '"' end||','||
          case when new."ENABLE_ACH_PDF" is null then '' else '"' || replace(replace(new."ENABLE_ACH_PDF",'\','\\'),'"','\"') || '"' end||','||
          case when new."FINANCIAL_INFO" is null then '' else '"' || replace(replace(new."FINANCIAL_INFO",'\','\\'),'"','\"') || '"' end,
                                                
          case when old."USER_ID" is null then '' else '"' || trim(char(old."USER_ID")) || '"' end||','||
          case when old."ACCNTNUM" is null then '' else '"' || trim(char(old."ACCNTNUM")) || '"' end||','||
          case when old."USERTYP" is null then '' else '"' || replace(replace(old."USERTYP",'\','\\'),'"','\"') || '"' end||','||
          case when old."USERNAME" is null then '' else '"' || replace(replace(old."USERNAME",'\','\\'),'"','\"') || '"' end||','||
          case when old."PASSWORD" is null then '' else '"' || replace(replace(old."PASSWORD",'\','\\'),'"','\"') || '"' end||','||
          case when old."SSN" is null then '' else '"' || trim(char(old."SSN")) || '"' end||','||
          case when old."ZIPCD" is null then '' else '"' || trim(char(old."ZIPCD")) || '"' end||','||
          case when old."EMAILID" is null then '' else '"' || replace(replace(old."EMAILID",'\','\\'),'"','\"') || '"' end||','||
          case when old."PASSWORD_QUES" is null then '' else '"' || replace(replace(old."PASSWORD_QUES",'\','\\'),'"','\"') || '"' end||','||
          case when old."PASSWORD_ANS" is null then '' else '"' || replace(replace(old."PASSWORD_ANS",'\','\\'),'"','\"') || '"' end||','||
          case when old."LIBRARY" is null then '' else '"' || replace(replace(old."LIBRARY",'\','\\'),'"','\"') || '"' end||','||
          case when old."COMP_LIBRARY" is null then '' else '"' || replace(replace(old."COMP_LIBRARY",'\','\\'),'"','\"') || '"' end||','||
          case when old."INSERT_ID" is null then '' else '"' || replace(replace(old."INSERT_ID",'\','\\'),'"','\"') || '"' end||','||
          case when old."INSERT_DATE" is null then '' else '"' || rtrim(char(year(timestamp_iso(old."INSERT_DATE"))))||'-'||substr(digits(month(timestamp_iso(old."INSERT_DATE"))),9)||'-'||substr(digits(day(timestamp_iso(old."INSERT_DATE"))),9)||' '||substr(digits(hour(timestamp_iso(old."INSERT_DATE"))),9)||':'||substr(digits(minute(timestamp_iso(old."INSERT_DATE"))),9)||':'||substr(digits(second(timestamp_iso(old."INSERT_DATE"))),9)||'.'||RIGHT(REPEAT('0',6)||rtrim(char(microsecond(timestamp_iso(old."INSERT_DATE")))),6) || '"' end||','||
          case when old."UPDATE_ID" is null then '' else '"' || replace(replace(old."UPDATE_ID",'\','\\'),'"','\"') || '"' end||','||
          case when old."UPDATE_DATE" is null then '' else '"' || rtrim(char(year(timestamp_iso(old."UPDATE_DATE"))))||'-'||substr(digits(month(timestamp_iso(old."UPDATE_DATE"))),9)||'-'||substr(digits(day(timestamp_iso(old."UPDATE_DATE"))),9)||' '||substr(digits(hour(timestamp_iso(old."UPDATE_DATE"))),9)||':'||substr(digits(minute(timestamp_iso(old."UPDATE_DATE"))),9)||':'||substr(digits(second(timestamp_iso(old."UPDATE_DATE"))),9)||'.'||RIGHT(REPEAT('0',6)||rtrim(char(microsecond(timestamp_iso(old."UPDATE_DATE")))),6) || '"' end||','||
          case when old."STATUSS" is null then '' else '"' || replace(replace(old."STATUSS",'\','\\'),'"','\"') || '"' end||','||
          case when old."BAD_LOGIN_COUNT" is null then '' else '"' || trim(char(old."BAD_LOGIN_COUNT")) || '"' end||','||
          case when old."LAST_LOGIN_DATE" is null then '' else '"' || rtrim(char(year(timestamp_iso(old."LAST_LOGIN_DATE"))))||'-'||substr(digits(month(timestamp_iso(old."LAST_LOGIN_DATE"))),9)||'-'||substr(digits(day(timestamp_iso(old."LAST_LOGIN_DATE"))),9)||' '||substr(digits(hour(timestamp_iso(old."LAST_LOGIN_DATE"))),9)||':'||substr(digits(minute(timestamp_iso(old."LAST_LOGIN_DATE"))),9)||':'||substr(digits(second(timestamp_iso(old."LAST_LOGIN_DATE"))),9)||'.'||RIGHT(REPEAT('0',6)||rtrim(char(microsecond(timestamp_iso(old."LAST_LOGIN_DATE")))),6) || '"' end||','||
          case when old."ACCEPTTRMS" is null then '' else '"' || replace(replace(old."ACCEPTTRMS",'\','\\'),'"','\"') || '"' end||','||
          case when old."ACCOUNT" is null then '' else '"' || replace(replace(old."ACCOUNT",'\','\\'),'"','\"') || '"' end||','||
          case when old."PAYMNTSERV" is null then '' else '"' || replace(replace(old."PAYMNTSERV",'\','\\'),'"','\"') || '"' end||','||
          case when old."RESRCECENTRE" is null then '' else '"' || replace(replace(old."RESRCECENTRE",'\','\\'),'"','\"') || '"' end||','||
          case when old."ACCSUMMARY" is null then '' else '"' || replace(replace(old."ACCSUMMARY",'\','\\'),'"','\"') || '"' end||','||
          case when old."MNTHLYSTMT" is null then '' else '"' || replace(replace(old."MNTHLYSTMT",'\','\\'),'"','\"') || '"' end||','||
          case when old."ESCRWANAL" is null then '' else '"' || replace(replace(old."ESCRWANAL",'\','\\'),'"','\"') || '"' end||','||
          case when old."YRENDSTMT" is null then '' else '"' || replace(replace(old."YRENDSTMT",'\','\\'),'"','\"') || '"' end||','||
          case when old."TRNSHST" is null then '' else '"' || replace(replace(old."TRNSHST",'\','\\'),'"','\"') || '"' end||','||
          case when old."HOMEOWNINS" is null then '' else '"' || replace(replace(old."HOMEOWNINS",'\','\\'),'"','\"') || '"' end||','||
          case when old."TAXINFO" is null then '' else '"' || replace(replace(old."TAXINFO",'\','\\'),'"','\"') || '"' end||','||
          case when old."PAYOFFREQ" is null then '' else '"' || replace(replace(old."PAYOFFREQ",'\','\\'),'"','\"') || '"' end||','||
          case when old."CHNGEPWD" is null then '' else '"' || replace(replace(old."CHNGEPWD",'\','\\'),'"','\"') || '"' end||','||
          case when old."CONTACTDET" is null then '' else '"' || replace(replace(old."CONTACTDET",'\','\\'),'"','\"') || '"' end||','||
          case when old."PYMTRIGHT" is null then '' else '"' || replace(replace(old."PYMTRIGHT",'\','\\'),'"','\"') || '"' end||','||
          case when old."PWD_EXP_DATE" is null then '' else '"' || trim(char(old."PWD_EXP_DATE")) || '"' end||','||
          case when old."PWD_EXPIRE_DAYS" is null then '' else '"' || trim(char(old."PWD_EXPIRE_DAYS")) || '"' end||','||
          case when old."ENABLE_ACH" is null then '' else '"' || replace(replace(old."ENABLE_ACH",'\','\\'),'"','\"') || '"' end||','||
          case when old."ENABLE_ACH_PDF" is null then '' else '"' || replace(replace(old."ENABLE_ACH_PDF",'\','\\'),'"','\"') || '"' end||','||
          case when old."FINANCIAL_INFO" is null then '' else '"' || replace(replace(old."FINANCIAL_INFO",'\','\\'),'"','\"') || '"' end,
                                                'default',
                                                null,
                                                sym_source_node_id,
                                                null,
                                                CURRENT_TIMESTAMP);
                                    END IF;
                                                                                                                                                                               
                                END
Tagsdialect: as/400, dialect: db2

Activities

elong

2018-05-24 12:17

developer   ~0001193

Is it because the sym_trigger.sync_on_insert_condition is set to "1" instead of blank or "1=1"?

rajurudru

2018-05-24 12:30

reporter   ~0001194

Hi elong,
Thanks for your quick response. I have the entries in sym_trigger as shown in the attachment. Please let me know if I need to change anything.
symdb.PNG (4,193 bytes)   
symdb.PNG (4,193 bytes)   

elong

2018-05-24 12:34

developer   ~0001195

Here is the columns I was asking about:

select sync_on_insert_condition, sync_on_update_condition, sync_on_delete_condition from sym_trigger

rajurudru

2018-05-24 12:48

reporter   ~0001196

Please see the below screen shot
symdb2.PNG (2,565 bytes)   
symdb2.PNG (2,565 bytes)   

elong

2018-05-24 12:50

developer   ~0001197

I wanted to see sync_on_insert_condition, not sync_on_insert. Can you run the SQL I provided?

rajurudru

2018-05-24 12:55

reporter   ~0001198

My apologies. I have included the correct screen shot now
symdb3.png (3,082 bytes)   
symdb3.png (3,082 bytes)   

elong

2018-05-24 13:09

developer   ~0001199

I think the error comes from the "IF 1" in the trigger SQL, but I don't understand where it's coming from.

Can you try setting the condition to "1=1", like this:

update sym_trigger set
sync_on_insert_condition = '1=1',
sync_on_update_condition = '1=1',
sync_on_delete_condition = '1=1'

rajurudru

2018-05-24 13:17

reporter   ~0001200

I will update and let you know the result.

rajurudru

2018-05-24 14:02

reporter   ~0001201

After making the changes you have suggested. The error is resolved now. Thanks for your help.

elong

2018-05-24 14:09

developer   ~0001202

Glad that worked, closing this issue.

Issue History

Date Modified Username Field Change
2018-05-24 11:39 rajurudru New Issue
2018-05-24 11:39 rajurudru Tag Attached: AS/400
2018-05-24 11:39 rajurudru Tag Attached: DB2
2018-05-24 11:39 rajurudru Tag Attached: IBM i
2018-05-24 12:17 elong Note Added: 0001193
2018-05-24 12:30 rajurudru File Added: symdb.PNG
2018-05-24 12:30 rajurudru Note Added: 0001194
2018-05-24 12:34 elong Note Added: 0001195
2018-05-24 12:48 rajurudru File Added: symdb2.PNG
2018-05-24 12:48 rajurudru Note Added: 0001196
2018-05-24 12:50 elong Note Added: 0001197
2018-05-24 12:55 rajurudru File Added: symdb3.png
2018-05-24 12:55 rajurudru Note Added: 0001198
2018-05-24 13:09 elong Note Added: 0001199
2018-05-24 13:17 rajurudru Note Added: 0001200
2018-05-24 14:02 rajurudru Note Added: 0001201
2018-05-24 14:09 elong Note Added: 0001202
2018-05-24 14:10 elong Assigned To => elong
2018-05-24 14:10 elong Status new => closed
2018-05-24 14:10 elong Resolution open => no change required
2019-04-12 16:35 admin Tag Detached: IBM i
2019-04-23 20:45 admin Tag Renamed AS/400 => dialect: as/400
2019-04-23 20:46 admin Tag Renamed DB2 => dialect: db2