Bug #89169
closed
Redirects indey key too long after source path increase
Added by Mathias Brodala about 5 years ago.
Updated over 4 years ago.
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 to Bug #88336: redirects: longer URL strings are stripped in source_path added
- Related to Bug #82080: Indexes too large for some tables with utf8mb4 added
- Description updated (diff)
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).
- Related to Bug #89063: Index too large for sys_redirect added
- Has duplicate Bug #89063: Index too large for sys_redirect added
- Related to deleted (Bug #89063: Index too large for sys_redirect)
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.
- 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?
- 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