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.
The DYNAMIC row format, default in modern MariaDB versions, optimizes storage for large BLOB/TEXT columns by storing them on separate overflow pages.
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.
The limit for indexing column values depends on the value:
The default row format is DYNAMIC, as long as the 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 table option at all in a or statement.
It is recommended to set the system variable to ON when using this row format.
For example:
InnoDB uses the Dynamic row format for new InnoDB tables by default, because the 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:
Connect to the server using :
Confirm that the default storage engine is InnoDB by checking the system variable using the statement:
Confirm that InnoDB's default row format is Dynamic by checking the system variable using the statement:
If the database does not exist, then create the database for the table using the statement:
Create the table using the statement:
Confirm that the table uses the Dynamic row format by querying the table:
ROW_FORMATAn 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:
Connect to the server using :
Confirm that the default storage engine is InnoDB by checking the system variable using the statement:
Confirm that InnoDB's default row format is not Dynamic by checking the system variable using the statement:
If the database does not exist, then create the database for the table using the statement:
Create the table using the statement, and specify the Dynamic row format using the ROW_FORMAT table option:
Confirm that the table uses the Dynamic row format by querying the table:
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:
Connect to the server using MariaDB Client:
Search for InnoDB tables that do not use the Dynamic row format by querying the table:
Alter the table using the statement, and specify the Dynamic row format using the ROW_FORMAT table option:
Confirm that the table uses the Dynamic row format by querying the table again:
The DYNAMIC row format supports index prefixes up to 3072 bytes. In earlier versions of MariaDB, the system variable is used to configure the maximum index prefix length. In these versions, if 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.
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 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 , , and 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 . 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 .
For and columns, only values longer than 40 bytes are considered for storage on overflow pages. For and 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 . Even though a column using the data type can hold at most 255 characters, a column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the 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 . 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 , , and data types.
This page is licensed: CC BY-SA / Gnu FDL
Learn about the COMPRESSED row format, which compresses data and index pages using algorithms like zlib to minimize storage footprint at the cost of CPU.
An alternative (and usually superior) way to compress InnoDB tables is by using . See .
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 , , and data types.
The COMPRESSED row format also supports compression of all data and index pages.
The Compressed row format supports the following block sizes:
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.
The limit for indexing column values depends on the innodb_page_size value:
16k
3072 bytes
8k
1536 bytes
4k
768 bytes
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 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:
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:
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:
Connect to the server using MariaDB Client:
Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:
Create the table using the CREATE TABLE statement, and specify the Compressed row format using the ROW_FORMAT table option:
Confirm that the table uses the Compressed row format with an 8 KB block size by querying the information_schema.INNODB_SYS_TABLES table:
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.
Connect to the server using MariaDB Client:
Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:
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:
Confirm that the table uses the Compressed row format with an 8 KB block size by querying the information_schema.INNODB_SYS_TABLES table:
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.
The following INFORMATION_SCHEMA tables can be used to monitor the performances of InnoDB compressed tables:
The COMPRESSED row format supports index prefixes up to 3072 bytes. In 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.
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.
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.
This page is licensed: CC BY-SA / Gnu FDL
16k 32k 16k
3072 bytes
8k
1536 bytes
4k
768 bytes
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;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 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;$ mariadb --user=rootSHOW SESSION VARIABLES
LIKE 'default_storage_engine';+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+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;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 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;$ mariadb --user=rootSHOW SESSION VARIABLES
LIKE 'default_storage_engine';+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+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;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 |
+-------------------+------------+---------------+SET SESSION innodb_strict_mode=ON;
SET GLOBAL innodb_default_row_format='dynamic';
CREATE TABLE tab (
id INT,
str VARCHAR(50)
) ENGINE=InnoDB;$ mariadb --user=rootSHOW SESSION VARIABLES
LIKE 'default_storage_engine';+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+SHOW GLOBAL VARIABLES
LIKE 'innodb_default_row_format';+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+CREATE DATABASE hq_sales;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)
);SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';+-------------------+------------+
| NAME | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Dynamic |
+-------------------+------------+$ mariadb --user=rootSHOW SESSION VARIABLES
LIKE 'default_storage_engine';+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+SHOW GLOBAL VARIABLES
LIKE 'innodb_default_row_format';+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | compact |
+---------------------------+---------+CREATE DATABASE hq_sales;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;SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';+-------------------+------------+
| NAME | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Dynamic |
+-------------------+------------+$ mariadb --user=rootSELECT 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 |
+-------------------+------------+ALTER TABLE hq_sales.invoices
ROW_FORMAT = DYNAMIC;SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';+-------------------+------------+
| NAME | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Dynamic |
+-------------------+------------+Detailed information on the COMPACT row format, which reduces storage space by roughly 20% compared to REDUNDANT, handling NULLs and variable-length columns efficiently.
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.
COMPACT Row FormatIf 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 table option to COMPACT in a or statement.
It is recommended to set the system variable to ON when using this row format.
The COMPACT row format is supported by both the Antelope and the Barracuda , so tables with this row format can be created regardless of the value of the system variable.
For example:
COMPACT Row FormatThe COMPACT row format supports index prefixes up to 767 bytes.
COMPACT Row FormatAll 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 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 , , and 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 . 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 .
For , , and 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 . Even though a column using the data type can hold at most 255 characters, a column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the 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 . 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
SET SESSION innodb_strict_mode=ON;
CREATE TABLE tab (
id INT,
str VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;An overview of the four InnoDB row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED), comparing their storage efficiency and feature support.
The InnoDB storage engine supports four different row formats:
The 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:
One way to specify an InnoDB table's row format is by setting the table option to the relevant row format in a or statement:
The statement can be used to see the row format used by a table:
The table can also be queried to see the row format used by a table:
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 are tagged with the Antelope file format in the table.
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 for more information.
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 for more information.
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 , , and data types. However, InnoDB tables using the COMPRESSED row format are more efficient.
See for more information.
An alternative way to compress InnoDB tables is by using .
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 , , and data types.
The COMPRESSED row format also supports compression of all data and index pages.
See for more information.
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 and columns do not count towards this limit. Only the pointers for a table's and 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:
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 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 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:
And if the table were using the DYNAMIC or COMPRESSED row formats, then the error or warning would be the following:
These messages are raised in the following cases:
If is enabled and if a statement is executed that touches the table, such as or , then InnoDB will raise an error with this message
If is disabled and if a statement is executed that touches the table, such as or [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 is enabled, if a 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 .
In earlier versions, MariaDB doesn't properly calculate the row sizes while executing DDL. In these versions, unsafe tables can be created, even if is enabled. The calculations were fixed by .
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.
And users could also see the following message as an error or warning in the :
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 for more information.
For information on how to solve the problem, see .
This page is licensed: CC BY-SA / Gnu FDL
Information on the legacy REDUNDANT row format, primarily maintained for backward compatibility with older MySQL versions.
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.
REDUNDANT Row FormatRedundant 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 table option to REDUNDANT in a or statement.
It is recommended to set the system variable to ON when using this format.
The REDUNDANT row format is supported by both the Antelope and the Barracuda , so tables with this row format can be created regardless of the value of the system variable.
For example:
REDUNDANT Row FormatThe REDUNDANT row format supports index prefixes up to 767 bytes.
REDUNDANT Row FormatAll 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 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 , , and 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 . 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 .
For , , and 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 . Even though a column using the data type can hold at most 255 characters, a column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the 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
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
Feature
Default
Yes
No
No
No
Recommended
Yes
No
No
No
Efficiently stores large columns
Yes
SET SESSION innodb_strict_mode=ON;
SET GLOBAL innodb_default_row_format='dynamic';
CREATE TABLE tab (
id INT,
str VARCHAR(50)
) ENGINE=InnoDB;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;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: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: SingleERROR 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 BLOBsERROR 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.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.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.[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.SET SESSION innodb_strict_mode=ON;
CREATE TABLE tab (
id INT,
str VARCHAR(50)
) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;Diagnose and fix 'Row size too large' errors in InnoDB, usually caused by exceeding the maximum row size, by changing row formats to DYNAMIC or adjusting column data types.
With InnoDB, users can see the following message as an error or warning:
And they can also see the following message as an error or warning in the error log:
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 is disabled and if a statement is executed that touches the table, such as or , then InnoDB will raise a warning with this message.
Regardless of whether is enabled, if a 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.
Here is an example 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 system variable. See 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 and 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:
InnoDB does not currently have an easy way to check all existing tables to determine which tables have this problem. See for more information.
One method to check a single existing table for this problem is to enable , and then try to create a duplicate of the table with . If the table has this problem, then the operation will fail:
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:
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.
There are several potential solutions available to solve this problem.
If the table is using either the or the row format, then one potential solution to this problem is to convert the table to use the 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 and before, and in MySQL 5.6 and before, the row format was the default row format.
In MySQL 4.1 and before, the row format was the default row format.
The 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 for more information.
Therefore, a potential solution to the Row size too large error is to convert the table to use the row format:
You can use the table in the database to find all tables that use the or the 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 row format. For example, the following query can find those tables, while excluding :
In and later, the 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.
If the table is already using the 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.
BLOB or TEXTFor and columns, the row format can store these columns on overflow pages. See for more information.
Therefore, a potential solution to the Row size too large error is to convert some columns to the or data types.
VARBINARY ColumnsFor columns, the row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See for more information.
Therefore, a potential solution to the Row size too large error is to ensure that all columns are at least as long as varbinary(256).
VARCHAR ColumnsFor columns, the row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See 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 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 columns are at least as long as 256 bytes. The number of characters required to reach the 256 byte limit depends on the used by the column.
For example, when using InnoDB's row format and a default character set of (which requires up to 1 byte per character), the 256 byte limit means that a column will only be stored on overflow pages if it is at least as large as a varchar(256):
And when using InnoDB's row format and a default character set of (which requires up to 3 bytes per character), the 256 byte limit means that a column will only be stored on overflow pages if it is at least as large as a varchar(86):
And when using InnoDB's row format and a default character set of (which requires up to 4 bytes per character), the 256 byte limit means that a column will only be stored on overflow pages if it is at least as large as a varchar(64):
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.
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.
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 .
The JSON document can be stored in a column that uses one of the following data types:
: The maximum size of a column is 64 KB.
: The maximum size of a column is 16 MB.
: The maximum size of a column is 4 GB.
: This is just an alias for the data type.
This workaround can even work if your table is so wide that the previous solutions have failed to solve them problem for your table.
An unsafe workaround is to disable . can be disabled by setting the 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 is disabled:
But as mentioned above, if is disabled and if a statement is executed, then InnoDB will still raise a warning with this message. The statement can be used to view the warning:
As mentioned above, even though InnoDB is allowing the table to be created, there is still an opportunity for errors. Regardless of whether is enabled, if a 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 .
This page is licensed: CC BY-SA / Gnu FDL
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.[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.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.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../rowsize.sh host user password#!/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."ALTER TABLE tab ROW_FORMAT=DYNAMIC;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');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;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;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;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;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)