[MDEV-20400] Implement a way to query an InnoDB table's max row size - Jira

XML

Word

Printable

Details

Type: New Feature
Status: Open (View Workflow)
Priority: Critical
Resolution: Unresolved
Fix Version/s: None
Component/s: Storage Engine - InnoDB
Labels:
None

Description

Users see this error quite frequently:

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.

I think the reason users run into this issue a lot is because:

The COMPACT row format was the default row format for quite a while, and the COMPACT row format can store less data on overflow pages than the DYNAMIC row format, which means that it is easier to create tables that run into the maximum row size.
innodb_strict_mode was set to OFF by default for quite a while, so InnoDB would not throw an error if a user created a table that ran into the maximum row size.
InnoDB used incorrect calculations to determine the maximum row size before ~~MDEV-19292~~ was fixed.

Once a table that exceeds the maximum row size is created, it can be quite difficult to determine which tables can hit this issue. The easiest way to currently check if a table hits the limit is to set innodb_strict_mode=ON, and then to create another table with the exact same definition. For example:

MariaDB [db1]> SET SESSION innodb_strict_mode=ON;

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> 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.

https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/#checking-existing-tables-for-the-problem

However, if you want to check all tables on the system, then this method is impractical for systems that have a lot of tables.

I think we should have some way to more easily determine which tables on a server have an "unsafe" maximum row size that can exceed the row format's maximum row size.

Some potential ways to implement this are:

Make "CHECK TABLE" or "CHECK TABLE ... EXTENDED" return an error if a table's maximum row size is too big for the row format, and if innodb_strict_mode=ON is set. This does not currently happen.

"CHECK TABLE" does currently return a warning in this case sometimes, but I can't tell if that behavior is intentional, or if it is a bug that was introduced by the fix for ~~MDEV-19292~~. See ~~MDEV-20194~~ for more information.

For example:

MariaDB [db1]> SET SESSION innodb_strict_mode=OFF;

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> 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 ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;

Query OK, 0 rows affected, 1 warning (0.018 sec)

MariaDB [db1]> 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)

MariaDB [db1]> CHECK TABLE tab;

+---------+-------+----------+----------+

| Table   | Op    | Msg_type | Msg_text |

+---------+-------+----------+----------+

| db1.tab | check | status   | OK       |

+---------+-------+----------+----------+

1 row in set (0.000 sec)

MariaDB [db1]> CHECK TABLE tab EXTENDED;

+---------+-------+----------+----------+

| Table   | Op    | Msg_type | Msg_text |

+---------+-------+----------+----------+

| db1.tab | check | status   | OK       |

+---------+-------+----------+----------+

1 row in set (0.000 sec)

MariaDB [db1]> SET SESSION innodb_strict_mode=ON;

Query OK, 0 rows affected (0.000 sec)

MariaDB [db1]> CHECK TABLE tab;

+---------+-------+----------+----------+

| Table   | Op    | Msg_type | Msg_text |

+---------+-------+----------+----------+

| db1.tab | check | status   | OK       |

+---------+-------+----------+----------+

1 row in set (0.000 sec)

MariaDB [db1]> CHECK TABLE tab EXTENDED;

+---------+-------+----------+----------+

| Table   | Op    | Msg_type | Msg_text |

+---------+-------+----------+----------+

| db1.tab | check | status   | OK       |

+---------+-------+----------+----------+

1 row in set (0.000 sec)

Another option would be to add a new column to the information_schema.INNODB_SYS_TABLES table. This column could be called something like "MAX_ROW_SIZE", and it could contain the maximum number of bytes that each row in the table could take on the main data page with the table's current row format.

Users could use this to determine if a table is at risk of surpassing InnoDB's maximum row size by looking at the row for the table, and then checking if this row's "MAX_ROW_SIZE" value is greater than 1/2 of innodb_page_size.

https://mariadb.com/kb/en/library/information-schema-innodb_sys_tables-table/

Another option would be to add a new column to the information_schema.INNODB_SYS_INDEXES table. This column could be called something like "MAX_RECORD_SIZE", and it could contain the maximum number of bytes that each record in the table could take in the index's leaf page.

Users could use this to determine if a table is at risk of surpassing InnoDB's maximum row size by looking at the row for the table's PRIMARY index, and then checking if this row's "MAX_RECORD_SIZE" value is greater than 1/2 of innodb_page_size.

https://mariadb.com/kb/en/library/information-schema-innodb_sys_indexes-table/

Another option would be to add a new column to the information_schema.INNODB_SYS_COLUMNS table. This column could be called something like "INLINE_LEN", and it could contain the maximum number of bytes that the column would store in the table on the main data page.

Users could use this to determine if a table is at risk of surpassing InnoDB's maximum row size by looking at the rows for all columns in the table, and then checking if the sum of the rows' "INLINE_LEN" values is greater than 1/2 of innodb_page_size.

https://mariadb.com/kb/en/library/information-schema-innodb_sys_columns-table/

Attachments

Issue Links

is blocked by

MDEV-20194 Warnings inconsistently issued upon CHECK on table from older versions

Closed

relates to

MDEV-17380 innodb_flush_neighbors=ON should be ignored on SSD

Closed

MDEV-20000 Consider adopting innodb.innodb_pagesize_max_recordsize from MySQL

Open

MDEV-20194 Warnings inconsistently issued upon CHECK on table from older versions

Closed

MDEV-20832 Don't print "row size too large" warnings in error log if innodb_strict_mode=OFF and log_warnings<=2

Closed

MDEV-21261 When performing instant add column, InnoDB ignores innodb_strict_mode for "row size too large" check

Confirmed

MDEV-23521 Implement an InnoDB row format that can use overflow pages for smaller columns

Open

MDEV-16969 Fix error message as promised in upstream Bug #69336

Open

MDEV-19292 "Row size too large" error when creating table with lots columns when row format is DYNAMIC or COMPRESSED

Closed

MDEV-20194 Warnings inconsistently issued upon CHECK on table from older versions

Closed

MDEV-20448 Add link to release notes about "Row size too large" errors

Closed

MDEV-20786 MariaDB server writes warnings into errorlog although 'log_warnings=0'

Closed

MDEV-20949 Stop performing unnecessary maximum row size check for DML

Closed

MDEV-21429 TRUNCATE and OPTIMIZE are being refused due to "row size too large"

Closed

(9 relates to)

Activity

People

Assignee:: Sergei Golubchik

Reporter:: Geoff Montee (Inactive)

Votes:: 8 Vote for this issue

Watchers:: 17 Start watching this issue

Dates

Created:: 2019-08-21 22:48

Updated:: 2024-09-06 10:00

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.