[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 9 months, 1 week ago by RRowley.
• This reply was modified 9 months, 1 week ago by RRowley.
• This reply was modified 9 months, 1 week 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 10 months, 3 weeks 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 10 months, 3 weeks ago by RRowley.

[RRowley]

Via phpMyAdmin, select the si_invoice_item_tax table and export it. Then zip the exported sql file and attach the zipped file to a response to this message. Chances are that the changes in patch 330 have already been applied. I want to verify this before I advise you what to do next.

[RRowley]

The master_2023 version has been updated to better support customer and biller user roles. Along with a couple of non-report fixes, the reports have been modified to only present report links for ones that can be used based on the user’s role name.

Note that master_2023 requires PHP 8.1 or later and is now the default version of SI offered for users to use.

[RRowley]

The new master_2023 version has been delivered. This version requires that a system is using 8.1 or later version of PHP. I am running it on both my development and production sites. I develop on Windows 11 and deploy on a LINUX webserver. Please try it out and let me know how it works for you.

[RRowley]

Sorry for the delayed response. I’ve been invested timewise with two updates delivered for use to. One is for the master_2020 version to formally support globalization of numeric fields. The other is support for systems running 8.1 or later versions of PHP. This is the new master_2023 version.

Work on reports is something I will address in a yet to be determined, future update.

[RRowley]

Look in the Inc/Claz/Util.php file. Find the date method. Notice in the comments the supported date formats. Say you want the long format (ex: May 8, 2024). In the field you mentioned you would have:

field=$invoice.date|utilDate:long

If you need a format other than those in the Util::date method, you would need to implement extension code to do it. View the Extensions topic.

[RRowley]

This is an issue that will take some time to resolve. In the mean time, if you modify the templates/default/payment/process.tpl at line #48 to remove the validate-number class, your payment should work. This bypasses the interactive, at entry time, field validation which is what isn’t honoring your globalization settings.

[Author]

Posts