Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

MySQL 5.7 Update; insert if new problem

7 - Meteor

 Hello Alteryx Gurus!


I have a flow that attempts to keep a database of employee information up to date by merging data from several sources.  At one point in my flow, I am attempting to update the database using the, "Update; Insert if new" output option.  I continually get a duplicate entry error at this point.  But that is illogical, because to get a duplicate entry error, there must exist a row that was available to be updated.


I checked my MySQL logs and I see that both the UPDATE and the INSERT statement are occurring.  I believe the UPDATE must be succeeding because there is no rollback after the statement, but there is a rollback after the INSERT.


At a different point in my flow, I get a lot of "Warning: Output Data (22): DataWrap2ODBC: No record found" messages.  After searching the forums, I found a post that said if your update data is exactly the same as the database data, then you get this warning ( ).  Seems strange to say, "no record found," when a record actually was found. But I can live with this behavior. 


At my "Update; Insert if New," step, it appears to me that the warning (which I don't see, but assume is still occurring), is being taken as a reason, to do the insert.  And this causes me to get the Duplicate entry errors.  This is my guess anyway.


So how about it, Alteryx Gurus?  Is this a bug, or a chair to keyboard interface error?  What can I do to solve this problem?


Here is there error message:  




Here is the MySQL log:

2017-11-16T13:58:46.977052Z 15 Query COMMIT
2017-11-16T13:58:46.977052Z 15 Prepare UPDATE `mv_employee_role_info` SET `AD_TITLE`=?, `HR_SUPERVISORY_LEVEL`=?, `HR_MANAGER_LEVEL`=?, `HR_JOB_CODE`=?, `HR_LASTUPDATE_DTTM`=?, `HR_MANAGER_LEVEL_DESCR`=?, `HR_TITLE`=? WHERE `HR_EMPLID`=?
2017-11-16T13:58:46.977052Z 15 Prepare Insert into `mv_employee_role_info`(`HR_EMPLID`, `AD_TITLE`, `HR_SUPERVISORY_LEVEL`, `HR_MANAGER_LEVEL`, `HR_JOB_CODE`, `HR_LASTUPDATE_DTTM`, `HR_MANAGER_LEVEL_DESCR`, `HR_TITLE`) Values (?,?,?,?,?,?,?,?)
2017-11-16T13:58:46.992668Z 15 Query set @@sql_select_limit=DEFAULT
2017-11-16T13:58:46.992668Z 15 Execute UPDATE `mv_employee_role_info` SET `AD_TITLE`='None', `HR_SUPERVISORY_LEVEL`='SPVSRY', `HR_MANAGER_LEVEL`='7', `HR_JOB_CODE`='cccccc', `HR_LASTUPDATE_DTTM`='2017-07-10 00:00:00', `HR_MANAGER_LEVEL_DESCR`='Supervisor', `HR_TITLE`='Super-Water Quality Lab' WHERE `HR_EMPLID`='aaaaa'
2017-11-16T13:58:46.992668Z 15 Execute Insert into `mv_employee_role_info`(`HR_EMPLID`, `AD_TITLE`, `HR_SUPERVISORY_LEVEL`, `HR_MANAGER_LEVEL`, `HR_JOB_CODE`, `HR_LASTUPDATE_DTTM`, `HR_MANAGER_LEVEL_DESCR`, `HR_TITLE`) Values ('aaaaa', 'None', 'SPVSRY', '7', 'cccccc', '2017-07-10 00:00:00', 'Supervisor', 'Super-Water Quality Lab')
2017-11-16T13:58:46.992668Z 15 Query ROLLBACK


@patrickk79 Can you confirm that the data with the duplicate key is changed or different? As you pointed out MySQL requires that update requests to a row controlled by a primary key make some sort of change to the data present in that row. If the data is identical to what is currently in the row MySQL will error and fail to update or insert the records. Whether you get the error or warning response depending on the output method selected. An easy way to avoid this is to include a date field that time stamps updates to the data. You can easily set a value for this field with the DateTimeNow() function, and this will ensure there is always a change to any existing values in the database. Even if that change is only to the time stamp itself. Since the data changes the update should succeed without error or warning. Alternatively, you could parse out any unchanged records before submitting your update to the database.


You can also try enabling the safe mode option in your MySQL ODBC driver as we have had some users have success with this option enabled. Please note thought that if you share this workflow or upload it to a server you will likely encounter the error again unless the option is also disabled on the server or other users driver.

7 - Meteor



Thanks for the response.  


MySQL may require a difference before actually making an update, but it seems to me that Alteryx's interpretation of the MySQL result is wrong.  I ran the update below using the MySQL command line client.  As you can see, no changes were made, but it did MATCH a row.  Since MySQL reported that it found a match, why would Alteryx then decide it should do an insert?  I got a match, so an insert is doomed to fail.


mysql> update mv_employee_role_info set
    ->         AD_TITLE = 'None', HR_SUPERVISORY_LEVEL = 'SPVSRY', HR_MANAGER_LEVEL = '7', HR_JOB_CODE = 'bbbbbb',
    ->         HR_LASTUPDATE_DTTM = '2017-07-10', HR_MANAGER_LEVEL_DESCR = 'Supervisor', HR_TITLE = 'Super-Water Quality Lab'
    -> where HR_EMPLID = 'xxxxx';
Query OK, 0 rows affected (0.09 sec)
Rows matched: 1  Changed: 0  Warnings: 0


7 - Meteor



I tried the safe mode driver setting that you suggested.  I did not see any change in behavior.

5 - Atom

I am also having a problem with the Update; Insert if new command, connecting to a MySQL db (5.6.17). My situation is a little different to the OP's so if the mods want to move this to a different thread, by all means do so. I put it here because my thread title would be almost identical.


I can use "Append Existing" on the rows of data the first time they are found, but I cannot use "Update; Insert if new". When I try to do so, either with a small change to one of the fields or with new records as part of the set it fails to update or insert any new rows that might have appeared.


I get this error:

Output Data (51) Error SQLPrepare: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.17]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


The same error occurs when I use the Update; Warn and Update; Error options. Append Existing fails to spot the updates to the row, and fails because the unique index already exists. When I use Delete & Append, it truncates the table, inserts the processed rows ok, but everything that is not in the current output (historical data) is obviously wiped.


I have tried with multiple db users including root and always get the same errors.


I am migrating existing functionality to Alteryx. Previously I have used INSERT ... ON DUPLICATE KEY UPDATE ... and I absolutely need this capability. Some of my existing tables are GB-sized and I simply cannot bring them into the workflow and work out which of the workflow outputs are new, which are updates and which are duplicates.


Maybe there is a way to output a flat file and then tell Alteryx to run some custom sql on that? If not, I don't know what I'm going to do here... Any suggestions (that don't involve taking millions of rows out of the db)?

5 - Atom

Further to my post directly above, I have found that my problem occurs only:

1) when there are no new rows to insert and

2) at least one of the rows is unchanged


In other words, if the dataset includes new rows, it will insert them and update existing records. Similarly, if all rows have changes it again works perfectly.


The problem occurs when only some of the rows have updates AND when none of the rows are new inserts.

7 - Meteor

Sounds like your situation might call for Alteryx's In-DB tools.  Hopefully you'll be able to identify changed vs new rows and then output them to separate update tools.  I haven't used those tools yet, so I can't provide any real insight. 


I solved my own problem by re-working my process so that I only had new rows at that step, so that I could use the Append Existing option.


Good luck.

5 - Atom

What I ended up doing to get around this was to join my input data (input left) with the existing output table (input right) and joining on the key fields.  I then took the output left, which should contain only new records and set the output to Append Existing.  I then take the output join and Update, warn on update failure.


All new records are added, and any existing records that match on the existing keys will update and throw warnings, but won't stop the workflow.

7 - Meteor

My workaround:

- in MySQL: created one column in the target table called etl_timestamp

- in Alteryx workflow: appended a new column to the output that changes with every run an puts the current timestamp as a new column


This way MySQL will always see an update on the already existing rows and will update the matching ones and also will insert the new ones successfully.

7 - Meteor

hi @jpenston ,


I am also having the same issue here with Update;Insert if new. Were you able to find any concrete solutions?