[resolved] CDC fails on insert because of NULL values in tcdc_view (TIS)

[Anonymous]

Hi,
I use TIS (CDC) for replication on 2 databases , i my case MYSQL. So i catch changes in one database and replicate them on the other database in asynchronous mode.
When i insert a row and delete the same row in the table i want to replicate, the process fails during replication because the row has been deleted from the original table (link between tcdc_MYTABLE, MYTABLE and tcdc_view_MYTABLE) so that NULL values are reported in tcdc_view for the insertion operation ; and as i can't insert NULL values in the table the process fails.
This is a very blocking problem for the CDC component.
I'm blocked if i don't want to create (manually of course) a temporary table instead of the view

[Anonymous]

Hi
Which version of TIS are you using?
Best regards
Shong

[Anonymous]

Hi,
I use TIS 3.2.3.r35442

[Anonymous]

Hi
We have confirmed the problem and give a workaround on bug:
http://www.talendforge.org/bugs/view.php?id=18831
Best regards
Shong

[Anonymous]

Hi,
I still have the problem if i apply this modification because empty values are really inserted in the table after insert+delete+insert of the same row.
You say that the fields are not NULL. How can I get the values?
Best regards
dko

[Anonymous]

Hi

You say that the fields are not NULL.

In the target table, you define the filed as not NULL. That's why you get the problem when you are inserting a NULL value.
Best regards
Shong

[Anonymous]

Even if i define in tMap the fields as NULL, they are NOT NULL in the database. I cannot alter my database tables to set the fields nullable.
But ok ; let me take the example in "CDC tutorial" with 2 databases (MySQL for instance) A and B and the same table (customers) in both databases.
I want to replicate changes from A. customers to B.customers
- I alter A.customers and B.customers so that all the fields (except the key) are nullable.
- I insert a row with ID=50 in A.customers
- I delete this row because I've made an error
- I decide to create again the same row with ID=50
- If i look in A.customers, the row with ID=50 is inserted with all the values i want
Now i want to replicate all theses changes in the same sequence in B.customers
- the tcdc_customers table contains all these changes
- the tcdc_view_customers will have the history of the modifications ( for the insert, we got the values inserted including the value of the key ; for delete, only the key is given. the other fields are NULL and it's normal)
- If i apply the modication for the "insert" on the tMap, all NULL values contained in the history view will be replaced by ""
- I run the job
- No error
- I look on B.customers
- row with ID=50 is inserted but i've no trace of the values of the other fields. They all have "" (except the key)
If i didn't alter B.customers to set the fields nullable, the job will fail even if i directly modify tMap by specifying values are nullable
So i don't know where i can get the values i've last inserted in A.customers so that B.customers with ID=50 is the same that A.customers with ID=50

[Anonymous]

Hi
I can't reproduce the problem if I alter all the fields(except the key) are nullable. The difference is that all other fileds are not null or "" after I change manually the talend_cdc_state from 0 to 1.(please see my screenshot), I am testing it on v323 and v412, both of the two release work fine.
Best regards
Shong

[Anonymous]

Hi Shong,
Thanks for your answer. I don't know the difference between your development version of TIS 3.2.3 compare to my version 3.2.3.r35442.
Regarding your screenshot 4, when you change manually the cdc_state from 0 to 1, you only get 2 types of operations (I, D). Therefore, you've done 3 operations (I,D,I). There's no trace of the first insertion in the database in tcdc_view_customer1. Probably at the end the result are the same (trying to delete a row that doesn't exist before inserting a row and inserting a row, deleting the row and inserting the same row) but in my version, after a delete there is NULL value in the other fields and i don't know why. Problem of version?
Regards
dko

[Anonymous]

Hi dko
I am working on TDQv323 r35442, BUT I can confirm it should be same as other edition like TISV323 R35442. I checked again and the result is the same, even thought I have done 3 operations(I, D, I) and there are three records(I, D, I) in tcdc_customer1, BUT There are only two records(D,I) in tcdc_view_customer1 and the fields are not NULL. Don't know what you have done, is it possible for you to record an video and send it to me?
Best regards
Shong