Database I/O Manager (DbIo) Plugin [Support Thread]

[RixStix]

My quick perusal of the support thread didn't find an answer.
If I missed it in the readme, just tell me that I need to do a better job of reading.

zc156a vanilla install
drop db tables and import from backup of live db
run db cleanup from phmmyadmin
run zc install
setup shipping and payment modules
install zca_bootstrap
install dbio-1.5.1

Go to Admin: Tools: Database I/O manager and get red error
The DbIo Manager does not support your installation, due to a mismatch between your DB_CHARSET (utf8mb4) and CHARSET (utf-8) values.


Probably over my head, but is this a db server setup, not a zc database setup? Scares the crap out of me to go mucking around the db server unsupervised. :-)

All the db tables show utf8_general_ci for the collation & MyISAM for type

[lat9]

My quick perusal of the support thread didn't find an answer.
If I missed it in the readme, just tell me that I need to do a better job of reading.

zc156a vanilla install
drop db tables and import from backup of live db
run db cleanup from phmmyadmin
run zc install
setup shipping and payment modules
install zca_bootstrap
install dbio-1.5.1

Go to Admin: Tools: Database I/O manager and get red error
The DbIo Manager does not support your installation, due to a mismatch between your DB_CHARSET (utf8mb4) and CHARSET (utf-8) values.


Probably over my head, but is this a db server setup, not a zc database setup? Scares the crap out of me to go mucking around the db server unsupervised. :-)

All the db tables show utf8_general_ci for the collation & MyISAM for type

I had the feeling that that new DB_CHARSET value was going to cause a problem. I'll get a 1.5.2 release going; GitHub issue created: https://github.com/lat9/dbio/issues/124

[lat9]

I had the feeling that that new DB_CHARSET value was going to cause a problem. I'll get a 1.5.2 release going; GitHub issue created: https://github.com/lat9/dbio/issues/124

Just pushed a change to /YOUR_ADMIN/dbio_manager.php to the DbIo GitHub repository to correct the issue.

[RixStix]

Thank you Cindy

[RixStix]

Just installed the git version.

Export products
Edit the price on 2 or 3 products
SaveAs csv
Import (FULL) csv just to see how things worked.

The prices were updated.
Top of the screen had a message:

The DbIo import from file "/home/cPanel/public_html/156/MyAdminFolder/dbio/dbio.Products.01242019.csv" was completed with 5 errors and 540 warnings. 534 records were inserted or updated.

The error file is a bit over a MB and 2681 rows tall

Using Excel Version1812 (Build 11126.202666 CTR) Changed csv encoding to latin1

There's a lot of these

2019-01-24 14:47:09: [*] products.products_weight, line#3: The value (0.1) exceeds the field's maximum length (); the value will be truncated.

Most of our weights use decimal tenth pounds. There are quite a few products that use decimal pound but the weight is in a format 0.200000003 pounds from USPS shipping mod from years past. Weight field in db is Double. The extra precision is no longer being used but the weights have never been edited back to single tenth digits.

Did I foul things up?
I call myself reading the instructions but could be a user, short circuit between the ears.
OR is a log file generated for each import?

[lat9]

Hmm, I'll need to check that weight-related warning out. In a "standard" Zen Cart database, that products:roducts_weight field is a float; is that the case for yours (before I head down a rabbit-hole)?

[lat9]

FWIW, I just did a test import on a product with a weight of 0.1 and that value was accepted, so there's something different about how your store's products' weights are set in the database.

[RixStix]

Hmm, I'll need to check that weight-related warning out. In a "standard" Zen Cart database, that products:roducts_weight field is a float; is that the case for yours (before I head down a rabbit-hole)?

Let's not go down any rabbithole

Back in the zc138a days, maybe 139, there was a USPS module that used double to allow extra digits in the weight field. Those extra digits out in far right places to embed product volume in cubic inches. Unique way of handling the volume of product that could be put into USPS flatrate boxes and disable the USPS FR box size if the products being purchased would not fit. It is no longer used but db field sort of abandoned in place. I wouldn't think that restoring the db back to original field would be a problem if that is the root cause of headache.

productsroducts_weight field is DOUBLE

[lat9]

Rick, I believe I see the issue. DbIo isn't recognizing that double field-type. Could/would you make the following modification to /admin/includes/classes/dbio/DbIoHandler.php's importGetHeader method?

Find this section:

                    $field_type = false;
                    if (isset($this->tables[$table_name]) && isset($this->tables[$table_name]['fields'][$current_field])) {
                        $valid_string_field = false;
                        switch ($this->tables[$table_name]['fields'][$current_field]['data_type']) {
                            case 'int':
                            case 'smallint':
                            case 'mediumint':
                            case 'bigint':
                            case 'tinyint':
                                $field_type = 'integer';
                                break;
                            case 'float':
                            case 'decimal':
                            case 'double':
                                $field_type = 'float';
                                break;
                            case 'date':
                            case 'datetime':
                                $field_type = $this->tables[$table_name]['fields'][$current_field]['data_type'];
                                break;
                            case 'char':
                            case 'text':
                            case 'varchar':
                            case 'mediumtext':
                                $valid_string_field = true;  //-Indicate that a value string-type field was found and fall through to common processing
                            default:
                                $field_type = 'string';
                                if (!$valid_string_field) {
                                    $message = "Unknown datatype (" . $this->tables[$table_name]['fields'][$current_field]['data_type'] . ") for $table_name::$current_field on line #" . $this->stats['record_count'];
                                    $this->debugMessage("[*] importGetHeader: $message", self::DBIO_WARNING);
                                }
                                break;
                        }
                    }

... adding the highlighted line. Does that correct your issue?

[RixStix]

Rick, I believe I see the issue. DbIo isn't recognizing that double field-type. Could/would you make the following modification to /admin/includes/classes/dbio/DbIoHandler.php's importGetHeader method?

... adding the highlighted line. Does that correct your issue?

YES, it does. Thank you. Is this change going to be incorporated into future updates? Customizations that do not make it into permanent updates get me in trouble down the road.
It also helps when you use some of the onscreen help.

First pass, logfile generated with the onscreen note of 5 errors.
I couldn't find anything in the logfile that looked like an error or had the word error associated.

Then, I saw the "View Import Details" button. that showed the 5 errors. All were due to sort order value not being an integer. Somehow, over the years a sort order value of -1 had crept into the mix. Edit those to an integer and no errors.

One question though,
The ViewImportDetails seems to have a hiccup. I edited the csv to be imported to fix the non-integer, renamed the file, imported, click "ViewImportDetails" and the screen is unchanged. Message for the last file imported but I have imported another file during the same admin session.

Then I deleted the first import file that had the -1 sort orders so the only import file was the one generated today. Click the button again and it still shows the message for the file that has been deleted.