Database Conversion Tools [Support Thread]

What was the database's base charset/collation and that for the coupons table originally?

UTF8.

sending both db's as requested

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.
While there is only one AUTO_INCREMENT column in that table, its value is specified as a KEY ... not a PRIMARY KEY. The tool sets a table's AUTO_INCREMENT field as its PRIMARY KEY and then adds any additional table-keys. That's why the error message is being displayed, since the PRIMARY KEY is now the same as the to-be-added KEY.

CREATE TABLE `zen_better_together_admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `linkage_type` char(20) CHARACTER SET utf8mb4 NOT NULL,
  `field1` int(11) NOT NULL DEFAULT '0',
  `field2` int(11) NOT NULL DEFAULT '0',
  `active` char(1) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'Y',
  `discount_units` char(1) CHARACTER SET utf8mb4 NOT NULL DEFAULT '%',
  `discount_amount` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `start_date` date NOT NULL DEFAULT '0001-01-01',
  `end_date` date NOT NULL DEFAULT '0001-01-01',
  KEY `idx` (`id`),
  KEY `idx_date_added` (`date_added`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

The tool uses *only *a site's /includes/local/configure.php (if present) and the /includes/configure.php to determine the database-related configuration values, so it's not aware of the site's /logs sub-directory. That's why the error(s) aren't created in a log file.

Note: I'm running my testing with the following MySQL configuration settings:

define('DB_MYSQL_MODE', 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION');

Note, too, that while the site's configure.php indicates a DB_PREFIX of 'zen_', there are a number of tables in the database that don't use a 'zen_' prefix. Those won't participate in any charset/collation conversion.

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 'flavor' of utf8mb4_unicode collation was requested? There are a couple, e.g. utf8mb4_unicode_ci, utf8mb4_unicode_520_ci.

What 'flavor' of utf8mb4_unicode collation was requested?
utf8mb4_unicode_ci

there are a number of tables in the database that don't use a 'zen_' prefix.
Yep just ignore those. They aren't for Zen Cart.

While there is only one AUTO_INCREMENT column in that table, its value is specified as a KEY ... not a PRIMARY KEY.

Ah, nice catch. This is an ancient table that i should have junked anyway. Let's call issue 1 fixed with the caveat that people need to check by hand if any created tables have this problem.

Cleaned up a couple more issues like this and it worked like a champ. Thank you! (This was problem #1 - still unsure about #2)

On #2

[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

I have modified the field length of coupon_code but only from 32 -> 255. Not sure how it got to 1000.

swguy:

On #2

[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

I have modified the field length of coupon_code but only from 32 -> 255. Not sure how it got to 1000.
That would be it; when Zen Cart started supporting utf8mb4, all char/varchar fields that acted as indices were changed to **191 **from 255. See this MySQL link for additional details: https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-conversion.html

Holy cow! Thanks for this detective work. Case closed!

Note for other upgraders: these changes (255->191) were done in the 1.5.6 upgrade.

And now I realize you provided an INCREDIBLY CLEAR error message at the bottom of the output that I very cleverly missed because I was so busy trying to chase down the log file. :(

Nope there is still an issue. This message only works in PHP 7.4. When you run in PHP 8.1, it dies silently.

You can easily dup this by adding the old better_together_admin table def, which has the primary key error.

DROP TABLE IF EXISTS better_together_admin;
CREATE TABLE better_together_admin (
  id int(11) NOT NULL auto_increment,
  linkage_type char(20) NOT NULL default ' ',
  field1 int(11) NOT NULL default '0',
  field2 int(11) NOT NULL default '0',
  active char(1) NOT NULL default 'Y',
  discount_units char(1) NOT NULL default '%',
  discount_amount decimal(15,4) NOT NULL default '0.0000',
  date_added datetime NOT NULL default '0001-01-01 00:00:00',
  start_date date NOT NULL default '0001-01-01',
  end_date date NOT NULL default '0001-01-01',
  KEY idx_date_added (date_added),
  KEY idx(id)
) ENGINE=MyISAM;

(Not sure this helps, but the old converter also had an issue in PHP 8+ with silent death.)

Fix submitted for this issue.

swguy:

Fix submitted for this issue.
Thanks for that; the update will be included in v2.0.1.

lat9:

Thanks for that; the update will be included in v2.0.1.
v2.0.1 of the Database Conversion tool is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2367

This release contains a BUGFIX (see GitHub issue #8) that enables interoperability with PHP versions 8.0 and later. Thanks to @swguy for the identification and correction of the issue.

The use of the new converter plugin at https://www.zen-cart.com/downloads.php?do=file&id=2367 is now the recommended approach for UTF-8 conversions.

So I just garbed the new one. I do not see how or where to pick to convert to just utf8 its all utf8mb4 something

You want utf8mb4.

swguy:

You want utf8mb4.

ok I guess i missed ready something where utf8 was what it should be

The converter fails out for me at Processing table [address_book]:
Error log says:
[19-Sep-2023 13:16:26 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Data too long for column 'address_title' at row 19641 in /home/pcs/dev/utf8mb4-conversion.php:170
Stack trace:
#0 /home/pcs/dev/utf8mb4-conversion.php(170): mysqli_query(Object(mysqli), 'ALTER TABLE `ad...')
#1 {main}
thrown in /home/pcs/dev/utf8mb4-conversion.php on line 170

I don't know the sort order, how can I find row 19641 to fix the address?

That's not a default field. You'll have to adjust your database to allow for the greater length required by UTF8MB4.

Ya, I had just figured out it's leftover garbage from an old mod. The uninstall SQL didn't clean up what it added. :censored: Yay, now I get to dig through the whole DB for extra garbage.