First some background:
MySQL has, at least since 5.7, the default InnoDB row format as DYNAMIC. MySQL and MariaDB should, after 5.7 and 10.2.2, work identically when it comes to InnoDB row format, with one exception described at https://mariadb.com/kb/en/innodb-row-formats-overview/#upgrading-causes-row-size-too-large-errors:
Extract from manual:
Prior to MariaDB 10.2.26, MariaDB 10.3.17, and MariaDB 10.4.7, MariaDB doesn't properly calculate the row sizes while executing DDL. In these versions, unsafe tables can be created, even if InnoDB strict mode is enabled. The calculations were fixed by MDEV-19292 in MariaDB 10.2.26, MariaDB 10.3.17, and MariaDB 10.4.7.
As a side effect, some tables that could be created or altered in previous versions may get rejected with the following error in these releases and any later releases.
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to
|
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
|
End of manual extract.
One can avoid the above problem by setting innodb_strict_mode=OFF
In general most tables are not likely to exceed the max row length for ROW_FORMAT=COMPACT.
There is no reason to change row format for old tables during upgrade (which is a very slow operation for big tables. The reason is that if one did not get ERROR 1118, during INSERT or UPDATE, before upgrading, one is not likely to get it after upgrading even if the default row format has change.
ROW_FORMAT=DYNAMIC allows InnoDB to move more data, in tables containing VARBINARY, VARCHAR, BLOB and TEXT data types, from the primary cluster index page to overflow pages, than ROW_FORMAT=COMPACT. This helps avoiding ERROR 1118. However it is still possible to get ERROR 1118 also with ROW_FORMAT=DYNAMIC in some cases (like with tables with many fixed length fields, including CHAR().
The following link should explain how to solve the issue if one gets ERROR 1118 with MariaDB or MySQL during CREATE, ALTER TABLE, INSERT or UPDATE:
https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/
Note that if one is sure that, during insert or update, the application will never exceed the maximum row size for a specific table, then one can set
SET SESSION innodb_strict_mode=OFF while creating or altering the table.
This will enable one to create tables where it is theoretically possible to exceed the maximum row length if all not-blob fields are using their maximum row length. In this case one will get a warning ,instead of an error, when creating a table that could have a too big maximum row length. InnoDB will instead give an error if one tries to insert a row that exceeds the maximum row length.
Bug
Critical
First some background:
MySQL has, at least since 5.7, the default InnoDB row format as DYNAMIC. MySQL and MariaDB should, after 5.7 and 10.2.2, work identically when it comes to InnoDB row format, with one exception described at https://mariadb.com/kb/en/innodb-row-formats-overview/#upgrading-causes-row-size-too-large-errors:
Extract from manual:
Prior to MariaDB 10.2.26, MariaDB 10.3.17, and MariaDB 10.4.7, MariaDB doesn't properly calculate the row sizes while executing DDL. In these versions, unsafe tables can be created, even if InnoDB strict mode is enabled. The calculations were fixed by
MDEV-19292in MariaDB 10.2.26, MariaDB 10.3.17, and MariaDB 10.4.7.As a side effect, some tables that could be created or altered in previous versions may get rejected with the following error in these releases and any later releases.
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns toTEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.End of manual extract.
One can avoid the above problem by setting innodb_strict_mode=OFF
In general most tables are not likely to exceed the max row length for ROW_FORMAT=COMPACT.
There is no reason to change row format for old tables during upgrade (which is a very slow operation for big tables. The reason is that if one did not get ERROR 1118, during INSERT or UPDATE, before upgrading, one is not likely to get it after upgrading even if the default row format has change.
ROW_FORMAT=DYNAMIC allows InnoDB to move more data, in tables containing VARBINARY, VARCHAR, BLOB and TEXT data types, from the primary cluster index page to overflow pages, than ROW_FORMAT=COMPACT. This helps avoiding ERROR 1118. However it is still possible to get ERROR 1118 also with ROW_FORMAT=DYNAMIC in some cases (like with tables with many fixed length fields, including CHAR().
The following link should explain how to solve the issue if one gets ERROR 1118 with MariaDB or MySQL during CREATE, ALTER TABLE, INSERT or UPDATE:
https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/
Note that if one is sure that, during insert or update, the application will never exceed the maximum row size for a specific table, then one can set
SET SESSION innodb_strict_mode=OFF while creating or altering the table.
This will enable one to create tables where it is theoretically possible to exceed the maximum row length if all not-blob fields are using their maximum row length. In this case one will get a warning ,instead of an error, when creating a table that could have a too big maximum row length. InnoDB will instead give an error if one tries to insert a row that exceeds the maximum row length.