search

wso2 / product-micro-integrator

The cloud-native configuration driven runtime that helps developers implement composite microservices.
https://wso2.com/integration/
Apache License 2.0
204 stars 221 forks source link

[Bug]: DB Event Poller is storing an invalid value in registry for Oracle DB #3523

Open anupama-pathirage opened 3 months ago

anupama-pathirage commented 3 months ago

Description

Move the issue from https://github.com/wso2-extensions/esb-inbound-dbevent/issues/21 This is raised by p0457 and refer to the below image for the follow up question as well.

image

Description: Using an Oracle DB, the first trigger of the event poller appears to capture the update, and creates the registry record of the last update value. This value is being stored incorrectly:

¨Ì�p

Configuration:

<inboundEndpoint class="org.wso2.carbon.inbound.poll.dbeventlistener.DBEventPollingConsumer" name="watcher" onError="watcher.faulthandler" sequence="watcher.sequence” suspend="false" xmlns="http://ws.apache.org/ns/synapse">
    <parameters>
        <parameter name="inbound.behavior">polling</parameter>
        <parameter name="interval">60000</parameter>
        <parameter name="sequential">true</parameter>
        <parameter name="coordination">true</parameter>
        <parameter name="driverName">oracle.jdbc.driver.OracleDriver</parameter>
        <parameter name="url">jdbc:oracle:thin:******/******@******</parameter>
        <parameter name="tableName">TABLE_NAME</parameter>
        <parameter name="filteringCriteria">byLastUpdatedTimestampColumn</parameter>
        <parameter name="filteringColumnName">TO_CHAR(LAST_UPDATE_DATE, 'YYYY-MM-DD hh24:mm:ss')</parameter>
        <parameter name="primaryKey">ID</parameter>
        <parameter name="connectionValidationQuery">SELECT 1 FROM DUAL</parameter>
        <parameter name="registryPath">watcher/timestamp</parameter>
    </parameters>
</inboundEndpoint>

I've had to convert the filteringColumnName to the value shown because an error was thrown as Oracle can't compare dates as strings.

Every poll after the first (when the registry value has the incorrect value) triggers the following error repeatedly in the wso2carbon logs for EI:

[2019-10-11 19:01:36,406] [-1] [] [pool-29-thread-1] ERROR {org.wso2.carbon.ntask.core.impl.TaskQuartzJobAdapter} -  Error in executing task: null
java.lang.NullPointerException
        at org.wso2.carbon.inbound.poll.dbeventlistener.DBEventPollingConsumer.fetchDataAndInject(DBEventPollingConsumer.java:182)
        at org.wso2.carbon.inbound.poll.dbeventlistener.DBEventPollingConsumer.poll(DBEventPollingConsumer.java:352)
        at org.wso2.carbon.inbound.endpoint.protocol.generic.GenericTask.taskExecute(GenericTask.java:41)
        at org.wso2.carbon.inbound.endpoint.common.InboundTask.execute(InboundTask.java:45)
        at org.wso2.carbon.mediation.ntask.NTaskAdapter.execute(NTaskAdapter.java:98)
        at org.wso2.carbon.ntask.core.impl.TaskQuartzJobAdapter.execute(TaskQuartzJobAdapter.java:67)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        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:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Suggested Labels: bug

Affected Product Version: WSO2 EI 6.5.0, most recent version of DB Event Listener (downloaded today) from the store: https://store.wso2.com/store/assets/esbconnector/details/da8c396a-281e-493b-87bc-b55a4cebe92d

OS, DB, other environment details and versions:
DB: Oracle 12C Environment: CentOS 7 VM

Steps to reproduce: Use the configuration above to deploy on the specified environment, and once the poller is first triggered, check the wso2carbon logs or the registry value to find the error or the incorrect value.

Follow up question.

Follow up to this, a varchar2 column configuration for a different table is storing the registry value as such: ¨Ì�t�2019-10-10 19:10:29

And is triggering the sequence every polling period. Intended behavior should be that the sequence is only invoked on items that have a LAST_UPDATE_DATE greater than the registry value (which in the SQL in Oracle evaluates to false). Since I haven't updated the table since it was created, the poller should not be firing the sequence on each interval.

CREATE TABLE TEMP (
    ID varchar2(64) PRIMARY KEY,
    LAST_UPDATE_DATE varchar2(60) NOT NULL
)

INSERT INTO TEMP (ID, LAST_UPDATE_DATE)
VALUES (1, '2019-10-10 23:00:00')

SELECT ID, LAST_UPDATE_DATE,  
    CASE WHEN LAST_UPDATE_DATE > '¨Ì��t��2019-10-10 19:10:29' THEN 'Y' ELSE 'N' END TRG
FROM TEMP;

The result column TRG evaluates to N.