Forum Replies Created
-
Posts
-
The issue is that the si_invoice_item_tax file has a foreign key set on the ID field of the si_invoice_items table. You need to make note of the foreign key settings and then delete the foreign key. Next add the auto increment to si_invoice_items ID field. Finally, add the foreign key back on the si_invoice_item_tax table.
The error appears to complain that there is an attempt to add and si_invoice_items record with a duplicate value of 0 for its primary key. The primary key is (should be) the ID field. This field could only be assigned 0 if it does not have the auto increment setting on. User phpMyAdmin to verify this. There are two issues to correct. One is that there is now a record in that table with a 0 value for its ID field. That is a problem. I suggest you find which invoice this is for and delete it. If you don’t find a record with a 0 ID value, it was probably created for the invoice you are adding, say for the first invoice item and then it got an error on the second. Once you’ve cleared up the 0 ID issue, you can set the auto increment setting on the table. This can be done in phpMyAdmin. If you had to delete and invoice, add it back now. Make note of the original invoice number to reference it to the new number assigned to it for your records. Once you’ve taken care of any deleted invoice, you can add the invoice you were trying to add when this error occurred. All should be OK at this point.
Concerning unable to apply foreign key changes error, the exception message tells you what the issue is. From your error log:
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 10 invoice_item_tax invoice_item_id invoice_items id 11 invoice_item_tax invoice_item_id invoice_items id 12 invoice_item_tax invoice_item_id invoice_items id 13
This is saying that there are si_invoice_item_tax table records with an id of 10, 11, 12 and 13, containing invoice_item_id values that do not exist in the si_invoice_items table.
There are two ways to resolve this. One is to create si_invoice_itens records that contain these values. The other is to delete these records from the si_invoice_item_tax table. Since the orphaned records are likely left over from the deletion of si_invoice_items records that failed to delete associated tax records, the solution to delete the si_invoice_item_tax records with id values of 10, 11, 12 and 13 is likely the best solution.
Thank you for pointing this out. I have uploaded a fix to the gitignore file that was causing this, along with the css files that it had erroneously been excluding.
I notice that the sql file inserts into the si_invoices table before the load of the si_invoice_type table.
Make sure you turn off the check foreign keys option being importing the sql file.
You need to determine which product numbers in the inventory table do not have a match to an id field value in the product table. If the product_id of an inventory record is wrong, change it to a correct value. If the inventory product_id should be for a product that is missing, you can add the missing product, get its id and then change the inventory record to the new product. Or you could delete the orphaned inventory record.
Sorry you have to go through this, but once fixed, the database will enforce foreign key settings to prevent future issues.
The utf8mb4 should be OK for the database. That is actually what I have but I changed to utf8… to verify it would work with the schema I provided.
I would like to know what the errors were you encountered when you imported the data. If it had to do with foreign key issues, you can turn off the foreign key check in import. If this allows your data to be loaded, you are no worse off than you are now but I expect you have number of orphaned records that should be resolved.
I’ve put instructions in the attached zip file. For some reason, it get an error if I try and save them outright in this comment box.
Glad you figured this out. Thanks for letting the forum know what your issue was and how you corrected it.
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.
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.
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.
Recent Comments