OpenSync

Home Forums OpenSync Duplicate entry for key 'itemnoninventory.PRIMARY'

This topic contains 3 replies, has 2 voices, and was last updated by  Yudel Rosales 9 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #67214

    store
    Participant

    Hello,

    I’m moving my install to a new server and am upgrading to 3.0.28 from 3.0.25.
    Running QB 2019 Enterprise, exporting data to a MySQL Database.

    I’ve setup a test server, created a backup of my live QB environment, restored it in the new environment. Installed Opensync 3.0.28. Connected to database, had opensync create a new database and selected the following tables for sync REFRESH FROM QUICKBOOKS:

    Account, Bill, Class, Company, Deposit, Inventory Adjustment, Invoice, Item Discount, Item Fixed Asset, Item Group, Item Inventory, Item Inventory Assembly, Item Non Inventory, Item Other Charge, Item Payment, Item Receipt, Item Sales Tax, Item Sales Tax Group, Items, Journal Entry, Purchase Order, Sales Order, Sales Receipt, Sales Tax Code, Vendor, Vendor Credit, Vendor Type

    When running the refresh on the new blank database I’m receiving the following error message:

    Error: [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.20]Duplicate entry ‘80002AB1-1527799310’ for key ‘itemnoninventory.PRIMARY’: itemnoninventory SalesOrPurchase in table items:-2147217900

    Can you assist with this please.

    #67219

    Yudel Rosales
    Keymaster

    You have to delete the primary key of all the item table that you will populate from the database base. That will do it.

    #67222

    store
    Participant

    Thanks, that did solve it and I did find that two of the exact same records were exported from Quickbooks. In this case the item was a ‘deleted’ item. I have encountered issues like this before https://synergration.com/forums/topic/the-itemgroup-table-is-populated-twice/ where duplicates of the same item were exported. Clearly I’m not an expert on QB Databases, would there be a possibility to run the export query as a DISTINCT? it will eliminate any duplicate issues where all fields are the same anyway. There must be a lot of junk building up over time in the QB database that doesn’t get fixed with a rebuild. I performed the same operation with a much smaller database / company file as described above without any issues before.

    Unfortunately, more errors popped up on other tables and I have decided to grab an old database structure I had before my server migration that did not have the constraints (which should be there – I agree). That database also had more non-unique indexes, so I won’t have to re-create these.

    Thanks!

    #67231

    Yudel Rosales
    Keymaster

    you are very welcome

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.