Important: #104153 - About database error "Row size too large"

Ensure storage engine is 'InnoDB'

TYPO3 typically utilizes the 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.

TYPO3 Core provides an automatic migration within Admin Tools > Maintenance > Analyze Database Structure and will suggest to migrate all tables to InnoDB.

You can manually verify the engine currently in use:

SELECT `TABLE_NAME`,`ENGINE`
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA`='my_database'
AND `TABLE_NAME`='tt_content';

Tables not using InnoDB should be converted via Admin Tools > Maintenance > Analyze Database Structure or manually via SQL:

USE `my_database`;
ALTER TABLE `tt_content` ENGINE=InnoDB;

Ensure InnoDB row format is 'Dynamic'

The InnoDB row format dictates how data is physically stored. The 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 Compact.

TYPO3 Core provides an automatic migration within Admin Tools > Maintenance > Analyze Database Structure and will suggest to migrate all tables to ROW_FORMAT=DYNAMIC.

You can manually verify the row format currently in use:

SELECT `TABLE_NAME`,`Row_format`
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA`='my_database'
AND `TABLE_NAME`='tt_content';

Tables not using Dynamic should be converted via Admin Tools > Maintenance > Analyze Database Structure or manually via SQL:

USE 'my_database`;
ALTER TABLE `tt_content` ROW_FORMAT=DYNAMIC;

Database, table and column charset

The column charset impacts length calculations. This document assumes utf8mb4 for columns, aligning with the default TYPO3 setup. Converting an existing instance to utf8mb4 can be a complex task depending on the currently used charset and is beyond the scope of this document.

A key point about utf8mb4 is that when dealing with the utf8mb4 charset for VARCHAR() columns, storage and index calculations need to be multiplied by four (4). For example, a VARCHAR(20) can take up to eighty (80) bytes since each of the twenty (20) characters can use up to four (4) bytes. In contrast, a VARCHAR(20) in a latin1 column will consume only twenty (20) bytes, as each character is only one byte long.

The TYPO3 Core may set individual columns to a charset like latin1 in the future, which will optimize storage for ASCII-character only columns, but most content-related columns should be utf8mb4 to avoid issues with multi-byte characters.

Note that column types that do not store characters (like INT) do not have a charset. An overview of current charsets can be retrieved:

# Default charset of the database, new tables use this charset when no
# explicit charset is given with a "CREATE TABLE" statement:
SELECT `SCHEMA_NAME`, `DEFAULT_CHARACTER_SET_NAME` FROM `INFORMATION_SCHEMA`.`SCHEMATA`
WHERE `SCHEMA_NAME`='my_database';

# Default charset of a table, new columns use this charset when no
# explicit charset is given with a "ALTER TABLE" statement:
SELECT `table`.`table_name`,`charset`.`character_set_name`
FROM `information_schema`.`TABLES` AS `table`,`information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `charset`
WHERE `charset`.`collation_name`=`table`.`table_collation`
AND `table`.`table_schema`='my_database'
AND `table`.`table_name`='tt_content';

# List table columns, their column types with length and selected charsets:
SELECT `column_name`,`column_type`,`character_set_name`
FROM `information_schema`.`COLUMNS`
WHERE `table_schema`='my_database'
AND `table_name`='tt_content';

Ensure innodb_page_size is 16384

Few instances modify the MySQL / MariaDB innodb_page_size system variable, and it is advisable to keep the default value of 16384. Verify the current value:

SHOW variables WHERE `Variable_name`='innodb_page_size';

Explanation

When altering the database schema of a table, such as adding or increasing the size of a VARCHAR column, the above error might occur.

Note the statement: "The maximum row size [...] is 65535".

MySQL / MariaDB impose a global maximum size per table row of 65kB. The combined length of all column types contribute to this limit, except for TEXT and BLOB types, which are stored "off row" where only a "pointer" to the actual storage location counts.

However, standard VARCHAR fields contribute their full maximum byte length towards this 65kB limit. For instance, a VARCHAR(2048) column with the utf8mb4 character set (4 bytes per character) requires 4 * 2048 = 8192 bytes. Therefore, only 65535 - 8192 = 57343 bytes remain available for the storage of all other table columns.

As another example, consider the query below which creates a table with a VARCHAR(16383) column alongside an INT column:

# ERROR 1118 (42000): Row size too large. The maximum row size [...] is 65535
CREATE TABLE test (c1 varchar(16383), c2 int) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Let's break down the calculation:

varchar 16383 characters = 16383 * 4 bytes = 65532 bytes
int = 4 bytes
Total: 65532 + 4 = 65536 bytes

This exceeds the maximum limit by one byte, causing the query to fail.

Mitigation

The primary strategy to mitigate the 65kB limit is to minimize the use of lengthy VARCHAR columns.

For instance, in the tt_content table of a default Core instance, there are approximately a dozen VARCHAR(255) columns, totaling about 12kB, alongside smaller INT and similar fields. This leaves ample room for additional custom VARCHAR() columns.

TYPO3 v13 has introduced improvements in two key areas:

Firstly, TCA fields with type='link' and type='slug' have been converted from VARCHAR(2048) (requiring 8kB of row space) to TEXT. The tt_content table was affected by this change in at least one column ( header_link). This adjustment provides more space by default for custom columns.

Additionally, the TYPO3 Core now defaults to using TEXT instead of VARCHAR() for TCA fields with type='input' when the TCA property max is set to a value greater than 255 and extension authors utilize the column auto creation feature.

Instances encountering the 65kB limit can consider adjusting fields with these considerations in mind:

• Priority should be given to reconsidering long VARCHAR() columns first. Changing a single utf8mb4 VARCHAR(2048) column to TEXT can free enough space for up to eight (8) utf8mb4 VARCHAR(255) columns.

• Consider reducing the length of VARCHAR() columns. For instance, columns containing database table or column names can be limited to VARCHAR(64), as MySQL / MariaDB restricts table and column names to a maximum of 64 characters. Similar considerations apply to "short" content fields, such as a column storing an author's name or similar potentially limited length information.

  However, be cautious, as setting VARCHAR() columns to "too short" lengths may impose a different limit, as discussed below.

• Consider removing entries from ext_tables.sql with TYPO3 Core v13: the column auto creation feature generally provides better-defined column definitions and ensures columns stay synchronized with TCA definitions automatically. The TYPO3 Core aims to provide sensible default definitions, often superior to a potentially imprecise definition by extension authors.
• Note that individual column definitions in ext_tables.sql always override TYPO3 Core v13's column auto creation feature. In rare cases where TYPO3 Core's definition is inappropriate, extension authors can always override these details.
• Note utf8mb4 VARCHAR(255) and TINYTEXT are not the same: a VARCHAR(255) size limit is 255 characters, while a TINYTEXT is 255 bytes. The proper substitution for a (4 bytes per character) utf8mb4 VARCHAR(255) field is TEXT, which allows for 65535 bytes.
• TEXT may negatively impact performance as it forces additional Input/Output operations in the database. This is typically not a significant issue with standard TYPO3 queries, as various other operations in TYPO3 have a greater impact on overall performance. However, indiscriminately changing all fields from VARCHAR() to TEXT or similar is not advisable.
• Be mindful of indices. When VARCHAR() columns that are part of an index are changed to TEXT or similar, these indexes may require adjustment. Ensure they are properly restricted in length to avoid a "Specified key was too long" error. The InnoDB key length limit with row format Dynamic is 3072 bytes (not characters). In general, indexes on VARCHAR() and all other "longish" columns should be set with care and only if really needed since long indexes can negatively impact database performance as well, especially when a table has many write operations in production.

Explanation

This error may occur when adding or updating table rows, not only when altering table schema.

Note the statement: "Row size too large (> 8126)". This differs from the previous error message. This error is not about a general row size limit of 65535 bytes, but a limit imposed by InnoDB tables.

The root cause is that InnoDB has a maximum row size equivalent to half of the innodb_page_size system variable value of 16384 bytes, which is 8192 bytes.

InnoDB mitigates this by storing certain variable-length columns on "overflow pages". The decision regarding which columns are actually stored on overflow pages is made dynamically when adding or changing rows. This is why the error can be raised at runtime and not only when altering the schema. Additionally, it makes accurately predicting whether the error will occur challenging. Furthermore, not all variable-length columns can be stored on overflow pages. This is why the error can be raised when altering table schema.

Variable-length columns of type TEXT and BLOB can always be stored on overflow pages, thus minimally impacting the main data page limit of 8192 bytes. However, VARCHAR columns can only be stored on overflow pages if their maximum length exceeds 255 bytes. Therefore, an unexpected solution to the "Row size too large 8192" error in many cases is to increase the length of some variable-length columns, enabling InnoDB to store them on overflow pages.

Mitigation

TYPO3 Core v13 has modified several default columns to mitigate the issue for instances with many custom columns. The TYPO3 Core maintainers expect this issue to occur infrequently in practice.

Instances encountering the 8192 bytes limit can consider adjusting fields with these considerations in mind:

• The calculation determining if a column can be stored on overflow pages is based on a minimum of 256 bytes, not characters. A typical utf8mb4 VARCHAR(255) equates to 1020 bytes, which can be stored on overflow pages. Changing such fields makes no difference.
• Changing a utf8mb4 VARCHAR(63) (or smaller) to VARCHAR(64) (64 characters utf8mb4 = 256 bytes) allows this column to be stored on overflow pages and does make a difference.
• Changing a utf8mb4 VARCHAR(63) (or smaller) to TINYTEXT should allow this column to be stored on overflow pages as well. However, this may not be the optimal solution due to potential performance penalties, as discussed earlier. Similarly, indiscriminately increasing the length of multiple variable-length columns is not advisable. Columns should ideally be kept as small as possible, only exceeding the 255-byte limit or converting to TEXT types if absolutely necessary. Also, refer to the note on indexes above when single columns are part of indexes.
• Columns using utf8mb4 that are smaller or equal to VARCHAR(63) and only store ASCII characters can be downsized by changing the charset to latin1. For instance, a VARCHAR(60) column occupies 4 * 60 = 240 bytes in row size, but only 60 bytes when using the latin1 charset. Currently, TYPO3 Core does not interpret charset definitions for individual columns from ext_tables.sql. The Core Team anticipates implementing this feature in the future.
• Note that increasing the length of VARCHAR columns can potentially conflict with the 65kB limit mentioned earlier. This is another reason to avoid indiscriminately increasing the length of variable-length columns.