search

averemee-si / oracdc

Oracle database CDC (Change Data Capture)
http://a2-solutions.eu/
Apache License 2.0
105 stars 36 forks source link

ERROR ORA-00936: missing expression (eu.solutions.a2.cdc.oracle.OraCdcLogMinerWorkerThread:669) #20

Closed ulan-yisaev closed 3 years ago

ulan-yisaev commented 3 years ago

Hi, Aleksei!

I am getting ORA-00936 error when switching archived logs. Please help me figure it out. My environment: CentOS Linux release 7.8.2003 kafka_2.13-2.8.0 (open source) jdk1.8.0_281 oracdc 0.9.7.8 My configs in the attached archive my_configs.zip

[2021-05-07 21:57:02,342] INFO WorkerSourceTask{id=oracdc-front-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:487)
[2021-05-07 21:57:02,907] INFO Adding archived log /global/arch/front/front_1_2981_1042104127.arc thread# 1 sequence# 2981 first change number 33848970951 next log first change 33849080379 (eu.solutions.a2.cdc.oracle.OraCdcV$ArchivedLogImpl:150)
[2021-05-07 21:57:02,949] ERROR ORA-00936: missing expression
 (eu.solutions.a2.cdc.oracle.OraCdcLogMinerWorkerThread:669)
[2021-05-07 21:57:02,950] ERROR SQL errorCode = 936, SQL state = '42000' (eu.solutions.a2.cdc.oracle.OraCdcLogMinerWorkerThread:672)
[2021-05-07 21:57:02,952] ERROR ORA-00936: missing expression

java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
        at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OraclePreparedStatement$$$Proxy.executeQuery(Unknown Source)
        at eu.solutions.a2.cdc.oracle.OraCdcLogMinerWorkerThread.run(OraCdcLogMinerWorkerThread.java:294)
Caused by: Error : 936, Position : 149, Sql = select SCN, TIMESTAMP, OPERATION_CODE, XID, RS_ID, SSN, CSF, ROW_ID, DATA_OBJ#, DATA_OBJD#, SQL_REDO
from   V$LOGMNR_CONTENTS
where ((OPERATION_CODE in (1,2,3)  and (DATA_OBJ# in ())) or OPERATION_CODE in (7,36)), OriginalSql = select SCN, TIMESTAMP, OPERATION_CODE, XID, RS_ID, SSN, CSF, ROW_ID, DATA_OBJ#, DATA_OBJD#, SQL_REDO
from   V$LOGMNR_CONTENTS
where ((OPERATION_CODE in (1,2,3)  and (DATA_OBJ# in ())) or OPERATION_CODE in (7,36)), Error Msg = ORA-00936: missing expression

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
        ... 16 more
 (eu.solutions.a2.cdc.oracle.OraCdcLogMinerWorkerThread:675)
[2021-05-07 21:57:02,953] INFO Stopping oracdc logminer source task. (eu.solutions.a2.cdc.oracle.OraCdcLogMinerTask:633)
[2021-05-07 21:57:07,921] INFO Saving oracdc state to /home/oracle/oracdc-master/tmp/oracdc-front.state file... (eu.solutions.a2.cdc.oracle.OraCdcLogMinerTask:683)
[2021-05-07 21:57:07,958] INFO oracdc state saved to /home/oracle/oracdc-master/tmp/oracdc-front.state file, elapsed 37 ms (eu.solutions.a2.cdc.oracle.OraCdcLogMinerTask:746)
Exception in thread "OraCdcLogMinerWorkerThread-3235410531843033" org.apache.kafka.connect.errors.ConnectException: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

        at eu.solutions.a2.cdc.oracle.OraCdcLogMinerWorkerThread.run(OraCdcLogMinerWorkerThread.java:681)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
        at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OraclePreparedStatement$$$Proxy.executeQuery(Unknown Source)
        at eu.solutions.a2.cdc.oracle.OraCdcLogMinerWorkerThread.run(OraCdcLogMinerWorkerThread.java:294)
Caused by: Error : 936, Position : 149, Sql = select SCN, TIMESTAMP, OPERATION_CODE, XID, RS_ID, SSN, CSF, ROW_ID, DATA_OBJ#, DATA_OBJD#, SQL_REDO
from   V$LOGMNR_CONTENTS
where ((OPERATION_CODE in (1,2,3)  and (DATA_OBJ# in ())) or OPERATION_CODE in (7,36)), OriginalSql = select SCN, TIMESTAMP, OPERATION_CODE, XID, RS_ID, SSN, CSF, ROW_ID, DATA_OBJ#, DATA_OBJD#, SQL_REDO
from   V$LOGMNR_CONTENTS
where ((OPERATION_CODE in (1,2,3)  and (DATA_OBJ# in ())) or OPERATION_CODE in (7,36)), Error Msg = ORA-00936: missing expression

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
        ... 16 more
[2021-05-07 21:57:12,343] INFO WorkerSourceTask{id=oracdc-front-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:487)

my_configs.zip

ulan-yisaev commented 3 years ago

connect.log.zip Attaching full log

averemee-si commented 3 years ago

Salemetsiz-be Ulan,

This is issue due to incorrect setting of a2.include parameter, connector tried to build list of OBJECT_ID's for tables/partitions/subpartitions which corresponds to a2.include/a2.exclude settings but unable to build it. While I'll work on better diagnostic could you please: 1) Send me value of your a2.include parameter to oracle@a2-solutions.eu 2) Try to run connector with parameter a2.table.list.style set to dynamic

Kop rahmet!

Best regards, Aleksei

averemee-si commented 3 years ago

Salemetsiz-be Ulan,

Could you please clone latest code with fix a2.include/a2.exclude validation - and test again.

Kop rahmet! Best regards, Aleksei

ulan-yisaev commented 3 years ago

Здравствуйте, Алексей!

Thank you so much for fixing the issue - now it works perfectly!

[2021-05-08 14:11:58,821] INFO WorkerSourceTask{id=oracdc-front-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:487)
[2021-05-08 14:12:03,453] INFO Adding archived log /global/arch/front/front_1_3176_1042104127.arc thread# 1 sequence# 3176 first change number 33850577521 next log first change 33850582378 (eu.solutions.a2.cdc.oracle.OraCdcV$ArchivedLogImpl:150)
[2021-05-08 14:12:08,822] INFO WorkerSourceTask{id=oracdc-front-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:487)