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
- 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.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.