Weird Error : Duplicate entry 'Key Value' for key 'PRIMARY'

[Bhagwati1]

Hello Guys,

Could you please help?

Problem Description : 

'Reject' component of tMysqlOutput is triggered even when there is  no rejected row.The row mentioned as rejected in console log is actually inserted.
In my Sub job i send an email whenever a row is rejected  and because of above issue , a false email notification is sent even when there is no rejected row.

Details :  

MySql Server : 5.7.11

Talend Open Studio :  6.1.1

I have a job, which inserts data from Excel files stored in a folder to Mysql table.I capture the 'Reject' trigger and notify the user about the rejected row.I am getting Duplicate entry 'Key Value' for key 'PRIMARY' error only when last file of the folder is processed.Mysql table has a Primary Key which consists of 3 columns.

Data is loaded into a staging table which is always cleared before loading. Basically it is a false error as the sub job inserts all the data from excel. What i noticed is that the error message always contains the primary key from the first row inserted.

Below are tlogrow output and error msg.

For input string: "Investor_id"
Audi_Inv|750|Infosys|We|B|1297|23/12/2015|530|It_Funds_US|0.63|1
Audi_Inv|750|Infosys|We|B|1102|05/12/2015|536|It_Funds_Asia|0.52|13
Audi_Inv|750|Infosys|We|B|1169|25/12/2015|542|It_Funds_US|0.6|11
Audi_Inv|750|TCS|We|S|1273|03/12/2015|560|It_Funds_APAC|0.1|2
Audi_Inv|750|TCS|We|B|1127|01/12/2015|572|It_Funds_Asia|0.52|0
Audi_Inv|750|TCS|We|S|1250|01/12/2015|596|It_Funds_US|0.32|1
Audi_Inv|750|IBM|We|S|1260|29/12/2015|620|It_Funds_APAC|0.13|2
Audi_Inv|750|IBM|We|S|1026|18/12/2015|626|It_Funds_US|0.27|11
Audi_Inv|750|IBM|We|B|1134|04/12/2015|632|It_Funds_New|0.1|5
Audi_Inv|750|TechnoCr|We|S|1187|21/12/2015|698|It_Funds_APAC|0.51|-2
Audi_Inv|750|TechnoCr|We|S|1007|02/12/2015|704|It_Funds|0.32|7
Audi_Inv|750|TechnoCr|We|B|1059|28/12/2015|728|It_Funds_New|0.36|4
Audi_Inv|750|TechnoCr|We|B|1070|12/12/2015|734|It_Funds_New|0.24|4
Audi_Inv|750|GenX|We|S|1016|26/12/2015|746|It_Funds_APAC|0.58|7
Audi_Inv|750|GenX|We|B|1258|01/12/2015|770|It_Funds_US|0.31|10
Audi_Inv|750|GenX|We|S|1176|13/12/2015|776|It_Funds_Asia|0.35|-2
Audi_Inv|750|GenX|We|S|1255|25/12/2015|782|It_Funds_US|0.43|-7
Audi_Inv|750|GenX|We|B|1079|06/12/2015|788|It_Funds_New|0.66|-6
Audi_Inv|750|Sonata|We|S|1020|25/12/2015|830|It_Funds_Asia|0.58|13
Audi_Inv|750|Sonata|We|B|1285|30/12/2015|836|It_Funds_US|0.38|-4
Audi_Inv|750|Sonata|We|B|1011|09/12/2015|854|It_Funds|0.52|5
Audi_Inv|750|TechM|We|B|1224|10/12/2015|878|It_Funds|0.64|-7
Audi_Inv|750|TechM|We|B|1174|26/12/2015|896|It_Funds_New|0.68|1
Audi_Inv|750|Infosys|We|S|1244|22/12/2015|956|It_Funds_US|0.44|10
Audi_Inv|750|TCS|We|S|1084|29/12/2015|998|It_Funds|0.3|-2
Audi_Inv|750|TCS|We|S|1078|21/12/2015|1004|It_Funds|0.38|5
Audi_Inv|750|TCS|We|S|1163|03/12/2015|1028|It_Funds|0.42|8
Audi_Inv|750|IBM|We|B|1282|06/12/2015|1040|It_Funds|0.2|-5
Audi_Inv|750|IBM|We|S|1223|24/12/2015|1046|It_Funds_APAC|0.64|13
Audi_Inv|750|IBM|We|S|1227|25/12/2015|1058|It_Funds_US|0.17|8
Audi_Inv|750|IBM|We|B|1182|12/12/2015|1076|It_Funds_Asia|0.25|-4
Audi_Inv|750|IBM|We|B|1140|15/12/2015|1082|It_Funds_US|0.4|13
Audi_Inv|750|IBM|We|S|1102|26/12/2015|1088|It_Funds_US|0.29|3
Duplicate entry '2015-12-01-572-We' for key 'PRIMARY'.

I am attaching job screenshots as well.

Thanks,

Bhagwati

[Anonymous]

Hello
Duplicate entry '2015-12-01-572-We' for key 'PRIMARY', it is duplicated entry . You are iterating multiple files, there must exist same entry '2015-12-01-572-We', only the first entry '2015-12-01-572-We' will be inserted into db, other duplicated entry will be rejected. For testing, you can unite all records before inserting, and use a tUniqRow to get the unique records and duplicated records, eg..
....tMap1--tUnite--tUniqRow--duplicated--tLogRow2

Check if tLogRows has records output.

Regards
Shong

[Bhagwati1]

Hi Shong,

I appreciate your response.

I need to notify the client whenever a duplicate row is received in the same file. And this can not be achieved if i use the approach you suggested.

Now back to the the query i raised, There are 2 things,

1.Table is blank while inserting the records,  So it is impossible that the error is raised because primary key constraint was violated.

2.The row which is shown as duplicated /rejected is actually inserted into the table.

Thanks,

Bhagwati

[Anonymous · Accepted solution]

Hi
1. If there exists duplicated entry, the error will be raised and the duplicated entry will be rejected, this is the normal situation, why you said it is impossible?
2. The unique entry will be inserted, the duplicated entry will be rejected. For example, assuming the input data are:
Audi_Inv|750|TCS|We|B|1127|01/12/2015|572|It_Funds_Asia|0.52|0
Audi_Inv|750|TCS|We|B|1250|01/12/2015|572|It_Funds_US|0.32|1
Audi_Inv|750|IBM|We|S|1260|29/12/2015|620|It_Funds_APAC|0.13|2
The first line and the second line have the same primary key '2015-12-01-572-We', after you execute the job, the first line and the third line will be inserted into DB, the second line will be rejected.

Regards
Shong

[Bhagwati1]

Thanks Shong.