We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

TDCE-49: When attempting to Write to MySQL with the output option "Update: Insert if New" you get an error if the table has a composite primary key of multiple fields

ntobon
Alteryx
Alteryx
Created

Issue

 

When attempting to write to MySQL with the output option "Update: Insert if New" you get an error if the table has a composite primary key of multiple fields and none of the data have changed in the row.
 

Output Data (7) DataWrap2ODBC::SendBatch: [Simba][MySQL] Duplicate entry '1-1-2022' for key 'PRIMARY' Insert into `borrell_update`(`countyCode`,`sicgpCode`,`year`,`employeeCount`) Values (?,?,?,?)
Designer is is trying to use an "Insert" statement rather than “Update” statement. 
 

Environment Details

 
  • Alteryx Designer
    • All Version(s).
  • Drivers: Simba MySQL ODBC Driver Version: 1.1.2.1002.   MySQL ODBC driver version 8.0.30


Cause

 

Defect: TDCE-49
Status: Open.



Diagnosis

The error occurs when all fields are exactly similar. You don't get the error if you try to update records that have actually changed.

 

Resolution

 
Defect TDCE-49 is target for resolution in future release.
 

Workaround #1:


If you are using MySQL driver, in the ODBC driver configuration, expand the details then check the "Enable safe options" under the Misc tab.

image.pngimage.png


Workaround #2:


Append a field (Eg: datetime ) to ensure that a certain value is always moving which makes the record to not be duplicate. You can add another field to the database table and set the value at current datetime, so that the value always gets updated.