Bug #89169

Redirects indey key too long after source path increase

Added by Mathias Brodala 3 months ago. Updated about 2 months ago.

Status:
Needs Feedback
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 Closed 2019-09-02

Associated revisions

Revision ac4e3bfa (diff)
Added by Manuel Selbach about 2 months ago

[BUGFIX] Respect changed indexes within schema migration

With this change all indexes identified as "changed" will get proper
SQL statements to update them (e.g. DROP and ADD afterwards).

The order has also been changed to apply the index changes first, to
avoid issues on changing a field on a table, see #89169.

Releases: master, 9.5
Resolves: #88176
Related: #89169
Related: #89063
Change-Id: Ia683ff1a6ccea47cc9b3848f2db0b9f32b984626
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/61476
Tested-by: TYPO3com <>
Tested-by: Daniel Goerz <>
Tested-by: Alexander Schnitzler <>
Tested-by: Manuel Selbach <>
Reviewed-by: Daniel Goerz <>
Reviewed-by: Guido Schmechel <>
Reviewed-by: Markus Klein <>
Reviewed-by: Alexander Schnitzler <>
Reviewed-by: Manuel Selbach <>

Revision 4e9c9ac3 (diff)
Added by Manuel Selbach about 2 months ago

[BUGFIX] Respect changed indexes within schema migration

With this change all indexes identified as "changed" will get proper
SQL statements to update them (e.g. DROP and ADD afterwards).

The order has also been changed to apply the index changes first, to
avoid issues on changing a field on a table, see #89169.

Releases: master, 9.5
Resolves: #88176
Related: #89169
Related: #89063
Change-Id: Ia683ff1a6ccea47cc9b3848f2db0b9f32b984626
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62063
Tested-by: TYPO3com <>
Tested-by: Manuel Selbach <>
Reviewed-by: Manuel Selbach <>

History

#1 Updated by Mathias Brodala 3 months ago

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

#2 Updated by Mathias Brodala 3 months ago

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

#3 Updated by Mathias Brodala 3 months ago

  • Description updated (diff)

Notice that

#4 Updated by Michael Kasten 2 months 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 2 months ago

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

#6 Updated by Frank Naegler 2 months ago

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

#7 Updated by Frank Naegler 2 months ago

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

#8 Updated by Frank Naegler 2 months 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 about 2 months 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?

Also available in: Atom PDF