Bug #89169
closed
Redirects indey key too long after source path increase
0%
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 | +---------------+-------------------------------------------------+
Updated by Mathias Brodala about 5 years ago
- Related to Bug #88336: redirects: longer URL strings are stripped in source_path added
Updated by Mathias Brodala about 5 years ago
- Related to Bug #82080: Indexes too large for some tables with utf8mb4 added
Updated by Michael Kasten about 5 years 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).
Updated by Guido Schmechel about 5 years ago
- Related to Bug #89063: Index too large for sys_redirect added
Updated by Frank Nägler about 5 years ago
- Has duplicate Bug #89063: Index too large for sys_redirect added
Updated by Frank Nägler about 5 years ago
- Related to deleted (Bug #89063: Index too large for sys_redirect)
Updated by Frank Nägler about 5 years 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.
Updated by Manuel Selbach about 5 years 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?
Updated by Susanne Moog over 4 years 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.