topic MySQL CDC functionality is not working in Talend Studio

MySQL CDC functionality is not working

VenkyM — Sat, 16 Nov 2024 01:50:36 GMT

Hi,

I am trying to learn how the Change Data Capture (CDC) works.

As part of it, I followed the steps given in the following link.

https://help.talend.com/reader/RLLVjeyBol_aw8PB0tyxJg/4Te87GKIHEWPvhnK0YY3xQ

My source and target are MYSQL. One is on the remote server, which is my source. The second one is my target, which in on my local laptop.

Created the connections as per the step1

While executing the script created during "Add CDC" step, i got the below error. I ignore the errors and proceeded.

I tried to test it by inserting a row into the source table, which is customer. The row got inserted, but i did not see any data in my target table. My understanding was that when I insert a row into the source, the triggers will automatically push the changes to the target table, which is on my local mysql.

The below is the screenshot of the source table.

The view all changes is empty.

I am guessing that the connection was not established properly. Any help will be appreicated!

Re: MySQL CDC functionality is not working

VenkyM — Tue, 21 Jul 2020 13:31:14 GMT

Here is the script generated by the add cdc operation.

alter table `sakila`.`TSUBSCRIBERS` modify `TALEND_CDC_TABLE_TO_WATCH` VARCHAR(255)

INSERT INTO `sakila`.`TSUBSCRIBERS`

(

`TALEND_CDC_TABLE_TO_WATCH`,

`TALEND_CDC_SUBSCRIBER_NAME`,

`TALEND_CDC_CREATION_DATE`

)

values ('entries1.customer','APP1',sysdate()) ;

CREATE TABLE `sakila`.`TCDC_customer`

(

`TALEND_CDC_SUBSCRIBERS_NAME` VARCHAR(50) NOT NULL,

`TALEND_CDC_STATE` VARCHAR(1),

`TALEND_CDC_TYPE` VARCHAR(1),

`TALEND_CDC_CREATION_DATE` DATETIME,

`customerId` INT(10) NOT NULL

) ;

CREATE OR REPLACE VIEW `sakila`.`TCDC_VIEW_customer` AS

SELECT

SRC.`TALEND_CDC_TYPE` TALEND_CDC_TYPE,

SRC.`TALEND_CDC_SUBSCRIBERS_NAME` TALEND_CDC_SUBSCRIBERS_NAME,

max(SRC.`TALEND_CDC_CREATION_DATE`) TALEND_CDC_CREATION_DATE,

SRC.`customerId` customerId,

TARG.`name` name,

TARG.`countryOfResidence` countryOfResidence

FROM `sakila`.`TCDC_customer` SRC LEFT JOIN `entries1`.`customer` TARG

ON SRC.`customerId`=TARG.`customerId`

WHERE SRC.`TALEND_CDC_STATE`='1'

GROUP BY SRC.`TALEND_CDC_SUBSCRIBERS_NAME`, SRC.`customerId`,

SRC.`TALEND_CDC_TYPE` ;

CREATE TRIGGER `entries1`.`TCDC_TG_customer_I`

AFTER INSERT ON `entries1`.`customer`

FOR each row

INSERT INTO `sakila`.`TCDC_customer`

(

`TALEND_CDC_SUBSCRIBERS_NAME`,

`TALEND_CDC_STATE`,

`TALEND_CDC_TYPE`,

`TALEND_CDC_CREATION_DATE`,

`customerId`

) SELECT

`TALEND_CDC_SUBSCRIBER_NAME`,

'0',

'I',

sysdate(),

new.`customerId`

FROM `sakila`.`TSUBSCRIBERS`

WHERE `TALEND_CDC_TABLE_TO_WATCH`='entries1.customer' ;

CREATE TRIGGER `entries1`.`TCDC_TG_customer_U`

AFTER UPDATE ON `entries1`.`customer`

FOR each row

INSERT INTO `sakila`.`TCDC_customer`

(

`TALEND_CDC_SUBSCRIBERS_NAME`,

`TALEND_CDC_STATE`,

`TALEND_CDC_TYPE`,

`TALEND_CDC_CREATION_DATE`,

`customerId`

) SELECT

`TALEND_CDC_SUBSCRIBER_NAME`,

'0',

'U',

sysdate(),

new.`customerId`

FROM `sakila`.`TSUBSCRIBERS`

WHERE `TALEND_CDC_TABLE_TO_WATCH`='entries1.customer' ;

CREATE TRIGGER `entries1`.`TCDC_TG_customer_D`

AFTER DELETE ON `entries1`.`customer`

FOR each row

INSERT INTO `sakila`.`TCDC_customer`

(

`TALEND_CDC_SUBSCRIBERS_NAME`,

`TALEND_CDC_STATE`,

`TALEND_CDC_TYPE`,

`TALEND_CDC_CREATION_DATE`,

`customerId`

) SELECT

`TALEND_CDC_SUBSCRIBER_NAME`,

'0',

'D',

sysdate(),

old.`customerId`

FROM `sakila`.`TSUBSCRIBERS`

WHERE `TALEND_CDC_TABLE_TO_WATCH`='entries1.customer' ;

Re: MySQL CDC functionality is not working

Anonymous — Wed, 22 Jul 2020 02:36:18 GMT

"values ('entries1.customer','APP1',sysdate()) ;"

About the error 'unknown database entries1'

The database used for creating CDC tables and the database which contains the source table should be in the same Mysql server, I guess the database used for creating CDC tables is in your local Mysql.

Regards

Shong

Re: MySQL CDC functionality is not working

VenkyM — Tue, 28 Jul 2020 20:04:26 GMT

Thanks for the reply. Appreciated!

Let me check it.