[RRowley]

Just following up to see if your issues have been addressed.

[RRowley]

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.

[RRowley]

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.

[RRowley]

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.

[RRowley]

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.

Attachments:

1. instructions.zip

[RRowley]

Glad you figured this out. Thanks for letting the forum know what your issue was and how you corrected it.

[RRowley]

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.

[RRowley]

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.

• This reply was modified 1 year, 4 months ago by RRowley.
• This reply was modified 1 year, 4 months ago by RRowley.
• This reply was modified 1 year, 4 months ago by RRowley.

[RRowley]

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.

Attachments:

1. dbs11418220_utf8.zip

[RRowley]

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?

[RRowley]

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.

[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.

[RRowley]

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

[RRowley]

The sinqle quotes surrounding the strings in the values were converted to a different character. I’ll try again and see if it stores correctly.

INSERT INTO si_sql_patchmanager (sql_patch_ref, sql_patch, sql_release, sql_statement, source) VALUES
(330, 'Add invoice_item_id as a key for the invoice_item_tax table.', '20220926', 'ALTER TABLE si_invoice_item_tax MODIFY invoice_item_id INT(11) UNSIGNED NOT NULL, MODIFY tax_id INT(11) UNSIGNED NOT NULL; ALTER TABLE si_invoice_item_tax ADD KEY invoice_item_id (invoice_item_id);', 'fearless359');

This appears to work. Give it a try.

• This reply was modified 1 year, 5 months ago by RRowley.

[RRowley]

Just wanted to verify that the changes that the update terminated on were indeed already present in your database. They are. So with the database at the point where the error occurred, execute the following SQL command to add the entry for the patch to the si_sql_patchmanager table:

INSERT INTO si_sql_patchmanager (sql_patch_ref, sql_patch, sql_release, sql_statement, source) VALUES
(330, ‘Add invoice_item_id as a key for the invoice_item_tax table.’, ‘20220926’, ‘ALTER TABLE si_invoice_item_tax MODIFY invoice_item_id INT(11) UNSIGNED NOT NULL, MODIFY tax_id INT(11) UNSIGNED NOT NULL; ALTER TABLE si_invoice_item_tax ADD KEY invoice_item_id (invoice_item_id);’, ‘fearless359’);

This entry will to SI that that patch 330 has been applied and you can access SI and it will pick up applying the patches following this. Let me know how this works.

• This reply was modified 1 year, 5 months ago by RRowley.

[Author]

Posts