Bug #91907

tinytext columns cause schema inspector loop when adding an index on these columns

Added by Stefan P 3 months ago. Updated 3 months ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2020-07-31
Due date:
% Done:

0%

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

Description

Some columns (I didn't check all, these are probably just some of them), like

  • sys_category.title
  • sys_file.name
  • sys_file_metadata.title

define itself as

<columname> tinytext
or
<columname> tinytext NOT NULL

First minor problem: BLOB-type columns (like BLOB and TEXT and their differnet sizes) are not not-nullable (specifying NULL or NOT NULL on them has no effect at all).

When adding an index on these columns like

CREATE TABLE sys_category (
    INDEX my_title_index (title(100))
);

The schema inspector will create the index first, but then always suggests to drop and recreate it, in an endless loop.

Why are these columns tinytext anyways? Instead of varchar(255)? tinytext is not Unicode-aware (it can only store 255 bytes, not 255 characters). Also it is a BLOB-type, which means usually it is way slower than varchar on lookup-time (depending on lots of factors, but it can never be faster than varchar, and usally it's really slower).

I marked this as v9, but I assume it's like this in any version.

History

#1 Updated by Stefan P 3 months ago

This seems only to happen with tinytext. Tested it on a text column (sys_file.identifier) -> works perfectly.

Also available in: Atom PDF