Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.23
-
None
Description
If a table's row format is DYNAMIC or COMPRESSED and if the table's row can be too big to fit entirely on a clustered index page, then I believe that InnoDB is supposed to store variable-length columns (such as columns using VARCHAR, VARBINARY, and BLOB and TEXT types) in overflow pages. Then the clustered index only contains a 20 byte pointer to the overflow page.
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
https://mariadb.com/kb/en/library/innodb-storage-formats/
However, this does not seem to work properly if you set innodb_strict_mode to ON, and if you try to create a table with a large number of small varchar columns.
For example, if you create a table that has varchar(40) columns, and if the table uses the utf8 character set, then each column requires the following storage:
40 characters/VARCHAR * 3 bytes/character (for utf8) + 1 byte/VARCHAR (for length)
= 121 bytes/VARCHAR
If we create a table with 198 of these columns, then the row size is:
198 VARCHARs * 121 bytes/VARCHAR
= 23,958 bytes
This is too large to fit in the 8126 bytes that InnoDB allocates for a row with the default innodb_page_size. However, with the DYNAMIC or COMPRESSED row formats, InnoDB should store values on overflow pages, and the clustered index should only contain a 20 byte pointer to the overflow page.
If only the primary key column is stored in the clustered index, and the rest are stored on overflow pages, then that should make the row size:
121 bytes/PK + (197 pointers * 20 byte/pointer)
= 121 bytes/PK + 3940 bytes/pointers
= 4061 bytes
This is less than the 8126 bytes that InnoDB allocates for a row with the default innodb_page_size. However, if innodb_strict_mode is ON, then InnoDB throws a "Row size too large" error if you try to create a table like this. to reproduce, run the following:
SET SESSION innodb_strict_mode=ON;
|
CREATE 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;
|
You'll see this error:
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.
|
If there are only 197 columns instead of 198, then there is no error with DYNAMIC, but the error is still present with COMPRESSED.
If there are only 187 columns instead of 198, then there is also no error with COMPRESSED.
If you convert the 197 non-PK varchar(40) columns to TEXT, then you *still* get the error:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 TEXT NOT NULL,
|
col3 TEXT NOT NULL,
|
col4 TEXT NOT NULL,
|
col5 TEXT NOT NULL,
|
col6 TEXT NOT NULL,
|
col7 TEXT NOT NULL,
|
col8 TEXT NOT NULL,
|
col9 TEXT NOT NULL,
|
col10 TEXT NOT NULL,
|
col11 TEXT NOT NULL,
|
col12 TEXT NOT NULL,
|
col13 TEXT NOT NULL,
|
col14 TEXT NOT NULL,
|
col15 TEXT NOT NULL,
|
col16 TEXT NOT NULL,
|
col17 TEXT NOT NULL,
|
col18 TEXT NOT NULL,
|
col19 TEXT NOT NULL,
|
col20 TEXT NOT NULL,
|
col21 TEXT NOT NULL,
|
col22 TEXT NOT NULL,
|
col23 TEXT NOT NULL,
|
col24 TEXT NOT NULL,
|
col25 TEXT NOT NULL,
|
col26 TEXT NOT NULL,
|
col27 TEXT NOT NULL,
|
col28 TEXT NOT NULL,
|
col29 TEXT NOT NULL,
|
col30 TEXT NOT NULL,
|
col31 TEXT NOT NULL,
|
col32 TEXT NOT NULL,
|
col33 TEXT NOT NULL,
|
col34 TEXT NOT NULL,
|
col35 TEXT NOT NULL,
|
col36 TEXT NOT NULL,
|
col37 TEXT NOT NULL,
|
col38 TEXT NOT NULL,
|
col39 TEXT NOT NULL,
|
col40 TEXT NOT NULL,
|
col41 TEXT NOT NULL,
|
col42 TEXT NOT NULL,
|
col43 TEXT NOT NULL,
|
col44 TEXT NOT NULL,
|
col45 TEXT NOT NULL,
|
col46 TEXT NOT NULL,
|
col47 TEXT NOT NULL,
|
col48 TEXT NOT NULL,
|
col49 TEXT NOT NULL,
|
col50 TEXT NOT NULL,
|
col51 TEXT NOT NULL,
|
col52 TEXT NOT NULL,
|
col53 TEXT NOT NULL,
|
col54 TEXT NOT NULL,
|
col55 TEXT NOT NULL,
|
col56 TEXT NOT NULL,
|
col57 TEXT NOT NULL,
|
col58 TEXT NOT NULL,
|
col59 TEXT NOT NULL,
|
col60 TEXT NOT NULL,
|
col61 TEXT NOT NULL,
|
col62 TEXT NOT NULL,
|
col63 TEXT NOT NULL,
|
col64 TEXT NOT NULL,
|
col65 TEXT NOT NULL,
|
col66 TEXT NOT NULL,
|
col67 TEXT NOT NULL,
|
col68 TEXT NOT NULL,
|
col69 TEXT NOT NULL,
|
col70 TEXT NOT NULL,
|
col71 TEXT NOT NULL,
|
col72 TEXT NOT NULL,
|
col73 TEXT NOT NULL,
|
col74 TEXT NOT NULL,
|
col75 TEXT NOT NULL,
|
col76 TEXT NOT NULL,
|
col77 TEXT NOT NULL,
|
col78 TEXT NOT NULL,
|
col79 TEXT NOT NULL,
|
col80 TEXT NOT NULL,
|
col81 TEXT NOT NULL,
|
col82 TEXT NOT NULL,
|
col83 TEXT NOT NULL,
|
col84 TEXT NOT NULL,
|
col85 TEXT NOT NULL,
|
col86 TEXT NOT NULL,
|
col87 TEXT NOT NULL,
|
col88 TEXT NOT NULL,
|
col89 TEXT NOT NULL,
|
col90 TEXT NOT NULL,
|
col91 TEXT NOT NULL,
|
col92 TEXT NOT NULL,
|
col93 TEXT NOT NULL,
|
col94 TEXT NOT NULL,
|
col95 TEXT NOT NULL,
|
col96 TEXT NOT NULL,
|
col97 TEXT NOT NULL,
|
col98 TEXT NOT NULL,
|
col99 TEXT NOT NULL,
|
col100 TEXT NOT NULL,
|
col101 TEXT NOT NULL,
|
col102 TEXT NOT NULL,
|
col103 TEXT NOT NULL,
|
col104 TEXT NOT NULL,
|
col105 TEXT NOT NULL,
|
col106 TEXT NOT NULL,
|
col107 TEXT NOT NULL,
|
col108 TEXT NOT NULL,
|
col109 TEXT NOT NULL,
|
col110 TEXT NOT NULL,
|
col111 TEXT NOT NULL,
|
col112 TEXT NOT NULL,
|
col113 TEXT NOT NULL,
|
col114 TEXT NOT NULL,
|
col115 TEXT NOT NULL,
|
col116 TEXT NOT NULL,
|
col117 TEXT NOT NULL,
|
col118 TEXT NOT NULL,
|
col119 TEXT NOT NULL,
|
col120 TEXT NOT NULL,
|
col121 TEXT NOT NULL,
|
col122 TEXT NOT NULL,
|
col123 TEXT NOT NULL,
|
col124 TEXT NOT NULL,
|
col125 TEXT NOT NULL,
|
col126 TEXT NOT NULL,
|
col127 TEXT NOT NULL,
|
col128 TEXT NOT NULL,
|
col129 TEXT NOT NULL,
|
col130 TEXT NOT NULL,
|
col131 TEXT NOT NULL,
|
col132 TEXT NOT NULL,
|
col133 TEXT NOT NULL,
|
col134 TEXT NOT NULL,
|
col135 TEXT NOT NULL,
|
col136 TEXT NOT NULL,
|
col137 TEXT NOT NULL,
|
col138 TEXT NOT NULL,
|
col139 TEXT NOT NULL,
|
col140 TEXT NOT NULL,
|
col141 TEXT NOT NULL,
|
col142 TEXT NOT NULL,
|
col143 TEXT NOT NULL,
|
col144 TEXT NOT NULL,
|
col145 TEXT NOT NULL,
|
col146 TEXT NOT NULL,
|
col147 TEXT NOT NULL,
|
col148 TEXT NOT NULL,
|
col149 TEXT NOT NULL,
|
col150 TEXT NOT NULL,
|
col151 TEXT NOT NULL,
|
col152 TEXT NOT NULL,
|
col153 TEXT NOT NULL,
|
col154 TEXT NOT NULL,
|
col155 TEXT NOT NULL,
|
col156 TEXT NOT NULL,
|
col157 TEXT NOT NULL,
|
col158 TEXT NOT NULL,
|
col159 TEXT NOT NULL,
|
col160 TEXT NOT NULL,
|
col161 TEXT NOT NULL,
|
col162 TEXT NOT NULL,
|
col163 TEXT NOT NULL,
|
col164 TEXT NOT NULL,
|
col165 TEXT NOT NULL,
|
col166 TEXT NOT NULL,
|
col167 TEXT NOT NULL,
|
col168 TEXT NOT NULL,
|
col169 TEXT NOT NULL,
|
col170 TEXT NOT NULL,
|
col171 TEXT NOT NULL,
|
col172 TEXT NOT NULL,
|
col173 TEXT NOT NULL,
|
col174 TEXT NOT NULL,
|
col175 TEXT NOT NULL,
|
col176 TEXT NOT NULL,
|
col177 TEXT NOT NULL,
|
col178 TEXT NOT NULL,
|
col179 TEXT NOT NULL,
|
col180 TEXT NOT NULL,
|
col181 TEXT NOT NULL,
|
col182 TEXT NOT NULL,
|
col183 TEXT NOT NULL,
|
col184 TEXT NOT NULL,
|
col185 TEXT NOT NULL,
|
col186 TEXT NOT NULL,
|
col187 TEXT NOT NULL,
|
col188 TEXT NOT NULL,
|
col189 TEXT NOT NULL,
|
col190 TEXT NOT NULL,
|
col191 TEXT NOT NULL,
|
col192 TEXT NOT NULL,
|
col193 TEXT NOT NULL,
|
col194 TEXT NOT NULL,
|
col195 TEXT NOT NULL,
|
col196 TEXT NOT NULL,
|
col197 TEXT NOT NULL,
|
col198 TEXT NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;
|
And if you do the same with BLOB, instead of TEXT, then you *still* get the error:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 BLOB NOT NULL,
|
col3 BLOB NOT NULL,
|
col4 BLOB NOT NULL,
|
col5 BLOB NOT NULL,
|
col6 BLOB NOT NULL,
|
col7 BLOB NOT NULL,
|
col8 BLOB NOT NULL,
|
col9 BLOB NOT NULL,
|
col10 BLOB NOT NULL,
|
col11 BLOB NOT NULL,
|
col12 BLOB NOT NULL,
|
col13 BLOB NOT NULL,
|
col14 BLOB NOT NULL,
|
col15 BLOB NOT NULL,
|
col16 BLOB NOT NULL,
|
col17 BLOB NOT NULL,
|
col18 BLOB NOT NULL,
|
col19 BLOB NOT NULL,
|
col20 BLOB NOT NULL,
|
col21 BLOB NOT NULL,
|
col22 BLOB NOT NULL,
|
col23 BLOB NOT NULL,
|
col24 BLOB NOT NULL,
|
col25 BLOB NOT NULL,
|
col26 BLOB NOT NULL,
|
col27 BLOB NOT NULL,
|
col28 BLOB NOT NULL,
|
col29 BLOB NOT NULL,
|
col30 BLOB NOT NULL,
|
col31 BLOB NOT NULL,
|
col32 BLOB NOT NULL,
|
col33 BLOB NOT NULL,
|
col34 BLOB NOT NULL,
|
col35 BLOB NOT NULL,
|
col36 BLOB NOT NULL,
|
col37 BLOB NOT NULL,
|
col38 BLOB NOT NULL,
|
col39 BLOB NOT NULL,
|
col40 BLOB NOT NULL,
|
col41 BLOB NOT NULL,
|
col42 BLOB NOT NULL,
|
col43 BLOB NOT NULL,
|
col44 BLOB NOT NULL,
|
col45 BLOB NOT NULL,
|
col46 BLOB NOT NULL,
|
col47 BLOB NOT NULL,
|
col48 BLOB NOT NULL,
|
col49 BLOB NOT NULL,
|
col50 BLOB NOT NULL,
|
col51 BLOB NOT NULL,
|
col52 BLOB NOT NULL,
|
col53 BLOB NOT NULL,
|
col54 BLOB NOT NULL,
|
col55 BLOB NOT NULL,
|
col56 BLOB NOT NULL,
|
col57 BLOB NOT NULL,
|
col58 BLOB NOT NULL,
|
col59 BLOB NOT NULL,
|
col60 BLOB NOT NULL,
|
col61 BLOB NOT NULL,
|
col62 BLOB NOT NULL,
|
col63 BLOB NOT NULL,
|
col64 BLOB NOT NULL,
|
col65 BLOB NOT NULL,
|
col66 BLOB NOT NULL,
|
col67 BLOB NOT NULL,
|
col68 BLOB NOT NULL,
|
col69 BLOB NOT NULL,
|
col70 BLOB NOT NULL,
|
col71 BLOB NOT NULL,
|
col72 BLOB NOT NULL,
|
col73 BLOB NOT NULL,
|
col74 BLOB NOT NULL,
|
col75 BLOB NOT NULL,
|
col76 BLOB NOT NULL,
|
col77 BLOB NOT NULL,
|
col78 BLOB NOT NULL,
|
col79 BLOB NOT NULL,
|
col80 BLOB NOT NULL,
|
col81 BLOB NOT NULL,
|
col82 BLOB NOT NULL,
|
col83 BLOB NOT NULL,
|
col84 BLOB NOT NULL,
|
col85 BLOB NOT NULL,
|
col86 BLOB NOT NULL,
|
col87 BLOB NOT NULL,
|
col88 BLOB NOT NULL,
|
col89 BLOB NOT NULL,
|
col90 BLOB NOT NULL,
|
col91 BLOB NOT NULL,
|
col92 BLOB NOT NULL,
|
col93 BLOB NOT NULL,
|
col94 BLOB NOT NULL,
|
col95 BLOB NOT NULL,
|
col96 BLOB NOT NULL,
|
col97 BLOB NOT NULL,
|
col98 BLOB NOT NULL,
|
col99 BLOB NOT NULL,
|
col100 BLOB NOT NULL,
|
col101 BLOB NOT NULL,
|
col102 BLOB NOT NULL,
|
col103 BLOB NOT NULL,
|
col104 BLOB NOT NULL,
|
col105 BLOB NOT NULL,
|
col106 BLOB NOT NULL,
|
col107 BLOB NOT NULL,
|
col108 BLOB NOT NULL,
|
col109 BLOB NOT NULL,
|
col110 BLOB NOT NULL,
|
col111 BLOB NOT NULL,
|
col112 BLOB NOT NULL,
|
col113 BLOB NOT NULL,
|
col114 BLOB NOT NULL,
|
col115 BLOB NOT NULL,
|
col116 BLOB NOT NULL,
|
col117 BLOB NOT NULL,
|
col118 BLOB NOT NULL,
|
col119 BLOB NOT NULL,
|
col120 BLOB NOT NULL,
|
col121 BLOB NOT NULL,
|
col122 BLOB NOT NULL,
|
col123 BLOB NOT NULL,
|
col124 BLOB NOT NULL,
|
col125 BLOB NOT NULL,
|
col126 BLOB NOT NULL,
|
col127 BLOB NOT NULL,
|
col128 BLOB NOT NULL,
|
col129 BLOB NOT NULL,
|
col130 BLOB NOT NULL,
|
col131 BLOB NOT NULL,
|
col132 BLOB NOT NULL,
|
col133 BLOB NOT NULL,
|
col134 BLOB NOT NULL,
|
col135 BLOB NOT NULL,
|
col136 BLOB NOT NULL,
|
col137 BLOB NOT NULL,
|
col138 BLOB NOT NULL,
|
col139 BLOB NOT NULL,
|
col140 BLOB NOT NULL,
|
col141 BLOB NOT NULL,
|
col142 BLOB NOT NULL,
|
col143 BLOB NOT NULL,
|
col144 BLOB NOT NULL,
|
col145 BLOB NOT NULL,
|
col146 BLOB NOT NULL,
|
col147 BLOB NOT NULL,
|
col148 BLOB NOT NULL,
|
col149 BLOB NOT NULL,
|
col150 BLOB NOT NULL,
|
col151 BLOB NOT NULL,
|
col152 BLOB NOT NULL,
|
col153 BLOB NOT NULL,
|
col154 BLOB NOT NULL,
|
col155 BLOB NOT NULL,
|
col156 BLOB NOT NULL,
|
col157 BLOB NOT NULL,
|
col158 BLOB NOT NULL,
|
col159 BLOB NOT NULL,
|
col160 BLOB NOT NULL,
|
col161 BLOB NOT NULL,
|
col162 BLOB NOT NULL,
|
col163 BLOB NOT NULL,
|
col164 BLOB NOT NULL,
|
col165 BLOB NOT NULL,
|
col166 BLOB NOT NULL,
|
col167 BLOB NOT NULL,
|
col168 BLOB NOT NULL,
|
col169 BLOB NOT NULL,
|
col170 BLOB NOT NULL,
|
col171 BLOB NOT NULL,
|
col172 BLOB NOT NULL,
|
col173 BLOB NOT NULL,
|
col174 BLOB NOT NULL,
|
col175 BLOB NOT NULL,
|
col176 BLOB NOT NULL,
|
col177 BLOB NOT NULL,
|
col178 BLOB NOT NULL,
|
col179 BLOB NOT NULL,
|
col180 BLOB NOT NULL,
|
col181 BLOB NOT NULL,
|
col182 BLOB NOT NULL,
|
col183 BLOB NOT NULL,
|
col184 BLOB NOT NULL,
|
col185 BLOB NOT NULL,
|
col186 BLOB NOT NULL,
|
col187 BLOB NOT NULL,
|
col188 BLOB NOT NULL,
|
col189 BLOB NOT NULL,
|
col190 BLOB NOT NULL,
|
col191 BLOB NOT NULL,
|
col192 BLOB NOT NULL,
|
col193 BLOB NOT NULL,
|
col194 BLOB NOT NULL,
|
col195 BLOB NOT NULL,
|
col196 BLOB NOT NULL,
|
col197 BLOB NOT NULL,
|
col198 BLOB NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;
|
All of these cases are also reproducible with MySQL 5.7.
Attachments
Issue Links
- causes
-
MDEV-20448 Add link to release notes about "Row size too large" errors
-
- Closed
-
- duplicates
-
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-20400 Implement a way to query an InnoDB table max row size
-
- Open
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
- links to
(10 relates to, 2 links to)
Activity
Right now I think that the bug resides in dict_index_too_big_for_tree() which is called on index creation. It checks for the max size of each field and does it incorrectly. For VARCHAR(120) utf8 it assumes that it will take 1 byte for length + 20 byte of prefix + 20 byte of pointer to other page. But field is stored actually as 1 byte of length + N bytes of actual data: all in one page. I will continue my investigations.
The provided patch does solve the problem for BLOB and TEXT types. For example, I am table to create this table after the patch:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 TEXT NOT NULL,
|
col3 TEXT NOT NULL,
|
col4 TEXT NOT NULL,
|
col5 TEXT NOT NULL,
|
col6 TEXT NOT NULL,
|
col7 TEXT NOT NULL,
|
col8 TEXT NOT NULL,
|
col9 TEXT NOT NULL,
|
col10 TEXT NOT NULL,
|
col11 TEXT NOT NULL,
|
col12 TEXT NOT NULL,
|
col13 TEXT NOT NULL,
|
col14 TEXT NOT NULL,
|
col15 TEXT NOT NULL,
|
col16 TEXT NOT NULL,
|
col17 TEXT NOT NULL,
|
col18 TEXT NOT NULL,
|
col19 TEXT NOT NULL,
|
col20 TEXT NOT NULL,
|
col21 TEXT NOT NULL,
|
col22 TEXT NOT NULL,
|
col23 TEXT NOT NULL,
|
col24 TEXT NOT NULL,
|
col25 TEXT NOT NULL,
|
col26 TEXT NOT NULL,
|
col27 TEXT NOT NULL,
|
col28 TEXT NOT NULL,
|
col29 TEXT NOT NULL,
|
col30 TEXT NOT NULL,
|
col31 TEXT NOT NULL,
|
col32 TEXT NOT NULL,
|
col33 TEXT NOT NULL,
|
col34 TEXT NOT NULL,
|
col35 TEXT NOT NULL,
|
col36 TEXT NOT NULL,
|
col37 TEXT NOT NULL,
|
col38 TEXT NOT NULL,
|
col39 TEXT NOT NULL,
|
col40 TEXT NOT NULL,
|
col41 TEXT NOT NULL,
|
col42 TEXT NOT NULL,
|
col43 TEXT NOT NULL,
|
col44 TEXT NOT NULL,
|
col45 TEXT NOT NULL,
|
col46 TEXT NOT NULL,
|
col47 TEXT NOT NULL,
|
col48 TEXT NOT NULL,
|
col49 TEXT NOT NULL,
|
col50 TEXT NOT NULL,
|
col51 TEXT NOT NULL,
|
col52 TEXT NOT NULL,
|
col53 TEXT NOT NULL,
|
col54 TEXT NOT NULL,
|
col55 TEXT NOT NULL,
|
col56 TEXT NOT NULL,
|
col57 TEXT NOT NULL,
|
col58 TEXT NOT NULL,
|
col59 TEXT NOT NULL,
|
col60 TEXT NOT NULL,
|
col61 TEXT NOT NULL,
|
col62 TEXT NOT NULL,
|
col63 TEXT NOT NULL,
|
col64 TEXT NOT NULL,
|
col65 TEXT NOT NULL,
|
col66 TEXT NOT NULL,
|
col67 TEXT NOT NULL,
|
col68 TEXT NOT NULL,
|
col69 TEXT NOT NULL,
|
col70 TEXT NOT NULL,
|
col71 TEXT NOT NULL,
|
col72 TEXT NOT NULL,
|
col73 TEXT NOT NULL,
|
col74 TEXT NOT NULL,
|
col75 TEXT NOT NULL,
|
col76 TEXT NOT NULL,
|
col77 TEXT NOT NULL,
|
col78 TEXT NOT NULL,
|
col79 TEXT NOT NULL,
|
col80 TEXT NOT NULL,
|
col81 TEXT NOT NULL,
|
col82 TEXT NOT NULL,
|
col83 TEXT NOT NULL,
|
col84 TEXT NOT NULL,
|
col85 TEXT NOT NULL,
|
col86 TEXT NOT NULL,
|
col87 TEXT NOT NULL,
|
col88 TEXT NOT NULL,
|
col89 TEXT NOT NULL,
|
col90 TEXT NOT NULL,
|
col91 TEXT NOT NULL,
|
col92 TEXT NOT NULL,
|
col93 TEXT NOT NULL,
|
col94 TEXT NOT NULL,
|
col95 TEXT NOT NULL,
|
col96 TEXT NOT NULL,
|
col97 TEXT NOT NULL,
|
col98 TEXT NOT NULL,
|
col99 TEXT NOT NULL,
|
col100 TEXT NOT NULL,
|
col101 TEXT NOT NULL,
|
col102 TEXT NOT NULL,
|
col103 TEXT NOT NULL,
|
col104 TEXT NOT NULL,
|
col105 TEXT NOT NULL,
|
col106 TEXT NOT NULL,
|
col107 TEXT NOT NULL,
|
col108 TEXT NOT NULL,
|
col109 TEXT NOT NULL,
|
col110 TEXT NOT NULL,
|
col111 TEXT NOT NULL,
|
col112 TEXT NOT NULL,
|
col113 TEXT NOT NULL,
|
col114 TEXT NOT NULL,
|
col115 TEXT NOT NULL,
|
col116 TEXT NOT NULL,
|
col117 TEXT NOT NULL,
|
col118 TEXT NOT NULL,
|
col119 TEXT NOT NULL,
|
col120 TEXT NOT NULL,
|
col121 TEXT NOT NULL,
|
col122 TEXT NOT NULL,
|
col123 TEXT NOT NULL,
|
col124 TEXT NOT NULL,
|
col125 TEXT NOT NULL,
|
col126 TEXT NOT NULL,
|
col127 TEXT NOT NULL,
|
col128 TEXT NOT NULL,
|
col129 TEXT NOT NULL,
|
col130 TEXT NOT NULL,
|
col131 TEXT NOT NULL,
|
col132 TEXT NOT NULL,
|
col133 TEXT NOT NULL,
|
col134 TEXT NOT NULL,
|
col135 TEXT NOT NULL,
|
col136 TEXT NOT NULL,
|
col137 TEXT NOT NULL,
|
col138 TEXT NOT NULL,
|
col139 TEXT NOT NULL,
|
col140 TEXT NOT NULL,
|
col141 TEXT NOT NULL,
|
col142 TEXT NOT NULL,
|
col143 TEXT NOT NULL,
|
col144 TEXT NOT NULL,
|
col145 TEXT NOT NULL,
|
col146 TEXT NOT NULL,
|
col147 TEXT NOT NULL,
|
col148 TEXT NOT NULL,
|
col149 TEXT NOT NULL,
|
col150 TEXT NOT NULL,
|
col151 TEXT NOT NULL,
|
col152 TEXT NOT NULL,
|
col153 TEXT NOT NULL,
|
col154 TEXT NOT NULL,
|
col155 TEXT NOT NULL,
|
col156 TEXT NOT NULL,
|
col157 TEXT NOT NULL,
|
col158 TEXT NOT NULL,
|
col159 TEXT NOT NULL,
|
col160 TEXT NOT NULL,
|
col161 TEXT NOT NULL,
|
col162 TEXT NOT NULL,
|
col163 TEXT NOT NULL,
|
col164 TEXT NOT NULL,
|
col165 TEXT NOT NULL,
|
col166 TEXT NOT NULL,
|
col167 TEXT NOT NULL,
|
col168 TEXT NOT NULL,
|
col169 TEXT NOT NULL,
|
col170 TEXT NOT NULL,
|
col171 TEXT NOT NULL,
|
col172 TEXT NOT NULL,
|
col173 TEXT NOT NULL,
|
col174 TEXT NOT NULL,
|
col175 TEXT NOT NULL,
|
col176 TEXT NOT NULL,
|
col177 TEXT NOT NULL,
|
col178 TEXT NOT NULL,
|
col179 TEXT NOT NULL,
|
col180 TEXT NOT NULL,
|
col181 TEXT NOT NULL,
|
col182 TEXT NOT NULL,
|
col183 TEXT NOT NULL,
|
col184 TEXT NOT NULL,
|
col185 TEXT NOT NULL,
|
col186 TEXT NOT NULL,
|
col187 TEXT NOT NULL,
|
col188 TEXT NOT NULL,
|
col189 TEXT NOT NULL,
|
col190 TEXT NOT NULL,
|
col191 TEXT NOT NULL,
|
col192 TEXT NOT NULL,
|
col193 TEXT NOT NULL,
|
col194 TEXT NOT NULL,
|
col195 TEXT NOT NULL,
|
col196 TEXT NOT NULL,
|
col197 TEXT NOT NULL,
|
col198 TEXT NOT NULL,
|
col199 TEXT NOT NULL,
|
col200 TEXT NOT NULL,
|
col201 TEXT NOT NULL,
|
col202 TEXT NOT NULL,
|
col203 TEXT NOT NULL,
|
col204 TEXT NOT NULL,
|
col205 TEXT NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;
|
However, the provided patch does not seem to work properly for varchar columns.
I've been told that with DYNAMIC and COMPRESSED row formats, values longer than 255 bytes should be stored on overflow pages. Let's look at a couple examples of those.
1.) Let's say that we have a table that uses the utf8 character set and that has a bunch of varchar(85) columns. Each of these columns will be:
85 chars/column * 3 bytes/char + 1 bytes (for length)/column
= 256 bytes
So a varchar(85) column that uses utf8 should be a candidate to be stored in an overflow page. However, this statement fails:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 varchar(85) NOT NULL,
|
col3 varchar(85) NOT NULL,
|
col4 varchar(85) NOT NULL,
|
col5 varchar(85) NOT NULL,
|
col6 varchar(85) NOT NULL,
|
col7 varchar(85) NOT NULL,
|
col8 varchar(85) NOT NULL,
|
col9 varchar(85) NOT NULL,
|
col10 varchar(85) NOT NULL,
|
col11 varchar(85) NOT NULL,
|
col12 varchar(85) NOT NULL,
|
col13 varchar(85) NOT NULL,
|
col14 varchar(85) NOT NULL,
|
col15 varchar(85) NOT NULL,
|
col16 varchar(85) NOT NULL,
|
col17 varchar(85) NOT NULL,
|
col18 varchar(85) NOT NULL,
|
col19 varchar(85) NOT NULL,
|
col20 varchar(85) NOT NULL,
|
col21 varchar(85) NOT NULL,
|
col22 varchar(85) NOT NULL,
|
col23 varchar(85) NOT NULL,
|
col24 varchar(85) NOT NULL,
|
col25 varchar(85) NOT NULL,
|
col26 varchar(85) NOT NULL,
|
col27 varchar(85) NOT NULL,
|
col28 varchar(85) NOT NULL,
|
col29 varchar(85) NOT NULL,
|
col30 varchar(85) NOT NULL,
|
col31 varchar(85) NOT NULL,
|
col32 varchar(85) NOT NULL,
|
col33 varchar(85) NOT NULL,
|
col34 varchar(85) NOT NULL,
|
col35 varchar(85) NOT NULL,
|
col36 varchar(85) NOT NULL,
|
col37 varchar(85) NOT NULL,
|
col38 varchar(85) NOT NULL,
|
col39 varchar(85) NOT NULL,
|
col40 varchar(85) NOT NULL,
|
col41 varchar(85) NOT NULL,
|
col42 varchar(85) NOT NULL,
|
col43 varchar(85) NOT NULL,
|
col44 varchar(85) NOT NULL,
|
col45 varchar(85) NOT NULL,
|
col46 varchar(85) NOT NULL,
|
col47 varchar(85) NOT NULL,
|
col48 varchar(85) NOT NULL,
|
col49 varchar(85) NOT NULL,
|
col50 varchar(85) NOT NULL,
|
col51 varchar(85) NOT NULL,
|
col52 varchar(85) NOT NULL,
|
col53 varchar(85) NOT NULL,
|
col54 varchar(85) NOT NULL,
|
col55 varchar(85) NOT NULL,
|
col56 varchar(85) NOT NULL,
|
col57 varchar(85) NOT NULL,
|
col58 varchar(85) NOT NULL,
|
col59 varchar(85) NOT NULL,
|
col60 varchar(85) NOT NULL,
|
col61 varchar(85) NOT NULL,
|
col62 varchar(85) NOT NULL,
|
col63 varchar(85) NOT NULL,
|
col64 varchar(85) NOT NULL,
|
col65 varchar(85) NOT NULL,
|
col66 varchar(85) NOT NULL,
|
col67 varchar(85) NOT NULL,
|
col68 varchar(85) NOT NULL,
|
col69 varchar(85) NOT NULL,
|
col70 varchar(85) NOT NULL,
|
col71 varchar(85) NOT NULL,
|
col72 varchar(85) NOT NULL,
|
col73 varchar(85) NOT NULL,
|
col74 varchar(85) NOT NULL,
|
col75 varchar(85) NOT NULL,
|
col76 varchar(85) NOT NULL,
|
col77 varchar(85) NOT NULL,
|
col78 varchar(85) NOT NULL,
|
col79 varchar(85) NOT NULL,
|
col80 varchar(85) NOT NULL,
|
col81 varchar(85) NOT NULL,
|
col82 varchar(85) NOT NULL,
|
col83 varchar(85) NOT NULL,
|
col84 varchar(85) NOT NULL,
|
col85 varchar(85) NOT NULL,
|
col86 varchar(85) NOT NULL,
|
col87 varchar(85) NOT NULL,
|
col88 varchar(85) NOT NULL,
|
col89 varchar(85) NOT NULL,
|
col90 varchar(85) NOT NULL,
|
col91 varchar(85) NOT NULL,
|
col92 varchar(85) NOT NULL,
|
col93 varchar(85) NOT NULL,
|
col94 varchar(85) NOT NULL,
|
col95 varchar(85) NOT NULL,
|
col96 varchar(85) NOT NULL,
|
col97 varchar(85) NOT NULL,
|
col98 varchar(85) NOT NULL,
|
col99 varchar(85) NOT NULL,
|
col100 varchar(85) NOT NULL,
|
col101 varchar(85) NOT NULL,
|
col102 varchar(85) NOT NULL,
|
col103 varchar(85) NOT NULL,
|
col104 varchar(85) NOT NULL,
|
col105 varchar(85) NOT NULL,
|
col106 varchar(85) NOT NULL,
|
col107 varchar(85) NOT NULL,
|
col108 varchar(85) NOT NULL,
|
col109 varchar(85) NOT NULL,
|
col110 varchar(85) NOT NULL,
|
col111 varchar(85) NOT NULL,
|
col112 varchar(85) NOT NULL,
|
col113 varchar(85) NOT NULL,
|
col114 varchar(85) NOT NULL,
|
col115 varchar(85) NOT NULL,
|
col116 varchar(85) NOT NULL,
|
col117 varchar(85) NOT NULL,
|
col118 varchar(85) NOT NULL,
|
col119 varchar(85) NOT NULL,
|
col120 varchar(85) NOT NULL,
|
col121 varchar(85) NOT NULL,
|
col122 varchar(85) NOT NULL,
|
col123 varchar(85) NOT NULL,
|
col124 varchar(85) NOT NULL,
|
col125 varchar(85) NOT NULL,
|
col126 varchar(85) NOT NULL,
|
col127 varchar(85) NOT NULL,
|
col128 varchar(85) NOT NULL,
|
col129 varchar(85) NOT NULL,
|
col130 varchar(85) NOT NULL,
|
col131 varchar(85) NOT NULL,
|
col132 varchar(85) NOT NULL,
|
col133 varchar(85) NOT NULL,
|
col134 varchar(85) NOT NULL,
|
col135 varchar(85) NOT NULL,
|
col136 varchar(85) NOT NULL,
|
col137 varchar(85) NOT NULL,
|
col138 varchar(85) NOT NULL,
|
col139 varchar(85) NOT NULL,
|
col140 varchar(85) NOT NULL,
|
col141 varchar(85) NOT NULL,
|
col142 varchar(85) NOT NULL,
|
col143 varchar(85) NOT NULL,
|
col144 varchar(85) NOT NULL,
|
col145 varchar(85) NOT NULL,
|
col146 varchar(85) NOT NULL,
|
col147 varchar(85) NOT NULL,
|
col148 varchar(85) NOT NULL,
|
col149 varchar(85) NOT NULL,
|
col150 varchar(85) NOT NULL,
|
col151 varchar(85) NOT NULL,
|
col152 varchar(85) NOT NULL,
|
col153 varchar(85) NOT NULL,
|
col154 varchar(85) NOT NULL,
|
col155 varchar(85) NOT NULL,
|
col156 varchar(85) NOT NULL,
|
col157 varchar(85) NOT NULL,
|
col158 varchar(85) NOT NULL,
|
col159 varchar(85) NOT NULL,
|
col160 varchar(85) NOT NULL,
|
col161 varchar(85) NOT NULL,
|
col162 varchar(85) NOT NULL,
|
col163 varchar(85) NOT NULL,
|
col164 varchar(85) NOT NULL,
|
col165 varchar(85) NOT NULL,
|
col166 varchar(85) NOT NULL,
|
col167 varchar(85) NOT NULL,
|
col168 varchar(85) NOT NULL,
|
col169 varchar(85) NOT NULL,
|
col170 varchar(85) NOT NULL,
|
col171 varchar(85) NOT NULL,
|
col172 varchar(85) NOT NULL,
|
col173 varchar(85) NOT NULL,
|
col174 varchar(85) NOT NULL,
|
col175 varchar(85) NOT NULL,
|
col176 varchar(85) NOT NULL,
|
col177 varchar(85) NOT NULL,
|
col178 varchar(85) NOT NULL,
|
col179 varchar(85) NOT NULL,
|
col180 varchar(85) NOT NULL,
|
col181 varchar(85) NOT NULL,
|
col182 varchar(85) NOT NULL,
|
col183 varchar(85) NOT NULL,
|
col184 varchar(85) NOT NULL,
|
col185 varchar(85) NOT NULL,
|
col186 varchar(85) NOT NULL,
|
col187 varchar(85) NOT NULL,
|
col188 varchar(85) NOT NULL,
|
col189 varchar(85) NOT NULL,
|
col190 varchar(85) NOT NULL,
|
col191 varchar(85) NOT NULL,
|
col192 varchar(85) NOT NULL,
|
col193 varchar(85) NOT NULL,
|
col194 varchar(85) NOT NULL,
|
col195 varchar(85) NOT NULL,
|
col196 varchar(85) NOT NULL,
|
col197 varchar(85) NOT NULL,
|
col198 varchar(85) NOT NULL,
|
col199 varchar(85) NOT NULL,
|
col200 varchar(85) NOT NULL,
|
col201 varchar(85) NOT NULL,
|
col202 varchar(85) NOT NULL,
|
col203 varchar(85) NOT NULL,
|
col204 varchar(85) NOT NULL,
|
col205 varchar(85) NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;
|
If these are changed to varchar(86), then the statement succeeds:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 varchar(86) NOT NULL,
|
col3 varchar(86) NOT NULL,
|
col4 varchar(86) NOT NULL,
|
col5 varchar(86) NOT NULL,
|
col6 varchar(86) NOT NULL,
|
col7 varchar(86) NOT NULL,
|
col8 varchar(86) NOT NULL,
|
col9 varchar(86) NOT NULL,
|
col10 varchar(86) NOT NULL,
|
col11 varchar(86) NOT NULL,
|
col12 varchar(86) NOT NULL,
|
col13 varchar(86) NOT NULL,
|
col14 varchar(86) NOT NULL,
|
col15 varchar(86) NOT NULL,
|
col16 varchar(86) NOT NULL,
|
col17 varchar(86) NOT NULL,
|
col18 varchar(86) NOT NULL,
|
col19 varchar(86) NOT NULL,
|
col20 varchar(86) NOT NULL,
|
col21 varchar(86) NOT NULL,
|
col22 varchar(86) NOT NULL,
|
col23 varchar(86) NOT NULL,
|
col24 varchar(86) NOT NULL,
|
col25 varchar(86) NOT NULL,
|
col26 varchar(86) NOT NULL,
|
col27 varchar(86) NOT NULL,
|
col28 varchar(86) NOT NULL,
|
col29 varchar(86) NOT NULL,
|
col30 varchar(86) NOT NULL,
|
col31 varchar(86) NOT NULL,
|
col32 varchar(86) NOT NULL,
|
col33 varchar(86) NOT NULL,
|
col34 varchar(86) NOT NULL,
|
col35 varchar(86) NOT NULL,
|
col36 varchar(86) NOT NULL,
|
col37 varchar(86) NOT NULL,
|
col38 varchar(86) NOT NULL,
|
col39 varchar(86) NOT NULL,
|
col40 varchar(86) NOT NULL,
|
col41 varchar(86) NOT NULL,
|
col42 varchar(86) NOT NULL,
|
col43 varchar(86) NOT NULL,
|
col44 varchar(86) NOT NULL,
|
col45 varchar(86) NOT NULL,
|
col46 varchar(86) NOT NULL,
|
col47 varchar(86) NOT NULL,
|
col48 varchar(86) NOT NULL,
|
col49 varchar(86) NOT NULL,
|
col50 varchar(86) NOT NULL,
|
col51 varchar(86) NOT NULL,
|
col52 varchar(86) NOT NULL,
|
col53 varchar(86) NOT NULL,
|
col54 varchar(86) NOT NULL,
|
col55 varchar(86) NOT NULL,
|
col56 varchar(86) NOT NULL,
|
col57 varchar(86) NOT NULL,
|
col58 varchar(86) NOT NULL,
|
col59 varchar(86) NOT NULL,
|
col60 varchar(86) NOT NULL,
|
col61 varchar(86) NOT NULL,
|
col62 varchar(86) NOT NULL,
|
col63 varchar(86) NOT NULL,
|
col64 varchar(86) NOT NULL,
|
col65 varchar(86) NOT NULL,
|
col66 varchar(86) NOT NULL,
|
col67 varchar(86) NOT NULL,
|
col68 varchar(86) NOT NULL,
|
col69 varchar(86) NOT NULL,
|
col70 varchar(86) NOT NULL,
|
col71 varchar(86) NOT NULL,
|
col72 varchar(86) NOT NULL,
|
col73 varchar(86) NOT NULL,
|
col74 varchar(86) NOT NULL,
|
col75 varchar(86) NOT NULL,
|
col76 varchar(86) NOT NULL,
|
col77 varchar(86) NOT NULL,
|
col78 varchar(86) NOT NULL,
|
col79 varchar(86) NOT NULL,
|
col80 varchar(86) NOT NULL,
|
col81 varchar(86) NOT NULL,
|
col82 varchar(86) NOT NULL,
|
col83 varchar(86) NOT NULL,
|
col84 varchar(86) NOT NULL,
|
col85 varchar(86) NOT NULL,
|
col86 varchar(86) NOT NULL,
|
col87 varchar(86) NOT NULL,
|
col88 varchar(86) NOT NULL,
|
col89 varchar(86) NOT NULL,
|
col90 varchar(86) NOT NULL,
|
col91 varchar(86) NOT NULL,
|
col92 varchar(86) NOT NULL,
|
col93 varchar(86) NOT NULL,
|
col94 varchar(86) NOT NULL,
|
col95 varchar(86) NOT NULL,
|
col96 varchar(86) NOT NULL,
|
col97 varchar(86) NOT NULL,
|
col98 varchar(86) NOT NULL,
|
col99 varchar(86) NOT NULL,
|
col100 varchar(86) NOT NULL,
|
col101 varchar(86) NOT NULL,
|
col102 varchar(86) NOT NULL,
|
col103 varchar(86) NOT NULL,
|
col104 varchar(86) NOT NULL,
|
col105 varchar(86) NOT NULL,
|
col106 varchar(86) NOT NULL,
|
col107 varchar(86) NOT NULL,
|
col108 varchar(86) NOT NULL,
|
col109 varchar(86) NOT NULL,
|
col110 varchar(86) NOT NULL,
|
col111 varchar(86) NOT NULL,
|
col112 varchar(86) NOT NULL,
|
col113 varchar(86) NOT NULL,
|
col114 varchar(86) NOT NULL,
|
col115 varchar(86) NOT NULL,
|
col116 varchar(86) NOT NULL,
|
col117 varchar(86) NOT NULL,
|
col118 varchar(86) NOT NULL,
|
col119 varchar(86) NOT NULL,
|
col120 varchar(86) NOT NULL,
|
col121 varchar(86) NOT NULL,
|
col122 varchar(86) NOT NULL,
|
col123 varchar(86) NOT NULL,
|
col124 varchar(86) NOT NULL,
|
col125 varchar(86) NOT NULL,
|
col126 varchar(86) NOT NULL,
|
col127 varchar(86) NOT NULL,
|
col128 varchar(86) NOT NULL,
|
col129 varchar(86) NOT NULL,
|
col130 varchar(86) NOT NULL,
|
col131 varchar(86) NOT NULL,
|
col132 varchar(86) NOT NULL,
|
col133 varchar(86) NOT NULL,
|
col134 varchar(86) NOT NULL,
|
col135 varchar(86) NOT NULL,
|
col136 varchar(86) NOT NULL,
|
col137 varchar(86) NOT NULL,
|
col138 varchar(86) NOT NULL,
|
col139 varchar(86) NOT NULL,
|
col140 varchar(86) NOT NULL,
|
col141 varchar(86) NOT NULL,
|
col142 varchar(86) NOT NULL,
|
col143 varchar(86) NOT NULL,
|
col144 varchar(86) NOT NULL,
|
col145 varchar(86) NOT NULL,
|
col146 varchar(86) NOT NULL,
|
col147 varchar(86) NOT NULL,
|
col148 varchar(86) NOT NULL,
|
col149 varchar(86) NOT NULL,
|
col150 varchar(86) NOT NULL,
|
col151 varchar(86) NOT NULL,
|
col152 varchar(86) NOT NULL,
|
col153 varchar(86) NOT NULL,
|
col154 varchar(86) NOT NULL,
|
col155 varchar(86) NOT NULL,
|
col156 varchar(86) NOT NULL,
|
col157 varchar(86) NOT NULL,
|
col158 varchar(86) NOT NULL,
|
col159 varchar(86) NOT NULL,
|
col160 varchar(86) NOT NULL,
|
col161 varchar(86) NOT NULL,
|
col162 varchar(86) NOT NULL,
|
col163 varchar(86) NOT NULL,
|
col164 varchar(86) NOT NULL,
|
col165 varchar(86) NOT NULL,
|
col166 varchar(86) NOT NULL,
|
col167 varchar(86) NOT NULL,
|
col168 varchar(86) NOT NULL,
|
col169 varchar(86) NOT NULL,
|
col170 varchar(86) NOT NULL,
|
col171 varchar(86) NOT NULL,
|
col172 varchar(86) NOT NULL,
|
col173 varchar(86) NOT NULL,
|
col174 varchar(86) NOT NULL,
|
col175 varchar(86) NOT NULL,
|
col176 varchar(86) NOT NULL,
|
col177 varchar(86) NOT NULL,
|
col178 varchar(86) NOT NULL,
|
col179 varchar(86) NOT NULL,
|
col180 varchar(86) NOT NULL,
|
col181 varchar(86) NOT NULL,
|
col182 varchar(86) NOT NULL,
|
col183 varchar(86) NOT NULL,
|
col184 varchar(86) NOT NULL,
|
col185 varchar(86) NOT NULL,
|
col186 varchar(86) NOT NULL,
|
col187 varchar(86) NOT NULL,
|
col188 varchar(86) NOT NULL,
|
col189 varchar(86) NOT NULL,
|
col190 varchar(86) NOT NULL,
|
col191 varchar(86) NOT NULL,
|
col192 varchar(86) NOT NULL,
|
col193 varchar(86) NOT NULL,
|
col194 varchar(86) NOT NULL,
|
col195 varchar(86) NOT NULL,
|
col196 varchar(86) NOT NULL,
|
col197 varchar(86) NOT NULL,
|
col198 varchar(86) NOT NULL,
|
col199 varchar(86) NOT NULL,
|
col200 varchar(86) NOT NULL,
|
col201 varchar(86) NOT NULL,
|
col202 varchar(86) NOT NULL,
|
col203 varchar(86) NOT NULL,
|
col204 varchar(86) NOT NULL,
|
col205 varchar(86) NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;
|
So it seems like the calculation to determine whether a varchar can be stored off-page is a bit wrong.
2.) Let's also reproduce this with an easier character set. Let's say that we have a table that uses the latin1 character set and that has a bunch of varchar(255) columns. Each of these columns will be:
255 chars/column * 1 bytes/char + 1 bytes (for length)/column
= 256 bytes
So a varchar(255) column that uses latin1 should be a candidate to be stored in an overflow page. However, this statement fails:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 varchar(255) NOT NULL,
|
col3 varchar(255) NOT NULL,
|
col4 varchar(255) NOT NULL,
|
col5 varchar(255) NOT NULL,
|
col6 varchar(255) NOT NULL,
|
col7 varchar(255) NOT NULL,
|
col8 varchar(255) NOT NULL,
|
col9 varchar(255) NOT NULL,
|
col10 varchar(255) NOT NULL,
|
col11 varchar(255) NOT NULL,
|
col12 varchar(255) NOT NULL,
|
col13 varchar(255) NOT NULL,
|
col14 varchar(255) NOT NULL,
|
col15 varchar(255) NOT NULL,
|
col16 varchar(255) NOT NULL,
|
col17 varchar(255) NOT NULL,
|
col18 varchar(255) NOT NULL,
|
col19 varchar(255) NOT NULL,
|
col20 varchar(255) NOT NULL,
|
col21 varchar(255) NOT NULL,
|
col22 varchar(255) NOT NULL,
|
col23 varchar(255) NOT NULL,
|
col24 varchar(255) NOT NULL,
|
col25 varchar(255) NOT NULL,
|
col26 varchar(255) NOT NULL,
|
col27 varchar(255) NOT NULL,
|
col28 varchar(255) NOT NULL,
|
col29 varchar(255) NOT NULL,
|
col30 varchar(255) NOT NULL,
|
col31 varchar(255) NOT NULL,
|
col32 varchar(255) NOT NULL,
|
col33 varchar(255) NOT NULL,
|
col34 varchar(255) NOT NULL,
|
col35 varchar(255) NOT NULL,
|
col36 varchar(255) NOT NULL,
|
col37 varchar(255) NOT NULL,
|
col38 varchar(255) NOT NULL,
|
col39 varchar(255) NOT NULL,
|
col40 varchar(255) NOT NULL,
|
col41 varchar(255) NOT NULL,
|
col42 varchar(255) NOT NULL,
|
col43 varchar(255) NOT NULL,
|
col44 varchar(255) NOT NULL,
|
col45 varchar(255) NOT NULL,
|
col46 varchar(255) NOT NULL,
|
col47 varchar(255) NOT NULL,
|
col48 varchar(255) NOT NULL,
|
col49 varchar(255) NOT NULL,
|
col50 varchar(255) NOT NULL,
|
col51 varchar(255) NOT NULL,
|
col52 varchar(255) NOT NULL,
|
col53 varchar(255) NOT NULL,
|
col54 varchar(255) NOT NULL,
|
col55 varchar(255) NOT NULL,
|
col56 varchar(255) NOT NULL,
|
col57 varchar(255) NOT NULL,
|
col58 varchar(255) NOT NULL,
|
col59 varchar(255) NOT NULL,
|
col60 varchar(255) NOT NULL,
|
col61 varchar(255) NOT NULL,
|
col62 varchar(255) NOT NULL,
|
col63 varchar(255) NOT NULL,
|
col64 varchar(255) NOT NULL,
|
col65 varchar(255) NOT NULL,
|
col66 varchar(255) NOT NULL,
|
col67 varchar(255) NOT NULL,
|
col68 varchar(255) NOT NULL,
|
col69 varchar(255) NOT NULL,
|
col70 varchar(255) NOT NULL,
|
col71 varchar(255) NOT NULL,
|
col72 varchar(255) NOT NULL,
|
col73 varchar(255) NOT NULL,
|
col74 varchar(255) NOT NULL,
|
col75 varchar(255) NOT NULL,
|
col76 varchar(255) NOT NULL,
|
col77 varchar(255) NOT NULL,
|
col78 varchar(255) NOT NULL,
|
col79 varchar(255) NOT NULL,
|
col80 varchar(255) NOT NULL,
|
col81 varchar(255) NOT NULL,
|
col82 varchar(255) NOT NULL,
|
col83 varchar(255) NOT NULL,
|
col84 varchar(255) NOT NULL,
|
col85 varchar(255) NOT NULL,
|
col86 varchar(255) NOT NULL,
|
col87 varchar(255) NOT NULL,
|
col88 varchar(255) NOT NULL,
|
col89 varchar(255) NOT NULL,
|
col90 varchar(255) NOT NULL,
|
col91 varchar(255) NOT NULL,
|
col92 varchar(255) NOT NULL,
|
col93 varchar(255) NOT NULL,
|
col94 varchar(255) NOT NULL,
|
col95 varchar(255) NOT NULL,
|
col96 varchar(255) NOT NULL,
|
col97 varchar(255) NOT NULL,
|
col98 varchar(255) NOT NULL,
|
col99 varchar(255) NOT NULL,
|
col100 varchar(255) NOT NULL,
|
col101 varchar(255) NOT NULL,
|
col102 varchar(255) NOT NULL,
|
col103 varchar(255) NOT NULL,
|
col104 varchar(255) NOT NULL,
|
col105 varchar(255) NOT NULL,
|
col106 varchar(255) NOT NULL,
|
col107 varchar(255) NOT NULL,
|
col108 varchar(255) NOT NULL,
|
col109 varchar(255) NOT NULL,
|
col110 varchar(255) NOT NULL,
|
col111 varchar(255) NOT NULL,
|
col112 varchar(255) NOT NULL,
|
col113 varchar(255) NOT NULL,
|
col114 varchar(255) NOT NULL,
|
col115 varchar(255) NOT NULL,
|
col116 varchar(255) NOT NULL,
|
col117 varchar(255) NOT NULL,
|
col118 varchar(255) NOT NULL,
|
col119 varchar(255) NOT NULL,
|
col120 varchar(255) NOT NULL,
|
col121 varchar(255) NOT NULL,
|
col122 varchar(255) NOT NULL,
|
col123 varchar(255) NOT NULL,
|
col124 varchar(255) NOT NULL,
|
col125 varchar(255) NOT NULL,
|
col126 varchar(255) NOT NULL,
|
col127 varchar(255) NOT NULL,
|
col128 varchar(255) NOT NULL,
|
col129 varchar(255) NOT NULL,
|
col130 varchar(255) NOT NULL,
|
col131 varchar(255) NOT NULL,
|
col132 varchar(255) NOT NULL,
|
col133 varchar(255) NOT NULL,
|
col134 varchar(255) NOT NULL,
|
col135 varchar(255) NOT NULL,
|
col136 varchar(255) NOT NULL,
|
col137 varchar(255) NOT NULL,
|
col138 varchar(255) NOT NULL,
|
col139 varchar(255) NOT NULL,
|
col140 varchar(255) NOT NULL,
|
col141 varchar(255) NOT NULL,
|
col142 varchar(255) NOT NULL,
|
col143 varchar(255) NOT NULL,
|
col144 varchar(255) NOT NULL,
|
col145 varchar(255) NOT NULL,
|
col146 varchar(255) NOT NULL,
|
col147 varchar(255) NOT NULL,
|
col148 varchar(255) NOT NULL,
|
col149 varchar(255) NOT NULL,
|
col150 varchar(255) NOT NULL,
|
col151 varchar(255) NOT NULL,
|
col152 varchar(255) NOT NULL,
|
col153 varchar(255) NOT NULL,
|
col154 varchar(255) NOT NULL,
|
col155 varchar(255) NOT NULL,
|
col156 varchar(255) NOT NULL,
|
col157 varchar(255) NOT NULL,
|
col158 varchar(255) NOT NULL,
|
col159 varchar(255) NOT NULL,
|
col160 varchar(255) NOT NULL,
|
col161 varchar(255) NOT NULL,
|
col162 varchar(255) NOT NULL,
|
col163 varchar(255) NOT NULL,
|
col164 varchar(255) NOT NULL,
|
col165 varchar(255) NOT NULL,
|
col166 varchar(255) NOT NULL,
|
col167 varchar(255) NOT NULL,
|
col168 varchar(255) NOT NULL,
|
col169 varchar(255) NOT NULL,
|
col170 varchar(255) NOT NULL,
|
col171 varchar(255) NOT NULL,
|
col172 varchar(255) NOT NULL,
|
col173 varchar(255) NOT NULL,
|
col174 varchar(255) NOT NULL,
|
col175 varchar(255) NOT NULL,
|
col176 varchar(255) NOT NULL,
|
col177 varchar(255) NOT NULL,
|
col178 varchar(255) NOT NULL,
|
col179 varchar(255) NOT NULL,
|
col180 varchar(255) NOT NULL,
|
col181 varchar(255) NOT NULL,
|
col182 varchar(255) NOT NULL,
|
col183 varchar(255) NOT NULL,
|
col184 varchar(255) NOT NULL,
|
col185 varchar(255) NOT NULL,
|
col186 varchar(255) NOT NULL,
|
col187 varchar(255) NOT NULL,
|
col188 varchar(255) NOT NULL,
|
col189 varchar(255) NOT NULL,
|
col190 varchar(255) NOT NULL,
|
col191 varchar(255) NOT NULL,
|
col192 varchar(255) NOT NULL,
|
col193 varchar(255) NOT NULL,
|
col194 varchar(255) NOT NULL,
|
col195 varchar(255) NOT NULL,
|
col196 varchar(255) NOT NULL,
|
col197 varchar(255) NOT NULL,
|
col198 varchar(255) NOT NULL,
|
col199 varchar(255) NOT NULL,
|
col200 varchar(255) NOT NULL,
|
col201 varchar(255) NOT NULL,
|
col202 varchar(255) NOT NULL,
|
col203 varchar(255) NOT NULL,
|
col204 varchar(255) NOT NULL,
|
col205 varchar(255) NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1;
|
If these are changed to varchar(256), then the statement succeeds:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 varchar(256) NOT NULL,
|
col3 varchar(256) NOT NULL,
|
col4 varchar(256) NOT NULL,
|
col5 varchar(256) NOT NULL,
|
col6 varchar(256) NOT NULL,
|
col7 varchar(256) NOT NULL,
|
col8 varchar(256) NOT NULL,
|
col9 varchar(256) NOT NULL,
|
col10 varchar(256) NOT NULL,
|
col11 varchar(256) NOT NULL,
|
col12 varchar(256) NOT NULL,
|
col13 varchar(256) NOT NULL,
|
col14 varchar(256) NOT NULL,
|
col15 varchar(256) NOT NULL,
|
col16 varchar(256) NOT NULL,
|
col17 varchar(256) NOT NULL,
|
col18 varchar(256) NOT NULL,
|
col19 varchar(256) NOT NULL,
|
col20 varchar(256) NOT NULL,
|
col21 varchar(256) NOT NULL,
|
col22 varchar(256) NOT NULL,
|
col23 varchar(256) NOT NULL,
|
col24 varchar(256) NOT NULL,
|
col25 varchar(256) NOT NULL,
|
col26 varchar(256) NOT NULL,
|
col27 varchar(256) NOT NULL,
|
col28 varchar(256) NOT NULL,
|
col29 varchar(256) NOT NULL,
|
col30 varchar(256) NOT NULL,
|
col31 varchar(256) NOT NULL,
|
col32 varchar(256) NOT NULL,
|
col33 varchar(256) NOT NULL,
|
col34 varchar(256) NOT NULL,
|
col35 varchar(256) NOT NULL,
|
col36 varchar(256) NOT NULL,
|
col37 varchar(256) NOT NULL,
|
col38 varchar(256) NOT NULL,
|
col39 varchar(256) NOT NULL,
|
col40 varchar(256) NOT NULL,
|
col41 varchar(256) NOT NULL,
|
col42 varchar(256) NOT NULL,
|
col43 varchar(256) NOT NULL,
|
col44 varchar(256) NOT NULL,
|
col45 varchar(256) NOT NULL,
|
col46 varchar(256) NOT NULL,
|
col47 varchar(256) NOT NULL,
|
col48 varchar(256) NOT NULL,
|
col49 varchar(256) NOT NULL,
|
col50 varchar(256) NOT NULL,
|
col51 varchar(256) NOT NULL,
|
col52 varchar(256) NOT NULL,
|
col53 varchar(256) NOT NULL,
|
col54 varchar(256) NOT NULL,
|
col55 varchar(256) NOT NULL,
|
col56 varchar(256) NOT NULL,
|
col57 varchar(256) NOT NULL,
|
col58 varchar(256) NOT NULL,
|
col59 varchar(256) NOT NULL,
|
col60 varchar(256) NOT NULL,
|
col61 varchar(256) NOT NULL,
|
col62 varchar(256) NOT NULL,
|
col63 varchar(256) NOT NULL,
|
col64 varchar(256) NOT NULL,
|
col65 varchar(256) NOT NULL,
|
col66 varchar(256) NOT NULL,
|
col67 varchar(256) NOT NULL,
|
col68 varchar(256) NOT NULL,
|
col69 varchar(256) NOT NULL,
|
col70 varchar(256) NOT NULL,
|
col71 varchar(256) NOT NULL,
|
col72 varchar(256) NOT NULL,
|
col73 varchar(256) NOT NULL,
|
col74 varchar(256) NOT NULL,
|
col75 varchar(256) NOT NULL,
|
col76 varchar(256) NOT NULL,
|
col77 varchar(256) NOT NULL,
|
col78 varchar(256) NOT NULL,
|
col79 varchar(256) NOT NULL,
|
col80 varchar(256) NOT NULL,
|
col81 varchar(256) NOT NULL,
|
col82 varchar(256) NOT NULL,
|
col83 varchar(256) NOT NULL,
|
col84 varchar(256) NOT NULL,
|
col85 varchar(256) NOT NULL,
|
col86 varchar(256) NOT NULL,
|
col87 varchar(256) NOT NULL,
|
col88 varchar(256) NOT NULL,
|
col89 varchar(256) NOT NULL,
|
col90 varchar(256) NOT NULL,
|
col91 varchar(256) NOT NULL,
|
col92 varchar(256) NOT NULL,
|
col93 varchar(256) NOT NULL,
|
col94 varchar(256) NOT NULL,
|
col95 varchar(256) NOT NULL,
|
col96 varchar(256) NOT NULL,
|
col97 varchar(256) NOT NULL,
|
col98 varchar(256) NOT NULL,
|
col99 varchar(256) NOT NULL,
|
col100 varchar(256) NOT NULL,
|
col101 varchar(256) NOT NULL,
|
col102 varchar(256) NOT NULL,
|
col103 varchar(256) NOT NULL,
|
col104 varchar(256) NOT NULL,
|
col105 varchar(256) NOT NULL,
|
col106 varchar(256) NOT NULL,
|
col107 varchar(256) NOT NULL,
|
col108 varchar(256) NOT NULL,
|
col109 varchar(256) NOT NULL,
|
col110 varchar(256) NOT NULL,
|
col111 varchar(256) NOT NULL,
|
col112 varchar(256) NOT NULL,
|
col113 varchar(256) NOT NULL,
|
col114 varchar(256) NOT NULL,
|
col115 varchar(256) NOT NULL,
|
col116 varchar(256) NOT NULL,
|
col117 varchar(256) NOT NULL,
|
col118 varchar(256) NOT NULL,
|
col119 varchar(256) NOT NULL,
|
col120 varchar(256) NOT NULL,
|
col121 varchar(256) NOT NULL,
|
col122 varchar(256) NOT NULL,
|
col123 varchar(256) NOT NULL,
|
col124 varchar(256) NOT NULL,
|
col125 varchar(256) NOT NULL,
|
col126 varchar(256) NOT NULL,
|
col127 varchar(256) NOT NULL,
|
col128 varchar(256) NOT NULL,
|
col129 varchar(256) NOT NULL,
|
col130 varchar(256) NOT NULL,
|
col131 varchar(256) NOT NULL,
|
col132 varchar(256) NOT NULL,
|
col133 varchar(256) NOT NULL,
|
col134 varchar(256) NOT NULL,
|
col135 varchar(256) NOT NULL,
|
col136 varchar(256) NOT NULL,
|
col137 varchar(256) NOT NULL,
|
col138 varchar(256) NOT NULL,
|
col139 varchar(256) NOT NULL,
|
col140 varchar(256) NOT NULL,
|
col141 varchar(256) NOT NULL,
|
col142 varchar(256) NOT NULL,
|
col143 varchar(256) NOT NULL,
|
col144 varchar(256) NOT NULL,
|
col145 varchar(256) NOT NULL,
|
col146 varchar(256) NOT NULL,
|
col147 varchar(256) NOT NULL,
|
col148 varchar(256) NOT NULL,
|
col149 varchar(256) NOT NULL,
|
col150 varchar(256) NOT NULL,
|
col151 varchar(256) NOT NULL,
|
col152 varchar(256) NOT NULL,
|
col153 varchar(256) NOT NULL,
|
col154 varchar(256) NOT NULL,
|
col155 varchar(256) NOT NULL,
|
col156 varchar(256) NOT NULL,
|
col157 varchar(256) NOT NULL,
|
col158 varchar(256) NOT NULL,
|
col159 varchar(256) NOT NULL,
|
col160 varchar(256) NOT NULL,
|
col161 varchar(256) NOT NULL,
|
col162 varchar(256) NOT NULL,
|
col163 varchar(256) NOT NULL,
|
col164 varchar(256) NOT NULL,
|
col165 varchar(256) NOT NULL,
|
col166 varchar(256) NOT NULL,
|
col167 varchar(256) NOT NULL,
|
col168 varchar(256) NOT NULL,
|
col169 varchar(256) NOT NULL,
|
col170 varchar(256) NOT NULL,
|
col171 varchar(256) NOT NULL,
|
col172 varchar(256) NOT NULL,
|
col173 varchar(256) NOT NULL,
|
col174 varchar(256) NOT NULL,
|
col175 varchar(256) NOT NULL,
|
col176 varchar(256) NOT NULL,
|
col177 varchar(256) NOT NULL,
|
col178 varchar(256) NOT NULL,
|
col179 varchar(256) NOT NULL,
|
col180 varchar(256) NOT NULL,
|
col181 varchar(256) NOT NULL,
|
col182 varchar(256) NOT NULL,
|
col183 varchar(256) NOT NULL,
|
col184 varchar(256) NOT NULL,
|
col185 varchar(256) NOT NULL,
|
col186 varchar(256) NOT NULL,
|
col187 varchar(256) NOT NULL,
|
col188 varchar(256) NOT NULL,
|
col189 varchar(256) NOT NULL,
|
col190 varchar(256) NOT NULL,
|
col191 varchar(256) NOT NULL,
|
col192 varchar(256) NOT NULL,
|
col193 varchar(256) NOT NULL,
|
col194 varchar(256) NOT NULL,
|
col195 varchar(256) NOT NULL,
|
col196 varchar(256) NOT NULL,
|
col197 varchar(256) NOT NULL,
|
col198 varchar(256) NOT NULL,
|
col199 varchar(256) NOT NULL,
|
col200 varchar(256) NOT NULL,
|
col201 varchar(256) NOT NULL,
|
col202 varchar(256) NOT NULL,
|
col203 varchar(256) NOT NULL,
|
col204 varchar(256) NOT NULL,
|
col205 varchar(256) NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1;
|
So that's more evidence that the calculation to determine whether a varchar can be stored off-page is a bit wrong.
Additionally, the patch doesn't fix this case that is mentioned in the MySQL documentation:
Fixed-length fields greater than or equal to 768 bytes are encoded as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html#innodb-row-format-dynamic
We can easily test this by trying to create a table uses the utf8mb4 character set and that has a bunch of char(255) columns. According to the MySQL documentation, these columns should be encoded as variable-length fields, which means that they could be stored off page. However, the following statement fails:
SET SESSION innodb_strict_mode=ON;
|
CREATE TABLE tab (
|
col1 varchar(40) NOT NULL,
|
col2 char(255) NOT NULL,
|
col3 char(255) NOT NULL,
|
col4 char(255) NOT NULL,
|
col5 char(255) NOT NULL,
|
col6 char(255) NOT NULL,
|
col7 char(255) NOT NULL,
|
col8 char(255) NOT NULL,
|
col9 char(255) NOT NULL,
|
col10 char(255) NOT NULL,
|
col11 char(255) NOT NULL,
|
col12 char(255) NOT NULL,
|
col13 char(255) NOT NULL,
|
col14 char(255) NOT NULL,
|
col15 char(255) NOT NULL,
|
col16 char(255) NOT NULL,
|
col17 char(255) NOT NULL,
|
col18 char(255) NOT NULL,
|
col19 char(255) NOT NULL,
|
col20 char(255) NOT NULL,
|
col21 char(255) NOT NULL,
|
col22 char(255) NOT NULL,
|
col23 char(255) NOT NULL,
|
col24 char(255) NOT NULL,
|
col25 char(255) NOT NULL,
|
col26 char(255) NOT NULL,
|
col27 char(255) NOT NULL,
|
col28 char(255) NOT NULL,
|
col29 char(255) NOT NULL,
|
col30 char(255) NOT NULL,
|
col31 char(255) NOT NULL,
|
col32 char(255) NOT NULL,
|
col33 char(255) NOT NULL,
|
col34 char(255) NOT NULL,
|
col35 char(255) NOT NULL,
|
col36 char(255) NOT NULL,
|
col37 char(255) NOT NULL,
|
col38 char(255) NOT NULL,
|
col39 char(255) NOT NULL,
|
col40 char(255) NOT NULL,
|
col41 char(255) NOT NULL,
|
col42 char(255) NOT NULL,
|
col43 char(255) NOT NULL,
|
col44 char(255) NOT NULL,
|
col45 char(255) NOT NULL,
|
col46 char(255) NOT NULL,
|
col47 char(255) NOT NULL,
|
col48 char(255) NOT NULL,
|
col49 char(255) NOT NULL,
|
col50 char(255) NOT NULL,
|
col51 char(255) NOT NULL,
|
col52 char(255) NOT NULL,
|
col53 char(255) NOT NULL,
|
col54 char(255) NOT NULL,
|
col55 char(255) NOT NULL,
|
col56 char(255) NOT NULL,
|
col57 char(255) NOT NULL,
|
col58 char(255) NOT NULL,
|
col59 char(255) NOT NULL,
|
col60 char(255) NOT NULL,
|
col61 char(255) NOT NULL,
|
col62 char(255) NOT NULL,
|
col63 char(255) NOT NULL,
|
col64 char(255) NOT NULL,
|
col65 char(255) NOT NULL,
|
col66 char(255) NOT NULL,
|
col67 char(255) NOT NULL,
|
col68 char(255) NOT NULL,
|
col69 char(255) NOT NULL,
|
col70 char(255) NOT NULL,
|
col71 char(255) NOT NULL,
|
col72 char(255) NOT NULL,
|
col73 char(255) NOT NULL,
|
col74 char(255) NOT NULL,
|
col75 char(255) NOT NULL,
|
col76 char(255) NOT NULL,
|
col77 char(255) NOT NULL,
|
col78 char(255) NOT NULL,
|
col79 char(255) NOT NULL,
|
col80 char(255) NOT NULL,
|
col81 char(255) NOT NULL,
|
col82 char(255) NOT NULL,
|
col83 char(255) NOT NULL,
|
col84 char(255) NOT NULL,
|
col85 char(255) NOT NULL,
|
col86 char(255) NOT NULL,
|
col87 char(255) NOT NULL,
|
col88 char(255) NOT NULL,
|
col89 char(255) NOT NULL,
|
col90 char(255) NOT NULL,
|
col91 char(255) NOT NULL,
|
col92 char(255) NOT NULL,
|
col93 char(255) NOT NULL,
|
col94 char(255) NOT NULL,
|
col95 char(255) NOT NULL,
|
col96 char(255) NOT NULL,
|
col97 char(255) NOT NULL,
|
col98 char(255) NOT NULL,
|
col99 char(255) NOT NULL,
|
col100 char(255) NOT NULL,
|
col101 char(255) NOT NULL,
|
col102 char(255) NOT NULL,
|
col103 char(255) NOT NULL,
|
col104 char(255) NOT NULL,
|
col105 char(255) NOT NULL,
|
col106 char(255) NOT NULL,
|
col107 char(255) NOT NULL,
|
col108 char(255) NOT NULL,
|
col109 char(255) NOT NULL,
|
col110 char(255) NOT NULL,
|
col111 char(255) NOT NULL,
|
col112 char(255) NOT NULL,
|
col113 char(255) NOT NULL,
|
col114 char(255) NOT NULL,
|
col115 char(255) NOT NULL,
|
col116 char(255) NOT NULL,
|
col117 char(255) NOT NULL,
|
col118 char(255) NOT NULL,
|
col119 char(255) NOT NULL,
|
col120 char(255) NOT NULL,
|
col121 char(255) NOT NULL,
|
col122 char(255) NOT NULL,
|
col123 char(255) NOT NULL,
|
col124 char(255) NOT NULL,
|
col125 char(255) NOT NULL,
|
col126 char(255) NOT NULL,
|
col127 char(255) NOT NULL,
|
col128 char(255) NOT NULL,
|
col129 char(255) NOT NULL,
|
col130 char(255) NOT NULL,
|
col131 char(255) NOT NULL,
|
col132 char(255) NOT NULL,
|
col133 char(255) NOT NULL,
|
col134 char(255) NOT NULL,
|
col135 char(255) NOT NULL,
|
col136 char(255) NOT NULL,
|
col137 char(255) NOT NULL,
|
col138 char(255) NOT NULL,
|
col139 char(255) NOT NULL,
|
col140 char(255) NOT NULL,
|
col141 char(255) NOT NULL,
|
col142 char(255) NOT NULL,
|
col143 char(255) NOT NULL,
|
col144 char(255) NOT NULL,
|
col145 char(255) NOT NULL,
|
col146 char(255) NOT NULL,
|
col147 char(255) NOT NULL,
|
col148 char(255) NOT NULL,
|
col149 char(255) NOT NULL,
|
col150 char(255) NOT NULL,
|
col151 char(255) NOT NULL,
|
col152 char(255) NOT NULL,
|
col153 char(255) NOT NULL,
|
col154 char(255) NOT NULL,
|
col155 char(255) NOT NULL,
|
col156 char(255) NOT NULL,
|
col157 char(255) NOT NULL,
|
col158 char(255) NOT NULL,
|
col159 char(255) NOT NULL,
|
col160 char(255) NOT NULL,
|
col161 char(255) NOT NULL,
|
col162 char(255) NOT NULL,
|
col163 char(255) NOT NULL,
|
col164 char(255) NOT NULL,
|
col165 char(255) NOT NULL,
|
col166 char(255) NOT NULL,
|
col167 char(255) NOT NULL,
|
col168 char(255) NOT NULL,
|
col169 char(255) NOT NULL,
|
col170 char(255) NOT NULL,
|
col171 char(255) NOT NULL,
|
col172 char(255) NOT NULL,
|
col173 char(255) NOT NULL,
|
col174 char(255) NOT NULL,
|
col175 char(255) NOT NULL,
|
col176 char(255) NOT NULL,
|
col177 char(255) NOT NULL,
|
col178 char(255) NOT NULL,
|
col179 char(255) NOT NULL,
|
col180 char(255) NOT NULL,
|
col181 char(255) NOT NULL,
|
col182 char(255) NOT NULL,
|
col183 char(255) NOT NULL,
|
col184 char(255) NOT NULL,
|
col185 char(255) NOT NULL,
|
col186 char(255) NOT NULL,
|
col187 char(255) NOT NULL,
|
col188 char(255) NOT NULL,
|
col189 char(255) NOT NULL,
|
col190 char(255) NOT NULL,
|
col191 char(255) NOT NULL,
|
col192 char(255) NOT NULL,
|
col193 char(255) NOT NULL,
|
col194 char(255) NOT NULL,
|
col195 char(255) NOT NULL,
|
col196 char(255) NOT NULL,
|
col197 char(255) NOT NULL,
|
col198 char(255) NOT NULL,
|
col199 char(255) NOT NULL,
|
col200 char(255) NOT NULL,
|
col201 char(255) NOT NULL,
|
col202 char(255) NOT NULL,
|
col203 char(255) NOT NULL,
|
col204 char(255) NOT NULL,
|
col205 char(255) NOT NULL,
|
PRIMARY KEY (col1)
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;
|
So it looks like this patch does not take this specific detail into account.
Thanks for your feedback GeoffMontee!
Right, varchar(85) charset utf8mb3 is 1 byte length and 255 of actual data. But only varchars with max actual data is bigger than 255 are stored in overflow pages. If this is not clear from documentation it should be fixed. So, behaviour you've described is not buggy.
GeoffMontee in your second example with char(255) charset utf8mb4 you see an error which comes from Server and not from InnoDB. You've hit the limitation of Server row format where max size of a row is 65536 and such fields are stored as 1020 bytes. So, you can put no more that roughly 62 such fields in a table.
When I shrink the number of fields I can see that InnoDB indeed transforms such fields to varchar(255) charset utf8mb4 which has the maximum data length of 1020 which makes them candidates to put in overflow pages and InnoDB really does so when computing a max row size for create table. Thus, I see no bug here.
Hi kevg,
Right, varchar(85) charset utf8mb3 is 1 byte length and 255 of actual data. But only varchars with max actual data is bigger than 255 are stored in overflow pages. If this is not clear from documentation it should be fixed. So, behaviour you've described is not buggy.
OK, thanks. This has been clarified in the documentation.
in your second example with char(255) charset utf8mb4 you see an error which comes from Server and not from InnoDB. You've hit the limitation of Server row format where max size of a row is 65536 and such fields are stored as 1020 bytes. So, you can put no more that roughly 62 such fields in a table.
When I shrink the number of fields I can see that InnoDB indeed transforms such fields to varchar(255) charset utf8mb4 which has the maximum data length of 1020 which makes them candidates to put in overflow pages and InnoDB really does so when computing a max row size for create table. Thus, I see no bug here.
Ah, right. That makes sense.
Thanks!
Still experiencing this on 10.4.7+maria~xenial
2019-08-09 5:15:35 38 [ERROR] InnoDB: Cannot add field `poc_2_phone_b` in table `zabbix`.`host_inventory` because after adding it, the row size is 8246 which is greater than maximum allowed size (8126) for a record on index leaf page.
Experiencing this while importing the default zabbix database schema included with 4.2.
See the following for more reports of this issue still occurring on mariadb versions that are reported above in this bug as "fixed" versions:
https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/383531-cannot-add-field-poc_2_phone_b-in-table-host_inventory-on-db-startup
https://support.zabbix.com/browse/ZBX-16465
Zabbix schema which triggers this is attached.
I see that this is the relevant table schema:
CREATE TABLE `host_inventory` (
|
`hostid` bigint unsigned NOT NULL,
|
`inventory_mode` integer DEFAULT '0' NOT NULL,
|
`type` varchar(64) DEFAULT '' NOT NULL,
|
`type_full` varchar(64) DEFAULT '' NOT NULL,
|
`name` varchar(64) DEFAULT '' NOT NULL,
|
`alias` varchar(64) DEFAULT '' NOT NULL,
|
`os` varchar(64) DEFAULT '' NOT NULL,
|
`os_full` varchar(255) DEFAULT '' NOT NULL,
|
`os_short` varchar(64) DEFAULT '' NOT NULL,
|
`serialno_a` varchar(64) DEFAULT '' NOT NULL,
|
`serialno_b` varchar(64) DEFAULT '' NOT NULL,
|
`tag` varchar(64) DEFAULT '' NOT NULL,
|
`asset_tag` varchar(64) DEFAULT '' NOT NULL,
|
`macaddress_a` varchar(64) DEFAULT '' NOT NULL,
|
`macaddress_b` varchar(64) DEFAULT '' NOT NULL,
|
`hardware` varchar(255) DEFAULT '' NOT NULL,
|
`hardware_full` text NOT NULL,
|
`software` varchar(255) DEFAULT '' NOT NULL,
|
`software_full` text NOT NULL,
|
`software_app_a` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_b` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_c` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_d` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_e` varchar(64) DEFAULT '' NOT NULL,
|
`contact` text NOT NULL,
|
`location` text NOT NULL,
|
`location_lat` varchar(16) DEFAULT '' NOT NULL,
|
`location_lon` varchar(16) DEFAULT '' NOT NULL,
|
`notes` text NOT NULL,
|
`chassis` varchar(64) DEFAULT '' NOT NULL,
|
`model` varchar(64) DEFAULT '' NOT NULL,
|
`hw_arch` varchar(32) DEFAULT '' NOT NULL,
|
`vendor` varchar(64) DEFAULT '' NOT NULL,
|
`contract_number` varchar(64) DEFAULT '' NOT NULL,
|
`installer_name` varchar(64) DEFAULT '' NOT NULL,
|
`deployment_status` varchar(64) DEFAULT '' NOT NULL,
|
`url_a` varchar(255) DEFAULT '' NOT NULL,
|
`url_b` varchar(255) DEFAULT '' NOT NULL,
|
`url_c` varchar(255) DEFAULT '' NOT NULL,
|
`host_networks` text NOT NULL,
|
`host_netmask` varchar(39) DEFAULT '' NOT NULL,
|
`host_router` varchar(39) DEFAULT '' NOT NULL,
|
`oob_ip` varchar(39) DEFAULT '' NOT NULL,
|
`oob_netmask` varchar(39) DEFAULT '' NOT NULL,
|
`oob_router` varchar(39) DEFAULT '' NOT NULL,
|
`date_hw_purchase` varchar(64) DEFAULT '' NOT NULL,
|
`date_hw_install` varchar(64) DEFAULT '' NOT NULL,
|
`date_hw_expiry` varchar(64) DEFAULT '' NOT NULL,
|
`date_hw_decomm` varchar(64) DEFAULT '' NOT NULL,
|
`site_address_a` varchar(128) DEFAULT '' NOT NULL,
|
`site_address_b` varchar(128) DEFAULT '' NOT NULL,
|
`site_address_c` varchar(128) DEFAULT '' NOT NULL,
|
`site_city` varchar(128) DEFAULT '' NOT NULL,
|
`site_state` varchar(64) DEFAULT '' NOT NULL,
|
`site_country` varchar(64) DEFAULT '' NOT NULL,
|
`site_zip` varchar(64) DEFAULT '' NOT NULL,
|
`site_rack` varchar(128) DEFAULT '' NOT NULL,
|
`site_notes` text NOT NULL,
|
`poc_1_name` varchar(128) DEFAULT '' NOT NULL,
|
`poc_1_email` varchar(128) DEFAULT '' NOT NULL,
|
`poc_1_phone_a` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_phone_b` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_cell` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_screen` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_notes` text NOT NULL,
|
`poc_2_name` varchar(128) DEFAULT '' NOT NULL,
|
`poc_2_email` varchar(128) DEFAULT '' NOT NULL,
|
`poc_2_phone_a` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_phone_b` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_cell` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_screen` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_notes` text NOT NULL,
|
PRIMARY KEY (hostid)
|
) ENGINE=InnoDB;
|
The problem here is that InnoDB has a maximum row size in MariaDB and MySQL that is roughly equivalent to half of innodb_page_size. InnoDB's row formats work around this problem by storing certain kinds of variable-length columns on overflow pages. However, different row formats can store different types of data on overflow pages. The DYNAMIC row format is the default row format in MariaDB 10.2 and later. For varchar columns, this row format can only store them on overflow pages if they are 256 bytes or longer:
All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See Maximum Row Size for more information about the other factors that can contribute to the maximum row size for InnoDB tables.
In the DYNAMIC row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, can be completely stored in overflow pages.
InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.
For BLOB and TEXT columns, only values longer than 40 bytes are considered for storage on overflow pages. For VARBINARY and VARCHAR columns, only values longer than 255 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards these limits. These limits are only based on the length of the actual column's data.
These limits differ from the limits for the COMPACT row format, where the limit is 767 bytes for all types.
Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.
If a column is chosen to be stored on overflow pages, then the entire value of the column is stored on overflow pages, and only a 20-byte pointer to the column's first overflow page is stored on the main page. Each overflow page is the size of innodb_page_size. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.
This behavior differs from the behavior of the COMPACT row format, which always stores the column prefix on the main page. This allows tables using the DYNAMIC row format to contain a high number of columns using the VARBINARY, VARCHAR, BLOB and TEXT data types.
Prior to MariaDB 10.2.26, 10.3.17, and 10.4.7, MariaDB didn't properly calculate the row sizes while executing DDL, so "unsafe" tables could be created, even with innodb_strict_mode=ON set. This was fixed by MDEV-19292. As a side effect, tables that could be created in previous versions may get rejected after the latest releases.
You have two options here:
1.) Disable InnoDB strict mode. i.e. this succeeds in MariaDB 10.2.26:
SET SESSION innodb_strict_mode=OFF;
|
|
|
CREATE TABLE `host_inventory` (
|
`hostid` bigint unsigned NOT NULL,
|
`inventory_mode` integer DEFAULT '0' NOT NULL,
|
`type` varchar(64) DEFAULT '' NOT NULL,
|
`type_full` varchar(64) DEFAULT '' NOT NULL,
|
`name` varchar(64) DEFAULT '' NOT NULL,
|
`alias` varchar(64) DEFAULT '' NOT NULL,
|
`os` varchar(64) DEFAULT '' NOT NULL,
|
`os_full` varchar(255) DEFAULT '' NOT NULL,
|
`os_short` varchar(64) DEFAULT '' NOT NULL,
|
`serialno_a` varchar(64) DEFAULT '' NOT NULL,
|
`serialno_b` varchar(64) DEFAULT '' NOT NULL,
|
`tag` varchar(64) DEFAULT '' NOT NULL,
|
`asset_tag` varchar(64) DEFAULT '' NOT NULL,
|
`macaddress_a` varchar(64) DEFAULT '' NOT NULL,
|
`macaddress_b` varchar(64) DEFAULT '' NOT NULL,
|
`hardware` varchar(255) DEFAULT '' NOT NULL,
|
`hardware_full` text NOT NULL,
|
`software` varchar(255) DEFAULT '' NOT NULL,
|
`software_full` text NOT NULL,
|
`software_app_a` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_b` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_c` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_d` varchar(64) DEFAULT '' NOT NULL,
|
`software_app_e` varchar(64) DEFAULT '' NOT NULL,
|
`contact` text NOT NULL,
|
`location` text NOT NULL,
|
`location_lat` varchar(16) DEFAULT '' NOT NULL,
|
`location_lon` varchar(16) DEFAULT '' NOT NULL,
|
`notes` text NOT NULL,
|
`chassis` varchar(64) DEFAULT '' NOT NULL,
|
`model` varchar(64) DEFAULT '' NOT NULL,
|
`hw_arch` varchar(32) DEFAULT '' NOT NULL,
|
`vendor` varchar(64) DEFAULT '' NOT NULL,
|
`contract_number` varchar(64) DEFAULT '' NOT NULL,
|
`installer_name` varchar(64) DEFAULT '' NOT NULL,
|
`deployment_status` varchar(64) DEFAULT '' NOT NULL,
|
`url_a` varchar(255) DEFAULT '' NOT NULL,
|
`url_b` varchar(255) DEFAULT '' NOT NULL,
|
`url_c` varchar(255) DEFAULT '' NOT NULL,
|
`host_networks` text NOT NULL,
|
`host_netmask` varchar(39) DEFAULT '' NOT NULL,
|
`host_router` varchar(39) DEFAULT '' NOT NULL,
|
`oob_ip` varchar(39) DEFAULT '' NOT NULL,
|
`oob_netmask` varchar(39) DEFAULT '' NOT NULL,
|
`oob_router` varchar(39) DEFAULT '' NOT NULL,
|
`date_hw_purchase` varchar(64) DEFAULT '' NOT NULL,
|
`date_hw_install` varchar(64) DEFAULT '' NOT NULL,
|
`date_hw_expiry` varchar(64) DEFAULT '' NOT NULL,
|
`date_hw_decomm` varchar(64) DEFAULT '' NOT NULL,
|
`site_address_a` varchar(128) DEFAULT '' NOT NULL,
|
`site_address_b` varchar(128) DEFAULT '' NOT NULL,
|
`site_address_c` varchar(128) DEFAULT '' NOT NULL,
|
`site_city` varchar(128) DEFAULT '' NOT NULL,
|
`site_state` varchar(64) DEFAULT '' NOT NULL,
|
`site_country` varchar(64) DEFAULT '' NOT NULL,
|
`site_zip` varchar(64) DEFAULT '' NOT NULL,
|
`site_rack` varchar(128) DEFAULT '' NOT NULL,
|
`site_notes` text NOT NULL,
|
`poc_1_name` varchar(128) DEFAULT '' NOT NULL,
|
`poc_1_email` varchar(128) DEFAULT '' NOT NULL,
|
`poc_1_phone_a` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_phone_b` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_cell` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_screen` varchar(64) DEFAULT '' NOT NULL,
|
`poc_1_notes` text NOT NULL,
|
`poc_2_name` varchar(128) DEFAULT '' NOT NULL,
|
`poc_2_email` varchar(128) DEFAULT '' NOT NULL,
|
`poc_2_phone_a` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_phone_b` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_cell` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_screen` varchar(64) DEFAULT '' NOT NULL,
|
`poc_2_notes` text NOT NULL,
|
PRIMARY KEY (hostid)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
|
2.) Keep InnoDB strict mode enabled, but change some of those varchar(N) columns to varchar(N >= 256 bytes). If the table's default character set is utf8mb4, then that means that N would have to be 64 or more. i.e. this also succeeds in MariaDB 10.2.26:
SET SESSION innodb_strict_mode=ON;
|
|
|
CREATE TABLE `host_inventory` (
|
`hostid` bigint unsigned NOT NULL,
|
`inventory_mode` integer DEFAULT '0' NOT NULL,
|
`type` varchar(255) DEFAULT '' NOT NULL,
|
`type_full` varchar(255) DEFAULT '' NOT NULL,
|
`name` varchar(255) DEFAULT '' NOT NULL,
|
`alias` varchar(255) DEFAULT '' NOT NULL,
|
`os` varchar(255) DEFAULT '' NOT NULL,
|
`os_full` varchar(255) DEFAULT '' NOT NULL,
|
`os_short` varchar(255) DEFAULT '' NOT NULL,
|
`serialno_a` varchar(255) DEFAULT '' NOT NULL,
|
`serialno_b` varchar(255) DEFAULT '' NOT NULL,
|
`tag` varchar(255) DEFAULT '' NOT NULL,
|
`asset_tag` varchar(255) DEFAULT '' NOT NULL,
|
`macaddress_a` varchar(255) DEFAULT '' NOT NULL,
|
`macaddress_b` varchar(255) DEFAULT '' NOT NULL,
|
`hardware` varchar(255) DEFAULT '' NOT NULL,
|
`hardware_full` text NOT NULL,
|
`software` varchar(255) DEFAULT '' NOT NULL,
|
`software_full` text NOT NULL,
|
`software_app_a` varchar(255) DEFAULT '' NOT NULL,
|
`software_app_b` varchar(255) DEFAULT '' NOT NULL,
|
`software_app_c` varchar(255) DEFAULT '' NOT NULL,
|
`software_app_d` varchar(255) DEFAULT '' NOT NULL,
|
`software_app_e` varchar(255) DEFAULT '' NOT NULL,
|
`contact` text NOT NULL,
|
`location` text NOT NULL,
|
`location_lat` varchar(255) DEFAULT '' NOT NULL,
|
`location_lon` varchar(255) DEFAULT '' NOT NULL,
|
`notes` text NOT NULL,
|
`chassis` varchar(255) DEFAULT '' NOT NULL,
|
`model` varchar(255) DEFAULT '' NOT NULL,
|
`hw_arch` varchar(255) DEFAULT '' NOT NULL,
|
`vendor` varchar(255) DEFAULT '' NOT NULL,
|
`contract_number` varchar(255) DEFAULT '' NOT NULL,
|
`installer_name` varchar(255) DEFAULT '' NOT NULL,
|
`deployment_status` varchar(255) DEFAULT '' NOT NULL,
|
`url_a` varchar(255) DEFAULT '' NOT NULL,
|
`url_b` varchar(255) DEFAULT '' NOT NULL,
|
`url_c` varchar(255) DEFAULT '' NOT NULL,
|
`host_networks` text NOT NULL,
|
`host_netmask` varchar(255) DEFAULT '' NOT NULL,
|
`host_router` varchar(255) DEFAULT '' NOT NULL,
|
`oob_ip` varchar(255) DEFAULT '' NOT NULL,
|
`oob_netmask` varchar(255) DEFAULT '' NOT NULL,
|
`oob_router` varchar(255) DEFAULT '' NOT NULL,
|
`date_hw_purchase` varchar(255) DEFAULT '' NOT NULL,
|
`date_hw_install` varchar(255) DEFAULT '' NOT NULL,
|
`date_hw_expiry` varchar(255) DEFAULT '' NOT NULL,
|
`date_hw_decomm` varchar(255) DEFAULT '' NOT NULL,
|
`site_address_a` varchar(255) DEFAULT '' NOT NULL,
|
`site_address_b` varchar(255) DEFAULT '' NOT NULL,
|
`site_address_c` varchar(255) DEFAULT '' NOT NULL,
|
`site_city` varchar(255) DEFAULT '' NOT NULL,
|
`site_state` varchar(255) DEFAULT '' NOT NULL,
|
`site_country` varchar(255) DEFAULT '' NOT NULL,
|
`site_zip` varchar(255) DEFAULT '' NOT NULL,
|
`site_rack` varchar(255) DEFAULT '' NOT NULL,
|
`site_notes` text NOT NULL,
|
`poc_1_name` varchar(255) DEFAULT '' NOT NULL,
|
`poc_1_email` varchar(255) DEFAULT '' NOT NULL,
|
`poc_1_phone_a` varchar(255) DEFAULT '' NOT NULL,
|
`poc_1_phone_b` varchar(255) DEFAULT '' NOT NULL,
|
`poc_1_cell` varchar(255) DEFAULT '' NOT NULL,
|
`poc_1_screen` varchar(255) DEFAULT '' NOT NULL,
|
`poc_1_notes` text NOT NULL,
|
`poc_2_name` varchar(255) DEFAULT '' NOT NULL,
|
`poc_2_email` varchar(255) DEFAULT '' NOT NULL,
|
`poc_2_phone_a` varchar(255) DEFAULT '' NOT NULL,
|
`poc_2_phone_b` varchar(255) DEFAULT '' NOT NULL,
|
`poc_2_cell` varchar(255) DEFAULT '' NOT NULL,
|
`poc_2_screen` varchar(255) DEFAULT '' NOT NULL,
|
`poc_2_notes` text NOT NULL,
|
PRIMARY KEY (hostid)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
|
We have added some documentation about side effects that this bug fix can have, and how to work around those side effects:
I understand the change for newly created tables - they cannot be created like they were in versions prior to 10.3.17. But what about existing tables which were created on previous version - for example 10.3.15 - and then the server was upgraded to 10.3.17.?
Are they functional?
Will they be in future versions?
I am asking namely because I see a lot of errors like this:
2019-09-25 20:03:40 10 [Warning] InnoDB: Cannot add field `location` in table `zabbix`.`host_inventory` because after adding it, the row size is 8772 which is greater than maximum allowed size (8126) for a record on index leaf page.
At this time there was no attempt to create table it was logged at the time of mariadb restart. So this is old existing table.
Must all these old tables be recreated?
Or is is safe to leave it as it is?
Or is it safe to permanently use innodb_strict_mode=OFF? Are will this be problem in the near future? I am asking this because another problem on some hosting servers where are many client's databases with incompatible tables and we cannot change them. Only customers can change them but as you can imagine, hosting customers will never do.
Hi Lachman,
I understand the change for newly created tables - they cannot be created like they were in versions prior to 10.3.17. But what about existing tables which were created on previous version - for example 10.3.15 - and then the server was upgraded to 10.3.17.?
Are they functional?
Yes, existing tables will still be functional after upgrading. However, if any existing tables are giving "row size too large" errors or warnings, then there may be edge cases where INSERT or UPDATE operations will fail with "row size too large" errors. If you want to prevent that, then you should try to fix the tables. Information on how to fix them can be found here:
I am asking namely because I see a lot of errors like this:
2019-09-25 20:03:40 10 [Warning] InnoDB: Cannot add field `location` in table `zabbix`.`host_inventory` because after adding it, the row size is 8772 which is greater than maximum allowed size (8126) for a record on index leaf page.
At this time there was no attempt to create table it was logged at the time of mariadb restart. So this is old existing table.
This specific warning is only supposed to be logged during DDL. However, it is incorrectly being logged in other cases as well. I believe that kevg plans to fix that as part of the fix for MDEV-20194.
Must all these old tables be recreated?
Or is is safe to leave it as it is?
Or is it safe to permanently use innodb_strict_mode=OFF? Are will this be problem in the near future?
I would not recommend leaving the tables as-is and just setting innodb_strict_mode=OFF. If you are seeing "row size too large" errors or warnings, then that means that there may be edge cases where INSERT or UPDATE operations will fail with "row size too large" errors. The safer option would be to fix the tables.
I am asking this because another problem on some hosting servers where are many client's databases with incompatible tables and we cannot change them. Only customers can change them but as you can imagine, hosting customers will never do.
I can see the problem. If you detect any issues with a customer's tables, then you may just want to let them know about the problem. You could also refer them to the following documentation pages/sections:
https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/
This looks like the upstream bug for the same problem:
https://bugs.mysql.com/bug.php?id=79941