Indexes too large for some tables with utf8mb4
after performing an update from an clean TYPO3 7.6.21 to 8.7.4 (maybe also when installing a clean 8.7.4) the Database Analyzer shows the following error:
Specified key was too long; max key length is 3072 bytes.
The query "CREATE INDEX `getDomainStartPage` ON `sys_domain` (pid, hidden, domainName)" fails, because domainName has varchar(80).
utf8 works, utf8mb4 not. I think varchar(80) should be raised or the index limited - https://github.com/TYPO3/TYPO3.CMS/blob/b66275c83818ddc02bda04ab67c53bf901cad54d/typo3/sysext/frontend/ext_tables.sql#L163.
What do you think? :)
[FEATURE] Use utf8mb4 on mysql for new instances
If installing a new TYPO3 instance on mysql, utf8mb4 is now used as
default charset for the database connection and as default collation.
Upgraders may change LocalConfiguration to use utf8mb4, too. They
however need to take care of changing their collations and setting
according table detaults on their own.
A reports status check verifies there is no mixed collation.
Reviewed-by: Christian Kuhn <email@example.com>
Tested-by: Christian Kuhn <firstname.lastname@example.org>
Tested-by: TYPO3com <email@example.com>
Reviewed-by: Lienhart Woitok <firstname.lastname@example.org>
Tested-by: Lienhart Woitok <email@example.com>
Reviewed-by: Georg Großberger <firstname.lastname@example.org>
Reviewed-by: Jigal van Hemert <email@example.com>
Tested-by: Jigal van Hemert <firstname.lastname@example.org>
#2 Updated by Tymoteusz Motylewski almost 2 years ago
Raising the domainName length will not make the issue disappear but will make it worse.
utf8mb4 uses 4 bytes per char, while "standard" utf8 collation uses 3 bytes per char.
This is why it fails on utf8mb4 and doesn't exceed the limit with plain utf8
#6 Updated by Sybille Peters over 1 year ago
What is the status on this?
Currently, if you want to use the (generally recommended) charset utf8mb4 for all tables, you can't because the indexes become too large (exceeds 1000 bytes).This is currently the case in TYPO3 8 for: