Bug #82080
closed
Indexes too large for some tables with utf8mb4
100%
Description
Hey,
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? :)
Best regards
Tobias
Updated by Riccardo De Contardi almost 7 years ago
- Status changed from New to Needs Feedback
I've checked on 8.7.9 and latest master - the field domainName is now varchar(255)
Do you think it is sufficient to consider the issue closed?
thank you.
Updated by Tymoteusz Motylewski almost 7 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
Updated by Riccardo De Contardi almost 7 years ago
- Status changed from Needs Feedback to New
Updated by Tymoteusz Motylewski over 6 years ago
- Related to Bug #82551: Upgrade Wizard Deadlock added
Updated by Tymoteusz Motylewski over 6 years ago
- Related to Feature #80398: Make default charset and collation for new tables configurable added
Updated by Sybille Peters over 6 years 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:- sys_domain
- fe_users
- sys_refindex
Updated by Sybille Peters over 6 years ago
- Subject changed from Specified key was too long when creating an index to Indexes too large for some tables with utf8mb4
Updated by Gerrit Code Review about 6 years ago
- Status changed from New to Under Review
Patch set 9 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/56440
Updated by Lienhart Woitok about 6 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset ed806ef550a63d9034bf4edba8b38b92b1fd71ed.
Updated by
Updated by Mathias Brodala about 5 years ago
- Related to Bug #89169: Redirects indey key too long after source path increase added