InnoDB ERROR 1118 (42000) – Troubleshooting Row Size Too Large Errors with InnoDB
InnoDB has a maximum row size in both MySQL and MariaDB, Which is almost equivalent to the half of innodb_page_size value. So what happens when you create a InnoDB row beyond the maximum row size limit ? You will immediately hit the 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. We have copied below an example to explain the scenario and also explained work around for the same error in this post:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 |
MariaDB [mdblab]> CREATE OR REPLACE TABLE mytab ( -> column1 varchar(40) NOT NULL, -> column2 varchar(40) NOT NULL, -> column3 varchar(40) NOT NULL, -> column4 varchar(40) NOT NULL, -> column5 varchar(40) NOT NULL, -> column6 varchar(40) NOT NULL, -> column7 varchar(40) NOT NULL, -> column8 varchar(40) NOT NULL, -> column9 varchar(40) NOT NULL, -> column10 varchar(40) NOT NULL, -> column11 varchar(40) NOT NULL, -> column12 varchar(40) NOT NULL, -> column13 varchar(40) NOT NULL, -> column14 varchar(40) NOT NULL, -> column15 varchar(40) NOT NULL, -> column16 varchar(40) NOT NULL, -> column17 varchar(40) NOT NULL, -> column18 varchar(40) NOT NULL, -> column19 varchar(40) NOT NULL, -> column20 varchar(40) NOT NULL, -> column21 varchar(40) NOT NULL, -> column22 varchar(40) NOT NULL, -> column23 varchar(40) NOT NULL, -> column24 varchar(40) NOT NULL, -> column25 varchar(40) NOT NULL, -> column26 varchar(40) NOT NULL, -> column27 varchar(40) NOT NULL, -> column28 varchar(40) NOT NULL, -> column29 varchar(40) NOT NULL, -> column30 varchar(40) NOT NULL, -> column31 varchar(40) NOT NULL, -> column32 varchar(40) NOT NULL, -> column33 varchar(40) NOT NULL, -> column34 varchar(40) NOT NULL, -> column35 varchar(40) NOT NULL, -> column36 varchar(40) NOT NULL, -> column37 varchar(40) NOT NULL, -> column38 varchar(40) NOT NULL, -> column39 varchar(40) NOT NULL, -> column40 varchar(40) NOT NULL, -> column41 varchar(40) NOT NULL, -> column42 varchar(40) NOT NULL, -> column43 varchar(40) NOT NULL, -> column44 varchar(40) NOT NULL, -> column45 varchar(40) NOT NULL, -> column46 varchar(40) NOT NULL, -> column47 varchar(40) NOT NULL, -> column48 varchar(40) NOT NULL, -> column49 varchar(40) NOT NULL, -> column50 varchar(40) NOT NULL, -> column51 varchar(40) NOT NULL, -> column52 varchar(40) NOT NULL, -> column53 varchar(40) NOT NULL, -> column54 varchar(40) NOT NULL, -> column55 varchar(40) NOT NULL, -> column56 varchar(40) NOT NULL, -> column57 varchar(40) NOT NULL, -> column58 varchar(40) NOT NULL, -> column59 varchar(40) NOT NULL, -> column60 varchar(40) NOT NULL, -> column61 varchar(40) NOT NULL, -> column62 varchar(40) NOT NULL, -> column63 varchar(40) NOT NULL, -> column64 varchar(40) NOT NULL, -> column65 varchar(40) NOT NULL, -> column66 varchar(40) NOT NULL, -> column67 varchar(40) NOT NULL, -> column68 varchar(40) NOT NULL, -> column69 varchar(40) NOT NULL, -> column70 varchar(40) NOT NULL, -> column71 varchar(40) NOT NULL, -> column72 varchar(40) NOT NULL, -> column73 varchar(40) NOT NULL, -> column74 varchar(40) NOT NULL, -> column75 varchar(40) NOT NULL, -> column76 varchar(40) NOT NULL, -> column77 varchar(40) NOT NULL, -> column78 varchar(40) NOT NULL, -> column79 varchar(40) NOT NULL, -> column80 varchar(40) NOT NULL, -> column81 varchar(40) NOT NULL, -> column82 varchar(40) NOT NULL, -> column83 varchar(40) NOT NULL, -> column84 varchar(40) NOT NULL, -> column85 varchar(40) NOT NULL, -> column86 varchar(40) NOT NULL, -> column87 varchar(40) NOT NULL, -> column88 varchar(40) NOT NULL, -> column89 varchar(40) NOT NULL, -> column90 varchar(40) NOT NULL, -> column91 varchar(40) NOT NULL, -> column92 varchar(40) NOT NULL, -> column93 varchar(40) NOT NULL, -> column94 varchar(40) NOT NULL, -> column95 varchar(40) NOT NULL, -> column96 varchar(40) NOT NULL, -> column97 varchar(40) NOT NULL, -> column98 varchar(40) NOT NULL, -> column99 varchar(40) NOT NULL, -> column100 varchar(40) NOT NULL, -> column101 varchar(40) NOT NULL, -> column102 varchar(40) NOT NULL, -> column103 varchar(40) NOT NULL, -> column104 varchar(40) NOT NULL, -> column105 varchar(40) NOT NULL, -> column106 varchar(40) NOT NULL, -> column107 varchar(40) NOT NULL, -> column108 varchar(40) NOT NULL, -> column109 varchar(40) NOT NULL, -> column110 varchar(40) NOT NULL, -> column111 varchar(40) NOT NULL, -> column112 varchar(40) NOT NULL, -> column113 varchar(40) NOT NULL, -> column114 varchar(40) NOT NULL, -> column115 varchar(40) NOT NULL, -> column116 varchar(40) NOT NULL, -> column117 varchar(40) NOT NULL, -> column118 varchar(40) NOT NULL, -> column119 varchar(40) NOT NULL, -> column120 varchar(40) NOT NULL, -> column121 varchar(40) NOT NULL, -> column122 varchar(40) NOT NULL, -> column123 varchar(40) NOT NULL, -> column124 varchar(40) NOT NULL, -> column125 varchar(40) NOT NULL, -> column126 varchar(40) NOT NULL, -> column127 varchar(40) NOT NULL, -> column128 varchar(40) NOT NULL, -> column129 varchar(40) NOT NULL, -> column130 varchar(40) NOT NULL, -> column131 varchar(40) NOT NULL, -> column132 varchar(40) NOT NULL, -> column133 varchar(40) NOT NULL, -> column134 varchar(40) NOT NULL, -> column135 varchar(40) NOT NULL, -> column136 varchar(40) NOT NULL, -> column137 varchar(40) NOT NULL, -> column138 varchar(40) NOT NULL, -> column139 varchar(40) NOT NULL, -> column140 varchar(40) NOT NULL, -> column141 varchar(40) NOT NULL, -> column142 varchar(40) NOT NULL, -> column143 varchar(40) NOT NULL, -> column144 varchar(40) NOT NULL, -> column145 varchar(40) NOT NULL, -> column146 varchar(40) NOT NULL, -> column147 varchar(40) NOT NULL, -> column148 varchar(40) NOT NULL, -> column149 varchar(40) NOT NULL, -> column150 varchar(40) NOT NULL, -> column151 varchar(40) NOT NULL, -> column152 varchar(40) NOT NULL, -> column153 varchar(40) NOT NULL, -> column154 varchar(40) NOT NULL, -> column155 varchar(40) NOT NULL, -> column156 varchar(40) NOT NULL, -> column157 varchar(40) NOT NULL, -> column158 varchar(40) NOT NULL, -> column159 varchar(40) NOT NULL, -> column160 varchar(40) NOT NULL, -> column161 varchar(40) NOT NULL, -> column162 varchar(40) NOT NULL, -> column163 varchar(40) NOT NULL, -> column164 varchar(40) NOT NULL, -> column165 varchar(40) NOT NULL, -> column166 varchar(40) NOT NULL, -> column167 varchar(40) NOT NULL, -> column168 varchar(40) NOT NULL, -> column169 varchar(40) NOT NULL, -> column170 varchar(40) NOT NULL, -> column171 varchar(40) NOT NULL, -> column172 varchar(40) NOT NULL, -> column173 varchar(40) NOT NULL, -> column174 varchar(40) NOT NULL, -> column175 varchar(40) NOT NULL, -> column176 varchar(40) NOT NULL, -> column177 varchar(40) NOT NULL, -> column178 varchar(40) NOT NULL, -> column179 varchar(40) NOT NULL, -> column180 varchar(40) NOT NULL, -> column181 varchar(40) NOT NULL, -> column182 varchar(40) NOT NULL, -> column183 varchar(40) NOT NULL, -> column184 varchar(40) NOT NULL, -> column185 varchar(40) NOT NULL, -> column186 varchar(40) NOT NULL, -> column187 varchar(40) NOT NULL, -> column188 varchar(40) NOT NULL, -> column189 varchar(40) NOT NULL, -> column190 varchar(40) NOT NULL, -> column191 varchar(40) NOT NULL, -> column192 varchar(40) NOT NULL, -> column193 varchar(40) NOT NULL, -> column194 varchar(40) NOT NULL, -> column195 varchar(40) NOT NULL, -> column196 varchar(40) NOT NULL, -> column197 varchar(40) NOT NULL, -> column198 varchar(40) NOT NULL, -> PRIMARY KEY (column1) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. MariaDB [mdblab]> |
There are several factors to determine the maximum row size of an InnoDB table. MariaDB also enforces maximum row size limit for all the storage engine and this applies to InnoDB tables also. If you try to create a table that exceeds MariaDB’s global limit on tables maximum row size, then you will see an error like what we have copied below:
|
1 2 3 4 5 |
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs MariaDB [mdblab]> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 |
MariaDB [mdblab]> CREATE OR REPLACE TABLE mytab ( -> column1 varchar(256) NOT NULL, -> column2 varchar(256) NOT NULL, -> column3 varchar(256) NOT NULL, -> column4 varchar(256) NOT NULL, -> column5 varchar(256) NOT NULL, -> column6 varchar(256) NOT NULL, -> column7 varchar(256) NOT NULL, -> column8 varchar(256) NOT NULL, -> column9 varchar(256) NOT NULL, -> column10 varchar(256) NOT NULL, -> column11 varchar(256) NOT NULL, -> column12 varchar(256) NOT NULL, -> column13 varchar(256) NOT NULL, -> column14 varchar(256) NOT NULL, -> column15 varchar(256) NOT NULL, -> column16 varchar(256) NOT NULL, -> column17 varchar(256) NOT NULL, -> column18 varchar(256) NOT NULL, -> column19 varchar(256) NOT NULL, -> column20 varchar(256) NOT NULL, -> column21 varchar(256) NOT NULL, -> column22 varchar(256) NOT NULL, -> column23 varchar(256) NOT NULL, -> column24 varchar(256) NOT NULL, -> column25 varchar(256) NOT NULL, -> column26 varchar(256) NOT NULL, -> column27 varchar(256) NOT NULL, -> column28 varchar(256) NOT NULL, -> column29 varchar(256) NOT NULL, -> column30 varchar(256) NOT NULL, -> column31 varchar(256) NOT NULL, -> column32 varchar(256) NOT NULL, -> column33 varchar(256) NOT NULL, -> column34 varchar(256) NOT NULL, -> column35 varchar(256) NOT NULL, -> column36 varchar(256) NOT NULL, -> column37 varchar(256) NOT NULL, -> column38 varchar(256) NOT NULL, -> column39 varchar(256) NOT NULL, -> column40 varchar(256) NOT NULL, -> column41 varchar(256) NOT NULL, -> column42 varchar(256) NOT NULL, -> column43 varchar(256) NOT NULL, -> column44 varchar(256) NOT NULL, -> column45 varchar(256) NOT NULL, -> column46 varchar(256) NOT NULL, -> column47 varchar(256) NOT NULL, -> column48 varchar(256) NOT NULL, -> column49 varchar(256) NOT NULL, -> column50 varchar(256) NOT NULL, -> column51 varchar(256) NOT NULL, -> column52 varchar(256) NOT NULL, -> column53 varchar(256) NOT NULL, -> column54 varchar(256) NOT NULL, -> column55 varchar(256) NOT NULL, -> column56 varchar(256) NOT NULL, -> column57 varchar(256) NOT NULL, -> column58 varchar(256) NOT NULL, -> column59 varchar(256) NOT NULL, -> column60 varchar(256) NOT NULL, -> column61 varchar(256) NOT NULL, -> column62 varchar(256) NOT NULL, -> column63 varchar(256) NOT NULL, -> column64 varchar(256) NOT NULL, -> column65 varchar(256) NOT NULL, -> column66 varchar(256) NOT NULL, -> column67 varchar(256) NOT NULL, -> column68 varchar(256) NOT NULL, -> column69 varchar(256) NOT NULL, -> column70 varchar(256) NOT NULL, -> column71 varchar(256) NOT NULL, -> column72 varchar(256) NOT NULL, -> column73 varchar(256) NOT NULL, -> column74 varchar(256) NOT NULL, -> column75 varchar(256) NOT NULL, -> column76 varchar(256) NOT NULL, -> column77 varchar(256) NOT NULL, -> column78 varchar(256) NOT NULL, -> column79 varchar(256) NOT NULL, -> column80 varchar(256) NOT NULL, -> column81 varchar(256) NOT NULL, -> column82 varchar(256) NOT NULL, -> column83 varchar(256) NOT NULL, -> column84 varchar(256) NOT NULL, -> column85 varchar(256) NOT NULL, -> column86 varchar(256) NOT NULL, -> column87 varchar(256) NOT NULL, -> column88 varchar(256) NOT NULL, -> column89 varchar(256) NOT NULL, -> column90 varchar(256) NOT NULL, -> column91 varchar(256) NOT NULL, -> column92 varchar(256) NOT NULL, -> column93 varchar(256) NOT NULL, -> column94 varchar(256) NOT NULL, -> column95 varchar(256) NOT NULL, -> column96 varchar(256) NOT NULL, -> column97 varchar(256) NOT NULL, -> column98 varchar(256) NOT NULL, -> column99 varchar(256) NOT NULL, -> column100 varchar(256) NOT NULL, -> column101 varchar(256) NOT NULL, -> column102 varchar(256) NOT NULL, -> column103 varchar(256) NOT NULL, -> column104 varchar(256) NOT NULL, -> column105 varchar(256) NOT NULL, -> column106 varchar(256) NOT NULL, -> column107 varchar(256) NOT NULL, -> column108 varchar(256) NOT NULL, -> column109 varchar(256) NOT NULL, -> column110 varchar(256) NOT NULL, -> column111 varchar(256) NOT NULL, -> column112 varchar(256) NOT NULL, -> column113 varchar(256) NOT NULL, -> column114 varchar(256) NOT NULL, -> column115 varchar(256) NOT NULL, -> column116 varchar(256) NOT NULL, -> column117 varchar(256) NOT NULL, -> column118 varchar(256) NOT NULL, -> column119 varchar(256) NOT NULL, -> column120 varchar(256) NOT NULL, -> column121 varchar(256) NOT NULL, -> column122 varchar(256) NOT NULL, -> column123 varchar(256) NOT NULL, -> column124 varchar(256) NOT NULL, -> column125 varchar(256) NOT NULL, -> column126 varchar(256) NOT NULL, -> column127 varchar(256) NOT NULL, -> column128 varchar(256) NOT NULL, -> column129 varchar(256) NOT NULL, -> column130 varchar(256) NOT NULL, -> column131 varchar(256) NOT NULL, -> column132 varchar(256) NOT NULL, -> column133 varchar(256) NOT NULL, -> column134 varchar(256) NOT NULL, -> column135 varchar(256) NOT NULL, -> column136 varchar(256) NOT NULL, -> column137 varchar(256) NOT NULL, -> column138 varchar(256) NOT NULL, -> column139 varchar(256) NOT NULL, -> column140 varchar(256) NOT NULL, -> column141 varchar(256) NOT NULL, -> column142 varchar(256) NOT NULL, -> column143 varchar(256) NOT NULL, -> column144 varchar(256) NOT NULL, -> column145 varchar(256) NOT NULL, -> column146 varchar(256) NOT NULL, -> column147 varchar(256) NOT NULL, -> column148 varchar(256) NOT NULL, -> column149 varchar(256) NOT NULL, -> column150 varchar(256) NOT NULL, -> column151 varchar(256) NOT NULL, -> column152 varchar(256) NOT NULL, -> column153 varchar(256) NOT NULL, -> column154 varchar(256) NOT NULL, -> column155 varchar(256) NOT NULL, -> column156 varchar(256) NOT NULL, -> column157 varchar(256) NOT NULL, -> column158 varchar(256) NOT NULL, -> column159 varchar(256) NOT NULL, -> column160 varchar(256) NOT NULL, -> column161 varchar(256) NOT NULL, -> column162 varchar(256) NOT NULL, -> column163 varchar(256) NOT NULL, -> column164 varchar(256) NOT NULL, -> column165 varchar(256) NOT NULL, -> column166 varchar(256) NOT NULL, -> column167 varchar(256) NOT NULL, -> column168 varchar(256) NOT NULL, -> column169 varchar(256) NOT NULL, -> column170 varchar(256) NOT NULL, -> column171 varchar(256) NOT NULL, -> column172 varchar(256) NOT NULL, -> column173 varchar(256) NOT NULL, -> column174 varchar(256) NOT NULL, -> column175 varchar(256) NOT NULL, -> column176 varchar(256) NOT NULL, -> column177 varchar(256) NOT NULL, -> column178 varchar(256) NOT NULL, -> column179 varchar(256) NOT NULL, -> column180 varchar(256) NOT NULL, -> column181 varchar(256) NOT NULL, -> column182 varchar(256) NOT NULL, -> column183 varchar(256) NOT NULL, -> column184 varchar(256) NOT NULL, -> column185 varchar(256) NOT NULL, -> column186 varchar(256) NOT NULL, -> column187 varchar(256) NOT NULL, -> column188 varchar(256) NOT NULL, -> column189 varchar(256) NOT NULL, -> column190 varchar(256) NOT NULL, -> column191 varchar(256) NOT NULL, -> column192 varchar(256) NOT NULL, -> column193 varchar(256) NOT NULL, -> column194 varchar(256) NOT NULL, -> column195 varchar(256) NOT NULL, -> column196 varchar(256) NOT NULL, -> column197 varchar(256) NOT NULL, -> column198 varchar(256) NOT NULL, -> PRIMARY KEY (column1) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected, 1 warning (0.032 sec) MariaDB [mdblab]> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 139 | Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) |
What is the root cause of the error, InnoDB ERROR 1118 (42000) – Troubleshooting Row Size Too Large Errors with InnoDB ?
The root cause of this issue is InnoDB has maximum row size that is roughly equivalent to half of innodb_page_size and you have created a InnoDB row beyond the maximum row size limit. All InnoDB row formats can store certain kinds of data in the overflow pages  and this makes maximum row size of an InnoDB possible than maximum amount of data that can be stored in the row’s main data page. InnoDB only considers using overflow pages if the table’s row size is more than half of innodb_page_size  and when row size is greater than this, InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.
How to solve this problem ?
There are several ways you can fix this error. If your table is either in REDUNDANT or COMPACT row format, then you can solve by converting table to use DYNAMIC row format.
when using InnoDB’s DYNAMIC row format and a default character set of latin1 (which requires up to 1 byte per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(256)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 |
MariaDB [mdblab]> SET GLOBAL innodb_default_row_format='dynamic'; Query OK, 0 rows affected (0.000 sec) MariaDB [mdblab]> SET SESSION innodb_strict_mode=ON; Query OK, 0 rows affected (0.000 sec) CREATE OR REPLACE TABLE mytab ( column1 varchar(256) NOT NULL, column2 varchar(256) NOT NULL, column3 varchar(256) NOT NULL, column4 varchar(256) NOT NULL, column5 varchar(256) NOT NULL, column6 varchar(256) NOT NULL, column7 varchar(256) NOT NULL, column8 varchar(256) NOT NULL, column9 varchar(256) NOT NULL, column10 varchar(256) NOT NULL, column11 varchar(256) NOT NULL, column12 varchar(256) NOT NULL, column13 varchar(256) NOT NULL, column14 varchar(256) NOT NULL, column15 varchar(256) NOT NULL, column16 varchar(256) NOT NULL, column17 varchar(256) NOT NULL, column18 varchar(256) NOT NULL, column19 varchar(256) NOT NULL, column20 varchar(256) NOT NULL, column21 varchar(256) NOT NULL, column22 varchar(256) NOT NULL, column23 varchar(256) NOT NULL, column24 varchar(256) NOT NULL, column25 varchar(256) NOT NULL, column26 varchar(256) NOT NULL, column27 varchar(256) NOT NULL, column28 varchar(256) NOT NULL, column29 varchar(256) NOT NULL, column30 varchar(256) NOT NULL, column31 varchar(256) NOT NULL, column32 varchar(256) NOT NULL, column33 varchar(256) NOT NULL, column34 varchar(256) NOT NULL, column35 varchar(256) NOT NULL, column36 varchar(256) NOT NULL, column37 varchar(256) NOT NULL, column38 varchar(256) NOT NULL, column39 varchar(256) NOT NULL, column40 varchar(256) NOT NULL, column41 varchar(256) NOT NULL, column42 varchar(256) NOT NULL, column43 varchar(256) NOT NULL, column44 varchar(256) NOT NULL, column45 varchar(256) NOT NULL, column46 varchar(256) NOT NULL, column47 varchar(256) NOT NULL, column48 varchar(256) NOT NULL, column49 varchar(256) NOT NULL, column50 varchar(256) NOT NULL, column51 varchar(256) NOT NULL, column52 varchar(256) NOT NULL, column53 varchar(256) NOT NULL, column54 varchar(256) NOT NULL, column55 varchar(256) NOT NULL, column56 varchar(256) NOT NULL, column57 varchar(256) NOT NULL, column58 varchar(256) NOT NULL, column59 varchar(256) NOT NULL, column60 varchar(256) NOT NULL, column61 varchar(256) NOT NULL, column62 varchar(256) NOT NULL, column63 varchar(256) NOT NULL, column64 varchar(256) NOT NULL, column65 varchar(256) NOT NULL, column66 varchar(256) NOT NULL, column67 varchar(256) NOT NULL, column68 varchar(256) NOT NULL, column69 varchar(256) NOT NULL, column70 varchar(256) NOT NULL, column71 varchar(256) NOT NULL, column72 varchar(256) NOT NULL, column73 varchar(256) NOT NULL, column74 varchar(256) NOT NULL, column75 varchar(256) NOT NULL, column76 varchar(256) NOT NULL, column77 varchar(256) NOT NULL, column78 varchar(256) NOT NULL, column79 varchar(256) NOT NULL, column80 varchar(256) NOT NULL, column81 varchar(256) NOT NULL, column82 varchar(256) NOT NULL, column83 varchar(256) NOT NULL, column84 varchar(256) NOT NULL, column85 varchar(256) NOT NULL, column86 varchar(256) NOT NULL, column87 varchar(256) NOT NULL, column88 varchar(256) NOT NULL, column89 varchar(256) NOT NULL, column90 varchar(256) NOT NULL, column91 varchar(256) NOT NULL, column92 varchar(256) NOT NULL, column93 varchar(256) NOT NULL, column94 varchar(256) NOT NULL, column95 varchar(256) NOT NULL, column96 varchar(256) NOT NULL, column97 varchar(256) NOT NULL, column98 varchar(256) NOT NULL, column99 varchar(256) NOT NULL, column100 varchar(256) NOT NULL, column101 varchar(256) NOT NULL, column102 varchar(256) NOT NULL, column103 varchar(256) NOT NULL, column104 varchar(256) NOT NULL, column105 varchar(256) NOT NULL, column106 varchar(256) NOT NULL, column107 varchar(256) NOT NULL, column108 varchar(256) NOT NULL, column109 varchar(256) NOT NULL, column110 varchar(256) NOT NULL, column111 varchar(256) NOT NULL, column112 varchar(256) NOT NULL, column113 varchar(256) NOT NULL, column114 varchar(256) NOT NULL, column115 varchar(256) NOT NULL, column116 varchar(256) NOT NULL, column117 varchar(256) NOT NULL, column118 varchar(256) NOT NULL, column119 varchar(256) NOT NULL, column120 varchar(256) NOT NULL, column121 varchar(256) NOT NULL, column122 varchar(256) NOT NULL, column123 varchar(256) NOT NULL, column124 varchar(256) NOT NULL, column125 varchar(256) NOT NULL, column126 varchar(256) NOT NULL, column127 varchar(256) NOT NULL, column128 varchar(256) NOT NULL, column129 varchar(256) NOT NULL, column130 varchar(256) NOT NULL, column131 varchar(256) NOT NULL, column132 varchar(256) NOT NULL, column133 varchar(256) NOT NULL, column134 varchar(256) NOT NULL, column135 varchar(256) NOT NULL, column136 varchar(256) NOT NULL, column137 varchar(256) NOT NULL, column138 varchar(256) NOT NULL, column139 varchar(256) NOT NULL, column140 varchar(256) NOT NULL, column141 varchar(256) NOT NULL, column142 varchar(256) NOT NULL, column143 varchar(256) NOT NULL, column144 varchar(256) NOT NULL, column145 varchar(256) NOT NULL, column146 varchar(256) NOT NULL, column147 varchar(256) NOT NULL, column148 varchar(256) NOT NULL, column149 varchar(256) NOT NULL, column150 varchar(256) NOT NULL, column151 varchar(256) NOT NULL, column152 varchar(256) NOT NULL, column153 varchar(256) NOT NULL, column154 varchar(256) NOT NULL, column155 varchar(256) NOT NULL, column156 varchar(256) NOT NULL, column157 varchar(256) NOT NULL, column158 varchar(256) NOT NULL, column159 varchar(256) NOT NULL, column160 varchar(256) NOT NULL, column161 varchar(256) NOT NULL, column162 varchar(256) NOT NULL, column163 varchar(256) NOT NULL, column164 varchar(256) NOT NULL, column165 varchar(256) NOT NULL, column166 varchar(256) NOT NULL, column167 varchar(256) NOT NULL, column168 varchar(256) NOT NULL, column169 varchar(256) NOT NULL, column170 varchar(256) NOT NULL, column171 varchar(256) NOT NULL, column172 varchar(256) NOT NULL, column173 varchar(256) NOT NULL, column174 varchar(256) NOT NULL, column175 varchar(256) NOT NULL, column176 varchar(256) NOT NULL, column177 varchar(256) NOT NULL, column178 varchar(256) NOT NULL, column179 varchar(256) NOT NULL, column180 varchar(256) NOT NULL, column181 varchar(256) NOT NULL, column182 varchar(256) NOT NULL, column183 varchar(256) NOT NULL, column184 varchar(256) NOT NULL, column185 varchar(256) NOT NULL, column186 varchar(256) NOT NULL, column187 varchar(256) NOT NULL, column188 varchar(256) NOT NULL, column189 varchar(256) NOT NULL, column190 varchar(256) NOT NULL, column191 varchar(256) NOT NULL, column192 varchar(256) NOT NULL, column193 varchar(256) NOT NULL, column194 varchar(256) NOT NULL, column195 varchar(256) NOT NULL, column196 varchar(256) NOT NULL, column197 varchar(256) NOT NULL, column198 varchar(256) NOT NULL, PRIMARY KEY (column1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.013 sec) |
when using InnoDB’s DYNAMIC row format and a default character set of utf8 (which requires up to 3 bytes per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(86):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 |
CREATE OR REPLACE TABLE mytab ( column1 varchar(86) NOT NULL, column2 varchar(86) NOT NULL, column3 varchar(86) NOT NULL, column4 varchar(86) NOT NULL, column5 varchar(86) NOT NULL, column6 varchar(86) NOT NULL, column7 varchar(86) NOT NULL, column8 varchar(86) NOT NULL, column9 varchar(86) NOT NULL, column10 varchar(86) NOT NULL, column11 varchar(86) NOT NULL, column12 varchar(86) NOT NULL, column13 varchar(86) NOT NULL, column14 varchar(86) NOT NULL, column15 varchar(86) NOT NULL, column16 varchar(86) NOT NULL, column17 varchar(86) NOT NULL, column18 varchar(86) NOT NULL, column19 varchar(86) NOT NULL, column20 varchar(86) NOT NULL, column21 varchar(86) NOT NULL, column22 varchar(86) NOT NULL, column23 varchar(86) NOT NULL, column24 varchar(86) NOT NULL, column25 varchar(86) NOT NULL, column26 varchar(86) NOT NULL, column27 varchar(86) NOT NULL, column28 varchar(86) NOT NULL, column29 varchar(86) NOT NULL, column30 varchar(86) NOT NULL, column31 varchar(86) NOT NULL, column32 varchar(86) NOT NULL, column33 varchar(86) NOT NULL, column34 varchar(86) NOT NULL, column35 varchar(86) NOT NULL, column36 varchar(86) NOT NULL, column37 varchar(86) NOT NULL, column38 varchar(86) NOT NULL, column39 varchar(86) NOT NULL, column40 varchar(86) NOT NULL, column41 varchar(86) NOT NULL, column42 varchar(86) NOT NULL, column43 varchar(86) NOT NULL, column44 varchar(86) NOT NULL, column45 varchar(86) NOT NULL, column46 varchar(86) NOT NULL, column47 varchar(86) NOT NULL, column48 varchar(86) NOT NULL, column49 varchar(86) NOT NULL, column50 varchar(86) NOT NULL, column51 varchar(86) NOT NULL, column52 varchar(86) NOT NULL, column53 varchar(86) NOT NULL, column54 varchar(86) NOT NULL, column55 varchar(86) NOT NULL, column56 varchar(86) NOT NULL, column57 varchar(86) NOT NULL, column58 varchar(86) NOT NULL, column59 varchar(86) NOT NULL, column60 varchar(86) NOT NULL, column61 varchar(86) NOT NULL, column62 varchar(86) NOT NULL, column63 varchar(86) NOT NULL, column64 varchar(86) NOT NULL, column65 varchar(86) NOT NULL, column66 varchar(86) NOT NULL, column67 varchar(86) NOT NULL, column68 varchar(86) NOT NULL, column69 varchar(86) NOT NULL, column70 varchar(86) NOT NULL, column71 varchar(86) NOT NULL, column72 varchar(86) NOT NULL, column73 varchar(86) NOT NULL, column74 varchar(86) NOT NULL, column75 varchar(86) NOT NULL, column76 varchar(86) NOT NULL, column77 varchar(86) NOT NULL, column78 varchar(86) NOT NULL, column79 varchar(86) NOT NULL, column80 varchar(86) NOT NULL, column81 varchar(86) NOT NULL, column82 varchar(86) NOT NULL, column83 varchar(86) NOT NULL, column84 varchar(86) NOT NULL, column85 varchar(86) NOT NULL, column86 varchar(86) NOT NULL, column87 varchar(86) NOT NULL, column88 varchar(86) NOT NULL, column89 varchar(86) NOT NULL, column90 varchar(86) NOT NULL, column91 varchar(86) NOT NULL, column92 varchar(86) NOT NULL, column93 varchar(86) NOT NULL, column94 varchar(86) NOT NULL, column95 varchar(86) NOT NULL, column96 varchar(86) NOT NULL, column97 varchar(86) NOT NULL, column98 varchar(86) NOT NULL, column99 varchar(86) NOT NULL, column100 varchar(86) NOT NULL, column101 varchar(86) NOT NULL, column102 varchar(86) NOT NULL, column103 varchar(86) NOT NULL, column104 varchar(86) NOT NULL, column105 varchar(86) NOT NULL, column106 varchar(86) NOT NULL, column107 varchar(86) NOT NULL, column108 varchar(86) NOT NULL, column109 varchar(86) NOT NULL, column110 varchar(86) NOT NULL, column111 varchar(86) NOT NULL, column112 varchar(86) NOT NULL, column113 varchar(86) NOT NULL, column114 varchar(86) NOT NULL, column115 varchar(86) NOT NULL, column116 varchar(86) NOT NULL, column117 varchar(86) NOT NULL, column118 varchar(86) NOT NULL, column119 varchar(86) NOT NULL, column120 varchar(86) NOT NULL, column121 varchar(86) NOT NULL, column122 varchar(86) NOT NULL, column123 varchar(86) NOT NULL, column124 varchar(86) NOT NULL, column125 varchar(86) NOT NULL, column126 varchar(86) NOT NULL, column127 varchar(86) NOT NULL, column128 varchar(86) NOT NULL, column129 varchar(86) NOT NULL, column130 varchar(86) NOT NULL, column131 varchar(86) NOT NULL, column132 varchar(86) NOT NULL, column133 varchar(86) NOT NULL, column134 varchar(86) NOT NULL, column135 varchar(86) NOT NULL, column136 varchar(86) NOT NULL, column137 varchar(86) NOT NULL, column138 varchar(86) NOT NULL, column139 varchar(86) NOT NULL, column140 varchar(86) NOT NULL, column141 varchar(86) NOT NULL, column142 varchar(86) NOT NULL, column143 varchar(86) NOT NULL, column144 varchar(86) NOT NULL, column145 varchar(86) NOT NULL, column146 varchar(86) NOT NULL, column147 varchar(86) NOT NULL, column148 varchar(86) NOT NULL, column149 varchar(86) NOT NULL, column150 varchar(86) NOT NULL, column151 varchar(86) NOT NULL, column152 varchar(86) NOT NULL, column153 varchar(86) NOT NULL, column154 varchar(86) NOT NULL, column155 varchar(86) NOT NULL, column156 varchar(86) NOT NULL, column157 varchar(86) NOT NULL, column158 varchar(86) NOT NULL, column159 varchar(86) NOT NULL, column160 varchar(86) NOT NULL, column161 varchar(86) NOT NULL, column162 varchar(86) NOT NULL, column163 varchar(86) NOT NULL, column164 varchar(86) NOT NULL, column165 varchar(86) NOT NULL, column166 varchar(86) NOT NULL, column167 varchar(86) NOT NULL, column168 varchar(86) NOT NULL, column169 varchar(86) NOT NULL, column170 varchar(86) NOT NULL, column171 varchar(86) NOT NULL, column172 varchar(86) NOT NULL, column173 varchar(86) NOT NULL, column174 varchar(86) NOT NULL, column175 varchar(86) NOT NULL, column176 varchar(86) NOT NULL, column177 varchar(86) NOT NULL, column178 varchar(86) NOT NULL, column179 varchar(86) NOT NULL, column180 varchar(86) NOT NULL, column181 varchar(86) NOT NULL, column182 varchar(86) NOT NULL, column183 varchar(86) NOT NULL, column184 varchar(86) NOT NULL, column185 varchar(86) NOT NULL, column186 varchar(86) NOT NULL, column187 varchar(86) NOT NULL, column188 varchar(86) NOT NULL, column189 varchar(86) NOT NULL, column190 varchar(86) NOT NULL, column191 varchar(86) NOT NULL, column192 varchar(86) NOT NULL, column193 varchar(86) NOT NULL, column194 varchar(86) NOT NULL, column195 varchar(86) NOT NULL, column196 varchar(86) NOT NULL, column197 varchar(86) NOT NULL, column198 varchar(86) NOT NULL, PRIMARY KEY (column1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.048 sec) MariaDB [mdblab]> |
There are several factors to determine the maximum row size of an InnoDB table. MariaDB also enforces maximum row size limit for all the storage engine and this applies to InnoDB tables also. If you try to create a table that exceeds MariaDB’s global limit on tables maximum row size, then you will see an error like what we have copied below:
|
1 2 3 4 5 |
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs MariaDB [mdblab]> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 |
MariaDB [mdblab]> CREATE OR REPLACE TABLE mytab ( -> column1 varchar(256) NOT NULL, -> column2 varchar(256) NOT NULL, -> column3 varchar(256) NOT NULL, -> column4 varchar(256) NOT NULL, -> column5 varchar(256) NOT NULL, -> column6 varchar(256) NOT NULL, -> column7 varchar(256) NOT NULL, -> column8 varchar(256) NOT NULL, -> column9 varchar(256) NOT NULL, -> column10 varchar(256) NOT NULL, -> column11 varchar(256) NOT NULL, -> column12 varchar(256) NOT NULL, -> column13 varchar(256) NOT NULL, -> column14 varchar(256) NOT NULL, -> column15 varchar(256) NOT NULL, -> column16 varchar(256) NOT NULL, -> column17 varchar(256) NOT NULL, -> column18 varchar(256) NOT NULL, -> column19 varchar(256) NOT NULL, -> column20 varchar(256) NOT NULL, -> column21 varchar(256) NOT NULL, -> column22 varchar(256) NOT NULL, -> column23 varchar(256) NOT NULL, -> column24 varchar(256) NOT NULL, -> column25 varchar(256) NOT NULL, -> column26 varchar(256) NOT NULL, -> column27 varchar(256) NOT NULL, -> column28 varchar(256) NOT NULL, -> column29 varchar(256) NOT NULL, -> column30 varchar(256) NOT NULL, -> column31 varchar(256) NOT NULL, -> column32 varchar(256) NOT NULL, -> column33 varchar(256) NOT NULL, -> column34 varchar(256) NOT NULL, -> column35 varchar(256) NOT NULL, -> column36 varchar(256) NOT NULL, -> column37 varchar(256) NOT NULL, -> column38 varchar(256) NOT NULL, -> column39 varchar(256) NOT NULL, -> column40 varchar(256) NOT NULL, -> column41 varchar(256) NOT NULL, -> column42 varchar(256) NOT NULL, -> column43 varchar(256) NOT NULL, -> column44 varchar(256) NOT NULL, -> column45 varchar(256) NOT NULL, -> column46 varchar(256) NOT NULL, -> column47 varchar(256) NOT NULL, -> column48 varchar(256) NOT NULL, -> column49 varchar(256) NOT NULL, -> column50 varchar(256) NOT NULL, -> column51 varchar(256) NOT NULL, -> column52 varchar(256) NOT NULL, -> column53 varchar(256) NOT NULL, -> column54 varchar(256) NOT NULL, -> column55 varchar(256) NOT NULL, -> column56 varchar(256) NOT NULL, -> column57 varchar(256) NOT NULL, -> column58 varchar(256) NOT NULL, -> column59 varchar(256) NOT NULL, -> column60 varchar(256) NOT NULL, -> column61 varchar(256) NOT NULL, -> column62 varchar(256) NOT NULL, -> column63 varchar(256) NOT NULL, -> column64 varchar(256) NOT NULL, -> column65 varchar(256) NOT NULL, -> column66 varchar(256) NOT NULL, -> column67 varchar(256) NOT NULL, -> column68 varchar(256) NOT NULL, -> column69 varchar(256) NOT NULL, -> column70 varchar(256) NOT NULL, -> column71 varchar(256) NOT NULL, -> column72 varchar(256) NOT NULL, -> column73 varchar(256) NOT NULL, -> column74 varchar(256) NOT NULL, -> column75 varchar(256) NOT NULL, -> column76 varchar(256) NOT NULL, -> column77 varchar(256) NOT NULL, -> column78 varchar(256) NOT NULL, -> column79 varchar(256) NOT NULL, -> column80 varchar(256) NOT NULL, -> column81 varchar(256) NOT NULL, -> column82 varchar(256) NOT NULL, -> column83 varchar(256) NOT NULL, -> column84 varchar(256) NOT NULL, -> column85 varchar(256) NOT NULL, -> column86 varchar(256) NOT NULL, -> column87 varchar(256) NOT NULL, -> column88 varchar(256) NOT NULL, -> column89 varchar(256) NOT NULL, -> column90 varchar(256) NOT NULL, -> column91 varchar(256) NOT NULL, -> column92 varchar(256) NOT NULL, -> column93 varchar(256) NOT NULL, -> column94 varchar(256) NOT NULL, -> column95 varchar(256) NOT NULL, -> column96 varchar(256) NOT NULL, -> column97 varchar(256) NOT NULL, -> column98 varchar(256) NOT NULL, -> column99 varchar(256) NOT NULL, -> column100 varchar(256) NOT NULL, -> column101 varchar(256) NOT NULL, -> column102 varchar(256) NOT NULL, -> column103 varchar(256) NOT NULL, -> column104 varchar(256) NOT NULL, -> column105 varchar(256) NOT NULL, -> column106 varchar(256) NOT NULL, -> column107 varchar(256) NOT NULL, -> column108 varchar(256) NOT NULL, -> column109 varchar(256) NOT NULL, -> column110 varchar(256) NOT NULL, -> column111 varchar(256) NOT NULL, -> column112 varchar(256) NOT NULL, -> column113 varchar(256) NOT NULL, -> column114 varchar(256) NOT NULL, -> column115 varchar(256) NOT NULL, -> column116 varchar(256) NOT NULL, -> column117 varchar(256) NOT NULL, -> column118 varchar(256) NOT NULL, -> column119 varchar(256) NOT NULL, -> column120 varchar(256) NOT NULL, -> column121 varchar(256) NOT NULL, -> column122 varchar(256) NOT NULL, -> column123 varchar(256) NOT NULL, -> column124 varchar(256) NOT NULL, -> column125 varchar(256) NOT NULL, -> column126 varchar(256) NOT NULL, -> column127 varchar(256) NOT NULL, -> column128 varchar(256) NOT NULL, -> column129 varchar(256) NOT NULL, -> column130 varchar(256) NOT NULL, -> column131 varchar(256) NOT NULL, -> column132 varchar(256) NOT NULL, -> column133 varchar(256) NOT NULL, -> column134 varchar(256) NOT NULL, -> column135 varchar(256) NOT NULL, -> column136 varchar(256) NOT NULL, -> column137 varchar(256) NOT NULL, -> column138 varchar(256) NOT NULL, -> column139 varchar(256) NOT NULL, -> column140 varchar(256) NOT NULL, -> column141 varchar(256) NOT NULL, -> column142 varchar(256) NOT NULL, -> column143 varchar(256) NOT NULL, -> column144 varchar(256) NOT NULL, -> column145 varchar(256) NOT NULL, -> column146 varchar(256) NOT NULL, -> column147 varchar(256) NOT NULL, -> column148 varchar(256) NOT NULL, -> column149 varchar(256) NOT NULL, -> column150 varchar(256) NOT NULL, -> column151 varchar(256) NOT NULL, -> column152 varchar(256) NOT NULL, -> column153 varchar(256) NOT NULL, -> column154 varchar(256) NOT NULL, -> column155 varchar(256) NOT NULL, -> column156 varchar(256) NOT NULL, -> column157 varchar(256) NOT NULL, -> column158 varchar(256) NOT NULL, -> column159 varchar(256) NOT NULL, -> column160 varchar(256) NOT NULL, -> column161 varchar(256) NOT NULL, -> column162 varchar(256) NOT NULL, -> column163 varchar(256) NOT NULL, -> column164 varchar(256) NOT NULL, -> column165 varchar(256) NOT NULL, -> column166 varchar(256) NOT NULL, -> column167 varchar(256) NOT NULL, -> column168 varchar(256) NOT NULL, -> column169 varchar(256) NOT NULL, -> column170 varchar(256) NOT NULL, -> column171 varchar(256) NOT NULL, -> column172 varchar(256) NOT NULL, -> column173 varchar(256) NOT NULL, -> column174 varchar(256) NOT NULL, -> column175 varchar(256) NOT NULL, -> column176 varchar(256) NOT NULL, -> column177 varchar(256) NOT NULL, -> column178 varchar(256) NOT NULL, -> column179 varchar(256) NOT NULL, -> column180 varchar(256) NOT NULL, -> column181 varchar(256) NOT NULL, -> column182 varchar(256) NOT NULL, -> column183 varchar(256) NOT NULL, -> column184 varchar(256) NOT NULL, -> column185 varchar(256) NOT NULL, -> column186 varchar(256) NOT NULL, -> column187 varchar(256) NOT NULL, -> column188 varchar(256) NOT NULL, -> column189 varchar(256) NOT NULL, -> column190 varchar(256) NOT NULL, -> column191 varchar(256) NOT NULL, -> column192 varchar(256) NOT NULL, -> column193 varchar(256) NOT NULL, -> column194 varchar(256) NOT NULL, -> column195 varchar(256) NOT NULL, -> column196 varchar(256) NOT NULL, -> column197 varchar(256) NOT NULL, -> column198 varchar(256) NOT NULL, -> PRIMARY KEY (column1) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected, 1 warning (0.032 sec) MariaDB [mdblab]> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 139 | Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) |
What is the root cause of the error, InnoDB ERROR 1118 (42000) – Troubleshooting Row Size Too Large Errors with InnoDB ?
The root cause of this issue is InnoDB has maximum row size that is roughly equivalent to half of innodb_page_size and you have created a InnoDB row beyond the maximum row size limit. All InnoDB row formats can store certain kinds of data in the overflow pages  and this makes maximum row size of an InnoDB possible than maximum amount of data that can be stored in the row’s main data page. InnoDB only considers using overflow pages if the table’s row size is more than half of innodb_page_size and when row size is greater than this, InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.
How to solve this problem ?
There are several ways you can fix this error. If your table is either in REDUNDANT or COMPACT row format, then you can solve by converting table to use DYNAMIC row format.
when using InnoDB’s DYNAMIC row format and a default character set of latin1 (which requires up to 1 byte per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(256)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 |
MariaDB [mdblab]> SET GLOBAL innodb_default_row_format='dynamic'; Query OK, 0 rows affected (0.000 sec) MariaDB [mdblab]> SET SESSION innodb_strict_mode=ON; Query OK, 0 rows affected (0.000 sec) CREATE OR REPLACE TABLE mytab ( column1 varchar(256) NOT NULL, column2 varchar(256) NOT NULL, column3 varchar(256) NOT NULL, column4 varchar(256) NOT NULL, column5 varchar(256) NOT NULL, column6 varchar(256) NOT NULL, column7 varchar(256) NOT NULL, column8 varchar(256) NOT NULL, column9 varchar(256) NOT NULL, column10 varchar(256) NOT NULL, column11 varchar(256) NOT NULL, column12 varchar(256) NOT NULL, column13 varchar(256) NOT NULL, column14 varchar(256) NOT NULL, column15 varchar(256) NOT NULL, column16 varchar(256) NOT NULL, column17 varchar(256) NOT NULL, column18 varchar(256) NOT NULL, column19 varchar(256) NOT NULL, column20 varchar(256) NOT NULL, column21 varchar(256) NOT NULL, column22 varchar(256) NOT NULL, column23 varchar(256) NOT NULL, column24 varchar(256) NOT NULL, column25 varchar(256) NOT NULL, column26 varchar(256) NOT NULL, column27 varchar(256) NOT NULL, column28 varchar(256) NOT NULL, column29 varchar(256) NOT NULL, column30 varchar(256) NOT NULL, column31 varchar(256) NOT NULL, column32 varchar(256) NOT NULL, column33 varchar(256) NOT NULL, column34 varchar(256) NOT NULL, column35 varchar(256) NOT NULL, column36 varchar(256) NOT NULL, column37 varchar(256) NOT NULL, column38 varchar(256) NOT NULL, column39 varchar(256) NOT NULL, column40 varchar(256) NOT NULL, column41 varchar(256) NOT NULL, column42 varchar(256) NOT NULL, column43 varchar(256) NOT NULL, column44 varchar(256) NOT NULL, column45 varchar(256) NOT NULL, column46 varchar(256) NOT NULL, column47 varchar(256) NOT NULL, column48 varchar(256) NOT NULL, column49 varchar(256) NOT NULL, column50 varchar(256) NOT NULL, column51 varchar(256) NOT NULL, column52 varchar(256) NOT NULL, column53 varchar(256) NOT NULL, column54 varchar(256) NOT NULL, column55 varchar(256) NOT NULL, column56 varchar(256) NOT NULL, column57 varchar(256) NOT NULL, column58 varchar(256) NOT NULL, column59 varchar(256) NOT NULL, column60 varchar(256) NOT NULL, column61 varchar(256) NOT NULL, column62 varchar(256) NOT NULL, column63 varchar(256) NOT NULL, column64 varchar(256) NOT NULL, column65 varchar(256) NOT NULL, column66 varchar(256) NOT NULL, column67 varchar(256) NOT NULL, column68 varchar(256) NOT NULL, column69 varchar(256) NOT NULL, column70 varchar(256) NOT NULL, column71 varchar(256) NOT NULL, column72 varchar(256) NOT NULL, column73 varchar(256) NOT NULL, column74 varchar(256) NOT NULL, column75 varchar(256) NOT NULL, column76 varchar(256) NOT NULL, column77 varchar(256) NOT NULL, column78 varchar(256) NOT NULL, column79 varchar(256) NOT NULL, column80 varchar(256) NOT NULL, column81 varchar(256) NOT NULL, column82 varchar(256) NOT NULL, column83 varchar(256) NOT NULL, column84 varchar(256) NOT NULL, column85 varchar(256) NOT NULL, column86 varchar(256) NOT NULL, column87 varchar(256) NOT NULL, column88 varchar(256) NOT NULL, column89 varchar(256) NOT NULL, column90 varchar(256) NOT NULL, column91 varchar(256) NOT NULL, column92 varchar(256) NOT NULL, column93 varchar(256) NOT NULL, column94 varchar(256) NOT NULL, column95 varchar(256) NOT NULL, column96 varchar(256) NOT NULL, column97 varchar(256) NOT NULL, column98 varchar(256) NOT NULL, column99 varchar(256) NOT NULL, column100 varchar(256) NOT NULL, column101 varchar(256) NOT NULL, column102 varchar(256) NOT NULL, column103 varchar(256) NOT NULL, column104 varchar(256) NOT NULL, column105 varchar(256) NOT NULL, column106 varchar(256) NOT NULL, column107 varchar(256) NOT NULL, column108 varchar(256) NOT NULL, column109 varchar(256) NOT NULL, column110 varchar(256) NOT NULL, column111 varchar(256) NOT NULL, column112 varchar(256) NOT NULL, column113 varchar(256) NOT NULL, column114 varchar(256) NOT NULL, column115 varchar(256) NOT NULL, column116 varchar(256) NOT NULL, column117 varchar(256) NOT NULL, column118 varchar(256) NOT NULL, column119 varchar(256) NOT NULL, column120 varchar(256) NOT NULL, column121 varchar(256) NOT NULL, column122 varchar(256) NOT NULL, column123 varchar(256) NOT NULL, column124 varchar(256) NOT NULL, column125 varchar(256) NOT NULL, column126 varchar(256) NOT NULL, column127 varchar(256) NOT NULL, column128 varchar(256) NOT NULL, column129 varchar(256) NOT NULL, column130 varchar(256) NOT NULL, column131 varchar(256) NOT NULL, column132 varchar(256) NOT NULL, column133 varchar(256) NOT NULL, column134 varchar(256) NOT NULL, column135 varchar(256) NOT NULL, column136 varchar(256) NOT NULL, column137 varchar(256) NOT NULL, column138 varchar(256) NOT NULL, column139 varchar(256) NOT NULL, column140 varchar(256) NOT NULL, column141 varchar(256) NOT NULL, column142 varchar(256) NOT NULL, column143 varchar(256) NOT NULL, column144 varchar(256) NOT NULL, column145 varchar(256) NOT NULL, column146 varchar(256) NOT NULL, column147 varchar(256) NOT NULL, column148 varchar(256) NOT NULL, column149 varchar(256) NOT NULL, column150 varchar(256) NOT NULL, column151 varchar(256) NOT NULL, column152 varchar(256) NOT NULL, column153 varchar(256) NOT NULL, column154 varchar(256) NOT NULL, column155 varchar(256) NOT NULL, column156 varchar(256) NOT NULL, column157 varchar(256) NOT NULL, column158 varchar(256) NOT NULL, column159 varchar(256) NOT NULL, column160 varchar(256) NOT NULL, column161 varchar(256) NOT NULL, column162 varchar(256) NOT NULL, column163 varchar(256) NOT NULL, column164 varchar(256) NOT NULL, column165 varchar(256) NOT NULL, column166 varchar(256) NOT NULL, column167 varchar(256) NOT NULL, column168 varchar(256) NOT NULL, column169 varchar(256) NOT NULL, column170 varchar(256) NOT NULL, column171 varchar(256) NOT NULL, column172 varchar(256) NOT NULL, column173 varchar(256) NOT NULL, column174 varchar(256) NOT NULL, column175 varchar(256) NOT NULL, column176 varchar(256) NOT NULL, column177 varchar(256) NOT NULL, column178 varchar(256) NOT NULL, column179 varchar(256) NOT NULL, column180 varchar(256) NOT NULL, column181 varchar(256) NOT NULL, column182 varchar(256) NOT NULL, column183 varchar(256) NOT NULL, column184 varchar(256) NOT NULL, column185 varchar(256) NOT NULL, column186 varchar(256) NOT NULL, column187 varchar(256) NOT NULL, column188 varchar(256) NOT NULL, column189 varchar(256) NOT NULL, column190 varchar(256) NOT NULL, column191 varchar(256) NOT NULL, column192 varchar(256) NOT NULL, column193 varchar(256) NOT NULL, column194 varchar(256) NOT NULL, column195 varchar(256) NOT NULL, column196 varchar(256) NOT NULL, column197 varchar(256) NOT NULL, column198 varchar(256) NOT NULL, PRIMARY KEY (column1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.013 sec) |
when using InnoDB’s DYNAMIC row format and a default character set of utf8 (which requires up to 3 bytes per character), the 256 byte limit means that a VARCHAR column will only be stored on overflow pages if it is at least as large as a varchar(86):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 |
CREATE OR REPLACE TABLE mytab ( column1 varchar(86) NOT NULL, column2 varchar(86) NOT NULL, column3 varchar(86) NOT NULL, column4 varchar(86) NOT NULL, column5 varchar(86) NOT NULL, column6 varchar(86) NOT NULL, column7 varchar(86) NOT NULL, column8 varchar(86) NOT NULL, column9 varchar(86) NOT NULL, column10 varchar(86) NOT NULL, column11 varchar(86) NOT NULL, column12 varchar(86) NOT NULL, column13 varchar(86) NOT NULL, column14 varchar(86) NOT NULL, column15 varchar(86) NOT NULL, column16 varchar(86) NOT NULL, column17 varchar(86) NOT NULL, column18 varchar(86) NOT NULL, column19 varchar(86) NOT NULL, column20 varchar(86) NOT NULL, column21 varchar(86) NOT NULL, column22 varchar(86) NOT NULL, column23 varchar(86) NOT NULL, column24 varchar(86) NOT NULL, column25 varchar(86) NOT NULL, column26 varchar(86) NOT NULL, column27 varchar(86) NOT NULL, column28 varchar(86) NOT NULL, column29 varchar(86) NOT NULL, column30 varchar(86) NOT NULL, column31 varchar(86) NOT NULL, column32 varchar(86) NOT NULL, column33 varchar(86) NOT NULL, column34 varchar(86) NOT NULL, column35 varchar(86) NOT NULL, column36 varchar(86) NOT NULL, column37 varchar(86) NOT NULL, column38 varchar(86) NOT NULL, column39 varchar(86) NOT NULL, column40 varchar(86) NOT NULL, column41 varchar(86) NOT NULL, column42 varchar(86) NOT NULL, column43 varchar(86) NOT NULL, column44 varchar(86) NOT NULL, column45 varchar(86) NOT NULL, column46 varchar(86) NOT NULL, column47 varchar(86) NOT NULL, column48 varchar(86) NOT NULL, column49 varchar(86) NOT NULL, column50 varchar(86) NOT NULL, column51 varchar(86) NOT NULL, column52 varchar(86) NOT NULL, column53 varchar(86) NOT NULL, column54 varchar(86) NOT NULL, column55 varchar(86) NOT NULL, column56 varchar(86) NOT NULL, column57 varchar(86) NOT NULL, column58 varchar(86) NOT NULL, column59 varchar(86) NOT NULL, column60 varchar(86) NOT NULL, column61 varchar(86) NOT NULL, column62 varchar(86) NOT NULL, column63 varchar(86) NOT NULL, column64 varchar(86) NOT NULL, column65 varchar(86) NOT NULL, column66 varchar(86) NOT NULL, column67 varchar(86) NOT NULL, column68 varchar(86) NOT NULL, column69 varchar(86) NOT NULL, column70 varchar(86) NOT NULL, column71 varchar(86) NOT NULL, column72 varchar(86) NOT NULL, column73 varchar(86) NOT NULL, column74 varchar(86) NOT NULL, column75 varchar(86) NOT NULL, column76 varchar(86) NOT NULL, column77 varchar(86) NOT NULL, column78 varchar(86) NOT NULL, column79 varchar(86) NOT NULL, column80 varchar(86) NOT NULL, column81 varchar(86) NOT NULL, column82 varchar(86) NOT NULL, column83 varchar(86) NOT NULL, column84 varchar(86) NOT NULL, column85 varchar(86) NOT NULL, column86 varchar(86) NOT NULL, column87 varchar(86) NOT NULL, column88 varchar(86) NOT NULL, column89 varchar(86) NOT NULL, column90 varchar(86) NOT NULL, column91 varchar(86) NOT NULL, column92 varchar(86) NOT NULL, column93 varchar(86) NOT NULL, column94 varchar(86) NOT NULL, column95 varchar(86) NOT NULL, column96 varchar(86) NOT NULL, column97 varchar(86) NOT NULL, column98 varchar(86) NOT NULL, column99 varchar(86) NOT NULL, column100 varchar(86) NOT NULL, column101 varchar(86) NOT NULL, column102 varchar(86) NOT NULL, column103 varchar(86) NOT NULL, column104 varchar(86) NOT NULL, column105 varchar(86) NOT NULL, column106 varchar(86) NOT NULL, column107 varchar(86) NOT NULL, column108 varchar(86) NOT NULL, column109 varchar(86) NOT NULL, column110 varchar(86) NOT NULL, column111 varchar(86) NOT NULL, column112 varchar(86) NOT NULL, column113 varchar(86) NOT NULL, column114 varchar(86) NOT NULL, column115 varchar(86) NOT NULL, column116 varchar(86) NOT NULL, column117 varchar(86) NOT NULL, column118 varchar(86) NOT NULL, column119 varchar(86) NOT NULL, column120 varchar(86) NOT NULL, column121 varchar(86) NOT NULL, column122 varchar(86) NOT NULL, column123 varchar(86) NOT NULL, column124 varchar(86) NOT NULL, column125 varchar(86) NOT NULL, column126 varchar(86) NOT NULL, column127 varchar(86) NOT NULL, column128 varchar(86) NOT NULL, column129 varchar(86) NOT NULL, column130 varchar(86) NOT NULL, column131 varchar(86) NOT NULL, column132 varchar(86) NOT NULL, column133 varchar(86) NOT NULL, column134 varchar(86) NOT NULL, column135 varchar(86) NOT NULL, column136 varchar(86) NOT NULL, column137 varchar(86) NOT NULL, column138 varchar(86) NOT NULL, column139 varchar(86) NOT NULL, |