Weird issue after upgrading MySQL to 8.0.34

**project jayden** · 23 Oct 2023, 02:45 PM

www.kdmracer.com
1.5.8a
PHP Version: 8.0.30
Database Engine: MySQL 8.0.34

So I am a total noob and unfortunately I am regretting my decision to upgrade from MySQL 5.7 to 8 because of a notification I received from my host that 5.7 would no longer be supported by the end of this month. Anyways I ran my server's compatibility test and everything seemed ok. (There were 2 errors that it detected and warnings but otherwise said that it should be ok to update.)

Errors:
innodb_support_xa - is set and will be removed
query_cache_size - is set and will be removed

So here's the issue: Since the update, I have noticed that some of the products that I am uploading using the latest version of easy populate are not ending up in the right category and sometimes ending up in multiple categories. Easypopulate is not throwing any errors so there are no logs to look at. Out of 5 new products I uploaded at least one of the products end up like this. The rest are in their correct place. I checked to see if the database needs repair and it shows that everything is ok.

I'm not sure where I should start so I can troubleshoot this. Appreciate your guys' help!

**gothstone** · 23 Oct 2023, 11:11 PM

Turn on Admin -> configuration -> logging -> report all errors, both store and admin if you haven't yet
go to admin -> modules -> plugin manager -> and install display logs if you haven't yet
start posting logs.

It's pretty odd for a mySQL upgrade to change data types, but from your thread in easypopulate it looks like that's what has happened. you may be in for a lot of fun correcting a trash database schema. I just had to do the same for other reasons.

**project jayden** · 24 Oct 2023, 03:01 AM

Originally Posted by gothstone

Turn on Admin -> configuration -> logging -> report all errors, both store and admin if you haven't yet
go to admin -> modules -> plugin manager -> and install display logs if you haven't yet
start posting logs.

It's pretty odd for a mySQL upgrade to change data types, but from your thread in easypopulate it looks like that's what has happened. you may be in for a lot of fun correcting a trash database schema. I just had to do the same for other reasons.

Thank you for the reply and the suggestions. I turned on all of those settings and the only warnings I am getting seem to be template related.

Code:

[24-Oct-2023 01:46:11 UTC] Request URI: /index.php?main_page=index&cPath=184_185_384_387, IP address: 51.222.253.18, Language id 1
#1  require(/home/*********/public_html/kdmracer/includes/modules/wokiee/zen_colorbox.php) called at [/home/*********/public_html/kdmracer/includes/classes/observers/ColorBoxObserver.php:42]
#2  ColorBoxObserver->update() called at [/home/*********/public_html/kdmracer/includes/classes/traits/NotifierManager.php:87]
#3  base->notify() called at [/home/*********/public_html/kdmracer/includes/functions/extra_functions/wt_wokiee_template_functions.php:643]
#4  wt_wokiee_additional_images() called at [/home/*********/public_html/kdmracer/includes/functions/extra_functions/wt_wokiee_template_functions.php:819]
#5  get_wt_wokiee_product_content() called at [/home/*********/public_html/kdmracer/includes/modules/wokiee/product_listing.php:168]
#6  include(/home/*********/public_html/kdmracer/includes/modules/wokiee/product_listing.php) called at [/home/*********/public_html/kdmracer/includes/templates/wokiee/templates/tpl_modules_product_listing.php:10]
#7  require(/home/*********/public_html/kdmracer/includes/templates/wokiee/templates/tpl_modules_product_listing.php) called at [/home/*********/public_html/kdmracer/includes/templates/wokiee/templates/tpl_index_product_list.php:115]
#8  require(/home/*********/public_html/kdmracer/includes/templates/wokiee/templates/tpl_index_product_list.php) called at [/home/*********/public_html/kdmracer/includes/modules/pages/index/main_template_vars.php:231]
#9  require(/home/*********/public_html/kdmracer/includes/modules/pages/index/main_template_vars.php) called at [/home/*********/public_html/kdmracer/includes/templates/wokiee/common/tpl_main_page.php:143]
#10 require(/home/*********/public_html/kdmracer/includes/templates/wokiee/common/tpl_main_page.php) called at [/home/*********/public_html/kdmracer/index.php:94]
--> PHP Warning: Undefined variable $thumb_regular in /home/*********/public_html/kdmracer/includes/modules/wokiee/zen_colorbox.php on line 19.

**gothstone** · 24 Oct 2023, 12:50 PM

For now, skip the ligs that end in warning or depriciated. The other ones will be flat out errors and probably what your looking for. Add products till one goes wrong, the grab the error log for that.

**project jayden** · 24 Oct 2023, 05:57 PM

Originally Posted by gothstone

For now, skip the ligs that end in warning or depriciated. The other ones will be flat out errors and probably what your looking for. Add products till one goes wrong, the grab the error log for that.

I was finally able to get EasyPopulate to generate an error log. This was after I created New main and sub categories. The products uploaded to the main category instead of the sub categories

Code:

MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Engine',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Air Intake',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Suspension',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Lowering Springs',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Engine',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Air Intake',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Suspension',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Lowering Springs',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Engine',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Air Intake',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Brakes',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Rotors',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Brakes',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Rotors',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Brakes',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Rotors',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Brakes',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Rotors',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Brakes',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '514' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                514, '', 514, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '514-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 514,
                    language_id     = 1,
                    categories_name = 'Rotors',
                    categories_description = ''

**mc12345678** · 28 Oct 2023, 12:17 AM

That basically happens, laying to the main category instead of the subcategory because well can blame the code I wrote with good intentions, that works for a mysql site running 8.0.34, or a setting associated with the database.

A default install/configuration of mysql 8.x sets a cache to exist for 24 hours. As a result, the loop that is supposed to provide a child category for a parent category doesn't get updated properly and causes the clashing of categories.

I'm going to rewrite the loop because unless you basically have full control of the database configuration, the setting can't be changed. On my test site the cache is disabled so I don't experience the reported issue even though I tried desperately to recreate it.

I plan to post something similar to this message on the EP4 thread where it belongs.