Upgrade Wizard Deadlock
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.
Updated by SICOR KDL GmbH about 3 years ago
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.
Updated by SICOR KDL GmbH almost 3 years ago
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.
Updated by Tymoteusz Motylewski over 2 years ago
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.