Hi there
I am now already struggling for hours with the known ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.problem on a utf8mb4 table that cannot be changed in any ways.
I tried:
DROP INDEX `url` ON `wp_4_mailpoet_newsletter_links`;
ALTER TABLE `wp_4_mailpoet_newsletter_links` ROW_FORMAT=DYNAMIC;
REPAIR TABLE `wp_4_mailpoet_newsletter_links`;
all get rejected by the same error message. I am not even able to create a dump of the table structure:
mysqldump --no-data dbname wp_4_mailpoet_newsletter_links
a similar table which is still working (without data) looks like this:
CREATE TABLE `wp_3_mailpoet_newsletter_links` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`newsletter_id` int unsigned NOT NULL,
`queue_id` int unsigned NOT NULL,
`url` varchar(2083) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`hash` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `newsletter_id` (`newsletter_id`),
KEY `queue_id` (`queue_id`),
KEY `url` (`url`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
All tutorials and forum posts about this problem pointed me to the following:
set global innodb_default_row_format = dynamic;
set global innodb_large_prefix = ON;
set global innodb_file_format = Barracuda;
But both innodb_large_prefix and innodb_file_format won’t exist in 8.0.21-12 Percona Server for MySQL.
Here’s an extract (cannot post all due to forum limitations) of my current InnoDB settings:
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_%';
+---------------------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------------------+------------------------+
| innodb_default_row_format | dynamic |
| innodb_file_per_table | ON |
| innodb_strict_mode | ON |
| innodb_version | 8.0.21-12 |
+---------------------------------------------+------------------------+
Any help greatly appreciated!! Cheers, Philip