Bug #82551
closed
Added by SICOR KDL GmbH over 6 years ago.
Updated over 5 years ago.
Description
Hello,
with newer 8.7.x versions, at least with 8.7.7, we experience a deadlock for these DB changes:
CREATE INDEX `cache_id` ON cf_cache_imagesizes (identifier, expires)
CREATE INDEX `cache_id` ON cf_cache_imagesizes_tags (identifier)
CREATE INDEX `cache_tag` ON cf_cache_imagesizes_tags (tag)
The Error message is three times:
SQL-ERROR: Index column size too large. The maximum column size is 767 bytes.
When executed manually, it is considered a "warning" only:
1071 Specified key was too long; max key length is 767 bytes 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
We are running Debian 9.1 with Maria 10.1 on the server.
Greetings,
Manuel
One of our technicians stayed up last night fixing the problem on the database side by switching Maria from antelope to barracuda.
I still see a need to work on the upgrade wizards though, as I think that a database warning shouldn't deadlock an upgrade wizard entirely. Even worse, it also prevented all other wizards from showing up, so I couldn't continue with the upgrade at all.
are you using utf8mb4? the index issue could be related with that.
Christian Kuhn wrote:
are you using utf8mb4? the index issue could be related with that.
Indeed that's part of the problem we have on this particular server (Probably some new i-MSCP default, actually it should create schemes with utf8-general-ci). We're going to dump all schemes and reimport with the proper encoding.
I guess it's okay to close the ticket here, unless you still want to do something to prevent wizard deadlocks in cases where something like that goes wrong.
- Related to Bug #82080: Indexes too large for some tables with utf8mb4 added
- Related to Feature #80398: Make default charset and collation for new tables configurable added
I saw similar errors when a db was created with utf8mb4 as default charset.
The error was thrown despite backup I was importing had correct utf8 (not mb4) used in the table create statement.
I've found 2 workarounds:
1) set default charset for db to utf-8.
2) add default row format (DYNAMIC) in the table create statement (and make sure innodb_large_prefix is enabled) https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
Also worth knowing is that since mysql 5.7 or mariadb 10.2, the default row format is dynamic and large_prefix is enabled.
- Status changed from New to Under Review
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
- Status changed from Resolved to Closed
Also available in: Atom
PDF