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.
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)
Steps to Reproduce
Status = 'DELETE'on all three of them.
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.
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.
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.
You can download the fix from http://download.synergration.com/opensync2.0.78.msi
This is also occurring in the latest 3.0.28
We are unable to delete invoices.
Exactly the same issue.
what is the error that you are having when trying to delete an invoice?
You must be logged in to reply to this topic.