Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
There's still time to register for the Q3 ACE Panel - Spatial Edition presentation tomorrow! | Need more information about the event? Check out the post here

Does Alteryx allow multiple primary keys when outputting data to a MySQL database?

Highlighted
5 - Atom

So as the title states I have a table in a MySQL database that has two columns designated a primary keys.  When I run the job in Alteryx for the first time everything loads fine into the database.  However when I try and run it a second time to update the data in the database I get the below error.

 

" Output Data (35) DataWrap2ODBC::SendBatch: [MySQL][ODBC 5.3(a) Driver][mysqld-5.5.32-enterprise-commercial-advanced-log]Duplicate entry 'DPL2LPI' for key 'PRIMARY' Insert into `SC_WU_Exshare`(`User_ID`,`Max_Date_Time`,`Sum_Sec`,`Update_Type`) Values (?,?,?,?)"

 

Which appears to state that there are duplicate primary keys and I don't know why the values are "?,?,?,?".

 

I also noticed when I look at the Append field map section in the output data component. Only one column is labeled as (key) and I don't see anyway to edit that even selecting "custom mapping"

 

Does anyone have any thoughts on these issues?

Highlighted
12 - Quasar

@EArcher

are you Running the same data set 2nd time, then yes, its a Duplicate entry for your PK

as per the error message "DPL2LPI" is the row has Dup rows. 

also, you can debug the data for dups as below

use Summarize tool and group by composite keys

use count 

use filter tool to get count >1

Dup.PNG

 

also, you can see the custom mapping as below image

 

Mysql Mapping.PNG

 

Hope this helps or let me know if the problem still persists

Highlighted
5 - Atom

Hi s_pichaipillai,

 

 

 

Highlighted
12 - Quasar

@EArcher

 

i see what you are trying to do and i will explain when Update , If new row option works

1. This option will work when there is a row which already loaded but changed in source ,so when you run it next time this will work. well, there is a catch, if ONLY one row has changed and But the other rows this will raise an error

so the workaround is, 

Create a FULL JOIN with Source and Target then Append the LEFT Output

Check for the changed rows and use the another output tool with Update if new Option

Below Workflow Image explains it :)

 

Update: to test it modify Employee name in the text inout tool then run it

UpdateNew.PNG

 

and also , i posted another solution similar to this .but ONLY with LEFT JOIN

http://community.alteryx.com/t5/Data-Preparation-Blending/Output-Data-Tool-MS-Access-Primary-Key-Req...

Choose whatever you like :)

PFA sample workflow i developed in 10.1 Version.

and Please feel free to ask me if anything comes up

 

@Grahambo

FYI.. this explains why it did not work for you when you used Update If new Option. 

Thanks

Saravanan

Highlighted
12 - Quasar

FYI below is the table script , if you want to test the workflow

CREATE TABLE `Employee` (
  `EmpID` int(11) NOT NULL,
  `EmployeeName` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`EmpID`)
)
Highlighted
5 - Atom

Hmmm so you're saying that Alteryx doesn't allow updates and inserts to be done at the same time correct?  That seems a bit odd based on other data transformation software i've used I would have thought this would be a base feature.

 

Either way if thats the case i'll have to use the work around.


Thanks again for the help!

Highlighted
6 - Meteoroid

Hi all,

 

I'm having the same issue here : I have a Mysql table with 2 primary key, and when I try to insert (update if new) into this table, ALteryx consider only one primary key and therefore does not insert all the lines I want.

The proposed solution is for me not a practical solution as you need to load the full table every time you want to insert/update a new line. So if you have one line to insert into a 1 million lines table, it will be very slow.

 

I have the solution to create a staging table (without PK ) and do a post Create SQL treatment :

INSERT INTO table ( SELECT * FROM staging_table ) ON DUPLICATE KEYS UPDATE SET field_to_change = VALUES( field_to_change )

I don't want to use this solution because it's just a workaround for me, and I don't want to create staging table everytime I want to do an insert.

So is there a way to support the multiple Primary keys on a MySQL database with the output tool? And if not I would really push to have this as a build in functionnality in Alteryx.

 

Thanks

Highlighted
Alteryx
Alteryx

Thank you for reporting this issue. There was a dfect with this functionality that has been fixed for the next release.

Thanks,

Linda

 

Highlighted
5 - Atom

Hi Lynda,

Could you please advise in which version it's fixed? I'm using 10.5.9 and it's still not working even with 1 primary key. It does update but doesn't do insert.

thank you!

Luba

Highlighted
Alteryx
Alteryx

The fix is in the new release 11.0

Labels