Database Conversion Tools [Support Thread]

[lat9]

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!

[lat9]

v4 is now available for download from the Plugins.

[mike14017]

Hi

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

Regards

Mike

[lat9]

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.

[mike14017]

Hi

Done all that and converted DB. Everything seems fine.

What a cracking module, thank you.

Mike

[shags38]

Thanks for a great tool Cindy - fast and seamless.

cheers,
Mike

[Bruce1952]

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

[lat9]

@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.

[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.

[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