upgrade 2019.2 to 2023 fatal error – referencing column incompatible

[hugh]

Hi,

Has anyone seen this problem?

“…
General error: 3780 Referencing column ‘invoice_item_id’ and referenced column ‘id’ in foreign key constraint ‘si_invoice_item_tax_ibfk_2’ are incompatible.
…”

I am upgrading from old SI version to 2019.2 and now to 2023. 2019.2 seemed to be working OK.

I am stuck at SQL patch 331 “Add foreign key for invoice_item_id to invoice_item_tax table. has not been applied to the database”. I had some foreign key issues which I fixed by deleting the records are described in the help section “Unable to set Foreign Keys Error Handling”.

However now I get this error….

Fatal error: Uncaught PDOException: SQLSTATE[HY000]:
General error: 3780 Referencing column ‘invoice_item_id’ and referenced column ‘id’ in foreign key constraint ‘si_invoice_item_tax_ibfk_2’ are incompatible.
in /var/www/html/simpleinvoices_2023/Inc/Claz/PdoDb.php:1294

Stack trace: #0 /var/www/html/simpleinvoices_2023/Inc/Claz/PdoDb.php(1294): PDOStatement->execute()
#1 /var/www/html/simpleinvoices_2023/Inc/Claz/SqlPatchManager.php(145): Inc\Claz\PdoDb->query()
#2 /var/www/html/simpleinvoices_2023/Inc/Claz/SqlPatchManager.php(215): Inc\Claz\SqlPatchManager::runSqlPatch()
#3 /var/www/html/simpleinvoices_2023/index.php(238): Inc\Claz\SqlPatchManager::runPatches()
#4 {main} thrown in /var/www/html/simpleinvoices_2023/Inc/Claz/PdoDb.php on line 1294

…any suggestions on what I should do?

thanks,

Hugh

[RRowley]

Please export the database structure (no data), zip it and attach to reply message for this topic.

[hugh]

Hi RRowley,

Thanks for your help. The file is attached.

Hugh

Attachments:

1. structure-only-mysimpleinvoicesdb-202307050830.zip

[RRowley]

Your database structure contains obsolete settings that need to be cleaned up. The best way I can think of to do this is to:

1) Restore your 2019.2 database. Hopefully you have a backup of this.
2) Export data only, no structure.
3) Delete all the tables in the 2019.2 database. Turn off foreign key checks to simplify this.
4) From you 2019.2 SI software backup, import the “databases/mysql/structure.sql” file. This creates all the tables without any data.
5) Import the SQL file with data only, made in step 2.

Hopefully this all works without an issue. If it does, export this database both structure and data, so you have a copy incase of issues still there. If there is an issue and you can’t resolve it, let me know what it is and we’ll move forward from there.

If all works well to this point and assuming the database name is the same as that defined in your 2023 custom.config.ini file, access your 2023 SI and see if the update errors are resolved.

[hugh]

Hi RRowley,

Thanks, I appreciate your detailed response.

I followed your steps but encountered problems when importing the data…issues with incorrect column counts.

Given the ongoing issues, I decided to install a separate fresh new 2023 version and keep the 2019.2 version running for reference with the old data.

Thanks again and keep up the great work.

Hugh

[Author]

Posts