View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0003578 | SymmetricDS | Bug | public | 2018-05-24 11:39 | 2019-04-12 16:35 |
Reporter | rajurudru | Assigned To | elong | ||
Priority | high | ||||
Status | closed | Resolution | no change required | ||
Product Version | 3.9.7 | ||||
Summary | 0003578: Failed to create triggers for table StackTraceKey.init [SqlException:1627968287] | ||||
Description | SymmetricDS 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 Reproduce | 1. 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 Information | The 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 | ||||
Tags | dialect: as/400, dialect: db2 | ||||
|
Is it because the sym_trigger.sync_on_insert_condition is set to "1" instead of blank or "1=1"? |
|
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. |
|
Here is the columns I was asking about: select sync_on_insert_condition, sync_on_update_condition, sync_on_delete_condition from sym_trigger |
|
Please see the below screen shot |
|
I wanted to see sync_on_insert_condition, not sync_on_insert. Can you run the SQL I provided? |
|
My apologies. I have included the correct screen shot now |
|
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' |
|
I will update and let you know the result. |
|
After making the changes you have suggested. The error is resolved now. Thanks for your help. |
|
Glad that worked, closing this issue. |
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 |