Details
-
New Feature
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
-
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-19292was 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.
|
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
-
-
- Closed
-
-
MDEV-20000 Consider adopting innodb.innodb_pagesize_max_recordsize from MySQL
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
(9 relates to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-16969 [ MDEV-16969 ] |
| Description |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 Once a table that exceeds the maximum row size is created, it can be quite difficult to determine which tables can hit this issue. I think we should have some way to determine which tables on a server have an "unsafe" maximum row size. Some potential ways to implement this are: * Make "CHECK TABLE ... EXTENDED" return an error if a table's maximum row size is too big for the row format. This does not currently happen, even if innodb_strict_mode=ON is set. For example: {noformat} 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) {noformat} * 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. 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/ |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 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. This 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 ... EXTENDED" return an error if a table's maximum row size is too big for the row format. This does not currently happen, even if innodb_strict_mode=ON is set. For example: {noformat} 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) {noformat} * 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/ |
| Description |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 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. This 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 ... EXTENDED" return an error if a table's maximum row size is too big for the row format. This does not currently happen, even if innodb_strict_mode=ON is set. For example: {noformat} 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) {noformat} * 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/ |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 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. This 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 For example: {noformat} 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) {noformat} * 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/ |
| Assignee | Marko Mäkelä [ marko ] | Eugene Kosov [ kevg ] |
| Link | This issue relates to MENT-366 [ MENT-366 ] |
| Link |
This issue relates to |
| Description |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 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. This 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 For example: {noformat} 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) {noformat} * 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/ |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 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: {noformat} 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. {noformat} 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 For example: {noformat} 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) {noformat} * 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/ |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Assignee | Eugene Kosov [ kevg ] | Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
For InnoDB, I would expose the following:
- both minimum and maximum record size
- separately for each leaf and non-leaf page for each index (and for the record heap, if applicable)
- possibly, indicate the maximum number of BLOB page chains that can be attached to a clustered index leaf page record
- expose what the sizes would be in other {{ROW_FORMAT}}s (if the sizes differ from the current one)
- indicate whether the table is in the
MDEV-11369/MDEV-15562‘instant add/drop’ format (this could be implied by indicating that when rebuilt in the current ROW_FORMAT, the sizes would be different)
I do not think that such an interface is applicable to MyRocks (which uses append-only compressed files) or ‘virtual’ storage engines, such as Spider.
I believe that the minimum and maximum sizes would make sense for any storage engine that uses B-trees. MyISAM and Aria use those for indexes, even though the data itself is heap-organized (unlike InnoDB’s index-organized tables, which store the data with the PRIMARY KEY in the clustered index). Also, the choice of ROW_FORMAT should affect the record sizes in MyISAM and Aria.
| Link | This issue relates to MENT-514 [ MENT-514 ] |
| Fix Version/s | 10.5 [ 23123 ] |
| Fix Version/s | 10.6 [ 24028 ] |
| Link | This issue relates to MDEV-21261 [ MDEV-21261 ] |
| Link |
This issue relates to |
The main reason this ticket was created was because of MDEV-19292. As this is now fixed, I don't think it's critical to have this feature done before 10.6.
Even when this is done, it will not be of much help for most users as very few will check the max row length possible for a table that was created.
The fact that InnoDB gives a warning that max row size can be exceeded on insert should be good enough for most users.
The real solution to users who has this problem is to either increase InnoDB block size or change long VARCHAR columns to TEXT
"The main reason this ticket was created was because of MDEV-19292. "
No, it was created in the aftermath of all the problems the MDEV-19292 change caused ...
No, it was created in the aftermath of all the problems the
MDEV-19292change caused ...
Right. This task was created because of the fix for MDEV-19292. After that was fixed, we found out that a large number of databases in the real world contained tables that had the "row size too large" error. The problem is that MariaDB does not have a good way to scan a database and check for tables that have this problem, so users have been stuck trying to fix tables in a reactive manner as they see this error. Generally, it would be better if the server had a way to proactively check which tables have this problem, so they can be fixed all at once.
juan.vera did create a script this week that can be used to do this until we can implement something in the server. See here:
#!/bin/bash
|
|
|
if [ -z "$3" ]
|
then
|
echo "Usage: $0 host user password" >&2
|
exit 1
|
fi
|
|
|
dt="tmp_$RANDOM$RANDOM"
|
|
|
mysql -h $1 -u $2 -p$3 -ABNe "create database $dt;"
|
if [ $? -ne 0 ]
|
then
|
echo "Error: $0 unable to run mysql CLI (or create database) with given credentials" >&2
|
exit 1
|
fi
|
echo "Created temporary database ${dt} on host $1"
|
|
|
c=0
|
for d in $(mysql -h $1 -u $2 -p$3 -ABNe "show databases;" | egrep -iv "information_schema|mysql|performance_schema|$dt")
|
do
|
# echo "database $d"
|
for t in $(mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d)
|
do
|
# echo "table $t"
|
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 "$0 done."
|
I would think that using a script like this is probably a good temporary workaround.
The real solution to users who has this problem is to either increase InnoDB block size or change long VARCHAR columns to TEXT
Actually, long VARCHAR columns do not need to be converted to TEXT. This is a common misunderstanding. With InnoDB's DYNAMIC row format, long VARCHAR columns are usually fine already, because that row format can store any VARCHAR columns that are 256 bytes or longer completely on overflow pages. Therefore, if the user is using InnoDB's DYNAMIC row format, then the problem is usually caused by short VARCHAR columns, and a counter-intuitive but effective solution to the problem is actually to increase the length of these short VARCHAR columns. See here:
For VARCHAR columns, the DYNAMIC row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.
The original table schema shown earlier on this page causes the Row size too large error, because all of the table's VARCHAR columns are smaller than 256 bytes, which means that they have to be stored on the row's main data page.
Therefore, a potential solution to the Row size too large error is to ensure that all VARCHAR columns are at least as long as 256 bytes. The number of characters required to reach the 256 byte limit depends on the character set used by the column.
For the record, sanja suggested that INFORMATION_SCHEMA.INNODB_% views be used for this. I strongly disagree with that due to the following:
- MDEV-11655 would remove the InnoDB internal data dictionary, and as part of that, INFORMATION_SCHEMA.INNODB_SYS_% would be removed as well.
- Table names are mangled in my_charset_filename encoding in storage engines, and can be mangled further by virtual storage engines, such as ha_partition or Spider. Is it really the storage engine’s or the user’s responsibility to demangle the names?
- There is no condition pushdown interface for INFORMATION_SCHEMA. If the table that the user is interested in is not in the table definition cache nor in the storage engine’s internal dictionary cache, how do we ensure that all data is returned? By loading all 100,000 table (or partition or subpartition) definitions just in case?
In my opinion, if this is to be implemented, it needs to be a proper storage engine interface that can be invoked after handler::open().
Side note: INFORMATION_SCHEMA.INNODB_SYS_% was reverted from MySQL 5.5 due to opposition by the senior MySQL architect Rune Humborstad. Unfortunately, it was added in MySQL 5.6 and extended in 5.7.
2. Ideally, the engine should see only names in the my_charset_filename, but I doubt we're there yet
3. There is some condition pushdown for INFORMATION_SCHEMA (see LOOKUP_FIELD_VALUES)
| Link |
This issue relates to |
I see that fill_schema_schemata(), which implements information_schema.schemata, is making use of LOOKUP_FIELD_VALUES. That might address my last point. But, given my long-term goal to remove the persistent InnoDB data dictionary, it would seem more appropriate to provide the information via class handler, which is not adequately capturing the underlying data format of InnoDB:
uint
|
ha_innobase::max_supported_key_length() const |
/*=========================================*/
|
{
|
/* An InnoDB page must store >= 2 keys; a secondary key record |
must also contain the primary key value. Therefore, if both
|
the primary key and the secondary key are at this maximum length,
|
it must be less than 1/4th of the free space on a page including
|
record overhead.
|
|
|
MySQL imposes its own limit to this number; MAX_KEY_LENGTH = 3072.
|
|
|
For page sizes = 16k, InnoDB historically reported 3500 bytes here,
|
But the MySQL limit of 3072 was always used through the handler
|
interface.
|
|
|
Note: Handle 16k and 32k pages the same here since the limits
|
are higher than imposed by MySQL. */
|
|
|
switch (srv_page_size) { |
case 4096: |
/* Hack: allow mysql.innodb_index_stats to be created. */ |
/* FIXME: rewrite this API, and in sql_table.cc consider |
that in index-organized tables (such as InnoDB), secondary
|
index records will be padded with the PRIMARY KEY, instead
|
of some short ROWID or record heap address. */
|
return(1173); |
case 8192: |
return(1536); |
default: |
return(3500); |
}
|
}
|
…
|
uint
|
ha_innobase::max_supported_key_part_length() const |
/*==============================================*/
|
{
|
/* A table format specific index column length check will be performed |
at ha_innobase::add_index() and row_create_index_for_mysql() */
|
return(REC_VERSION_56_MAX_INDEX_COL_LEN); |
}
|
In InnoDB, B-tree leaf and non-leaf pages impose different length limitations in different indexes. I can imagine cases where the clustered index record size is more limiting than any secondary index, but it could also be the opposite. The above API is limited to the table level only, and it does not even consider the ROW_FORMAT of the table. I vaguely remember that those functions could be called without opening a table handle first.
I think that this problem would be best addressed by adequately refactoring the handler API. For the InnoDB changes, I hope that the fix of MDEV-20194 will be useful.
| Assignee | Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] | Eugene Kosov [ kevg ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Link |
This issue is blocked by |
| Link |
This issue relates to |
I think that we could try to create a new view INFORMATION_SCHEMA.INNODB_TABLES that would expose this information and generally be a replacement for INNODB_SYS_TABLES. The names of the schema, table, partition and subpartition should be returned in a consistent fashion with non-InnoDB views in the INFORMATION_SCHEMA, and an attempt to support condition pushdown via LOOKUP_FIELD_VALUES should be made.
This new table could also export other attributes, such as the ON_SSD attribute that was added in MDEV-17380 in 10.4 and removed in MDEV-15528 in 10.5.
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Link | This issue relates to MENT-1125 [ MENT-1125 ] |
| Fix Version/s | 10.6 [ 24028 ] |
Maybe this does not need to be solved in the exact fashion like GeoffMontee asks for it,
He wants to know how much bytes is a row with a given table definition. If I make an assumption that output is deterministic, and only depends on DDL, then someone could and also write a perl function that takes a table definition text, parses it, does some math, and spits out the number, based on the same logic that is in C++. this function can be written in Perl, and combined with command client in a true Unix manner, via pipes mysql -e "show create table test.foobar" | perl do_some_math.pl ,
I do not see that every bug should automatically turn into an additional field in the information schema table, for a workaround that only few people need (only few people have zillions of tables so that the "slow" method can't be used)
Perhaps, with some effort, we can introduce an SQL function that looks up I_S.columns and spits out the number, and say in 10.6 it would be added to sys schema. But I'm almost sure that we do not need an additional column to slow down already slow I_S queries.
| Labels | need_feedback |
| Labels | need_feedback |
wlad, in a B-tree based storage engine like InnoDB, there are multiple different size constraints, not a single "row size". In addition to the combinations that I mentioned on 2019-11-14:
{minimum,maximum}_{leaf,internal}_{clustered,secondary}_index_record_size
|
there is one more constraint, caused by the fact that an InnoDB undo log record must fit in a single page: An attempt to UPDATE too many indexed columns may fail, because the undo log record must store the secondary index keys. This is most prominent when many prefix-indexed BLOB or TEXT columns are being updated in ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.
I think that the maximum number of index fields in a secondary index record is 16+16+1, for the maximum number of PRIMARY KEY columns (16), the maximum number of INDEX columns (16, if these are different from the PRIMARY KEY columns), and 1 child page number pointer (4 bytes). In the clustered index, other than the PRIMARY KEY columns may be stored off-page (as BLOBs), provided that they are stored as variable-size and the values are long enough. The rules are rather complex, and they depend on the ROW_FORMAT.
The maximum size (innodb_page_size minus some overhead, divided by 2) would typically be exceeded by a secondary index non-leaf page record, or by a clustered index record. I think that we must return both the maximum size and the name of the index that has the longest-maximum-size record.
| Labels | ServiceNow |
| Labels | ServiceNow | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
| Labels | 76qDvLB8Gju6Hs7nk3VY3EX42G795W5z |
| Workflow | MariaDB v3 [ 99083 ] | MariaDB v4 [ 131806 ] |
| Assignee | Eugene Kosov [ kevg ] | Marko Mäkelä [ marko ] |
| Link | This issue relates to MDEV-20000 [ MDEV-20000 ] |
serg, I think that an interface needs to be created that matches the storage model of InnoDB.
| Assignee | Marko Mäkelä [ marko ] | Sergei Golubchik [ serg ] |
| Description |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 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: {noformat} 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. {noformat} 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 For example: {noformat} 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) {noformat} * 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/ |
Users see this error quite frequently: {noformat} 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. {noformat} 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: {noformat} 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. {noformat} 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 For example: {noformat} 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) {noformat} * 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/ |
| Description |
Users see this error quite frequently: {noformat} 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. {noformat} 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: {noformat} 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. {noformat} 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 For example: {noformat} 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) {noformat} * 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/ |
Users see this error quite frequently:
{noformat} 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. {noformat} 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 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: {noformat} 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. {noformat} 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 For example: {noformat} 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) {noformat} * 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/ |
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |
| Status | Confirmed [ 10101 ] | Open [ 1 ] |
| Zendesk Related Tickets | 201658 103041 118056 195705 147879 113644 134995 110515 118409 185863 190150 160501 150204 114072 195104 185975 198426 109590 146240 164062 166795 159353 152108 110107 119522 184957 | |
| Zendesk active tickets | 201658 |
| Summary | Implement a way to query an InnoDB table's max row size | Implement a way to query an InnoDB tables max row size |
| Summary | Implement a way to query an InnoDB tables max row size | Implement a way to query an InnoDB table"s max row size |
| Summary | Implement a way to query an InnoDB table"s max row size | Implement a way to query an InnoDB table max row size |
InnoDB currently ignores any clauses in CHECK TABLE, except for QUICK.
I think that it would be reasonable for all forms of CHECK TABLE to check the maximum row size, based on the value of innodb_strict_mode. Side note: As far as I know, CHECK TABLE currently always returns a success code, and any errors are reported in the result-set.
I would not want to extend the output of any INFORMATION_SCHEMA.INNODB_SYS_ views. After all, I would like to remove these views along with the InnoDB internal data dictionary, related to MDEV-11655.