Error 1118 42000

With InnoDB, users can see the following message as an error or warning:

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.

And they can also see the following message as an error or warning in the error log:

[Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, 
the row size is 8478 which is greater than maximum allowed size (8126) for a 
record on index leaf page.

Example of the Problem
Root Cause of the Problem
Checking Existing Tables for the Problem
Finding All Tables That Currently Have the Problem
Solving the Problem
1. Converting the Table to the DYNAMIC Row Format
2. Fitting More Columns on Overflow Pages
  1. Converting Some Columns to BLOB or TEXT
  2. Increasing the Length of VARBINARY Columns
  3. Increasing the Length of VARCHAR Columns
Working Around the Problem
1. Refactoring the Table into Multiple Tables
2. Refactoring Some Columns into JSON
3. Disabling InnoDB Strict Mode

These messages indicate that the table’s definition allows rows that the table’s InnoDB row format can’t actually store.

These messages are raised in the following cases:

If InnoDB strict mode is enabled and if a DDL statement is executed that touches the table, such as CREATE TABLE or ALTER TABLE, then InnoDB will raise an error with this message
If InnoDB strict mode is disabled and if a DDL statement is executed that touches the table, such as CREATE TABLE or ALTER TABLE, then InnoDB will raise a warning with this message.
Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table’s InnoDB row format can’t store, then InnoDB will raise an error with this message.

Example of the Problem

Here is an example of the problem:

SET GLOBAL innodb_default_row_format='dynamic';

SET SESSION innodb_strict_mode=ON;

CREATE OR REPLACE TABLE tab (
   col1 varchar(40) NOT NULL,
   col2 varchar(40) NOT NULL,
   col3 varchar(40) NOT NULL,
   col4 varchar(40) NOT NULL,
   col5 varchar(40) NOT NULL,
   col6 varchar(40) NOT NULL,
   col7 varchar(40) NOT NULL,
   col8 varchar(40) NOT NULL,
   col9 varchar(40) NOT NULL,
   col10 varchar(40) NOT NULL,
   col11 varchar(40) NOT NULL,
   col12 varchar(40) NOT NULL,
   col13 varchar(40) NOT NULL,
   col14 varchar(40) NOT NULL,
   col15 varchar(40) NOT NULL,
   col16 varchar(40) NOT NULL,
   col17 varchar(40) NOT NULL,
   col18 varchar(40) NOT NULL,
   col19 varchar(40) NOT NULL,
   col20 varchar(40) NOT NULL,
   col21 varchar(40) NOT NULL,
   col22 varchar(40) NOT NULL,
   col23 varchar(40) NOT NULL,
   col24 varchar(40) NOT NULL,
   col25 varchar(40) NOT NULL,
   col26 varchar(40) NOT NULL,
   col27 varchar(40) NOT NULL,
   col28 varchar(40) NOT NULL,
   col29 varchar(40) NOT NULL,
   col30 varchar(40) NOT NULL,
   col31 varchar(40) NOT NULL,
   col32 varchar(40) NOT NULL,
   col33 varchar(40) NOT NULL,
   col34 varchar(40) NOT NULL,
   col35 varchar(40) NOT NULL,
   col36 varchar(40) NOT NULL,
   col37 varchar(40) NOT NULL,
   col38 varchar(40) NOT NULL,
   col39 varchar(40) NOT NULL,
   col40 varchar(40) NOT NULL,
   col41 varchar(40) NOT NULL,
   col42 varchar(40) NOT NULL,
   col43 varchar(40) NOT NULL,
   col44 varchar(40) NOT NULL,
   col45 varchar(40) NOT NULL,
   col46 varchar(40) NOT NULL,
   col47 varchar(40) NOT NULL,
   col48 varchar(40) NOT NULL,
   col49 varchar(40) NOT NULL,
   col50 varchar(40) NOT NULL,
   col51 varchar(40) NOT NULL,
   col52 varchar(40) NOT NULL,
   col53 varchar(40) NOT NULL,
   col54 varchar(40) NOT NULL,
   col55 varchar(40) NOT NULL,
   col56 varchar(40) NOT NULL,
   col57 varchar(40) NOT NULL,
   col58 varchar(40) NOT NULL,
   col59 varchar(40) NOT NULL,
   col60 varchar(40) NOT NULL,
   col61 varchar(40) NOT NULL,
   col62 varchar(40) NOT NULL,
   col63 varchar(40) NOT NULL,
   col64 varchar(40) NOT NULL,
   col65 varchar(40) NOT NULL,
   col66 varchar(40) NOT NULL,
   col67 varchar(40) NOT NULL,
   col68 varchar(40) NOT NULL,
   col69 varchar(40) NOT NULL,
   col70 varchar(40) NOT NULL,
   col71 varchar(40) NOT NULL,
   col72 varchar(40) NOT NULL,
   col73 varchar(40) NOT NULL,
   col74 varchar(40) NOT NULL,
   col75 varchar(40) NOT NULL,
   col76 varchar(40) NOT NULL,
   col77 varchar(40) NOT NULL,
   col78 varchar(40) NOT NULL,
   col79 varchar(40) NOT NULL,
   col80 varchar(40) NOT NULL,
   col81 varchar(40) NOT NULL,
   col82 varchar(40) NOT NULL,
   col83 varchar(40) NOT NULL,
   col84 varchar(40) NOT NULL,
   col85 varchar(40) NOT NULL,
   col86 varchar(40) NOT NULL,
   col87 varchar(40) NOT NULL,
   col88 varchar(40) NOT NULL,
   col89 varchar(40) NOT NULL,
   col90 varchar(40) NOT NULL,
   col91 varchar(40) NOT NULL,
   col92 varchar(40) NOT NULL,
   col93 varchar(40) NOT NULL,
   col94 varchar(40) NOT NULL,
   col95 varchar(40) NOT NULL,
   col96 varchar(40) NOT NULL,
   col97 varchar(40) NOT NULL,
   col98 varchar(40) NOT NULL,
   col99 varchar(40) NOT NULL,
   col100 varchar(40) NOT NULL,
   col101 varchar(40) NOT NULL,
   col102 varchar(40) NOT NULL,
   col103 varchar(40) NOT NULL,
   col104 varchar(40) NOT NULL,
   col105 varchar(40) NOT NULL,
   col106 varchar(40) NOT NULL,
   col107 varchar(40) NOT NULL,
   col108 varchar(40) NOT NULL,
   col109 varchar(40) NOT NULL,
   col110 varchar(40) NOT NULL,
   col111 varchar(40) NOT NULL,
   col112 varchar(40) NOT NULL,
   col113 varchar(40) NOT NULL,
   col114 varchar(40) NOT NULL,
   col115 varchar(40) NOT NULL,
   col116 varchar(40) NOT NULL,
   col117 varchar(40) NOT NULL,
   col118 varchar(40) NOT NULL,
   col119 varchar(40) NOT NULL,
   col120 varchar(40) NOT NULL,
   col121 varchar(40) NOT NULL,
   col122 varchar(40) NOT NULL,
   col123 varchar(40) NOT NULL,
   col124 varchar(40) NOT NULL,
   col125 varchar(40) NOT NULL,
   col126 varchar(40) NOT NULL,
   col127 varchar(40) NOT NULL,
   col128 varchar(40) NOT NULL,
   col129 varchar(40) NOT NULL,
   col130 varchar(40) NOT NULL,
   col131 varchar(40) NOT NULL,
   col132 varchar(40) NOT NULL,
   col133 varchar(40) NOT NULL,
   col134 varchar(40) NOT NULL,
   col135 varchar(40) NOT NULL,
   col136 varchar(40) NOT NULL,
   col137 varchar(40) NOT NULL,
   col138 varchar(40) NOT NULL,
   col139 varchar(40) NOT NULL,
   col140 varchar(40) NOT NULL,
   col141 varchar(40) NOT NULL,
   col142 varchar(40) NOT NULL,
   col143 varchar(40) NOT NULL,
   col144 varchar(40) NOT NULL,
   col145 varchar(40) NOT NULL,
   col146 varchar(40) NOT NULL,
   col147 varchar(40) NOT NULL,
   col148 varchar(40) NOT NULL,
   col149 varchar(40) NOT NULL,
   col150 varchar(40) NOT NULL,
   col151 varchar(40) NOT NULL,
   col152 varchar(40) NOT NULL,
   col153 varchar(40) NOT NULL,
   col154 varchar(40) NOT NULL,
   col155 varchar(40) NOT NULL,
   col156 varchar(40) NOT NULL,
   col157 varchar(40) NOT NULL,
   col158 varchar(40) NOT NULL,
   col159 varchar(40) NOT NULL,
   col160 varchar(40) NOT NULL,
   col161 varchar(40) NOT NULL,
   col162 varchar(40) NOT NULL,
   col163 varchar(40) NOT NULL,
   col164 varchar(40) NOT NULL,
   col165 varchar(40) NOT NULL,
   col166 varchar(40) NOT NULL,
   col167 varchar(40) NOT NULL,
   col168 varchar(40) NOT NULL,
   col169 varchar(40) NOT NULL,
   col170 varchar(40) NOT NULL,
   col171 varchar(40) NOT NULL,
   col172 varchar(40) NOT NULL,
   col173 varchar(40) NOT NULL,
   col174 varchar(40) NOT NULL,
   col175 varchar(40) NOT NULL,
   col176 varchar(40) NOT NULL,
   col177 varchar(40) NOT NULL,
   col178 varchar(40) NOT NULL,
   col179 varchar(40) NOT NULL,
   col180 varchar(40) NOT NULL,
   col181 varchar(40) NOT NULL,
   col182 varchar(40) NOT NULL,
   col183 varchar(40) NOT NULL,
   col184 varchar(40) NOT NULL,
   col185 varchar(40) NOT NULL,
   col186 varchar(40) NOT NULL,
   col187 varchar(40) NOT NULL,
   col188 varchar(40) NOT NULL,
   col189 varchar(40) NOT NULL,
   col190 varchar(40) NOT NULL,
   col191 varchar(40) NOT NULL,
   col192 varchar(40) NOT NULL,
   col193 varchar(40) NOT NULL,
   col194 varchar(40) NOT NULL,
   col195 varchar(40) NOT NULL,
   col196 varchar(40) NOT NULL,
   col197 varchar(40) NOT NULL,
   col198 varchar(40) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to 
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Root Cause of the Problem

The root cause is that InnoDB has a maximum row size that is roughly equivalent to half of the value of the innodb_page_size system variable. See InnoDB Row Formats Overview: Maximum Row Size for more information.

InnoDB’s row formats work around this limit by storing certain kinds of variable-length columns on overflow pages. However, different row formats can store different types of data on overflow pages. Some row formats can store more data in overflow pages than others. For example, the DYNAMIC and COMPRESSED row formats can store the most data in overflow pages. To learn how the various InnoDB row formats use overflow pages, see the following pages:

InnoDB REDUNDANT Row Format: Overflow Pages with the REDUNDANT Row Format
InnoDB COMPACT Row Format: Overflow Pages with the COMPACT Row Format
InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format
InnoDB COMPRESSED Row Format: Overflow Pages with the COMPRESSED Row Format

Checking Existing Tables for the Problem

InnoDB does not currently have an easy way to check all existing tables to determine which tables have this problem. See MDEV-20400 for more information.

One method to check a single existing table for this problem is to enable InnoDB strict mode, and then try to create a duplicate of the table with CREATE TABLE … LIKE. If the table has this problem, then the operation will fail. For example:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE tab_dup LIKE tab;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to 
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Finding All Tables That Currently Have the Problem

The following shell script will read through a MariaDB server to identify every table that has a row size definition that is too large for its row format and the server’s page size. It runs on most common distributions of Linux.

To run the script, copy the code below to a shell-script named rowsize.sh, make it executable with the command chmod 755 ./rowsize.sh, and invoke it with the following parameters:

./rowsize.sh host user password

When the script runs, it displays the name of the temporary database it creates, so that if the script is interrupted before cleaning up, the database can be easily identified and removed manually.

As the script runs it will output one line reporting the database and tablename for each table it finds that has the oversize row problem. If it finds none, it will print the following message: «No tables with rows size too big found.»

In either case, the script prints one final line to announce when it’s done: ./rowsize.sh done.

#!/bin/bash

[ -z "$3" ] && echo "Usage: $0 host user password" >&2 && exit 1

dt="tmp_$RANDOM$RANDOM"

mysql -h $1 -u $2 -p$3 -ABNe "create database $dt;"
[ $? -ne 0 ] && echo "Error: $0 terminating" >&2 exit 1

echo
echo "Created temporary database ${dt} on host $1"
echo

c=0
for d in $(mysql -h $1 -u $2 -p$3 -ABNe "show databases;" | egrep -iv "information_schema|mysql|performance_schema|$dt")
do
	for t in $(mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d)
	do
		tc=$(mysql -h $1 -u $2 -p$3 -ABNe "show create table $t\G" $d | egrep -iv "^*|^$t")
		
		echo $tc | grep -iq "ROW_FORMAT"
		if [ $? -ne 0 ]
		then
			tf=$(mysql -h $1 -u $2 -p$3 -ABNe "select row_format from information_schema.innodb_sys_tables where name = '${d}/${t}';")
			tc="$tc ROW_FORMAT=$tf"
		fi
		
		ef="/tmp/e$RANDOM$RANDOM"
		mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; set foreign_key_checks=0; ${tc};" $dt >/dev/null  2>$ef
		[ $? -ne 0 ] && cat $ef | grep -q "Row size too large" && echo "${d}.${t}" && let c++ || mysql -h $1 -u $2 -p$3 -ABNe "drop table if exists ${t};" $dt
		rm -f $ef
	done
done
mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; drop database $dt;"
[ $c -eq 0 ] && echo "No tables with rows size too large found." || echo && echo "$c tables found with row size too large."
echo
echo "$0 done."

Solving the Problem

There are several potential solutions available to solve this problem.

Converting the Table to the DYNAMIC Row Format

If the table is using either the REDUNDANT or the COMPACT row format, then one potential solution to this problem is to convert the table to use the DYNAMIC row format instead.

If your tables were originally created on an older version of MariaDB or MySQL, then your table may be using one of InnoDB’s older row formats:

In MariaDB 10.1 and before, and in MySQL 5.6 and before, the COMPACT row format was the default row format.
In MySQL 4.1 and before, the REDUNDANT row format was the default row format.

The DYNAMIC row format can store more data on overflow pages than these older row formats, so this row format may actually be able to store the table’s data safely. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

Therefore, a potential solution to the Row size too large error is to convert the table to use the DYNAMIC row format. For example:

ALTER TABLE tab ROW_FORMAT=DYNAMIC;

You can use the INNODB_SYS_TABLES table in the information_schema database to find all tables that use the REDUNDANT or the COMPACT row formats. This is helpful if you would like to convert all of your tables that you still use the older row formats to the DYNAMIC row format. For example, the following query can find those tables, while excluding InnoDB’s internal system tables:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE ROW_FORMAT IN('Redundant', 'Compact')
AND NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');

In MariaDB 10.2 and later, the DYNAMIC row format is the default row format. If your tables were originally created on one of these newer versions, then they may already be using this row format. In that case, you may need to try the next solution.

Fitting More Columns on Overflow Pages

If the table is already using the DYNAMIC row format, then another potential solution to this problem is to change the table schema, so that the row format can store more columns on overflow pages.

In order for InnoDB to store some variable-length columns on overflow pages, the length of those columns may need to be increased.

Therefore, a counter-intuitive solution to the Row size too large error in a lot of cases is actually to increase the length of some variable-length columns, so that InnoDB’s row format can store them on overflow pages.

Some possible ways to change the table schema are listed below.

Converting Some Columns to `BLOB` or `TEXT`

For BLOB and TEXT columns, the DYNAMIC row format can store these columns on overflow pages. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

Therefore, a potential solution to the Row size too large error is to convert some columns to the BLOB or TEXT data types.

Increasing the Length of `VARBINARY` Columns

For VARBINARY columns, the DYNAMIC row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

Therefore, a potential solution to the Row size too large error is to ensure that all VARBINARY columns are at least as long as varbinary(256).

Increasing the Length of `VARCHAR` Columns

For VARCHAR columns, the DYNAMIC row format can only store these columns on overflow pages if the maximum length of the column is 256 bytes or longer. See InnoDB DYNAMIC Row Format: Overflow Pages with the DYNAMIC Row Format for more information.

The original table schema shown earlier on this page causes the Row size too large error, because all of the table’s VARCHAR columns are smaller than 256 bytes, which means that they have to be stored on the row’s main data page.

Therefore, a potential solution to the Row size too large error is to ensure that all VARCHAR columns are at least as long as 256 bytes. The number of characters required to reach the 256 byte limit depends on the character set used by the column.

For example, 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):

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
   col1 varchar(256) NOT NULL,
   col2 varchar(256) NOT NULL,
   col3 varchar(256) NOT NULL,
   col4 varchar(256) NOT NULL,
   col5 varchar(256) NOT NULL,
   col6 varchar(256) NOT NULL,
   col7 varchar(256) NOT NULL,
   col8 varchar(256) NOT NULL,
   col9 varchar(256) NOT NULL,
   col10 varchar(256) NOT NULL,
   col11 varchar(256) NOT NULL,
   col12 varchar(256) NOT NULL,
   col13 varchar(256) NOT NULL,
   col14 varchar(256) NOT NULL,
   col15 varchar(256) NOT NULL,
   col16 varchar(256) NOT NULL,
   col17 varchar(256) NOT NULL,
   col18 varchar(256) NOT NULL,
   col19 varchar(256) NOT NULL,
   col20 varchar(256) NOT NULL,
   col21 varchar(256) NOT NULL,
   col22 varchar(256) NOT NULL,
   col23 varchar(256) NOT NULL,
   col24 varchar(256) NOT NULL,
   col25 varchar(256) NOT NULL,
   col26 varchar(256) NOT NULL,
   col27 varchar(256) NOT NULL,
   col28 varchar(256) NOT NULL,
   col29 varchar(256) NOT NULL,
   col30 varchar(256) NOT NULL,
   col31 varchar(256) NOT NULL,
   col32 varchar(256) NOT NULL,
   col33 varchar(256) NOT NULL,
   col34 varchar(256) NOT NULL,
   col35 varchar(256) NOT NULL,
   col36 varchar(256) NOT NULL,
   col37 varchar(256) NOT NULL,
   col38 varchar(256) NOT NULL,
   col39 varchar(256) NOT NULL,
   col40 varchar(256) NOT NULL,
   col41 varchar(256) NOT NULL,
   col42 varchar(256) NOT NULL,
   col43 varchar(256) NOT NULL,
   col44 varchar(256) NOT NULL,
   col45 varchar(256) NOT NULL,
   col46 varchar(256) NOT NULL,
   col47 varchar(256) NOT NULL,
   col48 varchar(256) NOT NULL,
   col49 varchar(256) NOT NULL,
   col50 varchar(256) NOT NULL,
   col51 varchar(256) NOT NULL,
   col52 varchar(256) NOT NULL,
   col53 varchar(256) NOT NULL,
   col54 varchar(256) NOT NULL,
   col55 varchar(256) NOT NULL,
   col56 varchar(256) NOT NULL,
   col57 varchar(256) NOT NULL,
   col58 varchar(256) NOT NULL,
   col59 varchar(256) NOT NULL,
   col60 varchar(256) NOT NULL,
   col61 varchar(256) NOT NULL,
   col62 varchar(256) NOT NULL,
   col63 varchar(256) NOT NULL,
   col64 varchar(256) NOT NULL,
   col65 varchar(256) NOT NULL,
   col66 varchar(256) NOT NULL,
   col67 varchar(256) NOT NULL,
   col68 varchar(256) NOT NULL,
   col69 varchar(256) NOT NULL,
   col70 varchar(256) NOT NULL,
   col71 varchar(256) NOT NULL,
   col72 varchar(256) NOT NULL,
   col73 varchar(256) NOT NULL,
   col74 varchar(256) NOT NULL,
   col75 varchar(256) NOT NULL,
   col76 varchar(256) NOT NULL,
   col77 varchar(256) NOT NULL,
   col78 varchar(256) NOT NULL,
   col79 varchar(256) NOT NULL,
   col80 varchar(256) NOT NULL,
   col81 varchar(256) NOT NULL,
   col82 varchar(256) NOT NULL,
   col83 varchar(256) NOT NULL,
   col84 varchar(256) NOT NULL,
   col85 varchar(256) NOT NULL,
   col86 varchar(256) NOT NULL,
   col87 varchar(256) NOT NULL,
   col88 varchar(256) NOT NULL,
   col89 varchar(256) NOT NULL,
   col90 varchar(256) NOT NULL,
   col91 varchar(256) NOT NULL,
   col92 varchar(256) NOT NULL,
   col93 varchar(256) NOT NULL,
   col94 varchar(256) NOT NULL,
   col95 varchar(256) NOT NULL,
   col96 varchar(256) NOT NULL,
   col97 varchar(256) NOT NULL,
   col98 varchar(256) NOT NULL,
   col99 varchar(256) NOT NULL,
   col100 varchar(256) NOT NULL,
   col101 varchar(256) NOT NULL,
   col102 varchar(256) NOT NULL,
   col103 varchar(256) NOT NULL,
   col104 varchar(256) NOT NULL,
   col105 varchar(256) NOT NULL,
   col106 varchar(256) NOT NULL,
   col107 varchar(256) NOT NULL,
   col108 varchar(256) NOT NULL,
   col109 varchar(256) NOT NULL,
   col110 varchar(256) NOT NULL,
   col111 varchar(256) NOT NULL,
   col112 varchar(256) NOT NULL,
   col113 varchar(256) NOT NULL,
   col114 varchar(256) NOT NULL,
   col115 varchar(256) NOT NULL,
   col116 varchar(256) NOT NULL,
   col117 varchar(256) NOT NULL,
   col118 varchar(256) NOT NULL,
   col119 varchar(256) NOT NULL,
   col120 varchar(256) NOT NULL,
   col121 varchar(256) NOT NULL,
   col122 varchar(256) NOT NULL,
   col123 varchar(256) NOT NULL,
   col124 varchar(256) NOT NULL,
   col125 varchar(256) NOT NULL,
   col126 varchar(256) NOT NULL,
   col127 varchar(256) NOT NULL,
   col128 varchar(256) NOT NULL,
   col129 varchar(256) NOT NULL,
   col130 varchar(256) NOT NULL,
   col131 varchar(256) NOT NULL,
   col132 varchar(256) NOT NULL,
   col133 varchar(256) NOT NULL,
   col134 varchar(256) NOT NULL,
   col135 varchar(256) NOT NULL,
   col136 varchar(256) NOT NULL,
   col137 varchar(256) NOT NULL,
   col138 varchar(256) NOT NULL,
   col139 varchar(256) NOT NULL,
   col140 varchar(256) NOT NULL,
   col141 varchar(256) NOT NULL,
   col142 varchar(256) NOT NULL,
   col143 varchar(256) NOT NULL,
   col144 varchar(256) NOT NULL,
   col145 varchar(256) NOT NULL,
   col146 varchar(256) NOT NULL,
   col147 varchar(256) NOT NULL,
   col148 varchar(256) NOT NULL,
   col149 varchar(256) NOT NULL,
   col150 varchar(256) NOT NULL,
   col151 varchar(256) NOT NULL,
   col152 varchar(256) NOT NULL,
   col153 varchar(256) NOT NULL,
   col154 varchar(256) NOT NULL,
   col155 varchar(256) NOT NULL,
   col156 varchar(256) NOT NULL,
   col157 varchar(256) NOT NULL,
   col158 varchar(256) NOT NULL,
   col159 varchar(256) NOT NULL,
   col160 varchar(256) NOT NULL,
   col161 varchar(256) NOT NULL,
   col162 varchar(256) NOT NULL,
   col163 varchar(256) NOT NULL,
   col164 varchar(256) NOT NULL,
   col165 varchar(256) NOT NULL,
   col166 varchar(256) NOT NULL,
   col167 varchar(256) NOT NULL,
   col168 varchar(256) NOT NULL,
   col169 varchar(256) NOT NULL,
   col170 varchar(256) NOT NULL,
   col171 varchar(256) NOT NULL,
   col172 varchar(256) NOT NULL,
   col173 varchar(256) NOT NULL,
   col174 varchar(256) NOT NULL,
   col175 varchar(256) NOT NULL,
   col176 varchar(256) NOT NULL,
   col177 varchar(256) NOT NULL,
   col178 varchar(256) NOT NULL,
   col179 varchar(256) NOT NULL,
   col180 varchar(256) NOT NULL,
   col181 varchar(256) NOT NULL,
   col182 varchar(256) NOT NULL,
   col183 varchar(256) NOT NULL,
   col184 varchar(256) NOT NULL,
   col185 varchar(256) NOT NULL,
   col186 varchar(256) NOT NULL,
   col187 varchar(256) NOT NULL,
   col188 varchar(256) NOT NULL,
   col189 varchar(256) NOT NULL,
   col190 varchar(256) NOT NULL,
   col191 varchar(256) NOT NULL,
   col192 varchar(256) NOT NULL,
   col193 varchar(256) NOT NULL,
   col194 varchar(256) NOT NULL,
   col195 varchar(256) NOT NULL,
   col196 varchar(256) NOT NULL,
   col197 varchar(256) NOT NULL,
   col198 varchar(256) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And 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):

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
   col1 varchar(86) NOT NULL,
   col2 varchar(86) NOT NULL,
   col3 varchar(86) NOT NULL,
   col4 varchar(86) NOT NULL,
   col5 varchar(86) NOT NULL,
   col6 varchar(86) NOT NULL,
   col7 varchar(86) NOT NULL,
   col8 varchar(86) NOT NULL,
   col9 varchar(86) NOT NULL,
   col10 varchar(86) NOT NULL,
   col11 varchar(86) NOT NULL,
   col12 varchar(86) NOT NULL,
   col13 varchar(86) NOT NULL,
   col14 varchar(86) NOT NULL,
   col15 varchar(86) NOT NULL,
   col16 varchar(86) NOT NULL,
   col17 varchar(86) NOT NULL,
   col18 varchar(86) NOT NULL,
   col19 varchar(86) NOT NULL,
   col20 varchar(86) NOT NULL,
   col21 varchar(86) NOT NULL,
   col22 varchar(86) NOT NULL,
   col23 varchar(86) NOT NULL,
   col24 varchar(86) NOT NULL,
   col25 varchar(86) NOT NULL,
   col26 varchar(86) NOT NULL,
   col27 varchar(86) NOT NULL,
   col28 varchar(86) NOT NULL,
   col29 varchar(86) NOT NULL,
   col30 varchar(86) NOT NULL,
   col31 varchar(86) NOT NULL,
   col32 varchar(86) NOT NULL,
   col33 varchar(86) NOT NULL,
   col34 varchar(86) NOT NULL,
   col35 varchar(86) NOT NULL,
   col36 varchar(86) NOT NULL,
   col37 varchar(86) NOT NULL,
   col38 varchar(86) NOT NULL,
   col39 varchar(86) NOT NULL,
   col40 varchar(86) NOT NULL,
   col41 varchar(86) NOT NULL,
   col42 varchar(86) NOT NULL,
   col43 varchar(86) NOT NULL,
   col44 varchar(86) NOT NULL,
   col45 varchar(86) NOT NULL,
   col46 varchar(86) NOT NULL,
   col47 varchar(86) NOT NULL,
   col48 varchar(86) NOT NULL,
   col49 varchar(86) NOT NULL,
   col50 varchar(86) NOT NULL,
   col51 varchar(86) NOT NULL,
   col52 varchar(86) NOT NULL,
   col53 varchar(86) NOT NULL,
   col54 varchar(86) NOT NULL,
   col55 varchar(86) NOT NULL,
   col56 varchar(86) NOT NULL,
   col57 varchar(86) NOT NULL,
   col58 varchar(86) NOT NULL,
   col59 varchar(86) NOT NULL,
   col60 varchar(86) NOT NULL,
   col61 varchar(86) NOT NULL,
   col62 varchar(86) NOT NULL,
   col63 varchar(86) NOT NULL,
   col64 varchar(86) NOT NULL,
   col65 varchar(86) NOT NULL,
   col66 varchar(86) NOT NULL,
   col67 varchar(86) NOT NULL,
   col68 varchar(86) NOT NULL,
   col69 varchar(86) NOT NULL,
   col70 varchar(86) NOT NULL,
   col71 varchar(86) NOT NULL,
   col72 varchar(86) NOT NULL,
   col73 varchar(86) NOT NULL,
   col74 varchar(86) NOT NULL,
   col75 varchar(86) NOT NULL,
   col76 varchar(86) NOT NULL,
   col77 varchar(86) NOT NULL,
   col78 varchar(86) NOT NULL,
   col79 varchar(86) NOT NULL,
   col80 varchar(86) NOT NULL,
   col81 varchar(86) NOT NULL,
   col82 varchar(86) NOT NULL,
   col83 varchar(86) NOT NULL,
   col84 varchar(86) NOT NULL,
   col85 varchar(86) NOT NULL,
   col86 varchar(86) NOT NULL,
   col87 varchar(86) NOT NULL,
   col88 varchar(86) NOT NULL,
   col89 varchar(86) NOT NULL,
   col90 varchar(86) NOT NULL,
   col91 varchar(86) NOT NULL,
   col92 varchar(86) NOT NULL,
   col93 varchar(86) NOT NULL,
   col94 varchar(86) NOT NULL,
   col95 varchar(86) NOT NULL,
   col96 varchar(86) NOT NULL,
   col97 varchar(86) NOT NULL,
   col98 varchar(86) NOT NULL,
   col99 varchar(86) NOT NULL,
   col100 varchar(86) NOT NULL,
   col101 varchar(86) NOT NULL,
   col102 varchar(86) NOT NULL,
   col103 varchar(86) NOT NULL,
   col104 varchar(86) NOT NULL,
   col105 varchar(86) NOT NULL,
   col106 varchar(86) NOT NULL,
   col107 varchar(86) NOT NULL,
   col108 varchar(86) NOT NULL,
   col109 varchar(86) NOT NULL,
   col110 varchar(86) NOT NULL,
   col111 varchar(86) NOT NULL,
   col112 varchar(86) NOT NULL,
   col113 varchar(86) NOT NULL,
   col114 varchar(86) NOT NULL,
   col115 varchar(86) NOT NULL,
   col116 varchar(86) NOT NULL,
   col117 varchar(86) NOT NULL,
   col118 varchar(86) NOT NULL,
   col119 varchar(86) NOT NULL,
   col120 varchar(86) NOT NULL,
   col121 varchar(86) NOT NULL,
   col122 varchar(86) NOT NULL,
   col123 varchar(86) NOT NULL,
   col124 varchar(86) NOT NULL,
   col125 varchar(86) NOT NULL,
   col126 varchar(86) NOT NULL,
   col127 varchar(86) NOT NULL,
   col128 varchar(86) NOT NULL,
   col129 varchar(86) NOT NULL,
   col130 varchar(86) NOT NULL,
   col131 varchar(86) NOT NULL,
   col132 varchar(86) NOT NULL,
   col133 varchar(86) NOT NULL,
   col134 varchar(86) NOT NULL,
   col135 varchar(86) NOT NULL,
   col136 varchar(86) NOT NULL,
   col137 varchar(86) NOT NULL,
   col138 varchar(86) NOT NULL,
   col139 varchar(86) NOT NULL,
   col140 varchar(86) NOT NULL,
   col141 varchar(86) NOT NULL,
   col142 varchar(86) NOT NULL,
   col143 varchar(86) NOT NULL,
   col144 varchar(86) NOT NULL,
   col145 varchar(86) NOT NULL,
   col146 varchar(86) NOT NULL,
   col147 varchar(86) NOT NULL,
   col148 varchar(86) NOT NULL,
   col149 varchar(86) NOT NULL,
   col150 varchar(86) NOT NULL,
   col151 varchar(86) NOT NULL,
   col152 varchar(86) NOT NULL,
   col153 varchar(86) NOT NULL,
   col154 varchar(86) NOT NULL,
   col155 varchar(86) NOT NULL,
   col156 varchar(86) NOT NULL,
   col157 varchar(86) NOT NULL,
   col158 varchar(86) NOT NULL,
   col159 varchar(86) NOT NULL,
   col160 varchar(86) NOT NULL,
   col161 varchar(86) NOT NULL,
   col162 varchar(86) NOT NULL,
   col163 varchar(86) NOT NULL,
   col164 varchar(86) NOT NULL,
   col165 varchar(86) NOT NULL,
   col166 varchar(86) NOT NULL,
   col167 varchar(86) NOT NULL,
   col168 varchar(86) NOT NULL,
   col169 varchar(86) NOT NULL,
   col170 varchar(86) NOT NULL,
   col171 varchar(86) NOT NULL,
   col172 varchar(86) NOT NULL,
   col173 varchar(86) NOT NULL,
   col174 varchar(86) NOT NULL,
   col175 varchar(86) NOT NULL,
   col176 varchar(86) NOT NULL,
   col177 varchar(86) NOT NULL,
   col178 varchar(86) NOT NULL,
   col179 varchar(86) NOT NULL,
   col180 varchar(86) NOT NULL,
   col181 varchar(86) NOT NULL,
   col182 varchar(86) NOT NULL,
   col183 varchar(86) NOT NULL,
   col184 varchar(86) NOT NULL,
   col185 varchar(86) NOT NULL,
   col186 varchar(86) NOT NULL,
   col187 varchar(86) NOT NULL,
   col188 varchar(86) NOT NULL,
   col189 varchar(86) NOT NULL,
   col190 varchar(86) NOT NULL,
   col191 varchar(86) NOT NULL,
   col192 varchar(86) NOT NULL,
   col193 varchar(86) NOT NULL,
   col194 varchar(86) NOT NULL,
   col195 varchar(86) NOT NULL,
   col196 varchar(86) NOT NULL,
   col197 varchar(86) NOT NULL,
   col198 varchar(86) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And when using InnoDB’s DYNAMIC row format and a default character set of utf8mb4 (which requires up to 4 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(64):

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=ON;
CREATE OR REPLACE TABLE tab (
   col1 varchar(64) NOT NULL,
   col2 varchar(64) NOT NULL,
   col3 varchar(64) NOT NULL,
   col4 varchar(64) NOT NULL,
   col5 varchar(64) NOT NULL,
   col6 varchar(64) NOT NULL,
   col7 varchar(64) NOT NULL,
   col8 varchar(64) NOT NULL,
   col9 varchar(64) NOT NULL,
   col10 varchar(64) NOT NULL,
   col11 varchar(64) NOT NULL,
   col12 varchar(64) NOT NULL,
   col13 varchar(64) NOT NULL,
   col14 varchar(64) NOT NULL,
   col15 varchar(64) NOT NULL,
   col16 varchar(64) NOT NULL,
   col17 varchar(64) NOT NULL,
   col18 varchar(64) NOT NULL,
   col19 varchar(64) NOT NULL,
   col20 varchar(64) NOT NULL,
   col21 varchar(64) NOT NULL,
   col22 varchar(64) NOT NULL,
   col23 varchar(64) NOT NULL,
   col24 varchar(64) NOT NULL,
   col25 varchar(64) NOT NULL,
   col26 varchar(64) NOT NULL,
   col27 varchar(64) NOT NULL,
   col28 varchar(64) NOT NULL,
   col29 varchar(64) NOT NULL,
   col30 varchar(64) NOT NULL,
   col31 varchar(64) NOT NULL,
   col32 varchar(64) NOT NULL,
   col33 varchar(64) NOT NULL,
   col34 varchar(64) NOT NULL,
   col35 varchar(64) NOT NULL,
   col36 varchar(64) NOT NULL,
   col37 varchar(64) NOT NULL,
   col38 varchar(64) NOT NULL,
   col39 varchar(64) NOT NULL,
   col40 varchar(64) NOT NULL,
   col41 varchar(64) NOT NULL,
   col42 varchar(64) NOT NULL,
   col43 varchar(64) NOT NULL,
   col44 varchar(64) NOT NULL,
   col45 varchar(64) NOT NULL,
   col46 varchar(64) NOT NULL,
   col47 varchar(64) NOT NULL,
   col48 varchar(64) NOT NULL,
   col49 varchar(64) NOT NULL,
   col50 varchar(64) NOT NULL,
   col51 varchar(64) NOT NULL,
   col52 varchar(64) NOT NULL,
   col53 varchar(64) NOT NULL,
   col54 varchar(64) NOT NULL,
   col55 varchar(64) NOT NULL,
   col56 varchar(64) NOT NULL,
   col57 varchar(64) NOT NULL,
   col58 varchar(64) NOT NULL,
   col59 varchar(64) NOT NULL,
   col60 varchar(64) NOT NULL,
   col61 varchar(64) NOT NULL,
   col62 varchar(64) NOT NULL,
   col63 varchar(64) NOT NULL,
   col64 varchar(64) NOT NULL,
   col65 varchar(64) NOT NULL,
   col66 varchar(64) NOT NULL,
   col67 varchar(64) NOT NULL,
   col68 varchar(64) NOT NULL,
   col69 varchar(64) NOT NULL,
   col70 varchar(64) NOT NULL,
   col71 varchar(64) NOT NULL,
   col72 varchar(64) NOT NULL,
   col73 varchar(64) NOT NULL,
   col74 varchar(64) NOT NULL,
   col75 varchar(64) NOT NULL,
   col76 varchar(64) NOT NULL,
   col77 varchar(64) NOT NULL,
   col78 varchar(64) NOT NULL,
   col79 varchar(64) NOT NULL,
   col80 varchar(64) NOT NULL,
   col81 varchar(64) NOT NULL,
   col82 varchar(64) NOT NULL,
   col83 varchar(64) NOT NULL,
   col84 varchar(64) NOT NULL,
   col85 varchar(64) NOT NULL,
   col86 varchar(64) NOT NULL,
   col87 varchar(64) NOT NULL,
   col88 varchar(64) NOT NULL,
   col89 varchar(64) NOT NULL,
   col90 varchar(64) NOT NULL,
   col91 varchar(64) NOT NULL,
   col92 varchar(64) NOT NULL,
   col93 varchar(64) NOT NULL,
   col94 varchar(64) NOT NULL,
   col95 varchar(64) NOT NULL,
   col96 varchar(64) NOT NULL,
   col97 varchar(64) NOT NULL,
   col98 varchar(64) NOT NULL,
   col99 varchar(64) NOT NULL,
   col100 varchar(64) NOT NULL,
   col101 varchar(64) NOT NULL,
   col102 varchar(64) NOT NULL,
   col103 varchar(64) NOT NULL,
   col104 varchar(64) NOT NULL,
   col105 varchar(64) NOT NULL,
   col106 varchar(64) NOT NULL,
   col107 varchar(64) NOT NULL,
   col108 varchar(64) NOT NULL,
   col109 varchar(64) NOT NULL,
   col110 varchar(64) NOT NULL,
   col111 varchar(64) NOT NULL,
   col112 varchar(64) NOT NULL,
   col113 varchar(64) NOT NULL,
   col114 varchar(64) NOT NULL,
   col115 varchar(64) NOT NULL,
   col116 varchar(64) NOT NULL,
   col117 varchar(64) NOT NULL,
   col118 varchar(64) NOT NULL,
   col119 varchar(64) NOT NULL,
   col120 varchar(64) NOT NULL,
   col121 varchar(64) NOT NULL,
   col122 varchar(64) NOT NULL,
   col123 varchar(64) NOT NULL,
   col124 varchar(64) NOT NULL,
   col125 varchar(64) NOT NULL,
   col126 varchar(64) NOT NULL,
   col127 varchar(64) NOT NULL,
   col128 varchar(64) NOT NULL,
   col129 varchar(64) NOT NULL,
   col130 varchar(64) NOT NULL,
   col131 varchar(64) NOT NULL,
   col132 varchar(64) NOT NULL,
   col133 varchar(64) NOT NULL,
   col134 varchar(64) NOT NULL,
   col135 varchar(64) NOT NULL,
   col136 varchar(64) NOT NULL,
   col137 varchar(64) NOT NULL,
   col138 varchar(64) NOT NULL,
   col139 varchar(64) NOT NULL,
   col140 varchar(64) NOT NULL,
   col141 varchar(64) NOT NULL,
   col142 varchar(64) NOT NULL,
   col143 varchar(64) NOT NULL,
   col144 varchar(64) NOT NULL,
   col145 varchar(64) NOT NULL,
   col146 varchar(64) NOT NULL,
   col147 varchar(64) NOT NULL,
   col148 varchar(64) NOT NULL,
   col149 varchar(64) NOT NULL,
   col150 varchar(64) NOT NULL,
   col151 varchar(64) NOT NULL,
   col152 varchar(64) NOT NULL,
   col153 varchar(64) NOT NULL,
   col154 varchar(64) NOT NULL,
   col155 varchar(64) NOT NULL,
   col156 varchar(64) NOT NULL,
   col157 varchar(64) NOT NULL,
   col158 varchar(64) NOT NULL,
   col159 varchar(64) NOT NULL,
   col160 varchar(64) NOT NULL,
   col161 varchar(64) NOT NULL,
   col162 varchar(64) NOT NULL,
   col163 varchar(64) NOT NULL,
   col164 varchar(64) NOT NULL,
   col165 varchar(64) NOT NULL,
   col166 varchar(64) NOT NULL,
   col167 varchar(64) NOT NULL,
   col168 varchar(64) NOT NULL,
   col169 varchar(64) NOT NULL,
   col170 varchar(64) NOT NULL,
   col171 varchar(64) NOT NULL,
   col172 varchar(64) NOT NULL,
   col173 varchar(64) NOT NULL,
   col174 varchar(64) NOT NULL,
   col175 varchar(64) NOT NULL,
   col176 varchar(64) NOT NULL,
   col177 varchar(64) NOT NULL,
   col178 varchar(64) NOT NULL,
   col179 varchar(64) NOT NULL,
   col180 varchar(64) NOT NULL,
   col181 varchar(64) NOT NULL,
   col182 varchar(64) NOT NULL,
   col183 varchar(64) NOT NULL,
   col184 varchar(64) NOT NULL,
   col185 varchar(64) NOT NULL,
   col186 varchar(64) NOT NULL,
   col187 varchar(64) NOT NULL,
   col188 varchar(64) NOT NULL,
   col189 varchar(64) NOT NULL,
   col190 varchar(64) NOT NULL,
   col191 varchar(64) NOT NULL,
   col192 varchar(64) NOT NULL,
   col193 varchar(64) NOT NULL,
   col194 varchar(64) NOT NULL,
   col195 varchar(64) NOT NULL,
   col196 varchar(64) NOT NULL,
   col197 varchar(64) NOT NULL,
   col198 varchar(64) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Working Around the Problem

There are a few ways to work around this problem.

If you would like a solution for the problem instead of just working around it, then see the solutions mentioned in the previous section.

Refactoring the Table into Multiple Tables

A safe workaround is to refactor the single wide table, so that its columns are spread among multiple tables.

This workaround can even work if your table is so wide that the previous solutions have failed to solve them problem for your table.

Refactoring Some Columns into JSON

A safe workaround is to refactor some of the columns into a JSON document.

The JSON document can be queried and manipulated using MariaDB’s JSON functions.

The JSON document can be stored in a column that uses one of the following data types:

TEXT: The maximum size of a TEXT column is 64 KB.
MEDIUMTEXT: The maximum size of a MEDIUMTEXT column is 16 MB.
LONGTEXT: The maximum size of a LONGTEXT column is 4 GB.
JSON: This is just an alias for the LONGTEXT data type.

This workaround can even work if your table is so wide that the previous solutions have failed to solve them problem for your table.

Disabling InnoDB Strict Mode

An unsafe workaround is to disable InnoDB strict mode. InnoDB strict mode can be disabled by setting the innodb_strict_mode system variable to OFF.

For example, even though the following table schema is too large for most InnoDB row formats to store, it can still be created when InnoDB strict mode is disabled:

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=OFF;
CREATE OR REPLACE TABLE tab (
   col1 varchar(40) NOT NULL,
   col2 varchar(40) NOT NULL,
   col3 varchar(40) NOT NULL,
   col4 varchar(40) NOT NULL,
   col5 varchar(40) NOT NULL,
   col6 varchar(40) NOT NULL,
   col7 varchar(40) NOT NULL,
   col8 varchar(40) NOT NULL,
   col9 varchar(40) NOT NULL,
   col10 varchar(40) NOT NULL,
   col11 varchar(40) NOT NULL,
   col12 varchar(40) NOT NULL,
   col13 varchar(40) NOT NULL,
   col14 varchar(40) NOT NULL,
   col15 varchar(40) NOT NULL,
   col16 varchar(40) NOT NULL,
   col17 varchar(40) NOT NULL,
   col18 varchar(40) NOT NULL,
   col19 varchar(40) NOT NULL,
   col20 varchar(40) NOT NULL,
   col21 varchar(40) NOT NULL,
   col22 varchar(40) NOT NULL,
   col23 varchar(40) NOT NULL,
   col24 varchar(40) NOT NULL,
   col25 varchar(40) NOT NULL,
   col26 varchar(40) NOT NULL,
   col27 varchar(40) NOT NULL,
   col28 varchar(40) NOT NULL,
   col29 varchar(40) NOT NULL,
   col30 varchar(40) NOT NULL,
   col31 varchar(40) NOT NULL,
   col32 varchar(40) NOT NULL,
   col33 varchar(40) NOT NULL,
   col34 varchar(40) NOT NULL,
   col35 varchar(40) NOT NULL,
   col36 varchar(40) NOT NULL,
   col37 varchar(40) NOT NULL,
   col38 varchar(40) NOT NULL,
   col39 varchar(40) NOT NULL,
   col40 varchar(40) NOT NULL,
   col41 varchar(40) NOT NULL,
   col42 varchar(40) NOT NULL,
   col43 varchar(40) NOT NULL,
   col44 varchar(40) NOT NULL,
   col45 varchar(40) NOT NULL,
   col46 varchar(40) NOT NULL,
   col47 varchar(40) NOT NULL,
   col48 varchar(40) NOT NULL,
   col49 varchar(40) NOT NULL,
   col50 varchar(40) NOT NULL,
   col51 varchar(40) NOT NULL,
   col52 varchar(40) NOT NULL,
   col53 varchar(40) NOT NULL,
   col54 varchar(40) NOT NULL,
   col55 varchar(40) NOT NULL,
   col56 varchar(40) NOT NULL,
   col57 varchar(40) NOT NULL,
   col58 varchar(40) NOT NULL,
   col59 varchar(40) NOT NULL,
   col60 varchar(40) NOT NULL,
   col61 varchar(40) NOT NULL,
   col62 varchar(40) NOT NULL,
   col63 varchar(40) NOT NULL,
   col64 varchar(40) NOT NULL,
   col65 varchar(40) NOT NULL,
   col66 varchar(40) NOT NULL,
   col67 varchar(40) NOT NULL,
   col68 varchar(40) NOT NULL,
   col69 varchar(40) NOT NULL,
   col70 varchar(40) NOT NULL,
   col71 varchar(40) NOT NULL,
   col72 varchar(40) NOT NULL,
   col73 varchar(40) NOT NULL,
   col74 varchar(40) NOT NULL,
   col75 varchar(40) NOT NULL,
   col76 varchar(40) NOT NULL,
   col77 varchar(40) NOT NULL,
   col78 varchar(40) NOT NULL,
   col79 varchar(40) NOT NULL,
   col80 varchar(40) NOT NULL,
   col81 varchar(40) NOT NULL,
   col82 varchar(40) NOT NULL,
   col83 varchar(40) NOT NULL,
   col84 varchar(40) NOT NULL,
   col85 varchar(40) NOT NULL,
   col86 varchar(40) NOT NULL,
   col87 varchar(40) NOT NULL,
   col88 varchar(40) NOT NULL,
   col89 varchar(40) NOT NULL,
   col90 varchar(40) NOT NULL,
   col91 varchar(40) NOT NULL,
   col92 varchar(40) NOT NULL,
   col93 varchar(40) NOT NULL,
   col94 varchar(40) NOT NULL,
   col95 varchar(40) NOT NULL,
   col96 varchar(40) NOT NULL,
   col97 varchar(40) NOT NULL,
   col98 varchar(40) NOT NULL,
   col99 varchar(40) NOT NULL,
   col100 varchar(40) NOT NULL,
   col101 varchar(40) NOT NULL,
   col102 varchar(40) NOT NULL,
   col103 varchar(40) NOT NULL,
   col104 varchar(40) NOT NULL,
   col105 varchar(40) NOT NULL,
   col106 varchar(40) NOT NULL,
   col107 varchar(40) NOT NULL,
   col108 varchar(40) NOT NULL,
   col109 varchar(40) NOT NULL,
   col110 varchar(40) NOT NULL,
   col111 varchar(40) NOT NULL,
   col112 varchar(40) NOT NULL,
   col113 varchar(40) NOT NULL,
   col114 varchar(40) NOT NULL,
   col115 varchar(40) NOT NULL,
   col116 varchar(40) NOT NULL,
   col117 varchar(40) NOT NULL,
   col118 varchar(40) NOT NULL,
   col119 varchar(40) NOT NULL,
   col120 varchar(40) NOT NULL,
   col121 varchar(40) NOT NULL,
   col122 varchar(40) NOT NULL,
   col123 varchar(40) NOT NULL,
   col124 varchar(40) NOT NULL,
   col125 varchar(40) NOT NULL,
   col126 varchar(40) NOT NULL,
   col127 varchar(40) NOT NULL,
   col128 varchar(40) NOT NULL,
   col129 varchar(40) NOT NULL,
   col130 varchar(40) NOT NULL,
   col131 varchar(40) NOT NULL,
   col132 varchar(40) NOT NULL,
   col133 varchar(40) NOT NULL,
   col134 varchar(40) NOT NULL,
   col135 varchar(40) NOT NULL,
   col136 varchar(40) NOT NULL,
   col137 varchar(40) NOT NULL,
   col138 varchar(40) NOT NULL,
   col139 varchar(40) NOT NULL,
   col140 varchar(40) NOT NULL,
   col141 varchar(40) NOT NULL,
   col142 varchar(40) NOT NULL,
   col143 varchar(40) NOT NULL,
   col144 varchar(40) NOT NULL,
   col145 varchar(40) NOT NULL,
   col146 varchar(40) NOT NULL,
   col147 varchar(40) NOT NULL,
   col148 varchar(40) NOT NULL,
   col149 varchar(40) NOT NULL,
   col150 varchar(40) NOT NULL,
   col151 varchar(40) NOT NULL,
   col152 varchar(40) NOT NULL,
   col153 varchar(40) NOT NULL,
   col154 varchar(40) NOT NULL,
   col155 varchar(40) NOT NULL,
   col156 varchar(40) NOT NULL,
   col157 varchar(40) NOT NULL,
   col158 varchar(40) NOT NULL,
   col159 varchar(40) NOT NULL,
   col160 varchar(40) NOT NULL,
   col161 varchar(40) NOT NULL,
   col162 varchar(40) NOT NULL,
   col163 varchar(40) NOT NULL,
   col164 varchar(40) NOT NULL,
   col165 varchar(40) NOT NULL,
   col166 varchar(40) NOT NULL,
   col167 varchar(40) NOT NULL,
   col168 varchar(40) NOT NULL,
   col169 varchar(40) NOT NULL,
   col170 varchar(40) NOT NULL,
   col171 varchar(40) NOT NULL,
   col172 varchar(40) NOT NULL,
   col173 varchar(40) NOT NULL,
   col174 varchar(40) NOT NULL,
   col175 varchar(40) NOT NULL,
   col176 varchar(40) NOT NULL,
   col177 varchar(40) NOT NULL,
   col178 varchar(40) NOT NULL,
   col179 varchar(40) NOT NULL,
   col180 varchar(40) NOT NULL,
   col181 varchar(40) NOT NULL,
   col182 varchar(40) NOT NULL,
   col183 varchar(40) NOT NULL,
   col184 varchar(40) NOT NULL,
   col185 varchar(40) NOT NULL,
   col186 varchar(40) NOT NULL,
   col187 varchar(40) NOT NULL,
   col188 varchar(40) NOT NULL,
   col189 varchar(40) NOT NULL,
   col190 varchar(40) NOT NULL,
   col191 varchar(40) NOT NULL,
   col192 varchar(40) NOT NULL,
   col193 varchar(40) NOT NULL,
   col194 varchar(40) NOT NULL,
   col195 varchar(40) NOT NULL,
   col196 varchar(40) NOT NULL,
   col197 varchar(40) NOT NULL,
   col198 varchar(40) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

But as mentioned above, if InnoDB strict mode is disabled and if a DDL statement is executed, then InnoDB will still raise a warning with this message. The SHOW WARNINGS statement can be used to view the warning. For example:

SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 139 | Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

As mentioned above, even though InnoDB is allowing the table to be created, there is still an opportunity for errors. Regardless of whether InnoDB strict mode is enabled, if a DML statement is executed that attempts to write a row that the table’s InnoDB row format can’t store, then InnoDB will raise an error with this message. This creates a somewhat unsafe situation, because it means that the application has the chance to encounter an additional error while executing DML.

Источник

Я хочу создать таблицу 325

15 ответов

недавно я боролся с тем же кодом ошибки из-за изменения MySQL Server 5.6.20.
Я смог решить проблему, изменив innodb_log_file_size в my.текстовый файл Ини.

в примечаниях к выпуску объясняется, что innodb_log_file_size, который слишком мал, вызовет «размер строки слишком большая ошибка.»

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

Я перепробовал все решения здесь, но только этот параметр

innodb_strict_mode             = 0

решил мой день…

из руководства:

параметр innodb_strict_mode влияет на обработку синтаксических ошибок
для CREATE TABLE, ALTER TABLE и CREATE INDEX операторы.
innodb_strict_mode также включает проверку размера записи, чтобы вставить
или обновление никогда не завершается неудачей из-за того, что запись слишком велика для
выбранный размер страницы.

ERROR 1118 (42000) at line 1852:    
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.

[mysqld]

innodb_log_file_size = 512M

innodb_strict_mode = 0

ubuntu 16.04 путь редактирования:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

в MS Windows путь будет чем-то вроде:

C:ProgramDataMySQLMySQL Server 5.7my.ini

Не забудьте перезапустить службу (или перезагрузить компьютер)

недавно я создал таблицу с 82 столбцами и имел ту же ошибку с InnoDB.
Чтобы обойти проблему, мы переключили формат таблицы на MyISAM поскольку он использовался только для базовой формы.

автор: Guillaume Pommier

MySQL довольно ясно о своем максимальном размере строки:

каждая таблица (независимо от двигателя хранения) имеет максимальный размер строки
65535 байт. Двигатели хранения могут накладывать на это дополнительные ограничения
предел, уменьшая эффективный максимальный размер строки.

. . .

отдельные механизмы хранения могут вводить дополнительные ограничения, которые
ограничить количество столбцов таблицы. Примеры:

InnoDB позволяет до 1000 столбцы.

InnoDB ограничивает размер строки чем-то меньше половины страницы базы данных
(приблизительно 8000 байт), не включая VARBINARY, VARCHAR, BLOB или
текстовые колонки.

различные форматы хранения InnoDB (сжатые, избыточные) используют разные
количество данных заголовка страницы и трейлера, которое влияет на количество
хранилище доступно для строк.

Если у вас есть 325 повторяющихся наборов столбцов, вы превышаете несколько ограничений. Это также подозрительный формат данных. У вас должно быть 325 строк для каждой строки в таблице, которую вы хотите, по одной для каждой группы столбцов.

Я просто хочу предоставить некоторым другим людям помощь с более серьезным вариантом этой проблемы. В некоторых ситуациях ошибка («размер строки слишком большой .. Изменение некоторых столбцов в текст или BLOB») будет происходить даже с операторами» alter table drop column «и» alter table modify column»!

следовательно, вы можете полностью застрять, не в состоянии изменить varchar на текст или удалить столбцы (попытка решить проблему по иронии судьбы приводит к тому же сообщение.)

Если у вас есть эта проблема, решение изменить или удалить несколько столбцов сразу. Вы можете сделать это в MySQL с синтаксисом «alter TABLE example drop column a, drop column b, drop column c», и если вы отбросите достаточно столбцов сразу, он фактически выполнит, а не поднимет ошибку.

для MySQL 5.7 на Mac OS X El Capitan:

OS X предоставляет пример файлов конфигурации в /usr / local/mysql/support-files / my-default.cnf

чтобы добавить переменные, сначала остановите сервер и просто скопируйте файл выше, /usr/local/mysql/etc/my.cnf

cmd : sudo cp /usr/local/mysql/support-files/my-default.cnf /usr/local/mysql/etc/my.cnf

Примечание: создайте папку » etc » под «mysql» в случае, если она не существует.

cmd : sudo mkdir /usr/local/mysql/etc

после того, как мой.cnf создается под etc. пришло время установить переменную внутри этого.

cmd: sudo nano my.cnf

установить переменные ниже [тузды]

[mysqld]
innodb_log_file_size = 512M
innodb_strict_mode = 0

теперь запустите сервер!

изменение в MyISAM не является решением. Для innodb following работал для меня.

установить следующие на мой.cnf

innodb_strict_mode = 0

Я также столкнулся с этим. Изменение «innodb_log_file_size», «innodb_log_buffer_size» и других настроек в » my.ini » файл не решил мою проблему. Я передаю его, изменяя типы столбцов «текст» на varchar(20) и не используя значения varchar больше 20 . Возможно, вы также можете уменьшить размер столбцов, если это возможно.
текст — — — >varchar (20)
varchar(256) —> varchar (20)

(РЕАЛЬНОЕ РЕШЕНИЕ MYSQL 5.7)

я столкнулся с той же ошибкой на новейшем сервере mysql (5.7.21):

размер строки слишком большой (>8126). Изменение некоторых столбцов на TEXT или BLOB может помочь. В текущем формате строки префикс BLOB 0 байт хранится в строке.

потратив несколько часов на чтение руководства MYSQL, нашел решение!

ключевой параметр: innodb_page_size

поддержка размеров страниц 32k и 64k была добавлена в MySQL 5.7. Для 32K и 64k страниц максимальная длина строки составляет приблизительно 16000 байт.

фишка в том, что этот параметр может быть изменен только во время инициализации экземпляра службы mysql, so он не влияет, если вы измените этот параметр после того, как экземпляр уже инициализирован (самый первый запуск экземпляра).

innodb_page_size может быть настроен только до инициализации экземпляра MySQL и не может быть изменен после этого. Если значение не указано, экземпляр инициализируется с использованием размера страницы по умолчанию. См. Раздел 14.6.1, «Конфигурация Запуска InnoDB».

поэтому, если вы не измените это значение в my.ini перед инициализацией значение по умолчанию будет 16K, которое будет иметь ограничение размера строки ~8K. Вот почему возникает ошибка.

Если вы увеличиваете innodb_page_size, то innodb_log_buffer_size необходимо также увеличить. установите его по крайней мере на 16M. также, если ROW_FORMAT имеет значение сжатый вы не можете увеличить innodb_page_size до 32k или 64K. Он должен быть динамическим (по умолчанию в 5.7).

ROW_FORMAT=COMPRESSED не поддерживается, если innodb_page_size имеет значение 32KB или 64KB. Для innodb_page_size=32k размер экстента составляет 2 МБ. Для innodb_page_size=64k, размер экстента 4 МБ. innodb_log_buffer_size должен быть установлен как минимум 16M (по умолчанию) при использовании 32K или 64k размеров страниц.

кроме того,значение параметра innodb_buffer_pool_size должно быть увеличено с 128 м до 512 м по крайней мере, в противном случае вы получите ошибку при инициализации экземпляра (у меня нет точной ошибки).

после этого ошибка размера строки исчезла.

проблема с этим это то, что вам нужно создать новый экземпляр MySql и перенести данные в новый экземпляр базы данных из старого.

параметры, которые я изменил и работает (после создания нового экземпляра и инициализации с моей.ini, который сначала изменяется с этими настройками):

innodb_page_size=64k
innodb_log_buffer_size=32M
innodb_buffer_pool_size=512M

все настройки и описания, в которых я нашел решение, можно найти здесь:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

надеюсь, что это помогает!

С уважением!

есть аналогичный вопрос сегодня утром и следующим образом спас мою жизнь:

вы пытаетесь отключить innodb_strict_mode?

SET GLOBAL innodb_strict_mode = 0;

а затем повторите попытку импорта.

innodb_strict_mode включен с помощью MySQL >= 5.7.7, прежде чем был выключен.

ни один из ответов на сегодняшний день не упоминает эффект параметра innodb_page_size. Возможно, потому, что изменение этого параметра не поддерживается работа до MySQL 5.7.6. От документация:

максимальная длина строки, за исключением столбцов переменной длины (VARBINARY, VARCHAR, BLOB и TEXT), немного меньше половины страницы базы данных для размеров страниц 4KB, 8KB, 16KB и 32KB. Например, максимальная длина строки по умолчанию innodb_page_size 16KB составляет около 8000 байт. Для страницы InnoDB размером 64 КБ максимальная длина строки составляет около 16000 байт. Столбцы LONGBLOB и LONGTEXT должны быть меньше 4 ГБ, а общая длина строки, включая столбцы BLOB и TEXT, должна быть меньше 4 ГБ.

обратите внимание, что увеличение размера страницы не лишена своих недостатков. Опять же из документации:

начиная с MySQL 5.7.6, 32KB и 64KB размеры страниц поддерживаются, но ROW_FORMAT=сжатый по-прежнему не поддерживается для размеров страниц больше 16KB. Для 32KB и 64KB страниц максимальный размер записи-16KB. Для innodb_page_size=32k размер экстента составляет 2 МБ. Для innodb_page_size=64К, размер мере 4МБ.

экземпляр MySQL, использующий определенный размер страницы InnoDB, не может использовать файлы данных или файлы журнала из экземпляра, который использует другой размер страницы. Это ограничение может повлиять на операции восстановления или понижения рейтинга с использованием данных MySQL 5.6, который поддерживает размеры страниц другие, чем 16 КБ.

Если вы используете MySQLWorkbench у вас есть возможность изменить, чтобы изменить query_alloc_block_size= 16258 и сохранить его.

Шаг 1. нажмите на кнопку options file с левой стороны.

Шаг 2: Нажмите кнопку General и выберите checkBox query_alloc_block_size и увеличить их размер. например, измените 8129 —> 16258

в моем случае это был корпус из ограничений на количество столбцов таблицы и размер строки
и внесение изменений, описанных в этом ответе, спасло мой день.

добавьте следующее в my.файл cnf в разделе [mysqld].

innodb_file_per_table
innodb_file_format = Барракуда
измените таблицу, чтобы использовать ROW_FORMAT=COMPRESSED.

ALTER TABLE имя_таблицы
Двигатель=InnoDB
ПОМОЩЬЮ ROW_FORMAT=СЖАТОМ
Key_block_size средства=8;

https://stackoverflow.com/a/15585700/2195130

Источник

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:

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:

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]>

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_sizeand 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)

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):

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]>

Disabling InnoDB Strict Mode

It’s unsafe to disable InnoDB strict mode and we don’t recommend you this setting . You can disable InnoDB strict mode by setting system variable innodb_strict_mode to OFF

MariaDB [mdblab]> SET GLOBAL innodb_default_row_format='dynamic';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mdblab]> SET SESSION innodb_strict_mode=OFF;
Query OK, 0 rows affected (0.000 sec)

CREATE OR REPLACE TABLE tab (
   col1 varchar(40) NOT NULL,
   col2 varchar(40) NOT NULL,
   col3 varchar(40) NOT NULL,
   col4 varchar(40) NOT NULL,
   col5 varchar(40) NOT NULL,
   col6 varchar(40) NOT NULL,
   col7 varchar(40) NOT NULL,
   col8 varchar(40) NOT NULL,
   col9 varchar(40) NOT NULL,
   col10 varchar(40) NOT NULL,
   col11 varchar(40) NOT NULL,
   col12 varchar(40) NOT NULL,
   col13 varchar(40) NOT NULL,
   col14 varchar(40) NOT NULL,
   col15 varchar(40) NOT NULL,
   col16 varchar(40) NOT NULL,
   col17 varchar(40) NOT NULL,
   col18 varchar(40) NOT NULL,
   col19 varchar(40) NOT NULL,
   col20 varchar(40) NOT NULL,
   col21 varchar(40) NOT NULL,
   col22 varchar(40) NOT NULL,
   col23 varchar(40) NOT NULL,
   col24 varchar(40) NOT NULL,
   col25 varchar(40) NOT NULL,
   col26 varchar(40) NOT NULL,
   col27 varchar(40) NOT NULL,
   col28 varchar(40) NOT NULL,
   col29 varchar(40) NOT NULL,
   col30 varchar(40) NOT NULL,
   col31 varchar(40) NOT NULL,
   col32 varchar(40) NOT NULL,
   col33 varchar(40) NOT NULL,
   col34 varchar(40) NOT NULL,
   col35 varchar(40) NOT NULL,
   col36 varchar(40) NOT NULL,
   col37 varchar(40) NOT NULL,
   col38 varchar(40) NOT NULL,
   col39 varchar(40) NOT NULL,
   col40 varchar(40) NOT NULL,
   col41 varchar(40) NOT NULL,
   col42 varchar(40) NOT NULL,
   col43 varchar(40) NOT NULL,
   col44 varchar(40) NOT NULL,
   col45 varchar(40) NOT NULL,
   col46 varchar(40) NOT NULL,
   col47 varchar(40) NOT NULL,
   col48 varchar(40) NOT NULL,
   col49 varchar(40) NOT NULL,
   col50 varchar(40) NOT NULL,
   col51 varchar(40) NOT NULL,
   col52 varchar(40) NOT NULL,
   col53 varchar(40) NOT NULL,
   col54 varchar(40) NOT NULL,
   col55 varchar(40) NOT NULL,
   col56 varchar(40) NOT NULL,
   col57 varchar(40) NOT NULL,
   col58 varchar(40) NOT NULL,
   col59 varchar(40) NOT NULL,
   col60 varchar(40) NOT NULL,
   col61 varchar(40) NOT NULL,
   col62 varchar(40) NOT NULL,
   col63 varchar(40) NOT NULL,
   col64 varchar(40) NOT NULL,
   col65 varchar(40) NOT NULL,
   col66 varchar(40) NOT NULL,
   col67 varchar(40) NOT NULL,
   col68 varchar(40) NOT NULL,
   col69 varchar(40) NOT NULL,
   col70 varchar(40) NOT NULL,
   col71 varchar(40) NOT NULL,
   col72 varchar(40) NOT NULL,
   col73 varchar(40) NOT NULL,
   col74 varchar(40) NOT NULL,
   col75 varchar(40) NOT NULL,
   col76 varchar(40) NOT NULL,
   col77 varchar(40) NOT NULL,
   col78 varchar(40) NOT NULL,
   col79 varchar(40) NOT NULL,
   col80 varchar(40) NOT NULL,
   col81 varchar(40) NOT NULL,
   col82 varchar(40) NOT NULL,
   col83 varchar(40) NOT NULL,
   col84 varchar(40) NOT NULL,
   col85 varchar(40) NOT NULL,
   col86 varchar(40) NOT NULL,
   col87 varchar(40) NOT NULL,
   col88 varchar(40) NOT NULL,
   col89 varchar(40) NOT NULL,
   col90 varchar(40) NOT NULL,
   col91 varchar(40) NOT NULL,
   col92 varchar(40) NOT NULL,
   col93 varchar(40) NOT NULL,
   col94 varchar(40) NOT NULL,
   col95 varchar(40) NOT NULL,
   col96 varchar(40) NOT NULL,
   col97 varchar(40) NOT NULL,
   col98 varchar(40) NOT NULL,
   col99 varchar(40) NOT NULL,
   col100 varchar(40) NOT NULL,
   col101 varchar(40) NOT NULL,
   col102 varchar(40) NOT NULL,
   col103 varchar(40) NOT NULL,
   col104 varchar(40) NOT NULL,
   col105 varchar(40) NOT NULL,
   col106 varchar(40) NOT NULL,
   col107 varchar(40) NOT NULL,
   col108 varchar(40) NOT NULL,
   col109 varchar(40) NOT NULL,
   col110 varchar(40) NOT NULL,
   col111 varchar(40) NOT NULL,
   col112 varchar(40) NOT NULL,
   col113 varchar(40) NOT NULL,
   col114 varchar(40) NOT NULL,
   col115 varchar(40) NOT NULL,
   col116 varchar(40) NOT NULL,
   col117 varchar(40) NOT NULL,
   col118 varchar(40) NOT NULL,
   col119 varchar(40) NOT NULL,
   col120 varchar(40) NOT NULL,
   col121 varchar(40) NOT NULL,
   col122 varchar(40) NOT NULL,
   col123 varchar(40) NOT NULL,
   col124 varchar(40) NOT NULL,
   col125 varchar(40) NOT NULL,
   col126 varchar(40) NOT NULL,
   col127 varchar(40) NOT NULL,
   col128 varchar(40) NOT NULL,
   col129 varchar(40) NOT NULL,
   col130 varchar(40) NOT NULL,
   col131 varchar(40) NOT NULL,
   col132 varchar(40) NOT NULL,
   col133 varchar(40) NOT NULL,
   col134 varchar(40) NOT NULL,
   col135 varchar(40) NOT NULL,
   col136 varchar(40) NOT NULL,
   col137 varchar(40) NOT NULL,
   col138 varchar(40) NOT NULL,
   col139 varchar(40) NOT NULL,
   col140 varchar(40) NOT NULL,
   col141 varchar(40) NOT NULL,
   col142 varchar(40) NOT NULL,
   col143 varchar(40) NOT NULL,
   col144 varchar(40) NOT NULL,
   col145 varchar(40) NOT NULL,
   col146 varchar(40) NOT NULL,
   col147 varchar(40) NOT NULL,
   col148 varchar(40) NOT NULL,
   col149 varchar(40) NOT NULL,
   col150 varchar(40) NOT NULL,
   col151 varchar(40) NOT NULL,
   col152 varchar(40) NOT NULL,
   col153 varchar(40) NOT NULL,
   col154 varchar(40) NOT NULL,
   col155 varchar(40) NOT NULL,
   col156 varchar(40) NOT NULL,
   col157 varchar(40) NOT NULL,
   col158 varchar(40) NOT NULL,
   col159 varchar(40) NOT NULL,
   col160 varchar(40) NOT NULL,
   col161 varchar(40) NOT NULL,
   col162 varchar(40) NOT NULL,
   col163 varchar(40) NOT NULL,
   col164 varchar(40) NOT NULL,
   col165 varchar(40) NOT NULL,
   col166 varchar(40) NOT NULL,
   col167 varchar(40) NOT NULL,
   col168 varchar(40) NOT NULL,
   col169 varchar(40) NOT NULL,
   col170 varchar(40) NOT NULL,
   col171 varchar(40) NOT NULL,
   col172 varchar(40) NOT NULL,
   col173 varchar(40) NOT NULL,
   col174 varchar(40) NOT NULL,
   col175 varchar(40) NOT NULL,
   col176 varchar(40) NOT NULL,
   col177 varchar(40) NOT NULL,
   col178 varchar(40) NOT NULL,
   col179 varchar(40) NOT NULL,
   col180 varchar(40) NOT NULL,
   col181 varchar(40) NOT NULL,
   col182 varchar(40) NOT NULL,
   col183 varchar(40) NOT NULL,
   col184 varchar(40) NOT NULL,
   col185 varchar(40) NOT NULL,
   col186 varchar(40) NOT NULL,
   col187 varchar(40) NOT NULL,
   col188 varchar(40) NOT NULL,
   col189 varchar(40) NOT NULL,
   col190 varchar(40) NOT NULL,
   col191 varchar(40) NOT NULL,
   col192 varchar(40) NOT NULL,
   col193 varchar(40) NOT NULL,
   col194 varchar(40) NOT NULL,
   col195 varchar(40) NOT NULL,
   col196 varchar(40) NOT NULL,
   col197 varchar(40) NOT NULL,
   col198 varchar(40) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

MariaDB [mdblab]> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                      |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  139 | Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mdblab]>

References

InnoDB System Variables explained – https://mariadb.com/kb/en/innodb-system-variables/
Row size too large – https://jira.mariadb.org/browse/MDEV-10364

Источник

При использовании InnoDB следующее сообщение может быть воспринято пользователем как ошибка или предупреждение:

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to 
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored 
inline.

И они также могут увидеть следующее сообщение как ошибку или предупреждение в журнале ошибок :

[Warning] InnoDB: Cannot add field col in table db1.tab because after adding it, 
the row size is 8478 which is greater than maximum allowed size (8126) for a 
record on index leaf page.

Эти сообщения указывают на то,что определение таблицы разрешает строки,которые формат строк таблицы InnoDB на самом деле не может хранить.

Эти сообщения поднимаются в следующих случаях:

Если InnoDB строгий режим будет включен , и если DDL выполняется оператор , который затрагивает таблицы, такой как CREATE TABLE или ALTER TABLE , то InnoDB вызовет ошибку с этим сообщением
Если строгий режим InnoDB отключен и если выполняется оператор DDL , который касается таблицы, такой как CREATE TABLE или ALTER TABLE , то InnoDB выдаст предупреждение с этим сообщением.
Независимо от того, включен ли строгий режим InnoDB , если выполняется оператор DML, который пытается записать строку, которую формат строки InnoDB таблицы не может сохранить, тогда InnoDB вызовет ошибку с этим сообщением.

Пример проблемы

Вот пример проблемы:

SET GLOBAL innodb_default_row_format='dynamic';

SET SESSION innodb_strict_mode=ON;

CREATE OR REPLACE TABLE tab (
   col1 varchar(40) NOT NULL,
   col2 varchar(40) NOT NULL,
   col3 varchar(40) NOT NULL,
   col4 varchar(40) NOT NULL,
   col5 varchar(40) NOT NULL,
   col6 varchar(40) NOT NULL,
   col7 varchar(40) NOT NULL,
   col8 varchar(40) NOT NULL,
   col9 varchar(40) NOT NULL,
   col10 varchar(40) NOT NULL,
   col11 varchar(40) NOT NULL,
   col12 varchar(40) NOT NULL,
   col13 varchar(40) NOT NULL,
   col14 varchar(40) NOT NULL,
   col15 varchar(40) NOT NULL,
   col16 varchar(40) NOT NULL,
   col17 varchar(40) NOT NULL,
   col18 varchar(40) NOT NULL,
   col19 varchar(40) NOT NULL,
   col20 varchar(40) NOT NULL,
   col21 varchar(40) NOT NULL,
   col22 varchar(40) NOT NULL,
   col23 varchar(40) NOT NULL,
   col24 varchar(40) NOT NULL,
   col25 varchar(40) NOT NULL,
   col26 varchar(40) NOT NULL,
   col27 varchar(40) NOT NULL,
   col28 varchar(40) NOT NULL,
   col29 varchar(40) NOT NULL,
   col30 varchar(40) NOT NULL,
   col31 varchar(40) NOT NULL,
   col32 varchar(40) NOT NULL,
   col33 varchar(40) NOT NULL,
   col34 varchar(40) NOT NULL,
   col35 varchar(40) NOT NULL,
   col36 varchar(40) NOT NULL,
   col37 varchar(40) NOT NULL,
   col38 varchar(40) NOT NULL,
   col39 varchar(40) NOT NULL,
   col40 varchar(40) NOT NULL,
   col41 varchar(40) NOT NULL,
   col42 varchar(40) NOT NULL,
   col43 varchar(40) NOT NULL,
   col44 varchar(40) NOT NULL,
   col45 varchar(40) NOT NULL,
   col46 varchar(40) NOT NULL,
   col47 varchar(40) NOT NULL,
   col48 varchar(40) NOT NULL,
   col49 varchar(40) NOT NULL,
   col50 varchar(40) NOT NULL,
   col51 varchar(40) NOT NULL,
   col52 varchar(40) NOT NULL,
   col53 varchar(40) NOT NULL,
   col54 varchar(40) NOT NULL,
   col55 varchar(40) NOT NULL,
   col56 varchar(40) NOT NULL,
   col57 varchar(40) NOT NULL,
   col58 varchar(40) NOT NULL,
   col59 varchar(40) NOT NULL,
   col60 varchar(40) NOT NULL,
   col61 varchar(40) NOT NULL,
   col62 varchar(40) NOT NULL,
   col63 varchar(40) NOT NULL,
   col64 varchar(40) NOT NULL,
   col65 varchar(40) NOT NULL,
   col66 varchar(40) NOT NULL,
   col67 varchar(40) NOT NULL,
   col68 varchar(40) NOT NULL,
   col69 varchar(40) NOT NULL,
   col70 varchar(40) NOT NULL,
   col71 varchar(40) NOT NULL,
   col72 varchar(40) NOT NULL,
   col73 varchar(40) NOT NULL,
   col74 varchar(40) NOT NULL,
   col75 varchar(40) NOT NULL,
   col76 varchar(40) NOT NULL,
   col77 varchar(40) NOT NULL,
   col78 varchar(40) NOT NULL,
   col79 varchar(40) NOT NULL,
   col80 varchar(40) NOT NULL,
   col81 varchar(40) NOT NULL,
   col82 varchar(40) NOT NULL,
   col83 varchar(40) NOT NULL,
   col84 varchar(40) NOT NULL,
   col85 varchar(40) NOT NULL,
   col86 varchar(40) NOT NULL,
   col87 varchar(40) NOT NULL,
   col88 varchar(40) NOT NULL,
   col89 varchar(40) NOT NULL,
   col90 varchar(40) NOT NULL,
   col91 varchar(40) NOT NULL,
   col92 varchar(40) NOT NULL,
   col93 varchar(40) NOT NULL,
   col94 varchar(40) NOT NULL,
   col95 varchar(40) NOT NULL,
   col96 varchar(40) NOT NULL,
   col97 varchar(40) NOT NULL,
   col98 varchar(40) NOT NULL,
   col99 varchar(40) NOT NULL,
   col100 varchar(40) NOT NULL,
   col101 varchar(40) NOT NULL,
   col102 varchar(40) NOT NULL,
   col103 varchar(40) NOT NULL,
   col104 varchar(40) NOT NULL,
   col105 varchar(40) NOT NULL,
   col106 varchar(40) NOT NULL,
   col107 varchar(40) NOT NULL,
   col108 varchar(40) NOT NULL,
   col109 varchar(40) NOT NULL,
   col110 varchar(40) NOT NULL,
   col111 varchar(40) NOT NULL,
   col112 varchar(40) NOT NULL,
   col113 varchar(40) NOT NULL,
   col114 varchar(40) NOT NULL,
   col115 varchar(40) NOT NULL,
   col116 varchar(40) NOT NULL,
   col117 varchar(40) NOT NULL,
   col118 varchar(40) NOT NULL,
   col119 varchar(40) NOT NULL,
   col120 varchar(40) NOT NULL,
   col121 varchar(40) NOT NULL,
   col122 varchar(40) NOT NULL,
   col123 varchar(40) NOT NULL,
   col124 varchar(40) NOT NULL,
   col125 varchar(40) NOT NULL,
   col126 varchar(40) NOT NULL,
   col127 varchar(40) NOT NULL,
   col128 varchar(40) NOT NULL,
   col129 varchar(40) NOT NULL,
   col130 varchar(40) NOT NULL,
   col131 varchar(40) NOT NULL,
   col132 varchar(40) NOT NULL,
   col133 varchar(40) NOT NULL,
   col134 varchar(40) NOT NULL,
   col135 varchar(40) NOT NULL,
   col136 varchar(40) NOT NULL,
   col137 varchar(40) NOT NULL,
   col138 varchar(40) NOT NULL,
   col139 varchar(40) NOT NULL,
   col140 varchar(40) NOT NULL,
   col141 varchar(40) NOT NULL,
   col142 varchar(40) NOT NULL,
   col143 varchar(40) NOT NULL,
   col144 varchar(40) NOT NULL,
   col145 varchar(40) NOT NULL,
   col146 varchar(40) NOT NULL,
   col147 varchar(40) NOT NULL,
   col148 varchar(40) NOT NULL,
   col149 varchar(40) NOT NULL,
   col150 varchar(40) NOT NULL,
   col151 varchar(40) NOT NULL,
   col152 varchar(40) NOT NULL,
   col153 varchar(40) NOT NULL,
   col154 varchar(40) NOT NULL,
   col155 varchar(40) NOT NULL,
   col156 varchar(40) NOT NULL,
   col157 varchar(40) NOT NULL,
   col158 varchar(40) NOT NULL,
   col159 varchar(40) NOT NULL,
   col160 varchar(40) NOT NULL,
   col161 varchar(40) NOT NULL,
   col162 varchar(40) NOT NULL,
   col163 varchar(40) NOT NULL,
   col164 varchar(40) NOT NULL,
   col165 varchar(40) NOT NULL,
   col166 varchar(40) NOT NULL,
   col167 varchar(40) NOT NULL,
   col168 varchar(40) NOT NULL,
   col169 varchar(40) NOT NULL,
   col170 varchar(40) NOT NULL,
   col171 varchar(40) NOT NULL,
   col172 varchar(40) NOT NULL,
   col173 varchar(40) NOT NULL,
   col174 varchar(40) NOT NULL,
   col175 varchar(40) NOT NULL,
   col176 varchar(40) NOT NULL,
   col177 varchar(40) NOT NULL,
   col178 varchar(40) NOT NULL,
   col179 varchar(40) NOT NULL,
   col180 varchar(40) NOT NULL,
   col181 varchar(40) NOT NULL,
   col182 varchar(40) NOT NULL,
   col183 varchar(40) NOT NULL,
   col184 varchar(40) NOT NULL,
   col185 varchar(40) NOT NULL,
   col186 varchar(40) NOT NULL,
   col187 varchar(40) NOT NULL,
   col188 varchar(40) NOT NULL,
   col189 varchar(40) NOT NULL,
   col190 varchar(40) NOT NULL,
   col191 varchar(40) NOT NULL,
   col192 varchar(40) NOT NULL,
   col193 varchar(40) NOT NULL,
   col194 varchar(40) NOT NULL,
   col195 varchar(40) NOT NULL,
   col196 varchar(40) NOT NULL,
   col197 varchar(40) NOT NULL,
   col198 varchar(40) NOT NULL,
   PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to 
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Коренная причина проблемы

Основная причина заключается в том, что максимальный размер строки InnoDB примерно равен половине значения системной переменной innodb_page_size . См. Обзор форматов строк InnoDB: максимальный размер строки для получения дополнительной информации.

Форматы строк InnoDB обходят это ограничение, сохраняя определенные типы столбцов переменной длины на страницах переполнения. Однако разные форматы строк могут хранить разные типы данных на страницах переполнения. Некоторые форматы строк могут хранить больше данных на страницах переполнения, чем другие. Например, форматы строк ДИНАМИЧЕСКИЙ и СЖАТЫЙ могут хранить большую часть данных на страницах переполнения. Чтобы узнать, как различные форматы строк InnoDB используют страницы переполнения, см. следующие страницы:

Формат рядков InnoDB REDUNDANT:Переполнение Страницы с Форматом строк REDUNDANT
Формат рядков InnoDB COMPACT:Переполнение Страницы с Форматом Рядовой Компакт
InnoDB DYNAMIC Row Format:Переливные страницы с форматом ряда DYNAMIC
Формат «InnoDB COMPRESSED Row»:Переполнение Страницы с Форматом сжатых рядов

Проверка существующих таблиц на наличие проблемы

InnoDB в настоящее время не имеет простого способа проверить все существующие таблицы, чтобы определить, в каких таблицах есть эта проблема. См. MDEV-20400 для получения дополнительной информации.

Один из способов проверить одну существующую таблицу на наличие этой проблемы — включить строгий режим InnoDB , а затем попытаться создать дубликат таблицы с помощью CREATE TABLE… LIKE . Если в таблице есть эта проблема, операция завершится ошибкой. Например:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE tab_dup LIKE tab;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to 
TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Поиск всех таблиц,в которых в настоящее время существует проблема

Следующий сценарий оболочки будет читать сервер MariaDB,чтобы определить каждую таблицу,которая имеет определение размера строки,слишком большое для ее формата строки и размера страницы сервера.Он работает на большинстве распространенных дистрибутивов Linux.

Чтобы запустить сценарий, скопируйте приведенный ниже код в сценарий оболочки с именем rowsize.sh , сделайте его исполняемым с помощью команды chmod 755 ./rowsize.sh и вызовите его со следующими параметрами:

./rowsize.sh host user password

Когда сценарий запущен,он отображает имя временной базы данных,которую он создает,так что если сценарий будет прерван перед очисткой,базу данных можно будет легко определить и удалить вручную.

По мере выполнения скрипта он выведет одну строку,сообщающую базу данных и имя вкладки для каждой таблицы,в которой обнаружена проблема превышения размера строки.Если он не найдет ни одной,то выведет следующее сообщение:»Таблиц со слишком большим размером строк не найдено».

В любом случае сценарий выводит последнюю строку, чтобы объявить о завершении: ./rowsize.sh done.

#!/bin/bash

[ -z "$3" ] && echo "Usage: $0 host user password" >&2 && exit 1

dt="tmp_$RANDOM$RANDOM"

mysql -h $1 -u $2 -p$3 -ABNe "create database $dt;"
[ $? -ne 0 ] && echo "Error: $0 terminating" >&2 exit 1

echo
echo "Created temporary database ${dt} on host $1"
echo

c=0
for d in $(mysql -h $1 -u $2 -p$3 -ABNe "show databases;" | egrep -iv "information_schema|mysql|performance_schema|$dt")
do
	for t in $(mysql -h $1 -u $2 -p$3 -ABNe "show tables;" $d)
	do
		tc=$(mysql -h $1 -u $2 -p$3 -ABNe "show create table $t\G" $d | egrep -iv "^*|^$t")
		
		echo $tc | grep -iq "ROW_FORMAT"
		if [ $? -ne 0 ]
		then
			tf=$(mysql -h $1 -u $2 -p$3 -ABNe "select row_format from information_schema.innodb_sys_tables where name = '${d}/${t}';")
			tc="$tc ROW_FORMAT=$tf"
		fi
		
		ef="/tmp/e$RANDOM$RANDOM"
		mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; set foreign_key_checks=0; ${tc};" $dt >/dev/null  2>$ef
		[ $? -ne 0 ] && cat $ef | grep -q "Row size too large" && echo "${d}.${t}" && let c++ || mysql -h $1 -u $2 -p$3 -ABNe "drop table if exists ${t};" $dt
		rm -f $ef
	done
done
mysql -h $1 -u $2 -p$3 -ABNe "set innodb_strict_mode=1; drop database $dt;"
[ $c -eq 0 ] && echo "No tables with rows size too large found." || echo && echo "$c tables found with row size too large."
echo
echo "$0 done."

Источник

If you are working with MySQL 5.7 and you find an exception when storing a lot of fields with text format:

SQLSTATE[42000]: Syntax error or access violation: 1118 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.

And you are sure that the row format of the table is set to Dynamic, you are probably facing an issue with the innodb log file size and the strict mode. In this article, we’ll show you how to prevent this exception from appearing in MySQL 5.7.

1. Find my.cnf file

As first step, you will need to search for the configuration file of MySQL. There is no internal MySQL command to trace the location of this file, so the file might be in 5 (or more) locations, and they would all be valid because they load cascading:

/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
[datadir]/my.cnf
~/.my.cnf

Those are the default locations MySQL looks at, however if you still don’t find the correct file, you may run the following command on your terminal:

find / -name my.cnf

Once you find the file, open it with a CLI editor like nano and follow the next step.

2. Increase innodb_log_file_size value

In our case, the file is located at /etc/mysql/my.cnf, so we could edit the file with nano using the file with the following command:

nano /etc/mysql/my.cnf

You will need to disable the strict mode of MySQL and increase the size of innodb log file. When innodb_strict_mode is enabled, InnoDB returns errors rather than warnings for certain conditions. Like many database management systems, MySQL uses logs to achieve data durability (when using the default InnoDB storage engine). This ensures that when a transaction is committed, data is not lost in the event of crash or power loss. MySQL’s InnoDB storage engine uses a fixed size (circular) Redo log space. The size is controlled by innodb_log_file_size. If you increase the value of this property, you will get rid off this exception when storing multiple columns of text in MySQL 5.7.

The theme about which size is right for the innodb log file won’t be covered in this article, instead we recommend you to read this article that contains a detailed explanation and facts about how to choose this value. As we are just sharing with you the solution to this problem, we’ll use the value of 512M, so the parameters to add to the mysqld block of the my.cfn file will be:

# Important: inside the mysqld block
[mysqld]
# Add new log file size
innodb_log_file_size=512M
# Disable strict mode
innodb_strict_mode=0

An example of how the file should look like:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
# Add new log file size
innodb_log_file_size=512M
# Disable strict mode
innodb_strict_mode=0

Although we didn’t cover a detailed explanation of the problem caused by the row size limitation that is removed by the dynamic row format, you may want to inform yourself about this problem visiting this article. After saving changes in the file , restart mysql with the cli depending of your os and installation process e.g:

# Ubuntu
sudo service mysql restart

# CentOS
/etc/init.d/mysqld start

Happy coding !

Источник

Contents

Example of the Problem

Root Cause of the Problem

Checking Existing Tables for the Problem

Finding All Tables That Currently Have the Problem

Solving the Problem

Converting the Table to the DYNAMIC Row Format

Fitting More Columns on Overflow Pages

Converting Some Columns to `BLOB` or `TEXT`

Increasing the Length of `VARBINARY` Columns

Increasing the Length of `VARCHAR` Columns

Working Around the Problem

Refactoring the Table into Multiple Tables

Refactoring Some Columns into JSON

Disabling InnoDB Strict Mode

15 ответов

How to solve this problem ?

More hacks to fix this problem

Disabling InnoDB Strict Mode

References

Пример проблемы

Коренная причина проблемы

Проверка существующих таблиц на наличие проблемы

Поиск всех таблиц,в которых в настоящее время существует проблема

1. Find my.cnf file

2. Increase innodb_log_file_size value

Contents

Example of the Problem

Root Cause of the Problem

Checking Existing Tables for the Problem

Finding All Tables That Currently Have the Problem

Solving the Problem

Converting the Table to the DYNAMIC Row Format

Fitting More Columns on Overflow Pages

Converting Some Columns to BLOB or TEXT

Increasing the Length of VARBINARY Columns

Increasing the Length of VARCHAR Columns

Working Around the Problem

Refactoring the Table into Multiple Tables

Refactoring Some Columns into JSON

Disabling InnoDB Strict Mode

15 ответов

How to solve this problem ?

More hacks to fix this problem

Disabling InnoDB Strict Mode

References

Пример проблемы

Коренная причина проблемы

Проверка существующих таблиц на наличие проблемы

Поиск всех таблиц,в которых в настоящее время существует проблема

1. Find my.cnf file

2. Increase innodb_log_file_size value

Converting Some Columns to `BLOB` or `TEXT`

Increasing the Length of `VARBINARY` Columns

Increasing the Length of `VARCHAR` Columns