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

Quote:

---

Originally Posted by 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

Quote:

---

Originally Posted by 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.

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.