Database Conversion Tools [Support Thread]

This plugin's been around for a while (https://www.zen-cart.com/downloads.php?do=file&id=2367) and deserves its own support thread!

I've just submitted v4 to the Plugins area. That version also checks the ***overall ***database collation and, if it's not currently utf8, makes that conversion, too!

The previous versions of this plugin only focused on converting the table-based collations. If a database started with a collation of latin1_general_ci, for instance, the previous versions would properly convert all the database tables to use utf8_general_ci but leave the overall collation unchanged.

The next time a new table is added to that database, that table and all character fields within it would default to the database's default: ** latin1_**general_ci. Not a good thing!

v4 is now available for download from the Plugins.

Hi

I have latin1 and utf8 tables in my database, is this module ok to use on this?

Regards

Mike

mike14017:

Hi

I have latin1 and utf8 tables in my database, is this module ok to use on this?

Regards

Mike
Yes, that's the problem that this module solves! The plugin's processing will convert the database, all tables and their fields to utf8 ... just remember to update your configure.php files (both admin and storefront) to identify that the database's DB_CHARSET is now utf8 instead of latin1.

You should also change the CHARSET definition in your store's base language files (e.g. /includes/languages/english.php, /YOUR_ADMIN/includes/languages/english.php) to reflect a utf8-type character encoding.

Hi

Done all that and converted DB. Everything seems fine.

What a cracking module, thank you.

Mike

Thanks for a great tool Cindy - fast and seamless.

cheers,
Mike

shags38:

Thanks for a great tool Cindy - fast and seamless.

cheers,
Mike
Mike, it's a collaborative effort ... with the primary thanks going to @a_berenzin (original author) for the idea and the base processing. Additional changes by @mc12345678 and myself were simply adding "frosting to the cake" to improve its processing.

I've just submitted v1.2-lat9 for the plugin moderators' review and will post back here when it's available for download.

This version, split from the plugin's base GitHub repository, adds the quotes required around text-fields' default values and now makes use of the site's database settings present in the associated /includes/configure.php.

Approved.

[**Houston, we have a problem.**

](https://en.wikipedia.org/wiki/Houston,_we_have_a_problem)It's never 'fun' upgrading a previous site to zc157, especially when the database contains the *dreaded *mixed collations. I've had the pleasure of updating a couple of sites that started with a mixture of various flavors of latin1 and utf8 and adopted the following procedure for those upgrades.

1. Change the site's configure.php files, setting DB_CHARSET to 'latin1'.
2. Run /zc_install/sql/install/zero_dates_cleanup.sql. Make sure it's the updated version of that script which uses the MySql CAST function.
3. Run /zc_install to upgrade the site's database.
4. Run the (older) convert_db2utf8.php script.
5. Change the site's configure.php files, setting DB_CHARSET to 'utf8'.
6. Run the utf8mb4_conversion.php script.
7. Change the site's configure.php files, setting DB_CHARSET to 'utf8mb4'.

All was going well, until I added a plugin whose installation adds a table which includes one or more character (i.e. 'char' or 'varchar') fields. When that table was added, it and its fields' collations were set to 'utf8mb4_unicode_520_ci'. Unfortunately, while the pre-existing tables' collations were set to 'utf8mb4_unicode_520_ci', those tables' fields were set to 'utf8mb4_general_ci'. When a SQL query that binds the newly-added table to a pre-existing one, I was met with:

HP Fatal error: 1267:Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='

I was able to correct the issue by logging into the site's webhost and, using phpMyAdmin's *Operations *tab, changing the collation of the database, all tables and all table fields to utf8mb4_general_ci.

I've got a beta release of my update to the convert_db2utf8 and utf8mb4-conversion utilities available for (er) some beta testing: https://github.com/lat9/database-converter/releases/tag/v2.0.0-beta1

Please remember that this is a beta, so make sure to back up any database that you're using to kick the tires! This tool provides the following features:

1. Detects and corrects zero-date defaults in database fields.
2. Detects and corrects zero-date values in database fields.
3. Reports on any mixed-collations present in the database and provides a means to correct them.
4. Provides a means to update a *latin1 *database to either *utf8 *or *utf8mb4 *or a *utf8 *database to utf8mb4.

I'd especially appreciate any testing on 'true' multi-byte (e.g. Japanese or Hebrew) databases, as I have no access to such an installation.

Any reports of issues (and, hopefully successes), please report back here.

Fantastic news, thank you Cindy.

For those who may not be aware, moving to UTF-8 is a best practice for Zen Cart. It allows you to support emojis, various foreign languages (including foreign names), and many other things. You can read about it here:

https://docs.zen-cart.com/user/upgrading/convert_to_utf8/

swguy:

Fantastic news, thank you Cindy.

For those who may not be aware, moving to UTF-8 is a best practice for Zen Cart. It allows you to support emojis, various foreign languages (including foreign names), and many other things. You can read about it here:

https://docs.zen-cart.com/user/upgrading/convert_to_utf8/
I believe that it's the database's **utf8mb4 **character-set that fully enables those features.

The updated Database Conversion Tool is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2367

Additional information can be found on the tool's GitHub repository: https://github.com/lat9/database-converter

Hi All,

Just received this message after running the program on a live server:

ALTER TABLE product_type_layout DROP INDEX unq_config_id_zen, DROP INDEX idx_key_value_zen, DROP INDEX idx_type_id_sort_order_zen
1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

I am running version 1.5.5.e and I am not sure what I should be doing with this error so I have restored the backup database that I had.

Any assistance would be a great help because the Collations (of live server before running the module) in the database consist of the following:

latin1_swedish_ci
utf8mb4_general_ci
utf8_general_ci

@Bruce1952, please access the site's cPanel's phpMyAdmin and post a screenshot of the "Structure View" of the site's product_type_layout table.

Weird issue:

[30-Jul-2023 18:30:02 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect table definition; there can be only one auto column and it must be defined as a key in /Users/scott/sites/client/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/client/ConvertDb.php(736): mysqli_query(Object(mysqli), 'ALTER TABLE ze...') #1 /Users/scott/sites/client/ConvertDb.php(375): ConvertDb->doQuery('ALTER TABLE ze...')
#2 /Users/scott/sites/client/convert_database.php(282): ConvertDb->updateTableCollations('zen_better_toge...', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/client/ConvertDb.php on line 736

What's confusing is that there is only one auto column and it is defined as a key. So I'm not sure why this is failing.

Also, this backtrace was not written to /logs, which might be a feature to add in the next version.

Another database, different issue:

[30-Jul-2023 22:06:38 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Specified key was too long; max key length is 1000 bytes in /Users/scott/sites/thatsoft_store/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/thatsoft_store/ConvertDb.php(736): mysqli_query(Object(mysqli), 'CREATE INDEX i...') #1 /Users/scott/sites/thatsoft_store/ConvertDb.php(395): ConvertDb->doQuery('CREATE INDEX i...')
#2 /Users/scott/sites/thatsoft_store/convert_database.php(282): ConvertDb->updateTableCollations('coupons', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/thatsoft_store/ConvertDb.php on line 736

Keys on coupons table are as per basic install in 1.5.8.

Running in PHP 8.1 + MySQL 5.7.39

swguy:

Weird issue:

[30-Jul-2023 18:30:02 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect table definition; there can be only one auto column and it must be defined as a key in /Users/scott/sites/client/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/client/ConvertDb.php(736): mysqli_query(Object(mysqli), 'ALTER TABLE ze...') #1 /Users/scott/sites/client/ConvertDb.php(375): ConvertDb->doQuery('ALTER TABLE ze...')
#2 /Users/scott/sites/client/convert_database.php(282): ConvertDb->updateTableCollations('zen_better_toge...', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/client/ConvertDb.php on line 736

What's confusing is that there is only one auto column and it is defined as a key. So I'm not sure why this is failing.

Also, this backtrace was not written to /logs, which might be a feature to add in the next version.
Shoot a copy of the original, zipped or gzipped database to my direct email and I'll give it a look-see.

swguy:

Another database, different issue:

[30-Jul-2023 22:06:38 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Specified key was too long; max key length is 1000 bytes in /Users/scott/sites/thatsoft_store/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/thatsoft_store/ConvertDb.php(736): mysqli_query(Object(mysqli), 'CREATE INDEX i...') #1 /Users/scott/sites/thatsoft_store/ConvertDb.php(395): ConvertDb->doQuery('CREATE INDEX i...')
#2 /Users/scott/sites/thatsoft_store/convert_database.php(282): ConvertDb->updateTableCollations('coupons', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/thatsoft_store/ConvertDb.php on line 736

Keys on coupons table are as per basic install in 1.5.8.

Running in PHP 8.1 + MySQL 5.7.39
What was the database's base charset/collation and that for the coupons table originally?