Home Forums OpenSync Error when trying to delete Invoice : Data too long for column Invoice.IDKEY

This topic contains 7 replies, has 4 voices, and was last updated by  Yudel Rosales 1 month, 1 week ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
  • #14964


    I’m encountering a confusing but consistent error when attempting to process Invoices that have been marked for deletion. I have tested and confirmed that this error affects invoices created through OpenSync as well as invoices created natively in QuickBooks.

    My Environment
    PC running Windows 10 Pro
    QuickBooks Enterprise 2015 (15.0 R5_20)
    MyODBC Drivers (64-bit v5.3.6)
    MySQL Server (64-bit v5.6.28)
    OpenSync v2.0.76

    Steps to Reproduce

    1. Create 3 new Invoices in QuickBooks (mine were all on the same Customer).
    2. Run an OpenSync Refresh/Update on the Invoice table.
    3. Confirm that the OpenSync Refresh() has transfered the three new Invoices back into the MySQL database.
    4. Get the Invoice.TxnID values for the three Invoices.
    5. Update the Invoice records and set Status = 'DELETE' on all three of them.
    6. Run a OpenSync Refresh/Update job on the Invoice table to apply those changes from MySQL to QuickBooks.

    For me, the last step in that sequence causes OpenSync to report an error (viewable through OpenSync in the MySQL Error Table). The error is indicating that there was an error deleting the 1st invoice marked for deletion. The error also seems to have interrupted the Update() job, so it never attempts to process the other 2 invoices marked for deletion.

    Error Num: -2147467259
    Error Table: invoice
    Error Field: critical
    IDKEY: 70C5FE-1478097412 (in my case, the Invoice.TxnID of the first invoice marked for deletion).
    Error Desc: [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.28]Data too long for column ‘IDKEY’ at row 1

    After encountering this error, the task exits. I investigated QuickBooks and the MySQL database. From what I can tell, the 1st invoice was actually deleted. I see no trace of the 1st Invoice or its InvoiceLineItem records in QuickBooks or MySQL. The other 2 invoices failed to delete in QuickBooks, but it looks like they’ve retained their Status='DELETE' state in the MySQL database. So, two more consecutive iterations of the same Invoice update() task would eventually delete these invoices from QuickBooks. However, this is not an acceptable solution considering there might be times where I have hundreds more invoices marked for deletion.

    I have verified that the column reported by the error (Invoice.TxnID) is configured as a varchar(255), so I’m not sure how a TxnID value is at risk of overflowing the field and generating this truncation error.

    You must be logged in to view attached files.


    We figured out the issue, so I’m following up. We enabled query logging in MySQL so that we could catch a glimpse of the commands that were executing against our MySQL database as the Invoice Refresh()/Update() task was running in OpenSync.

    Here’s what we found. I’ve attached a copy of the relevant MySQL query log lines that were captured while running the OpenSync task.

    It looks like there’s a syntax error in a SQL statement attempting to cleanup records in the txnitemlinedetail that may have been related to the invoice being deleted.

    Here’s the SQL statement from our MySQL query log file (line 16 of the attached log file):

    DELETE from txnitemlinedetail where IDKEY = 70C60C-1478103950

    As you can see, the IDKEY value is not wrapped in single quotes as is should be, so MySQL is interpreting the value as a column reference which doesn’t exist instead of a string value.

    That’s the first problem, and as a result, the deletion of these potentially related records will fail.

    However, I don’t believe that’s the error causing the rest of the Refresh()/Update() job and remaining queued Invoice deletes to fail.

    Right after that DELETE statement fails, OpenSync then attempts to log the error to the “error_table” database table by executing the following statement:

    Insert into error_table (error_table, error_num, error_field, error_desc, time_date, IDKEY) values ('invoice', '3', 'txnid', '[MySQL][ODBC 5.3(a) Driver][mysqld-5.6.28]Unknown column ''70C60C'' in ''where clause''', '2016/11/02 11:29:40', 'Microsoft OLE DB Provider for ODBC Drivers')

    I believe it’s THIS step that actually causes the rest of the OpenSync job to fail. For some reason, it looks like the “IDKEY” value in this case is instead making reference to the ODBC Driver Name, not the actual Invoice TxnID/IDKEY value. In the “error_table” database table, the “IDKEY” column is configured as a VARCHAR(40), and the value that’s being set in this query statement is 42 characters long. THIS is where the truncate message is emanating from.

    In our particular case, we don’t have any records at all in the “txnitemlinedetail” table. So, we’re able to work around the issue temporarily by simply expanding the column size of error_table.IDKEY and making it a VARCHAR(255) instead so that this truncation does not occur. However, this does not address the core issue that records in the txnitemlinedetail table will fail to delete properly under this set of circumstances.

    I have attached a subsection of our MySQL query log that details the execution of the Refresh()/Update() job and the error that was encountered so that the Synergration development team can review all the details. This is definitely a BUG that should be fixed.

    If you have questions or would like any additional information from us about recreating this issue, please contact me via email. Otherwise, we hope that Synergration will promptly author and release a fix for this issue. Honestly, we’re surprised that nobody has ever encountered or reported this issue before. I guess nobody has tried deleting Invoices through OpenSync?



    Looks like our log file didn’t attach to the last post based on file type restrictions. Trying again here with a zipped version of the same file.

    You must be logged in to view attached files.

    Tom Crawford

    Thanks for the superb diagnostics on this. We will look into it and get a patch out.



    Thanks, Tom. If you can provide any details on when we could expect a patch for this issue, we would greatly appreciate it.


    Tom Crawford

    Premium Member

    This is also occurring in the latest 3.0.28

    We are unable to delete invoices.

    Exactly the same issue.


    Yudel Rosales

    what is the error that you are having when trying to delete an invoice?

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

You must be logged in to reply to this topic.