Issues with K1 Database and MySQL Connector
I've been working with Quest support for a little bit on this issue and wanted to see if it's just our Org and how I could get my PowerBI reports working again.
Issue: Connecting to K1 database with MySQL/Net connector does not allow data to be pulled from multiple tables giving the error "An item with the same key has already been added" (Examples ORG1.MACHINE, ORG1.MACHINE_LOCATION)
When: After updating from 8.x to 9.x
What: Connecting to the DB via PowerBI/Excel (both use the Net connector)
I've verified that v6.10.7 MySQL/Net connector use to work, but does not anymore. Also tested 3 other 6.x versions and the latest 8.x.
I have found a page that describes the issue when PowerBI sees an indexed column with the same data more than once, it will throw that error. Indexed columns in ORG1.MACHINE: ID, KUID, OS_ID (Definitely have duplicates here), Inventory_Started (Could have duplicates here), BIOS_SERIAL_NUMBER, CS_MANUFACTURER (Definitely have duplicates here), NAME. I have checked all of these for duplicates and found none where expected (ID, KUID, BIOS_SERIAL_NUMBER, NAME).
Answers (1)
I was able to get it working with help from Quest support.
It seems like the MySQL/.NET connector works fine for 98% of the tables in the K1.
For the rest, I downloaded the MySQL/ODBC connector, set up a connecting in Windows ODBC, then moved the queries that were broken to the ODBC connection instead of the .NET.