MULE: MYSQL CONNECTOR INSERT ON DUPLICATE UPDATE

Question

MULE ESB 3.7 Community Edition, MYSQL DB Connector, Operation: Insert, Type: Parameter Anypoint Studio: Version: 5.4.0

MySQL Query:

INSERT INTO TABLE1 (VAR1,VAR2) VALUES (2,1) ON DUPLICATE KEY UPDATE VAR1= 2, VAR2 = 1;

VAR1 is a Unique Key for the table. It is NOT the primary key.

Problem: The above query works perfectly well from MySQL Workbech. In Mule, all works well as long as there are no duplicate values for VAR1 in the data. BUT If there is a duplicate value for VAR1 in the data, then following error comes

ERROR 2016-07-26 13:47:32,538 [[project1].HTTP_Listener_Configuration.worker.01] org.mule.exception.CatchMessagingExceptionStrategy:

Message : Duplicate entry '1' for key 'VAR1_KEY_UNIQUE' (com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException). Message payload is of type: LinkedHashMap Type : org.mule.api.MessagingException Code : MULE_ERROR--2 JavaDoc : http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html Payload : {VAR1=55,VAR2=97} SQL Code : 1062 SQL State : 23000

Exception stack is: 1. Duplicate entry '1' for key 'VAR1_UNIQUE'(SQL Code: 1062, SQL State: + 23000) (com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException) sun.reflect.NativeConstructorAccessorImpl:-2 (null) 2. Duplicate entry '1' for key 'VAR1_KEY_UNIQUE' (com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException). Message payload is of type: LinkedHashMap (org.mule.api.MessagingException) org.mule.module.db.internal.processor.AbstractDbMessageProcessor:93 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html)

QUESTION: How to make the INSERT ... ON DUPLICATE KEY UPDATE query work from Mule Anypoint Studio MySQL Connector?

Please avoid unnecessary bolding/uppercase and use appropriate formatting to make your post more readable. — mauris
this error says you have already an existing primary key value in your table. Check your code and avoid working like inserting a record in primary key field. — Vijunav Vastivch
@reds - VAR1 is a Unique Key. It is not the primary key. As per business logic, it needs to be inserted or updated as the case may be. The query given above works when I use it via workbench but gives an error when processed via Mule (Anypoint Studio) using CE 37. I am not sure what is causing the error. Hence the post. — Raj
We could trace the error to definition of variables in the Mule Flow. This query works well with Insert parameter. This post can be closed. Thanks. — Raj

duddukurimd duddukurimd · Accepted Answer · 2016-07-27T10:19:55

We can insert with duplicate key. please check SQL Query page, it might help you

enter link description here

MULE: MYSQL CONNECTOR INSERT ON DUPLICATE UPDATE

1 Answers