This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 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:
Error: Output Data (152): DataWrap2ODBC::SendBatch: [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.17-log]Duplicate entry 'aaaaa' for key 'PRIMARY' Insert into mv_employee_employment`(`HR_EMPLID`,`HR_SUPERVISOR_EMPLID`,`HR_EMPLOYMENT_STATUS`,`HR_EMPLOYMENT_TYPE`,`HR_PAYROLL_STATUS`,`HR_TERMINATION_DTTM`,`HR_DEPTID`,`HR_DEPTNAME`,`HR_LASTUPDATE_DTTM`,`HR_REG_TEMP`,`HR_FULL_PART_TIME`,`HR_FTE`,`HR_ACTION_EFFDT`,`HR_ACTION_DT`,`HR_ACTION_CODE`,`HR_ACTION_DESC`,`HR_ACTION_REASON_CODE`,`HR_ACTION_REASON_DESC`,`HR_POSITION_NBR`,`HR_JOB_CODE`,`HR_REPORTS_TO`,`HR_FLSA_STATUS`) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
@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.
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.
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)?
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.
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.