One Star

rollback parent and its related childs and continue processing next re

I iterate through a csv file and insert the data in parent and child table.If while inserting data in any child fails then it should rollback the current child and its parents remaining children which was inserted successfuly prior to the current child and continue iterating to the next parent.But when i try doing ,my processing is stopped on rollback
16 REPLIES
Seven Stars

Re: rollback parent and its related childs and continue processing next re

Screenshot your job design and have a look at http://www.talendforge.org/forum/viewtopic.php?id=26411. If your output components do not have "Die on error" checked, you shouldn't have a problem.
One Star

Re: rollback parent and its related childs and continue processing next re

updating screenshot image
Seven Stars

Re: rollback parent and its related childs and continue processing next re

Like I said: don't check "Die on error" in your output component. Instead connect tMysqlRollback to tMysqlOuput with a rejects flow.
BTW, isn't tMysqlCommit_2 redundant? There also seem to be a lot of unnecessary components: why do you need anything between tFixedFlowInput and tMysqlOuput in either subjob? And why do you need to read from the file twice?
One Star

Re: rollback parent and its related childs and continue processing next re

hi,
thanks.The solution worked.
Regarding the csv file being read twice is because ,the same csv contains parent data and child data.So i need to insert parent once and insert its multiple childs with parent id.
So for the inserted parent i am filtering out the childs and inserted them.
I am new to talend open studio.So did not the if its wrong or right.Please advice on how to proceed.
I have attached the corrected job design and example of my csv file.
Seven Stars

Re: rollback parent and its related childs and continue processing next re

Ignoring the committing/rolling-back issue for which you should refer to the topic I linked to above, I would just do:
tFileInputDelimited --> tMap --parent--> tUniqueRow --> tMysqlOutput -main-> tMysqlLastInsertID --> tSetGlobalVar
--child--> tJavaRow --> tMysqlOutput
The tJavaRow is to add the LastInsertID stored in the globalMap by tSetGlobalVar to your child data as the child data in tMap is populated before the parent flow is executed.
One Star

Re: rollback parent and its related childs and continue processing next re

hi,
I modified the job design as suggested by you.But wen i run the job it produced the following result in the console.
Starting job zipRollback3 at 11:05 04/10/2012.
connecting to socket on port 3708
connected
null
null
null
null
null
null
.-----+------+-----------+--------------+--------------.
| tLogRow_1 |
|=----+------+-----------+--------------+-------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|
|=----+------+-----------+--------------+-------------=|
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
|0 |null |null |null |null |
'-----+------+-----------+--------------+--------------'
disconnected
Job zipRollback3 ended at 11:05 04/10/2012.

Also it only inserted parent data.Child data was not inserted.Am i doing something wrong.Please advise.
Seven Stars

Re: rollback parent and its related childs and continue processing next re

Since all your child data was null, I'm not surprised it rejected. I can't tell you why that is from the screenprint but I'm sure you can figure it out with some additional tLogRows.
Equally, I'm not sure where the other nulls are coming from. Again, I'm sure you can figure it out by deactivating parts of the job.
Where are you adding the LastInsertID to your child data? I said to do that in tJavaRow. Also, I said to connect tMysqlLastInsertID with a row from staging_job not OnComponentOK.
I still don't see why you need tConvertType, tMap_2 and tMap_3; it should all be handled by tMap_1.
There are also problems with how you're committing and rolling-back but I'd rather leave those until the rest is working Smiley Wink
One Star

Re: rollback parent and its related childs and continue processing next re

Hi
I modified to consider row instead on onComponentOk.On doing so it was not giving me the appropiate result.It produced the following result
connecting to socket on port 3988
connected
.--------------+----------------------+--------------.
| tLogRow_2 |
|=-------------+----------------------+-------------=|
|staging_job_id|prime_contract_job_ref|last_insert_id|
|=-------------+----------------------+-------------=|
|0 |JOB0021 |0 |
|0 |JOB0022 |0 |
|0 |JOB0023 |0 |
'--------------+----------------------+--------------'
.-----+------+---------+------------------------.
| tLogRow_4 |
|=----+------+---------+-----------------------=|
|jobId|legRef|num_units|prime_contractor_job_ref|
|=----+------+---------+-----------------------=|
|0 |111 |4 |JOB0021 |
|0 |222 |3 |JOB0021 |
|0 |112 |4 |JOB0022 |
|0 |333 | |JOB0022 |
|0 |333 | |JOB0022 |
|0 |444 |4 |JOB0023 |
|0 |445 |3 |JOB0023 |
'-----+------+---------+------------------------'
.-----+------+-----------+--------------+--------------.
| tLogRow_1 |
|=----+------+-----------+--------------+-------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|
|=----+------+-----------+--------------+-------------=|
|0 |111 |4 | |0 |
|0 |222 |3 | |0 |
|0 |112 |4 | |0 |
|0 |333 | | |0 |
|0 |333 | | |0 |
|0 |444 |4 | |0 |
|0 |445 |3 | |0 |
'-----+------+-----------+--------------+--------------'
disconnected
Job zipRollback3 ended at 13:04 04/10/2012.
Also looking at the logRow it looks as if its inserting all parent data and then inserted all child data.
I have added the parent id in child data.Please let me know if its the wrong way.
One Star

Re: rollback parent and its related childs and continue processing next re

hi,
I modified the design.If all data is correct then insertion takes place fine.But if one of the child fails ,then the parent and all the previously inserted record is rollbacked.
connecting to socket on port 4054
connected
.----------------------+------------.
| tLogRow_1 |
|=---------------------+-----------=|
|prime_contract_job_ref|job_currency|
|=---------------------+-----------=|
|JOB0021 |rs |
|JOB0022 |dh |
|JOB0023 |rs |
'----------------------+------------'
.--------------+--------------.
| tLogRow_4 |
|=-------------+-------------=|
|staging_job_id|last_insert_id|
|=-------------+-------------=|
|0 |360 |
|0 |361 |
|0 |362 |
'--------------+--------------'
.-----+------+---------+------------------------.
| tLogRow_2 |
|=----+------+---------+-----------------------=|
|jobId|legRef|num_units|prime_contractor_job_ref|
|=----+------+---------+-----------------------=|
|360 |111 |4 |JOB0021 |
|360 |222 |3 |JOB0021 |
|361 |112 |4 |JOB0022 |
|361 |333 | |JOB0022 |
|361 |333 |6 |JOB0022 |
|362 |444 |4 |JOB0023 |
|362 |445 |3 |JOB0023 |
'-----+------+---------+------------------------'
.-----+------+-----------+--------------+--------------.
| tLogRow_3 |
|=----+------+-----------+--------------+-------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|
|=----+------+-----------+--------------+-------------=|
|0 |111 |4 | |360 |
|0 |222 |3 | |360 |
|0 |112 |4 | |361 |
|0 |333 |null | |361 |
|0 |333 |6 | |361 |
|0 |444 |4 | |362 |
|0 |445 |3 | |362 |
'-----+------+-----------+--------------+--------------'
.-----+------+-----------+--------------+--------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
| tLogRow_5 |
|=----+------+-----------+--------------+--------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------=|
|legId|legRef|num_of_unit|delivery_units|staging_job_id|errorCode|errorMessage |
|=----+------+-----------+--------------+--------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------=|
|0 |333 |null | |361 |null |Column 'num_of_unit' cannot be null - Line: 3 |
|0 |333 |6 | |361 |null |Cannot add or update a child row: a foreign key constraint fails (`stratus1`.`staging_leg`, CONSTRAINT `FK_staging_leg_1` FOREIGN KEY (`staging_job_id`) REFERENCES `staging_job` (`staging_job_id`)) - Line: 4|
'-----+------+-----------+--------------+--------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
disconnected

Also as observed it looks that it first inserts all parent and then tries to insert all child.My requirement is that it should first insert 1 parent,then its related childs,commit them once successful and then proceed to next.So that even if next record fails then the previously inserted records are not affected.Also the remaining records(remaing parent and child data) should proceed for processing.That is why initially i had been reading the file twice.But as per my requirement i will be extracting a zip file and reading it for inserting it into parent and child tables.Hence require to read the file once.
Please advise
Seven Stars

Re: rollback parent and its related childs and continue processing next re

Not sure why you think it first inserts all parent and then tries to insert all child? If you're going on the tLogRow outputs, the fact that you've selected table output means you can't as the tables, by definition, need to have all rows together for each tLogRow. Change to Basic or Vertical to see exactly when each row passes through each tLogRow.
Anyway, it now appears that the basic job design is correct and working. But as I said before, you need to follow my suggestions from the topic I linked to above for the committing/rolling-back to work as you want. At the moment, your job design is to roll-back everything up to the last rejected child and try to commit only successsful parents and children after that.
Your design is more complicated because of the multiple children and should be like the attached, I think.
tSetGlobalVar_4: "RecordsOK" set to ""
out2 has a filter condition: ((String)globalMap.get("RecordsOK")).equals("Y")
out3 has a filter condition: ((String)globalMap.get("RecordsOK")).equals("N")
tSetGlobalVar_2: "RecordsOK" set to "N"
tSetGlobalVar_1: "RecordsOK" set to "Y"
out5 has a filter condition: ((String)globalMap.get("RecordsOK")).equals("Y")
tSetGlobalVar_3: "RecordsOK" set to "N"
If condition: ((String)globalMap.get("RecordsOK")).equals("Y")
So the first parent passing through tMap_2 will only go to out4.
Each parent will set "RecordsOK" appropriately to Y or N depending on if it's successfully added to the table.
Children will only be attempted if the parent and all preceding children for that parent are successful.
If any child is rejected, "RecordsOK" will be set to N until the next parent is successful.
For each parent after the first, if the preceding parent and all its children were successful they will be committed (otherwise they will be rolled back) before the new parent is attempted.
If the final parent and its children are successful they will be committed.
PS: Still don't see why you need the extra two tMaps.
One Star

Re: rollback parent and its related childs and continue processing next re

Hi,
I modified as per the new design .But now it doesnt insert records only.
Seven Stars

Re: rollback parent and its related childs and continue processing next re

Do you understand what my suggested design is trying to do? If so, you should be able to figure out the problem. It's hard for me to do so based on just the screenprint (no component settings).
Having said that, obviously you need to resolve the tMap_2 errors by giving each of the commit and rollback flows an output schema (doesn't matter what) so they are actually used. Also, I can see that the output flows from tMap_2 are in the wrong order; commit and rollback (being for the preceding parent/child group) must be before the new parent on out2.
One Star

Re: rollback parent and its related childs and continue processing next re

hi alevy
The solution you mentioned worked.I was able to insert parent child records successfully.Also if child failed then it rollbacked its respective parent and siblings and commited the remaining one.
But i have an additional constraint on it.Each child record has to lookup a location table.If record exists in location table it should use the id of the lookup record.If it does not exists then based on context param locationCreate,if true it needs to insert in location table and then use the id.If false it should fail the child and rollback the child and its parent and continue to the next parent as before.
I have modified the design accordingly.But if my child1 of parent1 has location 'abc' which is required to be inserted.And if parent2 also has child1 with location 'abc' which needs to be inserted.Since location we insert during child1 insertion of parent1,it is not required to be inserted for child1 of parent2 else it will duplicate location record.But due to some constraint child2 of parent1 fails.In this case it rollbacks parent1 and its child.But finally even parent2 is not commited.

Please advise.
Seven Stars

Re: rollback parent and its related childs and continue processing next re

Not sure I follow entirely:
- Do you want the location id on the child record?
- If the child fails, do you still want the location for that child committed (if new)?
- Explain what each step in the locationData branch is trying to do.
One Star

Re: rollback parent and its related childs and continue processing next re

hi,
Its like this
we need to insert parent data in job table.
then for each parent,there will be child data(ie is the job leg data.)the child data also has location detail.So based on location detail(name of the location i need to look in location table if location exists.If so i need to use the id(primary key of that particular location record) in child table while inserting the child.If the location specified does not exists in location table,then based on context param locationCreate, if locationCreate is true then we need to insert location in location table and use the id. if locationCreate is false then we will not create location and the record should fail and all its siblings and its parent should get rollbacked.
But if same location name(not existing in location table) exists for 2 different records(Eg parent1's having 3 children, child2 has location 'abc'.Also parent3 having 2 children has child1 with location 'abc'.Since location abc does not exists we need to insert 'abc' in location table before inserting child2 of parent1 in db.Now if we insert 'abc' in location table during the child 2 of parent1's insertion, then during the iteration of parent 2 we shouldnt be inserting location again as it has been inserted during parent1's iteration and would result in duplicate records.Also if parent1's child3 fails then it will rollback its all inserted data including the location data.But since the same location was used for parent 2 it rollbacks the parent2 data also.
So we need a way where location data does not get duplicated in db.And also when parent1 gets rollbacked due to any failure,we still need parent 2 with its child data and location data.
How to handle this kind of situation
Seven Stars

Re: rollback parent and its related childs and continue processing next re

You didn't answer my question: If the child fails, do you still want the location for that child committed (if new)? I'll assume "yes" as that's easier.
I don't see the need for the complexity you have. The attached design should suffice, I think:
tMysqlConnection_2 is used for the location tMysqlOutput, tMysqlLastInsertId_2 and tMysqlCommit_3 so new locations are committed separately from the parents/children
out6 has a filter condition: row9.id==0 && context.locationCreate (assuming location is a left-join and id is an int)
tSetGlobalVar_5: "RecordsOK" set to "N" so parent/children not committed if the location insert fails
tSetGlobalVar_6: stores the new location id
out5 includes the looked-up row9.id
tJavaRow_1 has an expression like: output_row.locationid = input_row.locationid==0 && context.locationCreate?(Integer)globalMap.get("row12.last_insert_id"):input_row.locationid
This assumes that child records will be rejected due to a foreign-key violation on locationid when it is 0 because the location does not exist and context.locationCreate is false. This is necessary to trigger tSetGlobalVar_3 to set "RecordsOK" to "N" for that parent/child group.
Also note that in this design, the location lookup must use the "Reload at each row" model rather than "Load once" so that once a new location is inserted, it can be found for subsequent children. Read Scenario 6 of the tMap help to see an example of how to set this up so only the location id you need is read for each child.
Alternatively, if this makes the job too slow, you could use the "Load once" lookup model but tSetGlobalVar_6 would have to store every new location id rather than just the most recent (e.g. by using the location description as the key name) so that the tJavaRow_1 expression could be like: output_row.locationid = input_row.locationid==0 && context.locationCreate?(Integer)globalMap.get(input_row.location):input_row.locationid