Database Conversion Tools [Support Thread]

[swguy]

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

UTF8.

sending both db's as requested

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

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');

[lat9]

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.

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

[swguy]

> 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)

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

[lat9]

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...onversion.html

[swguy]

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.

[swguy]

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.

[swguy]

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