Database Conversion Tools [Support Thread]

[lat9]

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-con...g/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.

[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/upgra...nvert_to_utf8/

[lat9]

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/upgra...nvert_to_utf8/

I believe that it's the database's utf8mb4 character-set that fully enables those features.

[lat9]

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

[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

[lat9]

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.

[lat9]

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?