[BUGFIX] Apply InnoDB as default to CREATE TABLE options

[TYPO3IncTeam]

ℹ️ View this commit on Github
👥 Authored by Benjamin Franzke [email protected]
✔️ Merged by Benjamin Franzke [email protected]

Commit message

[BUGFIX] Apply InnoDB as default to CREATE TABLE options

Our database schema relies on InnoDB for certain indexes.
The entry_identifier key size of the sys_registry is 1024
((128 + 128) * 4 = 1024) when using a 4-byte utf8mb4 charset,
which exceeds the MySQL MyISAM limit of 1000 bytes per key.

Ensure that we apply InnoDB as a default for all tables (instead
of defining it explicitly for only some table and therefore miss
relevant ones).

Ensure we also create migrations to switch tables to InnoDB that do
not explicitly list ENGINE=MyISAM in their table options.

Also default to ROW_FORMAT=Dynamic to ensure existing
ENGINE=MyISAM ROW_FORMAT=Fixed tables can be switched to InnoDB
which does not support ROW_FORMAT=Fixed in strict mode.

See https://dev.mysql.com/doc/refman/5.7/en/create-table.html:

For InnoDB tables:

• …
• ROW_FORMAT=FIXED is not supported. If ROW_FORMAT=FIXED is specified
  while innodb_strict_mode is disabled, InnoDB issues a warning and
  assumes ROW_FORMAT=DYNAMIC. If ROW_FORMAT=FIXED is specified while
  innodb_strict_mode is enabled, which is the default, InnoDB returns
  an error.

The AboutDatabaseErrorRowSizeTooLarge is updated to reflect this change.
The previous section that describe these actions as manual checks have
been removed in order to reduce the amount of manual maintenance that an
integrator has to perform for a TYPO3 update.

Resolves: #105515
Related: #104153
Releases: main, 13.4
Change-Id: I96712c599b84c82da3d9ceae97ed975325a98f24
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/86878
Tested-by: Benjamin Franzke [email protected]
Tested-by: core-ci [email protected]
Reviewed-by: Stefan Bürk [email protected]
Reviewed-by: Benjamin Franzke [email protected]
Reviewed-by: Andreas Kienast [email protected]
Tested-by: Andreas Kienast [email protected]
Tested-by: Stefan Bürk [email protected]

➗ Modified files

13.2/Important-104153-AboutDatabaseErrorRowSizeTooLarge.rst

@@ -47,9 +47,13 @@ Ensure storage engine is 'InnoDB'
 
 TYPO3 typically utilizes the :sql:`InnoDB` storage engine for tables in
 MySQL / MariaDB databases. However, instances upgraded from older TYPO3 Core
-versions might still employ different storage engines for some tables. While the
-TYPO3 Core plans to automatically detect and transition these to :sql:`InnoDB`
-in the future, maintainers should manually verify the engine currently in use:
+versions might still employ different storage engines for some tables.
+
+TYPO3 Core provides an automatic migration within
+:guilabel:`Admin Tools > Maintenance > Analyze Database Structure` and will
+suggest to migrate all tables to :sql:`InnoDB`.
+
+You can manually verify the engine currently in use:
 
 ..  code-block:: sql
 
@@ -58,7 +62,9 @@ in the future, maintainers should manually verify the engine currently in use:
     WHERE `TABLE_SCHEMA`='my_database'
     AND `TABLE_NAME`='tt_content';
 
-Tables *not* using :sql:`InnoDB` should be converted to :sql:`InnoDB`:
+Tables *not* using :sql:`InnoDB` should be converted via
+:guilabel:`Admin Tools > Maintenance > Analyze Database Structure` or manually
+via SQL:
 
 ..  code-block:: sql
 
@@ -72,10 +78,13 @@ The :sql:`InnoDB` row format dictates how data is physically stored. The
 :sql:`Dynamic` row format provides better support for tables with many
 variable-length columns and has been the default format for some time. However,
 instances upgraded from older TYPO3 Core versions and older MySQL / MariaDB
-engines might still use the previous default format :sql:`Compact`. While the
-TYPO3 Core intends to automatically detect and transition such tables to the
-:sql:`Dynamic` row format in the future, maintainers should manually verify the
-format currently in use:
+engines might still use the previous default format :sql:`Compact`.
+
+TYPO3 Core provides an automatic migration within
+:guilabel:`Admin Tools > Maintenance > Analyze Database Structure` and will
+suggest to migrate all tables to :sql:`ROW_FORMAT=DYNAMIC`.
+
+You can manually verify the row format currently in use:
 
 ..  code-block:: sql
 
@@ -84,7 +93,9 @@ format currently in use:
     WHERE `TABLE_SCHEMA`='my_database'
     AND `TABLE_NAME`='tt_content';
 
-Tables *not* using 'Dynamic' should be converted:
+Tables *not* using :sql:`Dynamic` should be converted via
+:guilabel:`Admin Tools > Maintenance > Analyze Database Structure` or manually
+via SQL:
 
 ..  code-block:: sql
 
@@ -150,9 +161,11 @@ current value:
 Row size too large
 ------------------
 
-This document now assumes MySQL / MariaDB, the table in question uses the :sql:`InnoDB`
-storage engine with :sql:`Dynamic` row format, a system maintainer is aware of
-specific column charsets, and :sql:`innodb_page_size` default :sql:`16384` is kept.
+This document now assumes that MySQL / MariaDB is used, the table in question
+uses the :sql:`InnoDB` storage engine with :sql:`Dynamic` row format (please
+check :guilabel:`Admin Tools > Maintenance > Analyze Database Structure` which
+provides automatic migrations), :sql:`innodb_page_size` default :sql:`16384` is
+set, and that a system maintainer is aware of specific column charsets.
 
 
 Error "Row size too large 65535"