> What was the database's base charset/collation and that for the coupons table originally?
UTF8.
sending both db's as requested
Printable View
> What was the database's base charset/collation and that for the coupons table originally?
UTF8.
sending both db's as requested
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.
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.Code: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;
Note: I'm running my testing with the following MySQL configuration settings:
Code: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.
> 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.
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
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.
(Not sure this helps, but the old converter also had an issue in PHP 8+ with silent death.)Code: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;