All pages
Powered by GitBook
1 of 7

InnoDB Row Formats

Explore InnoDB row formats in MariaDB Server. Understand different formats like Compact, Dynamic, and Compressed, and how they impact storage efficiency and performance for your data.

InnoDB Row Formats Overview

The InnoDB storage engine supports four different row formats:

Default Row Format

The innodb_default_row_format system variable can be used to set the default row format for InnoDB tables. The possible values are:

  • redundant

  • compact

  • dynamic

This system variable's default value is dynamic, which means that the default row format is DYNAMIC.

This system variable cannot be set to compressed, which means that the default row format cannot be COMPRESSED.

For example, the following statements would create a table with the DYNAMIC row format:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_default_row_format='dynamic';

CREATE TABLE tab (
   id INT,
   str VARCHAR(50)
) ENGINE=InnoDB;

Setting a Table's Row Format

One way to specify an InnoDB table's row format is by setting the ROW_FORMAT table option to the relevant row format in a CREATE TABLE or ALTER TABLE statement:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_file_per_table=ON;

SET GLOBAL innodb_file_format='Barracuda';

CREATE TABLE tab (
   id INT,
   str VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Checking a Table's Row Format

The SHOW TABLE STATUS statement can be used to see the row format used by a table:

SHOW TABLE STATUS FROM db1 WHERE Name='tab'\G
*************************** 1. row ***************************
           Name: tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-04-18 20:24:04
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:

The information_schema.INNODB_SYS_TABLES table can also be queried to see the row format used by a table:

SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE name='db1/tab'\G
*************************** 1. row ***************************
     TABLE_ID: 42
         NAME: db1/tab
         FLAG: 33
       N_COLS: 4
        SPACE: 27
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

A table's tablespace is tagged with the lowest InnoDB file format that supports the table's row format. So, even if the Barracuda file format is enabled, tables that use the COMPACT or REDUNDANT row formats will be tagged with the Antelope file format in the information_schema.INNODB_SYS_TABLES table.

Row Formats

REDUNDANT Row Format

The REDUNDANT row format is the original non-compacted row format.

The REDUNDANT row format was the only available row format before MySQL 5.0.3. In that release, this row format was retroactively named the REDUNDANT row format. In the same release, the COMPACT row format was introduced as the new default row format.

See InnoDB REDUNDANT Row Format for more information.

COMPACT Row Format

Default row format in earlier versions COMPACT.

The COMPACT row format is similar to the REDUNDANT row format, but it stores data in a more compact manner that requires about 20% less storage.

See InnoDB COMPACT Row Format for more information.

DYNAMIC Row Format

DYNAMIC is the default row format.

The DYNAMIC row format is similar to the COMPACT row format, but tables using the DYNAMIC row format can store even more data on overflow pages than tables using the COMPACT row format. This results in more efficient data storage than tables using the COMPACT row format, especially for tables containing columns using the VARBINARY, VARCHAR, BLOB and TEXT data types. However, InnoDB tables using the COMPRESSED row format are more efficient.

See InnoDB DYNAMIC Row Format for more information.

COMPRESSED Row Format

An alternative way to compress InnoDB tables is by using InnoDB Page Compression.

The COMPRESSED row format is similar to the COMPACT row format, but tables using the COMPRESSED row format can store even more data on overflow pages than tables using the COMPACT row format. This results in more efficient data storage than tables using the COMPACT row format, especially for tables containing columns using the VARBINARY, VARCHAR, BLOB and TEXT data types.

The COMPRESSED row format also supports compression of all data and index pages.

See InnoDB COMPRESSED Row Format for more information.

Maximum Row Size

Several factors help determine the maximum row size of an InnoDB table.

First, MariaDB enforces a 65,535 byte limit on a table's maximum row size. The total size of a table's BLOB and TEXT columns do not count towards this limit. Only the pointers for a table's BLOB and TEXT columns count towards this limit. MariaDB enforces this limit for all storage engines, so this limit also applies to InnoDB tables. Therefore, this limit is the absolute maximum row size for an InnoDB table.

If you try to create a table that exceeds MariaDB's global limit on a table's maximum row size, then you will see an error like this:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, 
not counting BLOBs, is 65535. This includes storage overhead, check the manual. You 
have to change some columns to TEXT or BLOBs

However, InnoDB also has its own limits on the maximum row size, so an InnoDB table's maximum row size could be smaller than MariaDB's global limit.

Second, the maximum amount of data that an InnoDB table can store in a row's main data page depends on the value of the innodb_page_size system variable. At most, the data that a single row can consume on the row's main data page is half of the value of the innodb_page_size system variable. With the default value of 16k, that would mean that a single row can consume at most around 8 KB on the row's main data page. However, the limit on the row's main data page is not the absolute limit on the row's size.

Third, all InnoDB row formats can store certain kinds of data in overflow pages, so the maximum row size of an InnoDB table can be larger than the maximum amount of data that can be stored in the row's main data page.

Some row formats can store more data in overflow pages than others. For example, the DYNAMIC and COMPRESSED row formats can store the most data in overflow pages. To see how to determine the how the various InnoDB row formats can use overflow pages, see the following sections:

If a table's definition can allow rows that the table's InnoDB row format can't actually store, then InnoDB will raise errors or warnings in certain scenarios.

If the table were using the REDUNDANT or COMPACT row formats, then the error or warning would be the following:

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to 
TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED 
may help. In current row format, BLOB prefix of 768 bytes is stored inline.

And if the table were using the DYNAMIC or COMPRESSED row formats, then the error or warning would be the following:

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.

These messages are raised in the following cases:

  • If InnoDB strict mode is enabled and if a DDL statement is executed that touches the table, such as CREATE TABLE or ALTER TABLE, then InnoDB will raise an error with this message

  • If InnoDB strict mode is disabled and if a DDL statement is executed that touches the table, such as CREATE TABLEor [ALTER TABLE](../../../sql-statements-and-structure/sql-statements/data-definition/alter/alter-table.md), then InnoDB will raise a warning with this message.

  • Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table's InnoDB row format can't store, then InnoDB will raise an error with this message.

For information on how to solve the problem, see Troubleshooting Row Size Too Large Errors with InnoDB.

Feature Summary

Feature

Dynamic

Compressed

Compact

Redundant

Default

Yes

No

No

No

Recommended

Yes

No

No

No

Efficiently stores large columns

Yes

Yes

No

No

Efficiently utilizes buffer pool

Yes

No

Yes

Yes

Supported Page Sizes

• 64k • 32k • 16k • 8k • 4k

• 16k • 8k • 4k

• 64k • 32k • 16k • 8k • 4k

• 64k • 32k • 16k • 8k • 4k

Maximum size of indexed column values

• 3072 bytes (innodb_page_size >= 16k) • 1536 bytes (innodb_page_size == 8k) • 768 bytes (innodb_page_size == 4k)

• 3072 bytes (innodb_page_size >= 16k) • 1536 bytes (innodb_page_size == 8k) • 768 bytes (innodb_page_size == 4k)

767 bytes

767 bytes

Supports ADD/DROP column with INSTANT Algorithm

Yes

No

Yes

Yes

Known Issues

Upgrading Causes Row Size Too Large Errors

In earlier versions, 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.

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.

And users could also see the following message as an error or warning in the error log:

[Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, the row size is 8478 which is greater than maximum allowed size (8126) for a record on index leaf page.

InnoDB used the wrong calculations to determine row sizes for quite a long time, so a lot of users may unknowingly have unsafe tables that the InnoDB row format can't actually store.

InnoDB does not currently have an easy way to check which existing tables have this problem. See MDEV-20400 for more information.

For information on how to solve the problem, see Troubleshooting Row Size Too Large Errors with InnoDB.

This page is licensed: CC BY-SA / Gnu FDL

InnoDB COMPACT Row Format

NoteCOMPACT was the default row format in prior versions of MariaDB. MariaDB has since transitioned to DYNAMIC as the default row format.

The COMPACT row format is similar to the REDUNDANT row format, but it stores data in a more compact manner that requires about 20% less storage.

Using the COMPACT Row Format

  • If you performed a physical upgrade from older version of MariaDB Server or MySQL to a newer MariaDB Server version, then some of your tables may still use COMPACT row format.

  • Compact row format does not store large columns as efficiently as the Dynamic row format.

  • Compact row format limits indexing column values to 767 bytes, which is significant smaller than the Dynamic row format.

The easiest way to create an InnoDB table that uses the COMPACT row format is by setting the ROW_FORMAT table option to COMPACT in a CREATE TABLE or ALTER TABLE statement.

It is recommended to set the innodb_strict_mode system variable to ON when using this row format.

The COMPACT row format is supported by both the Antelope and the Barracuda file formats, so tables with this row format can be created regardless of the value of the innodb_file_format system variable.

For example:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE tab (
   id INT,
   str VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

Index Prefixes with the COMPACT Row Format

The COMPACT row format supports index prefixes up to 767 bytes.

Overflow Pages with the COMPACT Row Format

All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See Maximum Row Size for more information about the other factors that can contribute to the maximum row size for InnoDB tables.

In the COMPACT row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, can be partially stored in overflow pages.

InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.

For VARBINARY, VARCHAR, BLOB and TEXT columns, only values longer than 767 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards this limit. This limit is only based on the length of the actual column's data.

Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.

If a column is chosen to be stored on overflow pages, then the first 767 bytes of the column's value and a 20-byte pointer to the column's first overflow page are stored on the main page. Each overflow page is the size of innodb_page_size. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.

This page is licensed: CC BY-SA / Gnu FDL

InnoDB COMPRESSED Row Format

An alternative (and usually superior) way to compress InnoDB tables is by using InnoDB Page Compression. See Comparison with the COMPRESSED Row Format.

The COMPRESSED row format is similar to the COMPACT row format, but tables using the COMPRESSED row format can store even more data on overflow pages than tables using the COMPACT row format. This results in more efficient data storage than tables using the COMPACT row format, especially for tables containing columns using the VARBINARY, VARCHAR, BLOB and TEXT data types.

The COMPRESSED row format also supports compression of all data and index pages.

Supported Block Sizes

The Compressed row format supports the following block sizes:

Block Size
KEY_BLOCK_SIZE Value

1 KB

1

2 KB

2

4 KB

4

8 KB

8

16 KB

16

If the KEY_BLOCK_SIZE table option is not specified, the block size defaults to half of innodb_page_size. With the default 16 KB page size, the block size defaults to 8 KB.

For compression to be effective, the chosen block size should be smaller than the servers value of the innodb_page_size system variable.

Supported Index Prefix Limits

The limit for indexing column values depends on the innodb_page_size value:

Page Size
Index Prefix Limit

16k

3072 bytes

8k

1536 bytes

4k

768 bytes

Using the COMPRESSED Row Format

The Compressed row format does not efficiently use the InnoDB buffer pool, so it is not the recommended way to achieve InnoDB table compression. For more information about how to compress InnoDB tables, see Configure InnoDB Page Compression.

An InnoDB table that uses the COMPRESSED row format can be created by setting the ROW_FORMAT table option to COMPRESSED and by setting the KEY_BLOCK_SIZE table option to one of the following values in a CREATE TABLE or ALTER TABLE statement, where the units are in KB.

16k is the default value of the innodb_page_size system variable, so using 16 will usually result in minimal compression unless one of the following is true:

  • The table has many columns that can be stored in overflow pages, such as columns that use the VARBINARY, VARCHAR, BLOB and TEXT data types.

  • The server is using a non-default innodb_page_size value that is greater than 16k.

The value of the innodb_page_size system variable can be set to 32k and 64k. This is especially useful because the larger page size permits more columns using the VARBINARY, VARCHAR, BLOB and TEXT data types. Regardless, even when the value of the innodb_page_size system variable is set to some value higher than 16k, 16 is still the maximum value for the KEY_BLOCK_SIZE table option for InnoDB tables using the COMPRESSED row format.

The COMPRESSED row format cannot be set as the default row format with the innodb_default_row_format system variable.

The COMPRESSED row format is only supported by the Barracuda file format. In earlier versions, the COMPRESSED row format is only supported if the InnoDB file format is Barracuda. Therefore, the innodb_file_format system variable must be set to Barracuda to use these row formats in those versions.

In earlier versions, the COMPRESSED row format is also only supported if the table is in a file per-table tablespace. Therefore, the innodb_file_per_table system variable must be set to ON to use this row format in those versions.

It is also recommended to set the innodb_strict_mode system variable to ON when using this row format.

InnoDB automatically uses the COMPRESSED row format for a table if the KEY_BLOCK_SIZE table option is set to some value in a CREATE TABLE or ALTER TABLE statement:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_file_per_table=ON;

SET GLOBAL innodb_file_format='Barracuda';

CREATE TABLE tab (
   id INT,
   str VARCHAR(50)
) ENGINE=InnoDB KEY_BLOCK_SIZE=4;

If the KEY_BLOCK_SIZE table option is not set to some value, but the ROW_FORMAT table option is set to COMPRESSED in a CREATE TABLE or ALTER TABLE statement, then InnoDB uses a default value of 8 for the KEY_BLOCK_SIZE table option:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_file_per_table=ON;

SET GLOBAL innodb_file_format='Barracuda';

CREATE TABLE tab (
   id INT,
   str VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

Create an InnoDB Table with the Compressed Row Format using ROW_FORMAT

The default block size for tables that use the Compressed row format is half of innodb_page_size. With the default 16 KB page size, the block size defaults to 8 KB. A compressed table with the default block size can be created by setting the ROW_FORMAT table option to COMPRESSED:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ROW_FORMAT = Compressed;

Step-by-Step Procedure

  1. Connect to the server using MariaDB Client:

$ mariadb --user=root

  1. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

  1. Create the table using the CREATE TABLE statement, and specify the Compressed row format using the ROW_FORMAT table option:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ROW_FORMAT = Compressed;

  1. Confirm that the table uses the Compressed row format with an 8 KB block size by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT, ZIP_PAGE_SIZE
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+------------+---------------+
| NAME              | ROW_FORMAT | ZIP_PAGE_SIZE |
+-------------------+------------+---------------+
| hq_sales/invoices | Compressed |          8192 |
+-------------------+------------+---------------+

Create an InnoDB Table with the Compressed Row Format using KEY_BLOCK_SIZE

The default block size for tables that use the Compressed row format is half of innodb_page_size. With the default 16 KB page size, the block size defaults to 8 KB. A compressed table with a non-default block size can be created by setting the KEY_BLOCK_SIZE table option to the desired block size.

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) KEY_BLOCK_SIZE = 4;

Step-by-Step Procedure

  1. Connect to the server using MariaDB Client:

$ mariadb --user=root

  1. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

  1. Create the table using the CREATE TABLE statement, and specify the block size using the KEY_BLOCK_SIZE table option, which implies the Compressed row format:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) KEY_BLOCK_SIZE = 4;

  1. Confirm that the table uses the Compressed row format with an 8 KB block size by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT, ZIP_PAGE_SIZE
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+------------+---------------+
| NAME              | ROW_FORMAT | ZIP_PAGE_SIZE |
+-------------------+------------+---------------+
| hq_sales/invoices | Compressed |          4096 |
+-------------------+------------+---------------+

Compression with the COMPRESSED Row Format

The COMPRESSED row format supports compression of all data and index pages.

To avoid compressing and uncompressing pages too many times, InnoDB tries to keep both compressed and uncompressed pages in the buffer pool when there is enough room. This results in a bigger cache. When there is not enough room, an adaptive LRU algorithm is used to decide whether compressed or uncompressed pages should be evicted from the buffer: for CPU-bound workloads, the compressed pages are evicted first; for I/O-bound workloads, the uncompressed pages are evicted first. Of course, when necessary, both the compressed and uncompressed version of the same data can be evicted from the buffer.

Each compressed page has an uncompressed modification log, stored within the page itself. InnoDB writes small changes into it. When the space in the modification log runs out, the page is uncompressed, changes are applied, and the page is recompressed again. This is done to avoid some unnecessary decompression and compression operations.

Sometimes a compression failure might happen, because the data has grown too much to fit the page. When this happens, the page (and the index node) is split into two different pages. This process can be repeated recursively until the data fit the pages. This can be CPU-consuming on some busy servers which perform many write operations.

Before writing a compressed page into a data file, InnoDB writes it into the redo log. This ensures that the redo log can always be used to recover tables after a crash, even if the compression library is updated and some incompatibilities are introduced. But this also means that the redo log will grow faster and might need more space, or the frequency of checkpoints might need to increase.

Monitoring Performance of the COMPRESSED Row Format

The following INFORMATION_SCHEMA tables can be used to monitor the performances of InnoDB compressed tables:

Index Prefixes with the COMPRESSED Row Format

The COMPRESSED row format supports index prefixes up to 3072 bytes. In MariaDB 10.2 and before, the innodb_large_prefix system variable is used to configure the maximum index prefix length. In these versions, if innodb_large_prefix is set to ON, then the maximum prefix length is 3072 bytes, and if it is set to OFF, then the maximum prefix length is 767 bytes.

Overflow Pages with the COMPRESSED Row Format

All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See Maximum Row Size for more information about the other factors that can contribute to the maximum row size for InnoDB tables.

In the COMPRESSED row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, can be completely stored in overflow pages.

InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.

For BLOB and TEXT columns, only values longer than 40 bytes are considered for storage on overflow pages. For VARBINARY and VARCHAR columns, only values longer than 255 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards these limits. These limits are only based on the length of the actual column's data.

These limits differ from the limits for the COMPACT row format, where the limit is 767 bytes for all types.

Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.

If a column is chosen to be stored on overflow pages, then the entire value of the column is stored on overflow pages, and only a 20-byte pointer to the column's first overflow page is stored on the main page. Each overflow page is the size of innodb_page_size. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.

This behavior differs from the behavior of the COMPACT row format, which always stores the column prefix on the main page. This allows tables using the COMPRESSED row format to contain a high number of columns using the VARBINARY, VARCHAR, BLOB and TEXT data types.

Read-Only

MariaDB starting with 10.6

From MariaDB 10.6.0 until MariaDB 10.6.5, tables that are of the COMPRESSED row format are read-only by default. This was intended to be the first step towards removing write support and deprecating the feature. This plan has been scrapped, and from MariaDB 10.6.6, COMPRESSED tables are no longer read-only by default. From MariaDB 10.6.0 to MariaDB 10.6.5, set the innodb_read_only_compressed variable to OFF to make the tables writable.

See Also

This page is licensed: CC BY-SA / Gnu FDL

InnoDB DYNAMIC Row Format

DYNAMIC is the default InnoDB row format.

The DYNAMIC row format is similar to the COMPACT row format, but tables using the DYNAMIC row format can store even more data on overflow pages than tables using the COMPACT row format. This results in more efficient data storage than tables using the COMPACT row format, especially for tables containing columns using the VARBINARY, VARCHAR, BLOB and TEXT data types. While InnoDB tables using the COMPRESSED row format can result in even greater space-efficiency, COMPRESSED requires substantially more memory and CPU to both read and write, so there is a significant performance and concurrency trade-off for that space-efficiency gain. COMPRESSED tables are not recommended for production use in most situations, while DYNAMIC row format scales well in high-performance environments.

Supported Index Prefix Limits

The limit for indexing column values depends on the innodb_page_size value:

Page Size
Index Prefix Limit

16k 32k 16k

3072 bytes

8k

1536 bytes

4k

768 bytes

Using the DYNAMIC Row Format

The default row format is DYNAMIC, as long as the innodb_default_row_format system variable has not been modified. Therefore, in these versions, the easiest way to create an InnoDB table that uses the DYNAMIC row format is by not setting the ROW_FORMAT table option at all in a CREATE TABLE or ALTER TABLE statement.

It is recommended to set the innodb_strict_mode system variable to ON when using this row format.

For example:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_default_row_format='dynamic';

CREATE TABLE tab (
   id INT,
   str VARCHAR(50)
) ENGINE=InnoDB;

Create an InnoDB Table with the Dynamic Row Format by Default

InnoDB uses the Dynamic row format for new InnoDB tables by default, because the innodb_default_row_format system variable is dynamic by default.

Let's create an InnoDB table after confirming that the default storage engine is InnoDB and that InnoDB's default row format is Dynamic:

  1. Connect to the server using MariaDB Client:

$ mariadb --user=root

  1. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

  1. Confirm that InnoDB's default row format is Dynamic by checking the innodb_default_row_format system variable using the SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES
   LIKE 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+

  1. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

CREATE DATABASE hq_sales;

  1. Create the table using the CREATE TABLE statement:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
);

  1. Confirm that the table uses the Dynamic row format by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+------------+
| NAME              | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Dynamic    |
+-------------------+------------+

Create an InnoDB Table with the Dynamic Row Format using ROW_FORMAT

An InnoDB table that uses the Dynamic row format can be created using the ROW_FORMAT table option.

Let's create an InnoDB table after confirming that the default storage engine is InnoDB and that InnoDB's default row format is not Dynamic:

  1. Connect to the server using MariaDB Client:

$ mariadb --user=root

  1. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

  1. Confirm that InnoDB's default row format is not Dynamic by checking the innodb_default_row_format system variable using the SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES
   LIKE 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | compact |
+---------------------------+---------+

  1. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

CREATE DATABASE hq_sales;

  1. Create the table using the CREATE TABLE statement, and specify the Dynamic row format using the ROW_FORMAT table option:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ROW_FORMAT = Dynamic;

  1. Confirm that the table uses the Dynamic row format by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+------------+
| NAME              | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Dynamic    |
+-------------------+------------+

Convert InnoDB Tables to the Dynamic Row Format

If your database was physically upgraded from some older version of MariaDB Server or MySQL, then some of your tables may not be using the Dynamic row format. If you want to get the benefits of the Dynamic row format, then those tables will need to be converted to use it.

Let's convert some InnoDB tables to the Dynamic row format:

  1. Connect to the server using MariaDB Client:

$ mariadb --user=root

  1. Search for InnoDB tables that do not use the Dynamic row format by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME NOT LIKE 'SYS_%'
AND ROW_FORMAT != 'Dynamic';
+-------------------+------------+
| NAME              | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Compact    |
+-------------------+------------+

  1. Alter the table using the ALTER TABLE statement, and specify the Dynamic row format using the ROW_FORMAT table option:

ALTER TABLE hq_sales.invoices
   ROW_FORMAT = Dynamic;

  1. Confirm that the table uses the Dynamic row format by querying the information_schema.INNODB_SYS_TABLES table again:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+------------+
| NAME              | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Dynamic    |
+-------------------+------------+

Index Prefixes with the DYNAMIC Row Format

The DYNAMIC row format supports index prefixes up to 3072 bytes. In earlier versions of MariaDB, the innodb_large_prefix system variable is used to configure the maximum index prefix length. In these versions, if innodb_large_prefix is set to ON, then the maximum prefix length is 3072 bytes, and if it is set to OFF, then the maximum prefix length is 767 bytes.

Overflow Pages with the DYNAMIC Row Format

All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See Maximum Row Size for more information about the other factors that can contribute to the maximum row size for InnoDB tables.

In the DYNAMIC row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, can be completely stored in overflow pages.

InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.

For BLOB and TEXT columns, only values longer than 40 bytes are considered for storage on overflow pages. For VARBINARY and VARCHAR columns, only values longer than 255 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards these limits. These limits are only based on the length of the actual column's data.

These limits differ from the limits for the COMPACT row format, where the limit is 767 bytes for all types.

Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.

If a column is chosen to be stored on overflow pages, then the entire value of the column is stored on overflow pages, and only a 20-byte pointer to the column's first overflow page is stored on the main page. Each overflow page is the size of innodb_page_size. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.

This behavior differs from the behavior of the COMPACT row format, which always stores the column prefix on the main page. This allows tables using the DYNAMIC row format to contain a high number of columns using the VARBINARY, VARCHAR, BLOB and TEXT data types.

This page is licensed: CC BY-SA / Gnu FDL

InnoDB REDUNDANT Row Format

The REDUNDANT row format is the original non-compacted row format.

The REDUNDANT row format was the only available row format before MySQL 5.0.3. In that release, this row format was retroactively named the REDUNDANT row format. In the same release, the COMPACT row format was introduced as the new default row format.

Using the REDUNDANT Row Format

  • Redundant row format should not be used in modern versions of MariaDB Server.

  • Redundant row format does not store large columns as efficiently as the Dynamic row format.

  • Redundant row format limits indexing column values to 767 bytes, which is significant smaller than the Dynamic row format.

The easiest way to create an InnoDB table that uses the REDUNDANT row format is by setting the ROW_FORMAT table option to REDUNDANT in a CREATE TABLE or ALTER TABLE statement.

It is recommended to set the innodb_strict_mode system variable to ON when using this format.

The REDUNDANT row format is supported by both the Antelope and the Barracuda file formats, so tables with this row format can be created regardless of the value of the innodb_file_format system variable.

For example:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE tab (
   id INT,
   str VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;

Index Prefixes with the REDUNDANT Row Format

The REDUNDANT row format supports index prefixes up to 767 bytes.

Overflow Pages with the REDUNDANT Row Format

All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See Maximum Row Size for more information about the other factors that can contribute to the maximum row size for InnoDB tables.

In the REDUNDANT row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, can be partially stored in overflow pages.

InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.

For VARBINARY, VARCHAR, BLOB and TEXT columns, only values longer than 767 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards this limit. This limit is only based on the length of the actual column's data.

Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.

If a column is chosen to be stored on overflow pages, then the first 767 bytes of the column's value and a 20-byte pointer to the column's first overflow page are stored on the main page. Each overflow page is the size of [innodb-system-variables#innodb_page_size|innodb_page_size]]. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.

This page is licensed: CC BY-SA / Gnu FDL

Troubleshooting Row Size Too Large Errors with InnoDB

With InnoDB, users can see the following message as an error or warning:

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.

And they can also see the following message as an error or warning in the error log:

[Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, 
the row size is 8478 which is greater than maximum allowed size (8126) for a 
record on index leaf page.

These messages indicate that the table's definition allows rows that the table's InnoDB row format can't actually store.

These messages are raised in the following cases:

  • If InnoDB strict mode is enabled and if a DDL statement is executed that touches the table, such as CREATE TABLE or ALTER TABLE, then InnoDB will raise an error with this message

  • If InnoDB strict mode is disabled and if a DDL statement is executed that touches the table, such as CREATE TABLE or ALTER TABLE, then InnoDB will raise a warning with this message.

  • Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table's InnoDB row format can't store, then InnoDB will raise an error with this message.

Example of the Problem

Here is an example of the problem:

SET GLOBAL innodb_default_row_format='dynamic';

SET SESSION innodb_strict_mode=ON;

CREATE OR REPLACE TABLE tab (
   col1 VARCHAR(40) NOT NULL,
   col2 VARCHAR(40) NOT NULL,
   col3 VARCHAR(40) NOT NULL,
   col4 VARCHAR(40) NOT NULL,
   col5 VARCHAR(40) NOT NULL,
   col6 VARCHAR(40) NOT NULL,
   col7 VARCHAR(40) NOT NULL,
   col8 VARCHAR(40) NOT NULL,
   col9 VARCHAR(40) NOT NULL,
   col10 VARCHAR(40) NOT NULL,
   col11 VARCHAR(40) NOT NULL,
   col12 VARCHAR(40) NOT NULL,
   col13 VARCHAR(40) NOT NULL,
   col14 VARCHAR(40) NOT NULL,
   col15 VARCHAR(40) NOT NULL,
   col16 VARCHAR(40) NOT NULL,
   col17 VARCHAR(40) NOT NULL,
   col18 VARCHAR(40) NOT NULL,
   col19 VARCHAR(40) NOT NULL,
   col20 VARCHAR(40) NOT NULL,
   col21 VARCHAR(40) NOT NULL,
   col22 VARCHAR(40) NOT NULL,
   col23 VARCHAR(40) NOT NULL,
   col24 VARCHAR(40) NOT NULL,
   col25 VARCHAR(40) NOT NULL,
   col26 VARCHAR(40) NOT NULL,
   col27 VARCHAR(40) NOT NULL,
   col28 VARCHAR(40) NOT NULL,
   col29 VARCHAR(40) NOT NULL,
   col30 VARCHAR(40) NOT NULL,
   col31 VARCHAR(40) NOT NULL,
   col32 VARCHAR(40) NOT NULL,
   col33 VARCHAR(40) NOT NULL,
   col34 VARCHAR(40) NOT NULL,
   col35 VARCHAR(40) NOT NULL,
   col36 VARCHAR(40) NOT NULL,
   col37 VARCHAR(40) NOT NULL,
   col38 VARCHAR(40) NOT NULL,
   col39 VARCHAR(40) NOT NULL,
   col40 VARCHAR(40) NOT NULL,
   col41 VARCHAR(40) NOT NULL,
   col42 VARCHAR(40) NOT NULL,
   col43 VARCHAR(40) NOT NULL,
   col44 VARCHAR(40) NOT NULL,
   col45 VARCHAR(40) NOT NULL,
   col46 VARCHAR(40) NOT NULL,
   col47 VARCHAR(40) NOT NULL,
   col48 VARCHAR(40) NOT NULL,
   col49 VARCHAR(40) NOT NULL,
   col50 VARCHAR(40) NOT NULL,
   col51 VARCHAR(40) NOT NULL,
   col52 VARCHAR(40) NOT NULL,
   col53 VARCHAR(40) NOT NULL,
   col54 VARCHAR(40) NOT NULL,
   col55 VARCHAR(40) NOT NULL,
   col56 VARCHAR(40) NOT NULL,
   col57 VARCHAR(40) NOT NULL,
   col58 VARCHAR(40) NOT NULL,
   col59 VARCHAR(40) NOT NULL,
   col60 VARCHAR(40) NOT NULL,
   col61 VARCHAR(40) NOT NULL,
   col62 VARCHAR(40) NOT NULL,
   col63 VARCHAR(40) NOT NULL,
   col64 VARCHAR(40) NOT NULL,
   col65 VARCHAR(40) NOT NULL,
   col66 VARCHAR(40) NOT NULL,
   col67 VARCHAR(40) NOT NULL,
   col68 VARCHAR(40) NOT NULL,
   col69 VARCHAR(40) NOT NULL,
   col70 VARCHAR(40) NOT NULL,
   col71 VARCHAR(40) NOT NULL,
   col72 VARCHAR(40) NOT NULL,
   col73 VARCHAR(40) NOT NULL,
   col74 VARCHAR(40) NOT NULL,
   col75 VARCHAR(40) NOT NULL,
   col76 VARCHAR(40) NOT NULL,
   col77 VARCHAR(40) NOT NULL,
   col78 VARCHAR(40) NOT NULL,
   col79 VARCHAR(40) NOT NULL,
   col80 VARCHAR(40) NOT NULL,
   col81 VARCHAR(40) NOT NULL,
   col82 VARCHAR(40) NOT NULL,
   col83 VARCHAR(40) NOT NULL,
   col84 VARCHAR(40) NOT NULL,
   col85 VARCHAR(40) NOT NULL,
   col86 VARCHAR(40) NOT NULL,
   col87 VARCHAR(40) NOT NULL,
   col88 VARCHAR(40) NOT NULL,
   col89 VARCHAR(40) NOT NULL,
   col90 VARCHAR(40) NOT NULL,
   col91 VARCHAR(40) NOT NULL,
   col92 VARCHAR(40) NOT NULL,
   col93 VARCHAR(40) NOT NULL,
   col94 VARCHAR(40) NOT NULL,
   col95 VARCHAR(40) NOT NULL,
   col96 VARCHAR(40) NOT NULL,
   col97 VARCHAR(40) NOT NULL,
   col98 VARCHAR(40) NOT NULL,
   col99 VARCHAR(40) NOT NULL,
   col100 VARCHAR(40) NOT NULL,
   col101 VARCHAR(40) NOT NULL,
   col102 VARCHAR(40) NOT NULL,
   col103 VARCHAR(40) NOT NULL,
   col104 VARCHAR(40) NOT NULL,
   col105 VARCHAR(40) NOT NULL,
   col106 VARCHAR(40) NOT NULL,
   col107 VARCHAR(40) NOT NULL,
   col108 VARCHAR(40) NOT NULL,
   col109 VARCHAR(40) NOT NULL,
   col110 VARCHAR(40) NOT NULL,
   col111 VARCHAR(40) NOT NULL,
   col112 VARCHAR(40) NOT NULL,
   col113 VARCHAR(40) NOT NULL,
   col114 VARCHAR(40) NOT NULL,
   col115 VARCHAR(40) NOT NULL,
   col116 VARCHAR(40) NOT NULL,
   col117 VARCHAR(40) NOT NULL,
   col118 VARCHAR(40) NOT NULL,
   col119 VARCHAR(40) NOT NULL,
   col120 VARCHAR(40) NOT NULL,
   col121 VARCHAR(40) NOT NULL,
   col122 VARCHAR(40) NOT NULL,
   col123 VARCHAR(40) NOT NULL,
   col124 VARCHAR(40) NOT NULL,
   col125 VARCHAR(40) NOT NULL,
   col126 VARCHAR(40) NOT NULL,
   col127 VARCHAR(40) NOT NULL,
   col128 VARCHAR(40) NOT NULL,
   col129 VARCHAR(40) NOT NULL,
   col130 VARCHAR(40) NOT NULL,
   col131 VARCHAR(40) NOT NULL,
   col132 VARCHAR(40) NOT NULL,
   col133 VARCHAR(40) NOT NULL,
   col134 VARCHAR(40) NOT NULL,
   col135 VARCHAR(40) NOT NULL,
   col136 VARCHAR(40) NOT NULL,
   col137 VARCHAR(40) NOT NULL,
   col138 VARCHAR(40) NOT NULL,
   col139 VARCHAR(40) NOT NULL,
   col140 VARCHAR(40) NOT NULL,
   col141 VARCHAR(40) NOT NULL,
   col142 VARCHAR(40) NOT NULL,
   col143 VARCHAR(40) NOT NULL,
   col144 VARCHAR(40) NOT NULL,
   col145 VARCHAR(40) NOT NULL,
   col146 VARCHAR(40) NOT NULL,
   col147 VARCHAR(40) NOT NULL,
   col148 VARCHAR(40) NOT NULL,
   col149 VARCHAR(40) NOT NULL,
   col150 VARCHAR(40) NOT NULL,
   col151 VARCHAR(40) NOT NULL,
   col152 VARCHAR(40) NOT NULL,
   col153 VARCHAR(40) NOT NULL,
   col154 VARCHAR(40) NOT NULL,
   col155 VARCHAR(40) NOT NULL,
   col156 VARCHAR(40) NOT NULL,
   col157 VARCHAR(40) NOT NULL,
   col158 VARCHAR(40) NOT NULL,
   col159 VARCHAR(40) NOT NULL,
   col160 VARCHAR(40) NOT NULL,
   col161 VARCHAR(40) NOT NULL,
   col162 VARCHAR(40) NOT NULL,
   col163 VARCHAR(40) NOT NULL,
   col164 VARCHAR(40) NOT NULL,
   col165 VARCHAR(40) NOT NULL,
   col166 VARCHAR(40) NOT NULL,
   col167 VARCHAR(40) NOT NULL,
   col168 VARCHAR(40) NOT NULL,
   col169 VARCHAR(40) NOT NULL,
   col170 VARCHAR(40) NOT NULL,
   col171 VARCHAR(40) NOT NULL,
   col172 VARCHAR(40) NOT NULL,
   col173 VARCHAR(40) NOT NULL,
   col174 VARCHAR(40) NOT NULL,
   col175 VARCHAR(40) NOT NULL,
   col176 VARCHAR(40) NOT NULL,
   col177 VARCHAR(40) NOT NULL,
   col178 VARCHAR(40) NOT NULL,
   col179 VARCHAR(40) NOT NULL,
   col180 VARCHAR(40) NOT NULL,
   col181 VARCHAR(40) NOT NULL,
   col182 VARCHAR(40) NOT NULL,
   col183 VARCHAR(40) NOT NULL,
   col184 VARCHAR(40) NOT NULL,
   col185 VARCHAR(40) NOT NULL,
   col186 VARCHAR(40) NOT NULL,
   col187 VARCHAR(40) NOT NULL,
   col188 VARCHAR(40) NOT NULL,
   col189 VARCHAR(40) NOT NULL,
   col190 VARCHAR(40) NOT NULL,
   col191 VARCHAR(40) NOT NULL,
   col192 VARCHAR(40) NOT NULL,
   col193 VARCHAR(40) NOT NULL,
   col194 VARCHAR(40) NOT NULL,
   col195 VARCHAR(40) NOT NULL,
   col196 VARCHAR(40) NOT NULL,
   col197 VARCHAR(40) NOT NULL,
   col198 VARCHAR(40) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
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.

Root Cause of the Problem

The root cause is that InnoDB has a maximum row size that is roughly equivalent to half of the value of the innodb_page_size system variable. See InnoDB Row Formats Overview: Maximum Row Size for more information.

InnoDB's row formats work around this limit by storing certain kinds of variable-length columns on overflow pages. However, different row formats can store different types of data on overflow pages. Some row formats can store more data in overflow pages than others. For example, the DYNAMIC and COMPRESSED row formats can store the most data in overflow pages. To learn how the various InnoDB row formats use overflow pages, see the following pages:

Checking Existing Tables for the Problem

InnoDB does not currently have an easy way to check all existing tables to determine which tables have this problem. See MDEV-20400 for more information.

One method to check a single existing table for this problem is to enable InnoDB strict mode, and then try to create a duplicate of the table with CREATE TABLE ... LIKE. If the table has this problem, then the operation will fail:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE tab_dup LIKE tab;
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.

Finding All Tables That Currently Have the Problem

The following shell script will read through a MariaDB server to identify every table that has a row size definition that is too large for its row format and the server's page size. It runs on most common distributions of Linux.

To run the script, copy the code below to a shell-script named rowsize.sh, make it executable with the command chmod 755 ./rowsize.sh, and invoke it with the following parameters:

./rowsize.sh host user password

When the script runs, it displays the name of the temporary database it creates, so that if the script is interrupted before cleaning up, the database can be easily identified and removed manually.

As the script runs it will output one line reporting the database and tablename for each table it finds that has the oversize row problem. If it finds none, it will print the following message: "No tables with rows size too big found."

In either case, the script prints one final line to announce when it's done: ./rowsize.sh done.

#!/bin/bash

[ -z "$3" ] && echo "Usage: $0 host user password" >&2 && exit 1

dt="tmp_$RANDOM$RANDOM"

mysql -h $1 -u $2 -p$3 -ABNe "create database $dt;"
[ $? -ne 0 ] && echo "Error: $0 terminating" >&2 exit 1

echo
echo "Created temporary database ${dt} on host $1"
echo

c=0
for d in $(mysql -h $1 -u $2 -p$3 -ABNe "show databases;" | egrep -iv "information_schema|mysql|performance_schema|$dt")
do
	for t in $(mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d)
	do
		tc=$(mysql -h $1 -u $2 -p$3 -ABNe "show create table $t\\G" $d | egrep -iv "^\*|^$t")
		
		echo $tc | grep -iq "ROW_FORMAT"
		if [ $? -ne 0 ]
		then
			tf=$(mysql -h $1 -u $2 -p$3 -ABNe "select row_format from information_schema.innodb_sys_tables where name = '${d}/${t}';")
			tc="$tc ROW_FORMAT=$tf"
		fi
		
		ef="/tmp/e$RANDOM$RANDOM"
		mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; set foreign_key_checks=0; ${tc};" $dt >/dev/null  2>$ef
		[ $? -ne 0 ] && cat $ef | grep -q "Row size too large" && echo "${d}.${t}" && let c++ || mysql -h $1 -u $2 -p$3 -ABNe "drop table if exists ${t};" $dt
		rm -f $ef
	done
done
mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; drop database $dt;"
[ $c -eq 0 ] && echo "No tables with rows size too large found." || echo && echo "$c tables found with row size too large."
echo
echo "$0 done."

Solving the Problem

There are several potential solutions available to solve this problem.

Converting the Table to the DYNAMIC Row Format

If the table is using either the REDUNDANT or the COMPACT row format, then one potential solution to this problem is to convert the table to use the DYNAMIC row format instead.

If your tables were originally created on an older version of MariaDB or MySQL, then your table may be using one of InnoDB's older row formats:

  • In MariaDB 10.1 and before, and in MySQL 5.6 and before, the COMPACT row format was the default row format.

  • In MySQL 4.1 and before, the REDUNDANT row format was the default row format.

The DYNAMIC row format can store more data on overflow pages than these older row formats, so this row format may actually be able to store the table's data safely. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

Therefore, a potential solution to the Row size too large error is to convert the table to use the DYNAMIC row format:

ALTER TABLE tab ROW_FORMAT=DYNAMIC;

You can use the INNODB_SYS_TABLES table in the information_schema database to find all tables that use the REDUNDANT or the COMPACT row formats. This is helpful if you would like to convert all of your tables that you still use the older row formats to the DYNAMIC row format. For example, the following query can find those tables, while excluding InnoDB's internal system tables:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE ROW_FORMAT IN('Redundant', 'Compact')
AND NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');

In MariaDB 10.2 and later, the DYNAMIC row format is the default row format. If your tables were originally created on one of these newer versions, then they may already be using this row format. In that case, you may need to try the next solution.

Fitting More Columns on Overflow Pages

If the table is already using the DYNAMIC row format, then another potential solution to this problem is to change the table schema, so that the row format can store more columns on overflow pages.

In order for InnoDB to store some variable-length columns on overflow pages, the length of those columns may need to be increased.

Therefore, a counter-intuitive solution to the Row size too large error in a lot of cases is actually to increase the length of some variable-length columns, so that InnoDB's row format can store them on overflow pages.

Some possible ways to change the table schema are listed below.

Converting Some Columns to BLOB or TEXT

For BLOB and TEXT columns, the DYNAMIC row format can store these columns on overflow pages. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

Therefore, a potential solution to the Row size too large error is to convert some columns to the BLOB or TEXT data types.

Increasing the Length of VARBINARY Columns

For VARBINARY columns, the DYNAMIC row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

Therefore, a potential solution to the Row size too large error is to ensure that all VARBINARY columns are at least as long as varbinary(256).

Increasing the Length of VARCHAR Columns

For VARCHAR columns, the DYNAMIC row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

The original table schema shown earlier on this page causes the Row size too large error, because all of the table's VARCHAR columns are smaller than 256 bytes, which means that they have to be stored on the row's main data page.

Therefore, a potential solution to the Row size too large error is to ensure that all VARCHAR columns are at least as long as 256 bytes. The number of characters required to reach the 256 byte limit depends on the character set used by the column.

For example, when using InnoDB's DYNAMIC row format and a default character set of latin1 (which requires up to 1 byte per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(256):

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
   col1 VARCHAR(256) NOT NULL,
   col2 VARCHAR(256) NOT NULL,
   col3 VARCHAR(256) NOT NULL,
   col4 VARCHAR(256) NOT NULL,
   col5 VARCHAR(256) NOT NULL,
   col6 VARCHAR(256) NOT NULL,
   col7 VARCHAR(256) NOT NULL,
   col8 VARCHAR(256) NOT NULL,
   col9 VARCHAR(256) NOT NULL,
   col10 VARCHAR(256) NOT NULL,
   col11 VARCHAR(256) NOT NULL,
   col12 VARCHAR(256) NOT NULL,
   col13 VARCHAR(256) NOT NULL,
   col14 VARCHAR(256) NOT NULL,
   col15 VARCHAR(256) NOT NULL,
   col16 VARCHAR(256) NOT NULL,
   col17 VARCHAR(256) NOT NULL,
   col18 VARCHAR(256) NOT NULL,
   col19 VARCHAR(256) NOT NULL,
   col20 VARCHAR(256) NOT NULL,
   col21 VARCHAR(256) NOT NULL,
   col22 VARCHAR(256) NOT NULL,
   col23 VARCHAR(256) NOT NULL,
   col24 VARCHAR(256) NOT NULL,
   col25 VARCHAR(256) NOT NULL,
   col26 VARCHAR(256) NOT NULL,
   col27 VARCHAR(256) NOT NULL,
   col28 VARCHAR(256) NOT NULL,
   col29 VARCHAR(256) NOT NULL,
   col30 VARCHAR(256) NOT NULL,
   col31 VARCHAR(256) NOT NULL,
   col32 VARCHAR(256) NOT NULL,
   col33 VARCHAR(256) NOT NULL,
   col34 VARCHAR(256) NOT NULL,
   col35 VARCHAR(256) NOT NULL,
   col36 VARCHAR(256) NOT NULL,
   col37 VARCHAR(256) NOT NULL,
   col38 VARCHAR(256) NOT NULL,
   col39 VARCHAR(256) NOT NULL,
   col40 VARCHAR(256) NOT NULL,
   col41 VARCHAR(256) NOT NULL,
   col42 VARCHAR(256) NOT NULL,
   col43 VARCHAR(256) NOT NULL,
   col44 VARCHAR(256) NOT NULL,
   col45 VARCHAR(256) NOT NULL,
   col46 VARCHAR(256) NOT NULL,
   col47 VARCHAR(256) NOT NULL,
   col48 VARCHAR(256) NOT NULL,
   col49 VARCHAR(256) NOT NULL,
   col50 VARCHAR(256) NOT NULL,
   col51 VARCHAR(256) NOT NULL,
   col52 VARCHAR(256) NOT NULL,
   col53 VARCHAR(256) NOT NULL,
   col54 VARCHAR(256) NOT NULL,
   col55 VARCHAR(256) NOT NULL,
   col56 VARCHAR(256) NOT NULL,
   col57 VARCHAR(256) NOT NULL,
   col58 VARCHAR(256) NOT NULL,
   col59 VARCHAR(256) NOT NULL,
   col60 VARCHAR(256) NOT NULL,
   col61 VARCHAR(256) NOT NULL,
   col62 VARCHAR(256) NOT NULL,
   col63 VARCHAR(256) NOT NULL,
   col64 VARCHAR(256) NOT NULL,
   col65 VARCHAR(256) NOT NULL,
   col66 VARCHAR(256) NOT NULL,
   col67 VARCHAR(256) NOT NULL,
   col68 VARCHAR(256) NOT NULL,
   col69 VARCHAR(256) NOT NULL,
   col70 VARCHAR(256) NOT NULL,
   col71 VARCHAR(256) NOT NULL,
   col72 VARCHAR(256) NOT NULL,
   col73 VARCHAR(256) NOT NULL,
   col74 VARCHAR(256) NOT NULL,
   col75 VARCHAR(256) NOT NULL,
   col76 VARCHAR(256) NOT NULL,
   col77 VARCHAR(256) NOT NULL,
   col78 VARCHAR(256) NOT NULL,
   col79 VARCHAR(256) NOT NULL,
   col80 VARCHAR(256) NOT NULL,
   col81 VARCHAR(256) NOT NULL,
   col82 VARCHAR(256) NOT NULL,
   col83 VARCHAR(256) NOT NULL,
   col84 VARCHAR(256) NOT NULL,
   col85 VARCHAR(256) NOT NULL,
   col86 VARCHAR(256) NOT NULL,
   col87 VARCHAR(256) NOT NULL,
   col88 VARCHAR(256) NOT NULL,
   col89 VARCHAR(256) NOT NULL,
   col90 VARCHAR(256) NOT NULL,
   col91 VARCHAR(256) NOT NULL,
   col92 VARCHAR(256) NOT NULL,
   col93 VARCHAR(256) NOT NULL,
   col94 VARCHAR(256) NOT NULL,
   col95 VARCHAR(256) NOT NULL,
   col96 VARCHAR(256) NOT NULL,
   col97 VARCHAR(256) NOT NULL,
   col98 VARCHAR(256) NOT NULL,
   col99 VARCHAR(256) NOT NULL,
   col100 VARCHAR(256) NOT NULL,
   col101 VARCHAR(256) NOT NULL,
   col102 VARCHAR(256) NOT NULL,
   col103 VARCHAR(256) NOT NULL,
   col104 VARCHAR(256) NOT NULL,
   col105 VARCHAR(256) NOT NULL,
   col106 VARCHAR(256) NOT NULL,
   col107 VARCHAR(256) NOT NULL,
   col108 VARCHAR(256) NOT NULL,
   col109 VARCHAR(256) NOT NULL,
   col110 VARCHAR(256) NOT NULL,
   col111 VARCHAR(256) NOT NULL,
   col112 VARCHAR(256) NOT NULL,
   col113 VARCHAR(256) NOT NULL,
   col114 VARCHAR(256) NOT NULL,
   col115 VARCHAR(256) NOT NULL,
   col116 VARCHAR(256) NOT NULL,
   col117 VARCHAR(256) NOT NULL,
   col118 VARCHAR(256) NOT NULL,
   col119 VARCHAR(256) NOT NULL,
   col120 VARCHAR(256) NOT NULL,
   col121 VARCHAR(256) NOT NULL,
   col122 VARCHAR(256) NOT NULL,
   col123 VARCHAR(256) NOT NULL,
   col124 VARCHAR(256) NOT NULL,
   col125 VARCHAR(256) NOT NULL,
   col126 VARCHAR(256) NOT NULL,
   col127 VARCHAR(256) NOT NULL,
   col128 VARCHAR(256) NOT NULL,
   col129 VARCHAR(256) NOT NULL,
   col130 VARCHAR(256) NOT NULL,
   col131 VARCHAR(256) NOT NULL,
   col132 VARCHAR(256) NOT NULL,
   col133 VARCHAR(256) NOT NULL,
   col134 VARCHAR(256) NOT NULL,
   col135 VARCHAR(256) NOT NULL,
   col136 VARCHAR(256) NOT NULL,
   col137 VARCHAR(256) NOT NULL,
   col138 VARCHAR(256) NOT NULL,
   col139 VARCHAR(256) NOT NULL,
   col140 VARCHAR(256) NOT NULL,
   col141 VARCHAR(256) NOT NULL,
   col142 VARCHAR(256) NOT NULL,
   col143 VARCHAR(256) NOT NULL,
   col144 VARCHAR(256) NOT NULL,
   col145 VARCHAR(256) NOT NULL,
   col146 VARCHAR(256) NOT NULL,
   col147 VARCHAR(256) NOT NULL,
   col148 VARCHAR(256) NOT NULL,
   col149 VARCHAR(256) NOT NULL,
   col150 VARCHAR(256) NOT NULL,
   col151 VARCHAR(256) NOT NULL,
   col152 VARCHAR(256) NOT NULL,
   col153 VARCHAR(256) NOT NULL,
   col154 VARCHAR(256) NOT NULL,
   col155 VARCHAR(256) NOT NULL,
   col156 VARCHAR(256) NOT NULL,
   col157 VARCHAR(256) NOT NULL,
   col158 VARCHAR(256) NOT NULL,
   col159 VARCHAR(256) NOT NULL,
   col160 VARCHAR(256) NOT NULL,
   col161 VARCHAR(256) NOT NULL,
   col162 VARCHAR(256) NOT NULL,
   col163 VARCHAR(256) NOT NULL,
   col164 VARCHAR(256) NOT NULL,
   col165 VARCHAR(256) NOT NULL,
   col166 VARCHAR(256) NOT NULL,
   col167 VARCHAR(256) NOT NULL,
   col168 VARCHAR(256) NOT NULL,
   col169 VARCHAR(256) NOT NULL,
   col170 VARCHAR(256) NOT NULL,
   col171 VARCHAR(256) NOT NULL,
   col172 VARCHAR(256) NOT NULL,
   col173 VARCHAR(256) NOT NULL,
   col174 VARCHAR(256) NOT NULL,
   col175 VARCHAR(256) NOT NULL,
   col176 VARCHAR(256) NOT NULL,
   col177 VARCHAR(256) NOT NULL,
   col178 VARCHAR(256) NOT NULL,
   col179 VARCHAR(256) NOT NULL,
   col180 VARCHAR(256) NOT NULL,
   col181 VARCHAR(256) NOT NULL,
   col182 VARCHAR(256) NOT NULL,
   col183 VARCHAR(256) NOT NULL,
   col184 VARCHAR(256) NOT NULL,
   col185 VARCHAR(256) NOT NULL,
   col186 VARCHAR(256) NOT NULL,
   col187 VARCHAR(256) NOT NULL,
   col188 VARCHAR(256) NOT NULL,
   col189 VARCHAR(256) NOT NULL,
   col190 VARCHAR(256) NOT NULL,
   col191 VARCHAR(256) NOT NULL,
   col192 VARCHAR(256) NOT NULL,
   col193 VARCHAR(256) NOT NULL,
   col194 VARCHAR(256) NOT NULL,
   col195 VARCHAR(256) NOT NULL,
   col196 VARCHAR(256) NOT NULL,
   col197 VARCHAR(256) NOT NULL,
   col198 VARCHAR(256) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And when using InnoDB's DYNAMIC row format and a default character set of utf8 (which requires up to 3 bytes per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(86):

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
   col1 VARCHAR(86) NOT NULL,
   col2 VARCHAR(86) NOT NULL,
   col3 VARCHAR(86) NOT NULL,
   col4 VARCHAR(86) NOT NULL,
   col5 VARCHAR(86) NOT NULL,
   col6 VARCHAR(86) NOT NULL,
   col7 VARCHAR(86) NOT NULL,
   col8 VARCHAR(86) NOT NULL,
   col9 VARCHAR(86) NOT NULL,
   col10 VARCHAR(86) NOT NULL,
   col11 VARCHAR(86) NOT NULL,
   col12 VARCHAR(86) NOT NULL,
   col13 VARCHAR(86) NOT NULL,
   col14 VARCHAR(86) NOT NULL,
   col15 VARCHAR(86) NOT NULL,
   col16 VARCHAR(86) NOT NULL,
   col17 VARCHAR(86) NOT NULL,
   col18 VARCHAR(86) NOT NULL,
   col19 VARCHAR(86) NOT NULL,
   col20 VARCHAR(86) NOT NULL,
   col21 VARCHAR(86) NOT NULL,
   col22 VARCHAR(86) NOT NULL,
   col23 VARCHAR(86) NOT NULL,
   col24 VARCHAR(86) NOT NULL,
   col25 VARCHAR(86) NOT NULL,
   col26 VARCHAR(86) NOT NULL,
   col27 VARCHAR(86) NOT NULL,
   col28 VARCHAR(86) NOT NULL,
   col29 VARCHAR(86) NOT NULL,
   col30 VARCHAR(86) NOT NULL,
   col31 VARCHAR(86) NOT NULL,
   col32 VARCHAR(86) NOT NULL,
   col33 VARCHAR(86) NOT NULL,
   col34 VARCHAR(86) NOT NULL,
   col35 VARCHAR(86) NOT NULL,
   col36 VARCHAR(86) NOT NULL,
   col37 VARCHAR(86) NOT NULL,
   col38 VARCHAR(86) NOT NULL,
   col39 VARCHAR(86) NOT NULL,
   col40 VARCHAR(86) NOT NULL,
   col41 VARCHAR(86) NOT NULL,
   col42 VARCHAR(86) NOT NULL,
   col43 VARCHAR(86) NOT NULL,
   col44 VARCHAR(86) NOT NULL,
   col45 VARCHAR(86) NOT NULL,
   col46 VARCHAR(86) NOT NULL,
   col47 VARCHAR(86) NOT NULL,
   col48 VARCHAR(86) NOT NULL,
   col49 VARCHAR(86) NOT NULL,
   col50 VARCHAR(86) NOT NULL,
   col51 VARCHAR(86) NOT NULL,
   col52 VARCHAR(86) NOT NULL,
   col53 VARCHAR(86) NOT NULL,
   col54 VARCHAR(86) NOT NULL,
   col55 VARCHAR(86) NOT NULL,
   col56 VARCHAR(86) NOT NULL,
   col57 VARCHAR(86) NOT NULL,
   col58 VARCHAR(86) NOT NULL,
   col59 VARCHAR(86) NOT NULL,
   col60 VARCHAR(86) NOT NULL,
   col61 VARCHAR(86) NOT NULL,
   col62 VARCHAR(86) NOT NULL,
   col63 VARCHAR(86) NOT NULL,
   col64 VARCHAR(86) NOT NULL,
   col65 VARCHAR(86) NOT NULL,
   col66 VARCHAR(86) NOT NULL,
   col67 VARCHAR(86) NOT NULL,
   col68 VARCHAR(86) NOT NULL,
   col69 VARCHAR(86) NOT NULL,
   col70 VARCHAR(86) NOT NULL,
   col71 VARCHAR(86) NOT NULL,
   col72 VARCHAR(86) NOT NULL,
   col73 VARCHAR(86) NOT NULL,
   col74 VARCHAR(86) NOT NULL,
   col75 VARCHAR(86) NOT NULL,
   col76 VARCHAR(86) NOT NULL,
   col77 VARCHAR(86) NOT NULL,
   col78 VARCHAR(86) NOT NULL,
   col79 VARCHAR(86) NOT NULL,
   col80 VARCHAR(86) NOT NULL,
   col81 VARCHAR(86) NOT NULL,
   col82 VARCHAR(86) NOT NULL,
   col83 VARCHAR(86) NOT NULL,
   col84 VARCHAR(86) NOT NULL,
   col85 VARCHAR(86) NOT NULL,
   col86 VARCHAR(86) NOT NULL,
   col87 VARCHAR(86) NOT NULL,
   col88 VARCHAR(86) NOT NULL,
   col89 VARCHAR(86) NOT NULL,
   col90 VARCHAR(86) NOT NULL,
   col91 VARCHAR(86) NOT NULL,
   col92 VARCHAR(86) NOT NULL,
   col93 VARCHAR(86) NOT NULL,
   col94 VARCHAR(86) NOT NULL,
   col95 VARCHAR(86) NOT NULL,
   col96 VARCHAR(86) NOT NULL,
   col97 VARCHAR(86) NOT NULL,
   col98 VARCHAR(86) NOT NULL,
   col99 VARCHAR(86) NOT NULL,
   col100 VARCHAR(86) NOT NULL,
   col101 VARCHAR(86) NOT NULL,
   col102 VARCHAR(86) NOT NULL,
   col103 VARCHAR(86) NOT NULL,
   col104 VARCHAR(86) NOT NULL,
   col105 VARCHAR(86) NOT NULL,
   col106 VARCHAR(86) NOT NULL,
   col107 VARCHAR(86) NOT NULL,
   col108 VARCHAR(86) NOT NULL,
   col109 VARCHAR(86) NOT NULL,
   col110 VARCHAR(86) NOT NULL,
   col111 VARCHAR(86) NOT NULL,
   col112 VARCHAR(86) NOT NULL,
   col113 VARCHAR(86) NOT NULL,
   col114 VARCHAR(86) NOT NULL,
   col115 VARCHAR(86) NOT NULL,
   col116 VARCHAR(86) NOT NULL,
   col117 VARCHAR(86) NOT NULL,
   col118 VARCHAR(86) NOT NULL,
   col119 VARCHAR(86) NOT NULL,
   col120 VARCHAR(86) NOT NULL,
   col121 VARCHAR(86) NOT NULL,
   col122 VARCHAR(86) NOT NULL,
   col123 VARCHAR(86) NOT NULL,
   col124 VARCHAR(86) NOT NULL,
   col125 VARCHAR(86) NOT NULL,
   col126 VARCHAR(86) NOT NULL,
   col127 VARCHAR(86) NOT NULL,
   col128 VARCHAR(86) NOT NULL,
   col129 VARCHAR(86) NOT NULL,
   col130 VARCHAR(86) NOT NULL,
   col131 VARCHAR(86) NOT NULL,
   col132 VARCHAR(86) NOT NULL,
   col133 VARCHAR(86) NOT NULL,
   col134 VARCHAR(86) NOT NULL,
   col135 VARCHAR(86) NOT NULL,
   col136 VARCHAR(86) NOT NULL,
   col137 VARCHAR(86) NOT NULL,
   col138 VARCHAR(86) NOT NULL,
   col139 VARCHAR(86) NOT NULL,
   col140 VARCHAR(86) NOT NULL,
   col141 VARCHAR(86) NOT NULL,
   col142 VARCHAR(86) NOT NULL,
   col143 VARCHAR(86) NOT NULL,
   col144 VARCHAR(86) NOT NULL,
   col145 VARCHAR(86) NOT NULL,
   col146 VARCHAR(86) NOT NULL,
   col147 VARCHAR(86) NOT NULL,
   col148 VARCHAR(86) NOT NULL,
   col149 VARCHAR(86) NOT NULL,
   col150 VARCHAR(86) NOT NULL,
   col151 VARCHAR(86) NOT NULL,
   col152 VARCHAR(86) NOT NULL,
   col153 VARCHAR(86) NOT NULL,
   col154 VARCHAR(86) NOT NULL,
   col155 VARCHAR(86) NOT NULL,
   col156 VARCHAR(86) NOT NULL,
   col157 VARCHAR(86) NOT NULL,
   col158 VARCHAR(86) NOT NULL,
   col159 VARCHAR(86) NOT NULL,
   col160 VARCHAR(86) NOT NULL,
   col161 VARCHAR(86) NOT NULL,
   col162 VARCHAR(86) NOT NULL,
   col163 VARCHAR(86) NOT NULL,
   col164 VARCHAR(86) NOT NULL,
   col165 VARCHAR(86) NOT NULL,
   col166 VARCHAR(86) NOT NULL,
   col167 VARCHAR(86) NOT NULL,
   col168 VARCHAR(86) NOT NULL,
   col169 VARCHAR(86) NOT NULL,
   col170 VARCHAR(86) NOT NULL,
   col171 VARCHAR(86) NOT NULL,
   col172 VARCHAR(86) NOT NULL,
   col173 VARCHAR(86) NOT NULL,
   col174 VARCHAR(86) NOT NULL,
   col175 VARCHAR(86) NOT NULL,
   col176 VARCHAR(86) NOT NULL,
   col177 VARCHAR(86) NOT NULL,
   col178 VARCHAR(86) NOT NULL,
   col179 VARCHAR(86) NOT NULL,
   col180 VARCHAR(86) NOT NULL,
   col181 VARCHAR(86) NOT NULL,
   col182 VARCHAR(86) NOT NULL,
   col183 VARCHAR(86) NOT NULL,
   col184 VARCHAR(86) NOT NULL,
   col185 VARCHAR(86) NOT NULL,
   col186 VARCHAR(86) NOT NULL,
   col187 VARCHAR(86) NOT NULL,
   col188 VARCHAR(86) NOT NULL,
   col189 VARCHAR(86) NOT NULL,
   col190 VARCHAR(86) NOT NULL,
   col191 VARCHAR(86) NOT NULL,
   col192 VARCHAR(86) NOT NULL,
   col193 VARCHAR(86) NOT NULL,
   col194 VARCHAR(86) NOT NULL,
   col195 VARCHAR(86) NOT NULL,
   col196 VARCHAR(86) NOT NULL,
   col197 VARCHAR(86) NOT NULL,
   col198 VARCHAR(86) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And when using InnoDB's DYNAMIC row format and a default character set of utf8mb4 (which requires up to 4 bytes per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(64):

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
   col1 VARCHAR(64) NOT NULL,
   col2 VARCHAR(64) NOT NULL,
   col3 VARCHAR(64) NOT NULL,
   col4 VARCHAR(64) NOT NULL,
   col5 VARCHAR(64) NOT NULL,
   col6 VARCHAR(64) NOT NULL,
   col7 VARCHAR(64) NOT NULL,
   col8 VARCHAR(64) NOT NULL,
   col9 VARCHAR(64) NOT NULL,
   col10 VARCHAR(64) NOT NULL,
   col11 VARCHAR(64) NOT NULL,
   col12 VARCHAR(64) NOT NULL,
   col13 VARCHAR(64) NOT NULL,
   col14 VARCHAR(64) NOT NULL,
   col15 VARCHAR(64) NOT NULL,
   col16 VARCHAR(64) NOT NULL,
   col17 VARCHAR(64) NOT NULL,
   col18 VARCHAR(64) NOT NULL,
   col19 VARCHAR(64) NOT NULL,
   col20 VARCHAR(64) NOT NULL,
   col21 VARCHAR(64) NOT NULL,
   col22 VARCHAR(64) NOT NULL,
   col23 VARCHAR(64) NOT NULL,
   col24 VARCHAR(64) NOT NULL,
   col25 VARCHAR(64) NOT NULL,
   col26 VARCHAR(64) NOT NULL,
   col27 VARCHAR(64) NOT NULL,
   col28 VARCHAR(64) NOT NULL,
   col29 VARCHAR(64) NOT NULL,
   col30 VARCHAR(64) NOT NULL,
   col31 VARCHAR(64) NOT NULL,
   col32 VARCHAR(64) NOT NULL,
   col33 VARCHAR(64) NOT NULL,
   col34 VARCHAR(64) NOT NULL,
   col35 VARCHAR(64) NOT NULL,
   col36 VARCHAR(64) NOT NULL,
   col37 VARCHAR(64) NOT NULL,
   col38 VARCHAR(64) NOT NULL,
   col39 VARCHAR(64) NOT NULL,
   col40 VARCHAR(64) NOT NULL,
   col41 VARCHAR(64) NOT NULL,
   col42 VARCHAR(64) NOT NULL,
   col43 VARCHAR(64) NOT NULL,
   col44 VARCHAR(64) NOT NULL,
   col45 VARCHAR(64) NOT NULL,
   col46 VARCHAR(64) NOT NULL,
   col47 VARCHAR(64) NOT NULL,
   col48 VARCHAR(64) NOT NULL,
   col49 VARCHAR(64) NOT NULL,
   col50 VARCHAR(64) NOT NULL,
   col51 VARCHAR(64) NOT NULL,
   col52 VARCHAR(64) NOT NULL,
   col53 VARCHAR(64) NOT NULL,
   col54 VARCHAR(64) NOT NULL,
   col55 VARCHAR(64) NOT NULL,
   col56 VARCHAR(64) NOT NULL,
   col57 VARCHAR(64) NOT NULL,
   col58 VARCHAR(64) NOT NULL,
   col59 VARCHAR(64) NOT NULL,
   col60 VARCHAR(64) NOT NULL,
   col61 VARCHAR(64) NOT NULL,
   col62 VARCHAR(64) NOT NULL,
   col63 VARCHAR(64) NOT NULL,
   col64 VARCHAR(64) NOT NULL,
   col65 VARCHAR(64) NOT NULL,
   col66 VARCHAR(64) NOT NULL,
   col67 VARCHAR(64) NOT NULL,
   col68 VARCHAR(64) NOT NULL,
   col69 VARCHAR(64) NOT NULL,
   col70 VARCHAR(64) NOT NULL,
   col71 VARCHAR(64) NOT NULL,
   col72 VARCHAR(64) NOT NULL,
   col73 VARCHAR(64) NOT NULL,
   col74 VARCHAR(64) NOT NULL,
   col75 VARCHAR(64) NOT NULL,
   col76 VARCHAR(64) NOT NULL,
   col77 VARCHAR(64) NOT NULL,
   col78 VARCHAR(64) NOT NULL,
   col79 VARCHAR(64) NOT NULL,
   col80 VARCHAR(64) NOT NULL,
   col81 VARCHAR(64) NOT NULL,
   col82 VARCHAR(64) NOT NULL,
   col83 VARCHAR(64) NOT NULL,
   col84 VARCHAR(64) NOT NULL,
   col85 VARCHAR(64) NOT NULL,
   col86 VARCHAR(64) NOT NULL,
   col87 VARCHAR(64) NOT NULL,
   col88 VARCHAR(64) NOT NULL,
   col89 VARCHAR(64) NOT NULL,
   col90 VARCHAR(64) NOT NULL,
   col91 VARCHAR(64) NOT NULL,
   col92 VARCHAR(64) NOT NULL,
   col93 VARCHAR(64) NOT NULL,
   col94 VARCHAR(64) NOT NULL,
   col95 VARCHAR(64) NOT NULL,
   col96 VARCHAR(64) NOT NULL,
   col97 VARCHAR(64) NOT NULL,
   col98 VARCHAR(64) NOT NULL,
   col99 VARCHAR(64) NOT NULL,
   col100 VARCHAR(64) NOT NULL,
   col101 VARCHAR(64) NOT NULL,
   col102 VARCHAR(64) NOT NULL,
   col103 VARCHAR(64) NOT NULL,
   col104 VARCHAR(64) NOT NULL,
   col105 VARCHAR(64) NOT NULL,
   col106 VARCHAR(64) NOT NULL,
   col107 VARCHAR(64) NOT NULL,
   col108 VARCHAR(64) NOT NULL,
   col109 VARCHAR(64) NOT NULL,
   col110 VARCHAR(64) NOT NULL,
   col111 VARCHAR(64) NOT NULL,
   col112 VARCHAR(64) NOT NULL,
   col113 VARCHAR(64) NOT NULL,
   col114 VARCHAR(64) NOT NULL,
   col115 VARCHAR(64) NOT NULL,
   col116 VARCHAR(64) NOT NULL,
   col117 VARCHAR(64) NOT NULL,
   col118 VARCHAR(64) NOT NULL,
   col119 VARCHAR(64) NOT NULL,
   col120 VARCHAR(64) NOT NULL,
   col121 VARCHAR(64) NOT NULL,
   col122 VARCHAR(64) NOT NULL,
   col123 VARCHAR(64) NOT NULL,
   col124 VARCHAR(64) NOT NULL,
   col125 VARCHAR(64) NOT NULL,
   col126 VARCHAR(64) NOT NULL,
   col127 VARCHAR(64) NOT NULL,
   col128 VARCHAR(64) NOT NULL,
   col129 VARCHAR(64) NOT NULL,
   col130 VARCHAR(64) NOT NULL,
   col131 VARCHAR(64) NOT NULL,
   col132 VARCHAR(64) NOT NULL,
   col133 VARCHAR(64) NOT NULL,
   col134 VARCHAR(64) NOT NULL,
   col135 VARCHAR(64) NOT NULL,
   col136 VARCHAR(64) NOT NULL,
   col137 VARCHAR(64) NOT NULL,
   col138 VARCHAR(64) NOT NULL,
   col139 VARCHAR(64) NOT NULL,
   col140 VARCHAR(64) NOT NULL,
   col141 VARCHAR(64) NOT NULL,
   col142 VARCHAR(64) NOT NULL,
   col143 VARCHAR(64) NOT NULL,
   col144 VARCHAR(64) NOT NULL,
   col145 VARCHAR(64) NOT NULL,
   col146 VARCHAR(64) NOT NULL,
   col147 VARCHAR(64) NOT NULL,
   col148 VARCHAR(64) NOT NULL,
   col149 VARCHAR(64) NOT NULL,
   col150 VARCHAR(64) NOT NULL,
   col151 VARCHAR(64) NOT NULL,
   col152 VARCHAR(64) NOT NULL,
   col153 VARCHAR(64) NOT NULL,
   col154 VARCHAR(64) NOT NULL,
   col155 VARCHAR(64) NOT NULL,
   col156 VARCHAR(64) NOT NULL,
   col157 VARCHAR(64) NOT NULL,
   col158 VARCHAR(64) NOT NULL,
   col159 VARCHAR(64) NOT NULL,
   col160 VARCHAR(64) NOT NULL,
   col161 VARCHAR(64) NOT NULL,
   col162 VARCHAR(64) NOT NULL,
   col163 VARCHAR(64) NOT NULL,
   col164 VARCHAR(64) NOT NULL,
   col165 VARCHAR(64) NOT NULL,
   col166 VARCHAR(64) NOT NULL,
   col167 VARCHAR(64) NOT NULL,
   col168 VARCHAR(64) NOT NULL,
   col169 VARCHAR(64) NOT NULL,
   col170 VARCHAR(64) NOT NULL,
   col171 VARCHAR(64) NOT NULL,
   col172 VARCHAR(64) NOT NULL,
   col173 VARCHAR(64) NOT NULL,
   col174 VARCHAR(64) NOT NULL,
   col175 VARCHAR(64) NOT NULL,
   col176 VARCHAR(64) NOT NULL,
   col177 VARCHAR(64) NOT NULL,
   col178 VARCHAR(64) NOT NULL,
   col179 VARCHAR(64) NOT NULL,
   col180 VARCHAR(64) NOT NULL,
   col181 VARCHAR(64) NOT NULL,
   col182 VARCHAR(64) NOT NULL,
   col183 VARCHAR(64) NOT NULL,
   col184 VARCHAR(64) NOT NULL,
   col185 VARCHAR(64) NOT NULL,
   col186 VARCHAR(64) NOT NULL,
   col187 VARCHAR(64) NOT NULL,
   col188 VARCHAR(64) NOT NULL,
   col189 VARCHAR(64) NOT NULL,
   col190 VARCHAR(64) NOT NULL,
   col191 VARCHAR(64) NOT NULL,
   col192 VARCHAR(64) NOT NULL,
   col193 VARCHAR(64) NOT NULL,
   col194 VARCHAR(64) NOT NULL,
   col195 VARCHAR(64) NOT NULL,
   col196 VARCHAR(64) NOT NULL,
   col197 VARCHAR(64) NOT NULL,
   col198 VARCHAR(64) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Working Around the Problem

There are a few ways to work around this problem.

If you would like a solution for the problem instead of just working around it, then see the solutions mentioned in the previous section.

Refactoring the Table into Multiple Tables

A safe workaround is to refactor the single wide table, so that its columns are spread among multiple tables.

This workaround can even work if your table is so wide that the previous solutions have failed to solve them problem for your table.

Refactoring Some Columns into JSON

A safe workaround is to refactor some of the columns into a JSON document.

The JSON document can be queried and manipulated using MariaDB's JSON functions.

The JSON document can be stored in a column that uses one of the following data types:

This workaround can even work if your table is so wide that the previous solutions have failed to solve them problem for your table.

Disabling InnoDB Strict Mode

An unsafe workaround is to disable InnoDB strict mode. InnoDB strict mode can be disabled by setting the innodb_strict_mode system variable to OFF.

For example, even though the following table schema is too large for most InnoDB row formats to store, it can still be created when InnoDB strict mode is disabled:

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=OFF;
CREATE OR REPLACE TABLE tab (
   col1 VARCHAR(40) NOT NULL,
   col2 VARCHAR(40) NOT NULL,
   col3 VARCHAR(40) NOT NULL,
   col4 VARCHAR(40) NOT NULL,
   col5 VARCHAR(40) NOT NULL,
   col6 VARCHAR(40) NOT NULL,
   col7 VARCHAR(40) NOT NULL,
   col8 VARCHAR(40) NOT NULL,
   col9 VARCHAR(40) NOT NULL,
   col10 VARCHAR(40) NOT NULL,
   col11 VARCHAR(40) NOT NULL,
   col12 VARCHAR(40) NOT NULL,
   col13 VARCHAR(40) NOT NULL,
   col14 VARCHAR(40) NOT NULL,
   col15 VARCHAR(40) NOT NULL,
   col16 VARCHAR(40) NOT NULL,
   col17 VARCHAR(40) NOT NULL,
   col18 VARCHAR(40) NOT NULL,
   col19 VARCHAR(40) NOT NULL,
   col20 VARCHAR(40) NOT NULL,
   col21 VARCHAR(40) NOT NULL,
   col22 VARCHAR(40) NOT NULL,
   col23 VARCHAR(40) NOT NULL,
   col24 VARCHAR(40) NOT NULL,
   col25 VARCHAR(40) NOT NULL,
   col26 VARCHAR(40) NOT NULL,
   col27 VARCHAR(40) NOT NULL,
   col28 VARCHAR(40) NOT NULL,
   col29 VARCHAR(40) NOT NULL,
   col30 VARCHAR(40) NOT NULL,
   col31 VARCHAR(40) NOT NULL,
   col32 VARCHAR(40) NOT NULL,
   col33 VARCHAR(40) NOT NULL,
   col34 VARCHAR(40) NOT NULL,
   col35 VARCHAR(40) NOT NULL,
   col36 VARCHAR(40) NOT NULL,
   col37 VARCHAR(40) NOT NULL,
   col38 VARCHAR(40) NOT NULL,
   col39 VARCHAR(40) NOT NULL,
   col40 VARCHAR(40) NOT NULL,
   col41 VARCHAR(40) NOT NULL,
   col42 VARCHAR(40) NOT NULL,
   col43 VARCHAR(40) NOT NULL,
   col44 VARCHAR(40) NOT NULL,
   col45 VARCHAR(40) NOT NULL,
   col46 VARCHAR(40) NOT NULL,
   col47 VARCHAR(40) NOT NULL,
   col48 VARCHAR(40) NOT NULL,
   col49 VARCHAR(40) NOT NULL,
   col50 VARCHAR(40) NOT NULL,
   col51 VARCHAR(40) NOT NULL,
   col52 VARCHAR(40) NOT NULL,
   col53 VARCHAR(40) NOT NULL,
   col54 VARCHAR(40) NOT NULL,
   col55 VARCHAR(40) NOT NULL,
   col56 VARCHAR(40) NOT NULL,
   col57 VARCHAR(40) NOT NULL,
   col58 VARCHAR(40) NOT NULL,
   col59 VARCHAR(40) NOT NULL,
   col60 VARCHAR(40) NOT NULL,
   col61 VARCHAR(40) NOT NULL,
   col62 VARCHAR(40) NOT NULL,
   col63 VARCHAR(40) NOT NULL,
   col64 VARCHAR(40) NOT NULL,
   col65 VARCHAR(40) NOT NULL,
   col66 VARCHAR(40) NOT NULL,
   col67 VARCHAR(40) NOT NULL,
   col68 VARCHAR(40) NOT NULL,
   col69 VARCHAR(40) NOT NULL,
   col70 VARCHAR(40) NOT NULL,
   col71 VARCHAR(40) NOT NULL,
   col72 VARCHAR(40) NOT NULL,
   col73 VARCHAR(40) NOT NULL,
   col74 VARCHAR(40) NOT NULL,
   col75 VARCHAR(40) NOT NULL,
   col76 VARCHAR(40) NOT NULL,
   col77 VARCHAR(40) NOT NULL,
   col78 VARCHAR(40) NOT NULL,
   col79 VARCHAR(40) NOT NULL,
   col80 VARCHAR(40) NOT NULL,
   col81 VARCHAR(40) NOT NULL,
   col82 VARCHAR(40) NOT NULL,
   col83 VARCHAR(40) NOT NULL,
   col84 VARCHAR(40) NOT NULL,
   col85 VARCHAR(40) NOT NULL,
   col86 VARCHAR(40) NOT NULL,
   col87 VARCHAR(40) NOT NULL,
   col88 VARCHAR(40) NOT NULL,
   col89 VARCHAR(40) NOT NULL,
   col90 VARCHAR(40) NOT NULL,
   col91 VARCHAR(40) NOT NULL,
   col92 VARCHAR(40) NOT NULL,
   col93 VARCHAR(40) NOT NULL,
   col94 VARCHAR(40) NOT NULL,
   col95 VARCHAR(40) NOT NULL,
   col96 VARCHAR(40) NOT NULL,
   col97 VARCHAR(40) NOT NULL,
   col98 VARCHAR(40) NOT NULL,
   col99 VARCHAR(40) NOT NULL,
   col100 VARCHAR(40) NOT NULL,
   col101 VARCHAR(40) NOT NULL,
   col102 VARCHAR(40) NOT NULL,
   col103 VARCHAR(40) NOT NULL,
   col104 VARCHAR(40) NOT NULL,
   col105 VARCHAR(40) NOT NULL,
   col106 VARCHAR(40) NOT NULL,
   col107 VARCHAR(40) NOT NULL,
   col108 VARCHAR(40) NOT NULL,
   col109 VARCHAR(40) NOT NULL,
   col110 VARCHAR(40) NOT NULL,
   col111 VARCHAR(40) NOT NULL,
   col112 VARCHAR(40) NOT NULL,
   col113 VARCHAR(40) NOT NULL,
   col114 VARCHAR(40) NOT NULL,
   col115 VARCHAR(40) NOT NULL,
   col116 VARCHAR(40) NOT NULL,
   col117 VARCHAR(40) NOT NULL,
   col118 VARCHAR(40) NOT NULL,
   col119 VARCHAR(40) NOT NULL,
   col120 VARCHAR(40) NOT NULL,
   col121 VARCHAR(40) NOT NULL,
   col122 VARCHAR(40) NOT NULL,
   col123 VARCHAR(40) NOT NULL,
   col124 VARCHAR(40) NOT NULL,
   col125 VARCHAR(40) NOT NULL,
   col126 VARCHAR(40) NOT NULL,
   col127 VARCHAR(40) NOT NULL,
   col128 VARCHAR(40) NOT NULL,
   col129 VARCHAR(40) NOT NULL,
   col130 VARCHAR(40) NOT NULL,
   col131 VARCHAR(40) NOT NULL,
   col132 VARCHAR(40) NOT NULL,
   col133 VARCHAR(40) NOT NULL,
   col134 VARCHAR(40) NOT NULL,
   col135 VARCHAR(40) NOT NULL,
   col136 VARCHAR(40) NOT NULL,
   col137 VARCHAR(40) NOT NULL,
   col138 VARCHAR(40) NOT NULL,
   col139 VARCHAR(40) NOT NULL,
   col140 VARCHAR(40) NOT NULL,
   col141 VARCHAR(40) NOT NULL,
   col142 VARCHAR(40) NOT NULL,
   col143 VARCHAR(40) NOT NULL,
   col144 VARCHAR(40) NOT NULL,
   col145 VARCHAR(40) NOT NULL,
   col146 VARCHAR(40) NOT NULL,
   col147 VARCHAR(40) NOT NULL,
   col148 VARCHAR(40) NOT NULL,
   col149 VARCHAR(40) NOT NULL,
   col150 VARCHAR(40) NOT NULL,
   col151 VARCHAR(40) NOT NULL,
   col152 VARCHAR(40) NOT NULL,
   col153 VARCHAR(40) NOT NULL,
   col154 VARCHAR(40) NOT NULL,
   col155 VARCHAR(40) NOT NULL,
   col156 VARCHAR(40) NOT NULL,
   col157 VARCHAR(40) NOT NULL,
   col158 VARCHAR(40) NOT NULL,
   col159 VARCHAR(40) NOT NULL,
   col160 VARCHAR(40) NOT NULL,
   col161 VARCHAR(40) NOT NULL,
   col162 VARCHAR(40) NOT NULL,
   col163 VARCHAR(40) NOT NULL,
   col164 VARCHAR(40) NOT NULL,
   col165 VARCHAR(40) NOT NULL,
   col166 VARCHAR(40) NOT NULL,
   col167 VARCHAR(40) NOT NULL,
   col168 VARCHAR(40) NOT NULL,
   col169 VARCHAR(40) NOT NULL,
   col170 VARCHAR(40) NOT NULL,
   col171 VARCHAR(40) NOT NULL,
   col172 VARCHAR(40) NOT NULL,
   col173 VARCHAR(40) NOT NULL,
   col174 VARCHAR(40) NOT NULL,
   col175 VARCHAR(40) NOT NULL,
   col176 VARCHAR(40) NOT NULL,
   col177 VARCHAR(40) NOT NULL,
   col178 VARCHAR(40) NOT NULL,
   col179 VARCHAR(40) NOT NULL,
   col180 VARCHAR(40) NOT NULL,
   col181 VARCHAR(40) NOT NULL,
   col182 VARCHAR(40) NOT NULL,
   col183 VARCHAR(40) NOT NULL,
   col184 VARCHAR(40) NOT NULL,
   col185 VARCHAR(40) NOT NULL,
   col186 VARCHAR(40) NOT NULL,
   col187 VARCHAR(40) NOT NULL,
   col188 VARCHAR(40) NOT NULL,
   col189 VARCHAR(40) NOT NULL,
   col190 VARCHAR(40) NOT NULL,
   col191 VARCHAR(40) NOT NULL,
   col192 VARCHAR(40) NOT NULL,
   col193 VARCHAR(40) NOT NULL,
   col194 VARCHAR(40) NOT NULL,
   col195 VARCHAR(40) NOT NULL,
   col196 VARCHAR(40) NOT NULL,
   col197 VARCHAR(40) NOT NULL,
   col198 VARCHAR(40) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

But as mentioned above, if InnoDB strict mode is disabled and if a DDL statement is executed, then InnoDB will still raise a warning with this message. The SHOW WARNINGS statement can be used to view the warning:

SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 139 | 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. |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

As mentioned above, even though InnoDB is allowing the table to be created, there is still an opportunity for errors. Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table's InnoDB row format can't store, then InnoDB will raise an error with this message. This creates a somewhat unsafe situation, because it means that the application has the chance to encounter an additional error while executing DML.

This page is licensed: CC BY-SA / Gnu FDL