Problem changing all DB collations to utf8mb3_general_ci

[kevin_a]

I wish to upgrade from 1.5.7c to 2.1
The existing DB for v 1.5.7c has mixed colllation Latin and utf8-comms.
I understand that this existing 155b database needs upgrading to bring all the collations to utf8mb4 before it can be imported to the 2.1 site.

ZenCart v 1.5.7c
PHP 7.4.33
The existing DB forv 1.5.7c has mixed collation.... latin1_general_ci and utf8mb3_general_ci


I have run the tool available for global collation edit (convert_database.php)

I receive the following problem report

"The database's character-set doesn't match DB_CHARSET.
92 tables don't have the same collation as the base database's. These issues can be corrected without a full database conversion.
335 fields don't have the same collation as the base database's. These issues can be corrected without a full database conversion.
108 tables don't have the same collation as the base database's1.
372 fields don't have the same collation as the base database's1.
2 date/datetime/timestamp fields have a zero-date default. This must be corrected before any character-set and/or collation modifications are made to the database."

Is there a missing step that I should address, in order to run the tool?
All help appreciated thanks.

[dbltoe]

If you have cPanel and phpMyAdmin, select the database, navigate to the "Operations" tab, change the collation to utf8mb4_general_ci, and check the options to change all tables and columns to the new collation. Click on Go.

Note the use of 4 versus 3.

[kevin_a]

Thanks dbltoe, but that did not make any change.
Still a mix of latinxxx and utfxxxx?

[lat9]

What is the DB_CHARSET value set in /includes/configure.php?

[kevin_a]

define('DB_TYPE', 'mysql'); // always 'mysql'
define('DB_PREFIX', ''); // prefix for database table names -- preferred to be left empty
define('DB_CHARSET', 'utf8mb4'); // 'utf8mb4' or older 'utf8' / 'latin1' are most common
define('DB_SERVER', 'localhost'); // address of your db server

[barco57]

in phpmyadmin select the check all and in the "with selected" dropdown choose analyze table.
Default global setting for "information schema stats expiry" is 86400 seconds, so changes are not always apparent immediately until the cache expires (if the server is using MySQL 8.0)
So you have to force it to invalidate the cache of stats

[kevin_a]

Thank you barco
Unfortunately I do not see a button facilitating check/select all tables, ?
Where in the menu structure or elsewhere should I find it?

[barco57]

when you have your database open in phpmyadmin, scroll to the bottom, and there is the check all to select all tables in your database and then the dropdown is right next to that

[kevin_a]

Ok thanks barco
I ran Analyse selected (all) and the reply was "Table is already up to date" in every case.
However, structure tab lists mix latin1/ utf8mb3 / utf8mb4 ?

[barco57]

well, that confirms that the "convert_database.php" didn't do the conversion.