MySQL batch modes - how can I find the rejects?

[Anonymous]

Hi All,
I am looking to load a large number of rows into a MySQL table and would prefer to use one of the batch modes; either bulk insert or using the MySQL extended insert option.
The problem is that for either of these methods there is no flow for rejects. In the bulk insert there is no option to create a "badfile" from which I can determine the rejects. I have tried adding a tMySQLInput with the command SHOW WARNINGS to the job immediately after the exec, but this does not seem to return data if records are rejected.
In terms of using the extended insert I have noticed that if a failure of 1 record in the batch will reject all the records in the batch. In this instance though the SHOW WARNINGS does work.
I am considering the following for extended inserts:
- Read the input data 100 rows at a time
- write using extended inserts
- test for error
- if error then write the batch using non extended to gather the rejects.
For bulk mode I am considering writing to a temp table then performing a merge of the temp and actual tables. I haven't tried this yet, so don't know if this will allow me to detect any errors.
I was wondering therefore if anyone has any alternative methods/best practice/advice as to the most elegant way to achieve this?

Regards,
Rick

[Anonymous]

Hi, tchd
Thanks your attention on Talend. For your issue description, I just say yes, the batch model and extended insert can't used with reject together. The reject option requires the data to be processed one by one. Usually, we need to do some validation before loading them to target system, to avoid the error can be predicted, for example, use tSchemaComplianceCheck to check the length of data, null value, and date.

Best Regards
Sabrina

[Anonymous]

Thanks Sabrina,
In the end we have gone for extended insert with a die on error, so that the whole batch rolls back on error. The principle is as per your email in that we will ensure the data is clean prior to load.
I did find out that you can retrieve errors from the bulk loader by adding a MySQL input with 3 columns and the command "SHOW ERRORS". This will display any errors found during the load. Unfortunately it doesn't display rejected duplicates but that is a MySQL issue.
Regards,
Rick