community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

MySQL 5.7 Update; insert if new problem

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 ( https://community.alteryx.com/t5/Data-Sources/MySQL-ODBC-Connector-error-on-update/m-p/10707#M792 ).  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:  

 

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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

 

Here is the MySQL log:


2017-11-16T13:58:46.977052Z 15 Query SELECT `HR_EMPLID`, `AD_TITLE`, `HR_SUPERVISORY_LEVEL`, `HR_MANAGER_LEVEL`, `HR_JOB_CODE`, `HR_LASTUPDATE_DTTM`, `HR_MANAGER_LEVEL_DESCR`, `HR_TITLE` FROM `mv_employee_role_info`
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

Highlighted
Alteryx
Alteryx

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

Meteor

@KevinP,

 

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

 

Meteor

@KevinP 

 

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

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)?

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.

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.

Labels