Bug #89169

Redirects indey key too long after source path increase

Added by Mathias Brodala over 1 year ago. Updated about 1 year ago.

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

0%

Estimated time:
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_pathClosed2019-05-12

Actions
Related to TYPO3 Core - Bug #82080: Indexes too large for some tables with utf8mb4Closed2017-08-11

Actions
Has duplicate TYPO3 Core - Bug #89063: Index too large for sys_redirectClosed2019-09-02

Actions
#1

Updated by Mathias Brodala over 1 year ago

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

Updated by Mathias Brodala over 1 year ago

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

Updated by Mathias Brodala over 1 year ago

  • Description updated (diff)

Notice that

#4

Updated by Michael Kasten over 1 year 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 over 1 year ago

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

Updated by Frank Naegler over 1 year ago

  • Has duplicate Bug #89063: Index too large for sys_redirect added
#7

Updated by Frank Naegler over 1 year ago

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

Updated by Frank Naegler over 1 year 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.

#9

Updated by Manuel Selbach over 1 year ago

  • Status changed from New to Needs Feedback

This issue occurs, as the "changed index" of a table was never identified and with that the index might be without the limitation, if a setup got updated instead of a fresh database installation.

With the fix provided for https://forge.typo3.org/issues/88176 "changed indexes" will be identified and provided with SQL to update it in the "Analyze Database Structure" and should also be used for the steps mentioned here.

If the key is limited, the update of the table column should also be no problem.

May I ask you to check this with the current branch of 9.5 and provide some feedback?

#10

Updated by Susanne Moog about 1 year ago

  • Status changed from Needs Feedback to Closed

No feedback since a couple of months and issue likely fixed (see comments above) - closing now, please report back if there is anything still left to do.

Also available in: Atom PDF