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:
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 |
+---------------------------------------------+------------------------+
Hi, what is the exact operation that you are trying to accomplish?
you can check that the table is using the newer table format that allows bigger keys with select * FROM INFORMATION_SCHEMA.INNODB_TABLES where name LIKE ‘%your_table%’;
In 8.0 the variables you mention are removed as it behaves like they are enabled all the time.
select * FROM INFORMATION_SCHEMA.INNODB_TABLES where name LIKE ‘%TABLE_NAME%’;
results same error:
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
In my case I’ve added a custom INDEX to Wordpress postmeta table filled with data, ROW FORMAT was COMPAT, INDEX was created, today I’ve noticed that errors are occuring when executing:
ALTER TABLE karty_postmeta CHANGE COLUMN meta_id meta_id bigint(20) unsigned NOT NULL auto_increment
error:
Index column size too large. The maximum column size is 767 bytes.
Drop index, alter table to ROW_FORMAT=DYNAMIC result same error as above, drop table and drop database too.
I am still worried if this could affect other tables as well, as we’re in the middle of migrating thousands of tables from MyISAM to InnoDB with most of them dating back to pre-2018 when we were still running MySQL 5.6. Could you tell if there is any risk migrating a table with ALTER TABLE … ENGINE=InnoDB?
I am still struggling with getting rid of that broken table, even tried dropping the whole database:
mysql> DROP TABLE `wp_4_mailpoet_newsletter_links`;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> DROP DATABASE `mydb`;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Also tried removing the table’s *.ibd file from /var/lib/mysql/dbname/ , restarting MySQL afterwards, which also didn’t help.
My workaround then was to migrate the whole database with mysqldump --ignore-table=mydb.wp_4_mailpoet_newsletter_links to another database, and then restoring this single table from an old backup (before MySQL 8.0 migration).
But we still need to get rid of that table and as far as I understood https://bugs.mysql.com/bug.php?id=99791 this would only prevent it from happening in MySQL 8.0.22, but not fixing such already broken tables/indexes, right?
@igroene on one webserver we have several thousands of InnoDB tables with ROW_FORMAT=COMPACT. Do you think it makes sense to change those already today on Percona/MySQL 8.0.21 (prior to MySQL 8.0.22 bugfix release) to ROW_FORMAT=DYNAMIC to avoid this problem from happening again with another table? Or is there also a risk if I do so?
Aside that, I still don’t know how to get rid of that broken table. The only remaining option I can think of would be to do a full reset of the whole MySQL server and load a full dump of all customer databases (+ pt-show-grants dump), excluding only this single broken table. But that would only be possible in a night shift with a pretty long downtime, as we’re talking about 35G data in /var/lib/mysql and a full system mysqldump of 1G compressed (gzip).
What is your recommendation for me to lower the risk of this happening again on other tables? Will Percona release 8.0.22 anytime soon?
We should do a more thorough analysis of the current situation and some testing before recommending you a strategy. I suggest you consider a small consulting engagement for this. https://learn.percona.com/contact-me in case you are interested.
Hi @igroene - Happy New Year and thanks for your offer. I managed to fix the problem by a massive restore of all databases using a full mysqldump with --ignore-table option to exclude the broken table. The whole thing caused a 1.5h downtime for my customers, but it was worth it, as anyway I wanted to get ready for XtraBackup with a clean state of all InnoDB tables.