Talend community forum / Post a reply

Open Data SolutionsData Management solutionswarehouseOpen StudioBusinessExtract Transform LoadETL

You are not logged in.

Announcement

[2013-05-06] Boston Area User Group Meetup
[2013-05-02] NOTICE TO ALL TALENDFORGE USERS: maintenance completed
[2013-04-26] Talend Open Studio's 5.3.0 release is available
[2013-04-12] Ready for testing - Talend Open Studio's 5.3.0RC2 release is available
[2013-04-11] Talend’s corporate pages and groups on social media

Unanswered posts

parseDate change from 5.0.3 to 5.2.2? by Kwags300
Problem whit context variable in query. by Ginetto
Problem with SELECT a.name AS a_name, b.name AS b_name input by kacper
SayHelloRoute [...] by gary.liu1119@gmail.com
How to generate jar for use with tBRMS by adaemus
Deployment question regarding [...] by rhuiser
General question about jobservers and runtimes ... by rbadev

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

Post a reply

Write your message and submit

Name

E-mail

Message

[quote=dko]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[/quote]

Options

Never show smilies as icons for this post

Click in the dark area of the image to send your post.

Go back

Topic review (newest first)

llkojnjhfy: 2011-06-05 04:51:21

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, dior handbagsdeleting 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?

dko: 2011-04-04 16:15:35

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

good710: 2011-04-04 15:53:35

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

dko: 2011-02-22 17:52:06

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

shong: 2011-02-17 14:33:44

Hi dko
That is the culprit, if you check the box 'multiple thread exection', the subjobs will run parallelly.

Best regards
Shong

dko: 2011-02-17 11:11:15

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/tu … ;idTuto=44) to let my multithreading work. For the tests I've done for the moment, it works.
Maybe it can help.

Regards
dko

dko: 2011-02-16 18:25:17

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/tu … ;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

shong: 2011-02-16 16:32:11

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

shong: 2011-02-14 16:07:12

Hi
Ok, I receive your video and I will have a look at it tomorrow.

Best regards
Shong

dko: 2011-02-14 15:34:31

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

dko: 2011-02-14 15:31:34

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

dko: 2011-02-14 11:05:57

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

shong: 2011-02-14 10:59:30

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

dko: 2011-02-14 10:00:56

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

shong: 2011-02-12 04:07:14

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

Announcement

Unanswered posts

Post a reply

Topic review (newest first)

Board footer