- This topic has 25 replies, 3 voices, and was last updated 1 year, 3 months ago by
.
-
Posts
-
I’m stuck with this after trying to update from master_2020 to master_2023.
I can not roll back to what I had as I had to update the PHP version on my server.
I have no idea what to do at this point, I’ve never had a hard time like this updating.
At some point something ran and created a couple of log files however there have been many changes/attempts at getting it to work since.
I’ve made progress, I now have problems with
SqlPatchManager::runSqlPatch() error. See error log for more information.the FOREIGN KEY TABLE or some values in those seem to be at issue. I really only need to know if I can just delete the date or modify
Further progress, but stuck at this point
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint in /simpleinvoices/Inc/Claz/PdoDb.php:1294 Stack trace: #0 /simpleinvoices/Inc/Claz/PdoDb.php(1294): PDOStatement->execute() #1 /simpleinvoices/Inc/Claz/SqlPatchManager.php(145): Inc\Claz\PdoDb->query() #2 /simpleinvoices/Inc/Claz/SqlPatchManager.php(215): Inc\Claz\SqlPatchManager::runSqlPatch() #3 simpleinvoices/index.php(238): Inc\Claz\SqlPatchManager::runPatches() #4 {main} thrown in /simpleinvoices/Inc/Claz/PdoDb.php on line 1294
What the error is saying, is that you orphaned records in your si_inovice_item_tax file. That is, you have records in the si_invoice_item_tax file that reference records in the si_invoice_items file that no longer exist.
Verify this is the case by checking a few of the values. For instance, the first record says that there is no si_invoice_items record with an id of 63. Verify this is true. If it is, you can delete the record in the si_invoice_item_tax table with a invoice_item_id value of 63.
You’ll need to repeat this for each of the values specified. I know the list is long, but once this is cleaned up and the foreign key can be created, the foreign key will prevent this from happening in the future.
Let me know how this works out.
I deleted the records one by one, which took a little bit of AI to build the SQL to delete those records… it only took 4 minutes for Bard to get me the correct string I needed. Now I have the following (See attached)
To attach your DB structure, zip the sql file and attach the zip file. Additionally, if you look at the content of the si_sql_patchmanager table, what is the greatest value of the sql_patch_ref column?
The database is using a structure type, utf8mb3, that is deprecated. I believe that is what is causing you trouble. I have attached a zip file that will create your database using uft8 settings consistent with the structure used in SI 2023. To use this do the following:
1) Export your current database, structure and data, and save this somewhere for backup purpose.
2) Export the data only from your current database. Note the name and location of this file.
3) Delete the database completely. You might have to do this in two steps. Dropping all tables, then deleting the database.
4) Download the attached zip file. Extract the SQL file in it. This contains logic to create your database and its tables using the utf8 character format consistent with SI 2023. The extracted file name will be dbs1148220_utf8.sql. This will create the database with your existing name, dbs1148220.
5) Import the dbs1148220_utf8.sql file. Verify it completes correctly and that the username you were using to access this database is still present and set to access it. If not, add this database to that user.
6) Import the data only SQL file you created in step 2.
7) Assuming no errors to this point, you should now access your SI application and continue its database update.I hope this works. Let me know what happens errors or not.
No dice. I still get the wildly non specific error (errno: 150 “Foreign key constraint is incorrectly formed”) I’ve attached php.log
Apply this sql statement manually to your si_invoice_items table:
ALTER TABLE si_invoice_items MODIFY id INT(11) UNSIGNED NOT NULL
The id field in this table is not an unsigned type and therefore does not match the type of invoice_item_id field in the si_invoice_item_tax table.
After applying this command manual (via phpMyAdmin), run SI again. It should get past this error but might encounter others. If so, give me the info on them.
I got an error, however I am now able to use the database.
Fatal error: Uncaught PDOException: There is no active transaction in
/si/Inc/Claz/PdoDb.php:946 Stack trace: #0
/si/Inc/Claz/PdoDb.php(946): PDO->commit() #1
/si/Inc/Claz/SqlPatchManager.php(223): Inc\Claz\PdoDb->commit() #2
/si/index.php(238): Inc\Claz\SqlPatchManager::runPatches() #3 {main} thrown in
/si/Inc/Claz/PdoDb.php on line 946Fatal error: Uncaught PDOException: There is no active transaction in
/si/Inc/Claz/PdoDb.php:928 Stack trace: #0
/si/Inc/Claz/PdoDb.php(928): PDO->rollBack() #1
/si/Inc/Claz/PdoDb.php(100): Inc\Claz\PdoDb->rollback() #2 [internal function]: Inc\Claz\PdoDb->__destruct() #3 {main} thrown in /si/Inc/Claz/PdoDb.php on line 928A couple of artifacts I am dealing with.
1. I was only able to create one invoice with one line item. It added the line item to
si_invoice_itemsgiving it a index number of 0. When I try to add any other invoice/item it errors out with a warning of a duplicate index number.2. When entering line items, I get an warning “Invalid quantity value entered” when adding a fractional number like 1.5, I am forced to use a comma like 1,5 (I’m not in Spain yet, I don’t want to be forced to do that)
Also some house keeping things I’d like to help out with. Embedding fonts to be included when exporting pdf, I finally figured out how get that done and would like to share that with others. I also found that tooltipster.bundle.min.css (from the Tooltipster – The jQuery Tooltip Plugin) is missing from the Master 2023 download. I have a set of instructions to correct that as well I’d like to share.
It’s amazing how far all of this has evolved. I just noticed that my first invoice 03/28/2014.
I appreciate all your help, I hope I am not to bothersome.
Thank you.
I just upload some fixes for 2023 version that address the error of trying to commit or rollback changes when no transaction is in process. This was primarily an update issue but could affect other actions as well.
This doesn’t address your current issue where some field is not being set properly. I’d like to see the sql command being generated on your system that gets this error. To do that I need you to turn on the debug setting when invoice items are being generated.
To do this, you need to add lines to turn debug on and then off when the database request is made. So in your SI directory, find the Inc/Claz/Invoice.php file and open it in an editor program such as notepad or notepad+. Search for the function insertItem. It should be at line 681. In this function at line 688, is a line that executes the request to insert a new invoice item. You will insert a new line before this line and one after it that will turn the database object debug option on and off. Here is what you want to make it look like:
private static function insertItem(array $list, ?array $taxIds): int { global $pdoDb; try { $pdoDb->setFauxPost($list); $pdoDb->setExcludedFields("id"); $pdoDb->debugOn("Invoice::insertItem()"); $id = $pdoDb->request("INSERT", "invoice_items"); $pdoDb->debugOff(); self::chgInvoiceItemTax($id, $taxIds, $list['unit_price'], $list['quantity'], false); } catch (PdoDbException $pde) { $pdoDb->debugOff(); error_log("Invoice::insertItem() - Error: " . $pde->getMessage()); throw $pde; } return $id; }The change the turns the debugOn will cause the request function to output a copy of the SQL command in the error file. Notice that immediately after this command and again the catch statement, we turn the debug back off.
Now when you add an invoice item, SQL command that does this will being the error log along with other error information. Attach that log file to a response to this message and I’ll see if it lets me know what your issue is.
- You must be logged in to reply to this topic.
Recent Comments