Bug #89169

Redirects indey key too long after source path increase

Added by Mathias Brodala about 1 month ago. Updated 5 days ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2019-09-13
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
7.3
Tags:
Complexity:
Is Regression:
Yes
Sprint Focus:

Description

Due to #88336 redirect URLs can now be longer. However, this also affects the index_source index which includes source_host and source_path which now easily extends regular index limits:

$ typo3cms d:u -vvv
No schema updates were performed for update types:
"field.add", "field.change", "table.add", "table.change" 

The following errors occurred:
+---------------+-------------------------------------------------+-----------------------------+
| Type          | SQL Statement                                   | Message                     |
+---------------+-------------------------------------------------+-----------------------------+
| Change fields | ALTER TABLE `sys_redirect` CHANGE `source_path` | Specified key was too long; |
|               | `source_path` VARCHAR(2048) DEFAULT '' NOT NULL | max key length is 3072      |
|               |                                                 | bytes                       |
|               |                                                 |                             |
+---------------+-------------------------------------------------+-----------------------------+

The collation is utf_general_ci for the mentioned fields and table.

After manually dropping the index_source index the change can be applied however:

$ typo3cms d:u -vvv
The following database schema updates were performed:
+---------------+-------------------------------------------------+
| Type          | SQL Statements                                  |
+---------------+-------------------------------------------------+
| Add fields    | CREATE INDEX `index_source` ON `sys_redirect`   |
|               | (source_host(80), source_path(80))              |
| Change fields | ALTER TABLE `sys_redirect` CHANGE `source_path` |
|               | `source_path` VARCHAR(2048) DEFAULT '' NOT NULL |
+---------------+-------------------------------------------------+

Related issues

Related to TYPO3 Core - Bug #88336: redirects: longer URL strings are stripped in source_path Resolved 2019-05-12
Related to TYPO3 Core - Bug #82080: Indexes too large for some tables with utf8mb4 Closed 2017-08-11
Duplicated by TYPO3 Core - Bug #89063: Index too large for sys_redirect New 2019-09-02

History

#1 Updated by Mathias Brodala about 1 month ago

  • Related to Bug #88336: redirects: longer URL strings are stripped in source_path added

#2 Updated by Mathias Brodala about 1 month ago

  • Related to Bug #82080: Indexes too large for some tables with utf8mb4 added

#3 Updated by Mathias Brodala about 1 month ago

  • Description updated (diff)

Notice that

#4 Updated by Michael Kasten 14 days ago

  • PHP Version set to 7.3

Confirmed: with MySQL 5.7.27 on a Linux System (Ubuntu 18:04)

I guess, using an index is an performance issue, but using an long index like this, will not realy bring any performance points.

Ok an Url can be 2048 Characters long, and a varchar field can be hold a string with 2048 characters,
but the index will reach the Limits with a kollation like utf8_general_ci.
(with the utf8_general_ci kollation the string of the column source_path can be max 768 Chars long before the index will reach the limit)

But the RFC for URLs say that only ASCII Chars allowed. Maybe is it possible to use the ASCII kollation for this table or is this a bad idea?
(I test it for my own but not shure if there are others Problems with the ASCII kollation).

#5 Updated by Guido Schmechel 14 days ago

  • Related to Bug #89063: Index too large for sys_redirect added

#6 Updated by Frank Naegler 5 days ago

  • Duplicated by Bug #89063: Index too large for sys_redirect added

#7 Updated by Frank Naegler 5 days ago

  • Related to deleted (Bug #89063: Index too large for sys_redirect)

#8 Updated by Frank Naegler 5 days ago

The new index length is restricted: KEY index_source (source_host(80),source_path(80))
It looks like the old index is not dropped, I guess the schema migrator is not able to check this at the moment.

Also available in: Atom PDF