Review main Merged

87757[BUGFIX] Respect column `CHARACTER SET` AND `COLLATE` in ext_tables.sql

Stefan Bürk CR +2 V +2 Hard +322 −6 updated 04/07/25, 14:28

Commit message

With #104567 a basic support for table and column based
`COLLATE` and `CHARACTER SET` has been introduced based on
database connection `defaultTableOptions` for MySQL and MariaDB.

Having different `CHARACTER SET` and `COLLATE` on a column based
level is technically possible by the database systems `MySQL` and
`MariaDB`, but could not be configured until now.

One possible way to define the database schema is by providing a
file `ext_tables.sql` within extension folders using a subset of
`MySQL DDL` language.

This change extends `ext_tables.sql` parsing to read column specific
`CHARACTER SET` and `COLLATE` options following `MySQL` and `MariaDB`
definitions to allow setting options on a column level, different from
the connection, schema or table default settings. Further, internal
`ConnectionMigrator` is adjusted to ignore these settings for `SQLite`
and `PostgreSQL` due to compatibility issues and requiring not
compatible conversion definition and server installation requirements,
which cannot be expected to be available.

Configuring special per column character sets and collations remains
a rare usecase and should be used with care.

For the start `CHARACTER SET ascii COLLATE ascii_bin` is used for
table `sys_refindex` column `hash` to reduce required space for the
index using a single byte instead of multiple bytes per character.
This is possible since the hash field contains 'hex' ascii 0-9,a-f only.

This change is not considered breaking since applying the DB changes
is optional.

Review

  • Code review CR +2 Done
    • Christian Kuhn +2
    • Benni Mack +1
  • Verified V +2 Done
    • Christian Kuhn +2
    • Benni Mack +1
    • core-ci +1 CI

Issues 3

Files 8

Review log 2233

Show 48 earlier messages
  1. Stefan Bürk uploaded patch set 1 01/14/25, 12:35

  2. core-ci voted on patch set 1 V +1 01/14/25, 12:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/72459

  3. Stefan Bürk uploaded patch set 2 V +1 01/17/25, 14:37

  4. core-ci voted on patch set 2 V +1 01/17/25, 14:43 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/72619

  5. Stefan Bürk uploaded patch set 3 V +1 04/03/25, 09:49

  6. core-ci voted on patch set 3 V +1 04/03/25, 09:57 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/76933

  7. Stefan Bürk uploaded patch set 4 V +1 04/03/25, 10:18

  8. core-ci voted on patch set 4 V +1 04/03/25, 10:24 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/76937

  1. Stefan Bürk uploaded patch set 5 V +1 04/03/25, 15:28

  2. core-ci voted on patch set 5 V +1 04/03/25, 15:34 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/76956

  3. Stefan Bürk updated the commit message in patch set 6 V +1 04/03/25, 15:34

  4. core-ci voted on patch set 6 V +1 04/03/25, 15:40 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/76957

  5. Benjamin Franzke commented on patch set 6 CR -1 04/03/25, 18:34

    ext_tables.sql:198

    I think we should not use latin1, as it allows for awkward Windows-1252 characters when the 8th bit is set and is *incompatible* to UTF-8. We should use `ascii_bin` to limit to the 7bit ascii subset (that we actually desire) and that is compatible to utf-8, as it is a subset.
  6. Stefan Bürk uploaded patch set 7 CR -1 V +1 04/04/25, 08:30

  7. core-ci voted on patch set 7 V +1 04/04/25, 08:36 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/76990

  8. Stefan Bürk voted on patch set 7 CR -1 04/04/25, 08:46

  9. Stefan Bürk uploaded patch set 8 CR -1 V +1 04/04/25, 14:20

  10. Stefan Bürk updated the commit message in patch set 9 04/04/25, 14:22

  11. Stefan Bürk commented on patch set 9 04/04/25, 14:24

    ext_tables.sql:198 Reply

    I think you are absolutly right, thanks for the input. Changed it now to ascii/ascii bin now and tested the changes again.
  12. core-ci voted on patch set 8 V +1 04/04/25, 14:26 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/77008

  13. core-ci voted on patch set 9 V +1 04/04/25, 14:28 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/77009

  14. Benjamin Franzke commented on patch set 9 04/04/25, 15:34

    ConnectionMigrator.php:1687

    will be set to BINARY in line 1693 for sqlite. This makes this hard to understand, as the comment says it's enforced, but actually it is not, since it is overwritten later. Maybe the logic for sqlite vs postgres should be split?
  15. Stefan Bürk uploaded patch set 10 V +1 04/04/25, 19:19

  16. Stefan Bürk commented on patch set 10 04/04/25, 19:20

    ConnectionMigrator.php:1687 Reply

    Done
  17. core-ci voted on patch set 10 V +1 04/04/25, 19:25 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/77015

  18. Christian Kuhn uploaded patch set 11 V +1 04/07/25, 09:29

  19. Christian Kuhn voted on patch set 11 CR +1 V +1 04/07/25, 09:30

  20. core-ci voted on patch set 11 V +1 04/07/25, 09:35 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/77101

  21. Benni Mack voted on patch set 11 CR +1 V +1 04/07/25, 10:14

  22. Christian Kuhn voted on patch set 11 CR +2 V +2 04/07/25, 10:31

  23. Christian Kuhn merged patch set 12 04/07/25, 10:48

  24. Ayke Halder commented on patch set 12 04/07/25, 12:39

    > For the start `CHARACTER SET ascii COLLATE ascii_bin` is used for table `sys_refindex` column `hash` to reduce required space for the index using a single byte instead of multiple bytes per character. This is possible since the hash field contains 'hex' ascii 0-9,a-f only. With MariaDB I do not get any improvement of db size when `hash` columns character-set is changed to `ascii_bin` instead of default `utf8mb4_unicode_ci`. Do a before/after comparison with these statistics: ```sql OPTIMIZE TABLE `sys_refindex`; SHOW TABLE STATUS FROM `db` WHERE `Name` = 'sys_refindex'; ```
  25. Christian Kuhn commented on patch set 12 04/07/25, 14:28

    Reply

    compare something like this before/after: select database_name, index_name, (1.0stat_value@@innodb_page_size/1024/1024) as index_size, table_name from mysql.innodb_index_stats where stat_name = 'size' and index_name not in ('GEN_CLUST_INDEX') and database_name = 'coremaster' and table_name='sys_refindex' order by index_size desc;