Database Conversion Tools [Support Thread]

Bruce1952:

Excuse me no error log was produced in zencart the error came up on screen from the phpadmin panel when doing the update to the test site of the database.

I don't know where this error log you are talking about be as it is not in the zencart error log area at all on the test site.
If the error was issued during a phpMyAdmin session, then the error has nothing to do with either Zen Cart or the Database conversion tool.

Hey folks, so happy this tool exists! I'm having some trouble with it, though.

It's finding Zero-Date Values in three tables, but clicking the Correct Z-DV button responds with one bullet point UPDATE command on one table, and then shows

"No zero-value date/datetime fields required correction in the database."

and I feel like I'm stuck in a loop, with no conversion to utf8mb4

Any idea as to what I'm missing?

Thanks!

I have moved past this! Was I dealing with cache-ing? Maybe. Did I manually change a bunch of date/time fields? Yes!

I got this error in my log --see below.
After doing some research I found this database conversion tool. Would this script solve my problem?
When I did on my local test it seem to change some of the tables but not all of them.

**Just a quick recap **

1. i selected utf8mb4 clicked choose
2. Then selected utf8mb4_unicode_520_ci (i read i should choose this option (not sure if correct)
3. then i clicked on Check for Zero-Data Values
4. Start Conversion

Once it runs i see next action
49 tables don't have the same collation as the base database's. These issues can be corrected without a full database conversion.
137 fields don't have the same collation as the base database's. These issues can be corrected without a full database conversion.
73 tables don't have the same collation as the base database's1.
281 fields don't have the same collation as the base database's1.

Am I missing something. What would be my next step. Also could this mess my database?

--> PHP Fatal error: FATAL MySQL error 1267: Illegal mix of collations (utf8mb3_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like' :: select count(distinct p.products_id) as total  FROM (products p                     LEFT JOIN manufacturers m                     USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c ) LEFT JOIN dynamicOptions dynm on (dynm.products_id = p.products_id)   WHERE (p.products_status = 1                     AND p.products_id = pd.products_id                     AND pd.language_id = 1                     AND p.products_id = p2c.products_id                     AND p2c.categories_id = c.categories_id  AND ( (  pd.products_name LIKE '%the    %2527%2522\\\'\\\"%' OR  p.products_model LIKE '%the    %2527%2522\\\'\\\"%' OR  m.manufacturers_name LIKE '%the    %2527%2522\\\'\\\"%')  ))  ==> (as called by) /home/inverter/public_html/includes/classes/split_page_results.php on line 105 <== in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 757.

Be sure you have the character set set to utf8mb4 in each configure.php file.

Then, use utf8mb4_general_ci in the database as it is faster than utf8mb4_unicode_ci. Unicode may be necessary if your store is primarily in a language other than English.

While utf8mb4_0900_ai_ci is the latest and greatest for MySQL, it can have issues and is often replaced with _general_ci for compatibility. If you are not importing an old database, _900_ai_ci should work fine as long as your host provides MySQL at 8.0 or newer. (i.e, myZenCartHost is at 10.5)

Always back up a database before working on it! If you are working on a live site's DB, either work on it at an off time or (best) set the site down for maintenance while you work.

This tool is the best and safest, but you can also use phpMyAdmin to do the job.

Find your DB, click on the Operations tab, scroll down to Collation, select the desired collation, check both boxes (all tables and table columns), and click on Go.

After using either method, clear the display log for mixed collation and run a few laps around the site to make sure the error is completely gone.

I have just one comment to add about your database default collation. Looking at your error message "...(utf8mb3_unicode_ci,IMPLICIT)...", Your database is set use utf8mb3_unicode_ci by default, which is the collation that will be used when creating something new (database, table, column) without specifying the collation.
I don't think the conversion tool can change that. But you have to change it to utf8mb4_general_ci to avoid futur troubles. You can use phpMyAdmin or directly change the value for "collation_server" in your MySQL configuration file.

Guys thanks for the feedback I am a little confused on how this module works

I want to test my localhost before i touch the live database.
I used Xampp as a testing server and these are the current settings

Server: 127.0.0.1 via TCP/IP
Server type: MariaDB
Server connection: SSL is not being used Documentation
Server version: 10.4.32-MariaDB - mariadb.org binary distribution
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8mb4)

Apache/2.4.58 (Win64) OpenSSL/3.1.3 PHP/8.4.19
Database client version: libmysql - mysqlnd 8.4.19
PHP extension: mysqli Documentation curl Documentation mbstring Documentation
PHP version: 8.4.19

On my testing site the collation for some tables are utf8mb4_general_ci, utf8_general_ci and latin1_swedish_ci

I do have some knowledge of using the database but not my strong point.
What would be the best option. The database conversion tools only changes a few tables.

If anyone has a suggestion i am open.

dbltoe:

Be sure you have the character set set to utf8mb4 in each configure.php file.

Then, use utf8mb4_general_ci in the database as it is faster than utf8mb4_unicode_ci. Unicode may be necessary if your store is primarily in a language other than English.

While utf8mb4_0900_ai_ci is the latest and greatest for MySQL, it can have issues and is often replaced with _general_ci for compatibility. If you are not importing an old database, _900_ai_ci should work fine as long as your host provides MySQL at 8.0 or newer. (i.e, myZenCartHost is at 10.5)

Always back up a database before working on it! If you are working on a live site's DB, either work on it at an off time or (best) set the site down for maintenance while you work.

This tool is the best and safest, but you can also use phpMyAdmin to do the job.

Find your DB, click on the Operations tab, scroll down to Collation, select the desired collation, check both boxes (all tables and table columns), and click on Go.

After using either method, clear the display log for mixed collation and run a few laps around the site to make sure the error is completely gone.

I made the changes via the phpmyadmin under operations. And changed it to uft8mb4_general_ci Now all tables read Uft8mb4_general_ci. My question is can this break my site does it effect the product_name/categorey length value.

As in anything done with a proper backup, you can test all you want or need and replace the database if you have problems.

dbltoe:

As in anything done with a proper backup, you can test all you want or need and replace the database if you have problems.

Everything looks good on localhost so far. I’ll try it on the live site tomorrow.

I was overthinking it at first, but the steps really helped. I finally understand how to update the database collation_server. :smile: