Bug #82080

Indexes too large for some tables with utf8mb4

Added by Tobias Hüske over 2 years ago. Updated over 1 year ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Install Tool
Target version:
-
Start date:
2017-08-11
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

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


Related issues

Related to TYPO3 Core - Bug #82551: Upgrade Wizard Deadlock Closed 2017-09-25
Related to TYPO3 Core - Feature #80398: Make default charset and collation for new tables configurable Closed 2017-03-22
Related to TYPO3 Core - Bug #89169: Redirects indey key too long after source path increase Needs Feedback 2019-09-13

Associated revisions

Revision ed806ef5 (diff)
Added by Lienhart Woitok over 1 year ago

[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.

Resolves: #80398
Resolves: #82080
Resolves: #82551
Releases: master
Change-Id: I6bf464a22c6ed74631bf5aacff9c2cfe670077da
Reviewed-on: https://review.typo3.org/56440
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>
Tested-by: TYPO3com <>
Reviewed-by: Lienhart Woitok <>
Tested-by: Lienhart Woitok <>
Reviewed-by: Georg Großberger <>
Reviewed-by: Jigal van Hemert <>
Tested-by: Jigal van Hemert <>

History

#1 Updated by Riccardo De Contardi about 2 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.

#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

#3 Updated by Riccardo De Contardi almost 2 years ago

  • Status changed from Needs Feedback to New

#4 Updated by Tymoteusz Motylewski almost 2 years ago

  • Related to Bug #82551: Upgrade Wizard Deadlock added

#5 Updated by Tymoteusz Motylewski almost 2 years ago

  • Related to Feature #80398: Make default charset and collation for new tables configurable added

#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:
  • sys_domain
  • fe_users
  • sys_refindex

#7 Updated by Sybille Peters over 1 year ago

  • Subject changed from Specified key was too long when creating an index to Indexes too large for some tables with utf8mb4

#8 Updated by Gerrit Code Review over 1 year 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

#9 Updated by Lienhart Woitok over 1 year ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#10 Updated by Benni Mack over 1 year ago

  • Status changed from Resolved to Closed

#11 Updated by Mathias Brodala 5 months ago

  • Related to Bug #89169: Redirects indey key too long after source path increase added

Also available in: Atom PDF