One Star dko
One Star

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
One Star dko
One Star

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

Hi,
In fact the problem come from "INSERT" action in the output component.
You have to uncheck "extent insert" in advance parameters and use a single CDCinput to capture "insert", "delete" or "update". Don't also forget to tSortRow before connectiong the tMap;
Regards
23 REPLIES
Community Manager

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

Hi
Which version of TIS are you using?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star dko
One Star

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

Hi,
I use TIS 3.2.3.r35442
Community Manager

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

Hi
We have confirmed the problem and give a workaround on bug:
http://www.talendforge.org/bugs/view.php?id=18831
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star dko
One Star

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star dko
One Star

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star dko
One Star

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star dko
One Star

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

Ok. I'll be back to you with my video.
Regards
One Star dko
One Star

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

Hi Shong,
I've sent you by mail my videos.
Waiting for your return
Regards
dko
One Star dko
One Star

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

In these videos, I only do I+D on the same row and as you'll see, i get NULL values.
Community Manager

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

Hi
Ok, I receive your video and I will have a look at it tomorrow.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Community Manager

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

Hi dko
I have looked your video and don't see an exception on your CDC operation. The only difference is I use SQLYog to operate mysql and you use PHPMYAdmin. I have no clue at the moment.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star dko
One Star

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

Hi Shong,
Thanks for your answer.
I'm trying to apply your first proposal, replacing NULL values by default one ("", 0, etc) in Insert because if I do I+D on the same row, it doesn't matter if I insert the row with default values as I'll delete it rigth after. It may work I think even if I have a lot of work for that because I have to do it manually for each table and field as the number of fields per table is quite variable. Maybe it could help a lot if this is taken into account in your CDC code.
Therefore, how can I get get the same sequence (I+D) during my replication. I mean, for instance, if I follow the example you give for CDC in Oracle (http://www.talendforge.org/tutorials/tutorial.php?language=english&idTuto=44) Delete is always executed before Insert (due to the order of execution of my job) so that I'll synchronise Insert with the default values in the other table.
Regards
One Star dko
One Star

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

Hi,
Concerning the second part of my previous post, I think I know why it doesn't work when i want to keep the order of the changes during my synchronisation.
I've selected "multithreading execution" and deselect the use of the project settings in my job parameters. I've also deleted all "OnSubjobOk" (as described in http://www.talendforge.org/tutorials/tutorial.php?language=english&idTuto=44) to let my multithreading work. For the tests I've done for the moment, it works.
Maybe it can help.
Regards
dko
Community Manager

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

Hi dko
That is the culprit, if you check the box 'multiple thread exection', the subjobs will run parallelly.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star dko
One Star

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

Hi,
After deep tests today on CDC, I'm not able to get the same sequence of operations on table A (for instance I+D+I+I+D, where at least one I+D are on the same row) when I synchronize table A and table B.
Using multithreading was not the solution since it's not possible to guarantee the execution time of each thread.
This is remains a big problem.
Regards
dko
One Star

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

Thank you, for answering. But this method works for me only with .. In all other cases I get only strange results, like those below :lol:
One Star dko
One Star

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

Hi,
In fact the problem come from "INSERT" action in the output component.
You have to uncheck "extent insert" in advance parameters and use a single CDCinput to capture "insert", "delete" or "update". Don't also forget to tSortRow before connectiong the tMap;
Regards
One Star

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

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?

One Star kmv
One Star

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

Hi dko,
I am facing the same issue as you stated below and exactly similar order of operations in cases like INSERT, DELETE, INSERT the same keys for many reasons, my database does not pick the last insert or get deleted. I followed one input oracle component and three change of operations in the order of Insert, update, delete. Did you resolve your issue? If you did, what was the design approach you took for CDC? what do you mean by You have to uncheck "extent insert" in advance parameters.
I did not find that option of extent insert anywhere, can you please advise?
Thanks!!!
One Star kmv
One Star

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

Hello, 
Any ideas or suggestions?