- This topic has 14 replies, 3 voices, and was last updated 1 week, 6 days ago by
.
-
Posts
-
Hello, SI has started giving me this error out of the blue:
DataTables warning: table id=si-data-table - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1Nothing changed on my end but I cannot load years and years of invoices and customers anymore so I’m kinda screwed.
Does anyone know what this error means and how to solve it?I am using: Version: 2020.0.10 — 20201217
I’ve done more digging and I found that the file /public/data.json is empty… I’ve tried restoring it from backup but it doesn’t change… I’ve also tried deleting it in case it’s corrput but I keep getting the same error (the data.json) file is re-created upon page load but I keep getting the same error and no data in the tables…
This happens ONLY with the INVOICES and CUSTOMERS data, it does not happen with USERS, PAYMENT TYPES and all the others…
I’ve looked in the error logs that are being created and I see a series of errors like these:
[18-Jul-2025 17:46:44 America/New_York] Invoice::getInvoices() – Owing discrepancy on invoice id[1327] – index_id[1329]. Calculated owing[375] not equal to invoices table owning[0.000000]I don’t know how/why these errors are created and how to fix them but any advice would be greatly welcome and I’m willing to hire somebody to fix it.
I did write in the first post that I was using Version: 2020.0.10 — 20201217
I will see if I can update to the latestI have just updated to the last 2023 version however during the update I go this error:
SqlPatchManager::runSqlPatch() error. See error log for more information.
and now it is stuck and won’t proceed to the SQL patch 331.
I navigated to:
tmp/log/php.log
and the last line says:[19-Jul-2025 21:38:25 America/New_York] Unable to apply patch 331. Found foreign key table columns with NULL values or values not in the reference table column. The following list shows what values in foreign key columns are missing from reference columns. The records in the FOREIGN KEY TABLE are most likely invalid and should be deleted. FOREIGN KEY TABLE COLUMN REFERENCE TABLE COLUMN INVALID VALUE ------------------------ ------------------ ----------------------- --------- ------------- invoice_item_tax invoice_item_id invoice_items id 2 invoice_item_tax invoice_item_id invoice_items id 3 invoice_item_tax invoice_item_id invoice_items id 4 invoice_item_tax invoice_item_id invoice_items id 5 invoice_item_tax invoice_item_id invoice_items id 7 invoice_item_tax invoice_item_id invoice_items id 8 invoice_item_tax invoice_item_id invoice_items id 162 invoice_item_tax invoice_item_id invoice_items id 322 invoice_item_tax invoice_item_id invoice_items id 471 invoice_item_tax invoice_item_id invoice_items id 617 invoice_item_tax invoice_item_id invoice_items id 660 invoice_item_tax invoice_item_id invoice_items id 1063 invoice_item_tax invoice_item_id invoice_items id 1064 invoice_item_tax invoice_item_id invoice_items id 1058 invoice_item_tax invoice_item_id invoice_items id 1059 invoice_item_tax invoice_item_id invoice_items id 1062 invoice_item_tax invoice_item_id invoice_items id 1060 invoice_item_tax invoice_item_id invoice_items id 1061 invoice_item_tax invoice_item_id invoice_items id 1067 invoice_item_tax invoice_item_id invoice_items id 1068 invoice_item_tax invoice_item_id invoice_items id 1332 invoice_item_tax invoice_item_id invoice_items id 1379 invoice_item_tax invoice_item_id invoice_items id 1455 invoice_item_tax invoice_item_id invoice_items id 1456 invoice_item_tax invoice_item_id invoice_items id 1470 invoice_item_tax invoice_item_id invoice_items id 1598 invoice_item_tax invoice_item_id invoice_items id 1680 invoice_item_tax invoice_item_id invoice_items id 1693 invoice_item_tax invoice_item_id invoice_items id 1694 invoice_item_tax invoice_item_id invoice_items id 1751 invoice_item_tax invoice_item_id invoice_items id 1904 invoice_item_tax invoice_item_id invoice_items id 2008 invoice_item_tax invoice_item_id invoice_items id 2009 invoice_item_tax invoice_item_id invoice_items id 2010 invoice_item_tax invoice_item_id invoice_items id 2164 invoice_item_tax invoice_item_id invoice_items id 2275 invoice_item_tax invoice_item_id invoice_items id 2296 invoice_item_tax invoice_item_id invoice_items id 2401 invoice_item_tax invoice_item_id invoice_items id 2400 invoice_item_tax invoice_item_id invoice_items id 2402 invoice_item_tax invoice_item_id invoice_items id 2403 invoice_item_tax invoice_item_id invoice_items id 2404 invoice_item_tax invoice_item_id invoice_items id 2448 invoice_item_tax invoice_item_id invoice_items id 2449 invoice_item_tax invoice_item_id invoice_items id 2499 invoice_item_tax invoice_item_id invoice_items id 2512 invoice_item_tax invoice_item_id invoice_items id 2517 invoice_item_tax invoice_item_id invoice_items id 2518 invoice_item_tax invoice_item_id invoice_items id 2583 invoice_item_tax invoice_item_id invoice_items id 2586 invoice_item_tax invoice_item_id invoice_items id 2666 invoice_item_tax invoice_item_id invoice_items id 2719 invoice_item_tax invoice_item_id invoice_items id 2752 invoice_item_tax invoice_item_id invoice_items id 2772 invoice_item_tax invoice_item_id invoice_items id 2944 invoice_item_tax invoice_item_id invoice_items id 2989 invoice_item_tax invoice_item_id invoice_items id 2990 invoice_item_tax invoice_item_id invoice_items id 2988 invoice_item_tax invoice_item_id invoice_items id 3038 invoice_item_tax invoice_item_id invoice_items id 3070 [19-Jul-2025 21:38:25 America/New_York] SqlPatchManager::runSqlPatch() - SqlPatchManager::prePatch331() = Unable to set Foreign Keys.Please help
This update is adding foreign key support so that the situation you have now won’t occur in the future. The si_invoice_item_tax table contains orphaned records. That is, they contain a value in the invoice_item_id field that references a record in the si_invoice_items table that no longer exists. You can verify this for yourself by verifying that a record with the value shown in the INVALID VALUE column does not exist in the id field of any record in the si_invoice_items table, but does exist in the invoice_item_id field of the si_invoice_item_tax table.
Once you are satisfied this is the case, you just need to delete those orphaned records from the si_invoice_item_tax table. After the orphaned records are deleted, you can run SI again and it will pick up at this point and move forward from there.
So I have manually delete all of those items individually from the database with phpmyAdmin?
Do I go into the si_invoice_item_tax and delete all the rows with the invalid value shown on the right?Obviously the deleted items won’t appear in the si_invoice_item_tax any longer. So I’m wondering that items you are thinking of deleting from this table. I did run a test where I created an invoice with a tax item, then manually deleted it from the si_invoice_item_tax table. Although the si_invoices record still contained the total tax value in the record, when I displayed the invoice, it no longer showed the total tax. And when I went back to the database, the si_invoices tax_amount field was reset to zero (the correct value). So you should be able to reset the values by simply accessing the invoices.
By the way, how is your original issue? Everything displaying OK?
I am sorry if I was not clear:
my original issue persists, I cannot access the invoices AT ALL! I cannot even access the dashboard of SI! If I access SI I get the Database Update page which is stuck on the SQL patch 330… This is why I am asking what I should be deleting from the database manually in order to unlock this situation… right now I am stuck mid-installation unable to access anything SI-related AT ALL.
I would very much appreciate if we could schedule a zoom call and I would be happy to pay for your time or make a donation to the SI forum because I am not sure how to proceed from here.What errors are showing in your tmp/log/php.log file? If there are a bunch of old ones, delete the content of the file and run your inquiry again so only current errors are in the file. You can zip the file and attach to a response for me to review. Concerning a zoom, that is possible. Let’s try reviewing current errors first and if nothing there, do the zoom thing. As far as cost, I would leave that to you and ask that you post it via the GoFundMe link on the group page.
I just emptied the php.log file and re-loaded the root of the SI install, got the same page (stuck at SQL patch 330) and then I clicked on the UPDATE button and after the some time I got a white page with the following error message:
SqlPatchManager::runSqlPatch() error. See error log for more information.…then I opened the php.log file and the contents were these:
[22-Jul-2025 23:18:30 America/New_York] Unable to apply patch 331. Found foreign key table columns with NULL values or values not in the reference table column. The following list shows what values in foreign key columns are missing from reference columns. The records in the FOREIGN KEY TABLE are most likely invalid and should be deleted. FOREIGN KEY TABLE COLUMN REFERENCE TABLE COLUMN INVALID VALUE ------------------------ ------------------ ----------------------- --------- ------------- invoice_item_tax invoice_item_id invoice_items id 2 invoice_item_tax invoice_item_id invoice_items id 3 invoice_item_tax invoice_item_id invoice_items id 4 invoice_item_tax invoice_item_id invoice_items id 5 invoice_item_tax invoice_item_id invoice_items id 7 invoice_item_tax invoice_item_id invoice_items id 8 invoice_item_tax invoice_item_id invoice_items id 162 invoice_item_tax invoice_item_id invoice_items id 322 invoice_item_tax invoice_item_id invoice_items id 471 invoice_item_tax invoice_item_id invoice_items id 617 invoice_item_tax invoice_item_id invoice_items id 660 invoice_item_tax invoice_item_id invoice_items id 1063 invoice_item_tax invoice_item_id invoice_items id 1064 invoice_item_tax invoice_item_id invoice_items id 1058 invoice_item_tax invoice_item_id invoice_items id 1059 invoice_item_tax invoice_item_id invoice_items id 1062 invoice_item_tax invoice_item_id invoice_items id 1060 invoice_item_tax invoice_item_id invoice_items id 1061 invoice_item_tax invoice_item_id invoice_items id 1067 invoice_item_tax invoice_item_id invoice_items id 1068 invoice_item_tax invoice_item_id invoice_items id 1332 invoice_item_tax invoice_item_id invoice_items id 1379 invoice_item_tax invoice_item_id invoice_items id 1455 invoice_item_tax invoice_item_id invoice_items id 1456 invoice_item_tax invoice_item_id invoice_items id 1470 invoice_item_tax invoice_item_id invoice_items id 1598 invoice_item_tax invoice_item_id invoice_items id 1680 invoice_item_tax invoice_item_id invoice_items id 1693 invoice_item_tax invoice_item_id invoice_items id 1694 invoice_item_tax invoice_item_id invoice_items id 1751 invoice_item_tax invoice_item_id invoice_items id 1904 invoice_item_tax invoice_item_id invoice_items id 2008 invoice_item_tax invoice_item_id invoice_items id 2009 invoice_item_tax invoice_item_id invoice_items id 2010 invoice_item_tax invoice_item_id invoice_items id 2164 invoice_item_tax invoice_item_id invoice_items id 2275 invoice_item_tax invoice_item_id invoice_items id 2296 invoice_item_tax invoice_item_id invoice_items id 2401 invoice_item_tax invoice_item_id invoice_items id 2400 invoice_item_tax invoice_item_id invoice_items id 2402 invoice_item_tax invoice_item_id invoice_items id 2403 invoice_item_tax invoice_item_id invoice_items id 2404 invoice_item_tax invoice_item_id invoice_items id 2448 invoice_item_tax invoice_item_id invoice_items id 2449 invoice_item_tax invoice_item_id invoice_items id 2499 invoice_item_tax invoice_item_id invoice_items id 2512 invoice_item_tax invoice_item_id invoice_items id 2517 invoice_item_tax invoice_item_id invoice_items id 2518 invoice_item_tax invoice_item_id invoice_items id 2583 invoice_item_tax invoice_item_id invoice_items id 2586 invoice_item_tax invoice_item_id invoice_items id 2666 invoice_item_tax invoice_item_id invoice_items id 2719 invoice_item_tax invoice_item_id invoice_items id 2752 invoice_item_tax invoice_item_id invoice_items id 2772 invoice_item_tax invoice_item_id invoice_items id 2944 invoice_item_tax invoice_item_id invoice_items id 2989 invoice_item_tax invoice_item_id invoice_items id 2990 invoice_item_tax invoice_item_id invoice_items id 2988 invoice_item_tax invoice_item_id invoice_items id 3038 invoice_item_tax invoice_item_id invoice_items id 3070 [22-Jul-2025 23:18:30 America/New_York] SqlPatchManager::runSqlPatch() - SqlPatchManager::prePatch331() = Unable to set Foreign Keys.I am ready for that zoom unless you can tell me how to proceed.
This is the same error that you had before. As if the items you deleted from the invoice_item_tax table, were not deleted. Here is an example of a SQL DELETE statement for multiple records at a time:
DELETE FROM si_invoice_item_tax WHERE invoice_item_id IN (1354, 1355, 1356, 1357);Not sure how if you deleted these records before, they still show up. But you should verify that once they are deleted, an inquiry no longer finds one or more of them. Delete the php.log file and run SI again. Hopefully, you will be past this error.
Thank you for your help with this.
I have run the following query:
DELETE FROM si_invoice_item_tax WHERE invoice_item_id IN (2, 3, 4, 5, 7, 8, 162, 322, 471, 617, 660, 1063, 1064, 1058, 1059, 1062, 1060, 1061, 1067, 1068, 1332, 1379, 1455, 1456, 1470, 1598, 1680, 1693, 1694, 1751, 1904, 2008, 2009, 2010, 2164, 2275, 2296, 2401, 2400, 2402, 2403, 2404, 2448, 2449, 2499, 2512, 2517, 2518, 2583, 2586, 2666, 2719, 2752, 2772, 2944, 2989, 2990, 2988, 3038, 3070);And that worked. I was able to continue with the update and access my invoices.
I shall be making another donation to the support GoFund Me to thank you for your time.I do have two other questions but I will post them in separate threads so this thread can stay on topic.
- You must be logged in to reply to this topic.
Recent Comments