Error 1118 mysql - Исправление ошибок и поиск оптимальных решений проблем

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.

Источник

Stuck by the error ‘MySQL error 1118’?

Generally, this error occurs due to the status of innodb_strict_mode set in the database configuration file or due to the value of innodb_log_file_size.

At Bobcares, we often receive requests to resolve MySQL errors as a part of our Server Management Services.

Today, let’s see how our Support Engineers troubleshoot this error and fix it.

What causes MySQL error 1118 to occur?

Before getting into the fix, let’s discuss what causes this error to occur. Here, are the different reasons for this error to occur in the database server.

1. Status of innodb_strict_mode

InnoDB raises an error in certain cases only, rather than pop-up a warning message and execute the specified command.

The status set for InnoDB strict mode affects the handling of syntax errors on the CREATE TABLE, ALTER TABLE and CREATE INDEX commands.

If the strict mode is not enabled then InnoDB will ignore a few syntax errors and will create the table or index by providing only a warning message log.

2. Value of innodb_log_file_size

The log file size must be set as big as possible. However, not bigger than the necessary value. A bigger log file size is better for performance.

So, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows.

In case, if the value of innodb_log_file_size is smaller then it will trigger errors.

How we fix MySQL error 1118?

Having a decade of experience in managing servers, our Dedicated Engineers are familiar with these MySQL errors. Now, let’s discuss how our Support Engineers fix this error.

Recently, one of our customers ran into a problem using a MySQL database. He received the below error message while inserting values into the database tables.

ERROR 1118 (42000) at line 7019: 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.mysql database_xx_yy < database_xx_yy_dump.sql && mysql database_xx_yy < database_xx_yy_dump.sql2019-11-07T15:19:38.742462Z 9 [ERROR] [MY-011825] [InnoDB] Cannot add field LINE_STATUS_CODE2 in table database_xx_yy.xxomds_so_line_if because after adding it, the row size is 8166 which is greater than maximum allowed size (8126) for a record on index leaf page.

On investigating, we found that the Row size was too large for the database backup “database_xx_yy.sql”. Here, the innodb_strict_mode was causing the error. So, we disabled that parameter through the MySQL command prompt.

Initially, we checked for the innodb_strict_mode value using the command:

show variables like '%strict%';

Then, we ran the below command to disable InnoDB strict mode.

SET GLOBAL innodb_strict_mode = OFF;

After that, we rechecked it and here is the output.

Also, we have set innodb_strict_mode to 0 in /etc/my.cnf.d/mysql-server.cnf so that it won’t reset after any reboot. We did it by following the below steps:

We navigated to the path /etc/my.cnf.d

cd /etc/my.cnf.d

Then, we edited the file mysql-server.cnf

vi mysql-server.cnf

Here, we set innodb_strict_mode to 0

innodb_strict_mode = 0

Lastly, we saved and closed the file.

Finally, this fixed the error.

[Need any assistance with MySQL errors? – We’ll help you]

Conclusion

In short, the MySQL error 1118 occurs due to the status of innodb_strict_mode set in the configuration file or due to the value of innodb_log_file_size. Today, we saw how our Support Engineers fix this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

Приветствую тебя, читатель! Если ты, как и я однажды, столкнулся с этой ошибкой, скорее всего ты либо работаешь над крупным проектом, в базе данных которого встретилась таблица с большим количеством текстовых полей, либо схему данных твоего проекта нельзя назвать слишком-то оптимальной. В моём случае большое количество полей типа TEXT было действительно необходимо, что и привело к данной ошибке, которая стала для меня совершенной неожиданностью.

Вот её полный текст:

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.

Возможен ещё такой вариант:

Got error 139 from storage engine.

Неявные ограничения InnoDB

В InnoDB по умолчанию существует ограничение на объем данных, которые можно хранить в одной строке таблицы. Оно составляет 8 килобайт (половина от объема страницы памяти, с которыми работает MySQL). Поскольку первые 768 байтов каждого текстового поля хранится непосредственно в табличных строках, максимальное количество текстовых полей длиной больше 768 байт, которое можно безопасно создать в таблице InnoDB — 10.

Надо сказать, что эта особенность относится не только к полям типа TEXT, но и к полям любого другого типа, просто с помощью текстовых полей у нас больше шансов достигнуть лимита. Если архитектура проекта всё же требует именно такой структуры данных, ошибку можно победить в 3 несложных шага:

Шаг первый: формат InnoDB файлов Barracuda

Для включения нового формата файлов InnoDB в MySQL >=5.5 достаточно указать в файле my.cnf:


innodb_file_format = Barracuda

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


innodb_file_per_table = 1

Для более ранних версий MySQL формат Barracuda можно подключить через специальный плагин.

Шаг второй: innodb_strict_mode

В целях предотвращения неожиданностей стоит добавить в my.cnf innodb_strict_mode. Теперь, если таблица не будет удовлетворять ограничениям InnoDB, при её создании будет происходить ошибка.


innodb_strict_mode = ON

Шаг третий: формат строк Dynamic

А для тех таблиц, которые не влезают в ограничения InnoDB нужно установить ROW_FORMAT = Dynamic:


ALTER TABLE tableName ENGINE = InnoDB ROW_FORMAT = Dynamic;

Проделав эти шаги, можно начать наслаждаться отсутствием ошибок, а можно ещё разок подумать над структурой данных, которую разработчики MySQL, видимо, сочли бы невероятной и недостижимой

14.09.2014

Источник

I was working on an old project for a new requirement. Tried to add a new column in the database for additional descriptions of products. The table already had a lot of columns. Got the following error, on the MySQL server while performing the operation.

Error Number: 1118

Error Message: 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.

This means I have hit the limit of row size. Let’s dig in and see what the error is actually about and how to resolve it.

Take a look at the details of the error first.

Error Details

Error Number	1118
Error Symbol	ER_TOO_BIG_ROWSIZE
SQLSTATE	42000
Message	Row size too large. The maximum row size for the used table type, not counting BLOBs, is %ld. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.
Reason	The total size of the row has exceeded the max allowed size of 65,535 bytes
Error Type	Server-Side Error

Reason

Let’s try to regenerate the issue.

Try to create a new table that has some very large VARCHAR columns. Use the following query:

CREATE TABLE products (
	col_1 VARCHAR(10000),
	col_2 VARCHAR(10000),
	col_3 VARCHAR(10000),
	col_4 VARCHAR(10000),
	col_5 VARCHAR(10000),
	col_6 VARCHAR(10000),
	col_7 VARCHAR(10000)
) ENGINE=INNODB CHARACTER SET latin1;

You will get the same error. As the total size of a row, we are trying to allocate is more than 65,535 bytes.

Let’s check all the limits of MySQL row size.

MySQL has a limit of 65,535 bytes for a row.
For InnoDB, the maximum size of a row is half of the page size. Page size is defined in innodb_page_size setting.

You can find much more details here: https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/column-count-limit.html#row-size-limits

MySQL error no. 1118 means you have hit one of these limits.

TEXT and BLOB fields are stored separately, so the full size of those fields does not affect the row size. Those fields need 9 to 12 bytes in the row, to store the field-related information.

That is why the error message suggests You have to change some columns to TEXT or BLOBs at the end.

Solutions

Try any of the following solutions:

Set appropriate size for a column. Like, If there is a large VARCHAR field, and you think the length of the field can be reduced, then reduce the size of that VARCHAR field.
Use a TEXT field instead of a large VARCHAR field.
If both of the above solutions are not possible, then split the table into multiple tables.
If the issue is happening because of the page size of InnoDB, then try the following to resolve the issue.

SET GLOBAL innodb_strict_mode=OFF;

Or in the MySQL config file add the following line:

innodb_strict_mode = 0

Related Errors

Here is the list of related errors to MySQL Error: 1118. The error code is the same but the message can be different.

Error Number	Error Symbol	SQLSTATE	Message	Error Type
1118	ER_TOO_LONG_STRING	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.	Server-Side

Источник

Если Вы используете в своих проектах инфоблоки 2.0 и таблицы InnoDB, то есть шанс в один прекрасный момент столкнуться с ошибкой MySQL «SQL Error (1118): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs» (или «Got error 139 from storage engine»). Ниже рассмотрены причины и условия ее возникновения, а также различные методы устранения, с вмешательством в настройки сервера или без него.

Причины

Данная ошибка возникает в таблицах InnoDB при попытке чтения строки с большим количеством заполненных текстовых полей. Сумма длин всех текстовых полей (но не более 768 байтов каждого поля) не должна превышать половины страницы памяти, которая указана в настройках MySQL. В моем случае это было 16кб, отсюда и цифра 8126 байт в названии ошибки.

К ошибке приводит совокупность факторов:
— использование инфоблоков 2.0, т.е. хранение свойств в отдельных таблицах;
— MySQL в качестве сервера базы данных;
— тип InnoDB для таблицы свойств инфоблока;
— большое количество свойств строкового типа. Точная цифра зависит от настроек сервера.

Методы решения

Условно решения можно разделить на 2 группы — с необходимостью правки конфигурации сервера и без нее. Все советы даны применительно к битриксу. Естественно перед экспериментами нужно сделать резервную копию.

Без правки конфигурации:

1. Перевести таблицу в MyISAM. В моем случае это вызвало 2 проблемы — битрикс неправильно конвертировал заполненные значения свойств и при первом добавлении элемента инфоблока пропали половина свойств других элементов. Почему именно так получилось не ясно, но факт остается фактом — риск поломать таблицу есть.

2. Вернуться на инфоблоки 1.0. В этом случае каждое свойство будет храниться в отдельной строке общей таблицы, строка «влезет» в отведенную ей память и ошибка пропадет. Метод не всегда применим, потому что есть некоторые различия в форматах объектов, возвращаемых функцией CIBlockElement::GetList(), и в некоторых случаях придется переписывать код.

С вмешательством в настройки сервера:

1. Увеличить размер страницы памяти. Для этого в файле univ.i установить значения

#define UNIV_PAGE_SIZE (8 * 8192)
#define UNIV_PAGE_SIZE_SHIFT 16

и пересобрать MySQL.

Минусы подхода хорошо описаны в этой статье.

2. Для MySQL версии > 5.5 изменить формат таблиц на DYNAMIC

ALTER TABLE tableName ENGINE = InnoDB ROW_FORMAT = Dynamic;

Для MySQL версии > 5.0 нужно сначала подключить InnoDB через плагин, поддерживающий формат файлов barracuda.

Хостинг TimeWeb точно подвержен этой ошибке. Будьте внимательны и заранее продумывайте архитектуру проекта.

В заключение, несколько полезных ссылок по теме:
8123 байта хватит каждому
Инфоблоки+ и «Got error 139 from storage engine»
Blob Storage in Innodb

Источник

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

What causes MySQL error 1118 to occur?

1. Status of innodb_strict_mode

2. Value of innodb_log_file_size

How we fix MySQL error 1118?

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Неявные ограничения InnoDB

Шаг первый: формат InnoDB файлов Barracuda

Шаг второй: innodb_strict_mode

Шаг третий: формат строк Dynamic

Error Details

Reason

Solutions

Related Errors

Причины

Методы решения

Converting Some Columns to `BLOB` or `TEXT`

Increasing the Length of `VARBINARY` Columns

Increasing the Length of `VARCHAR` Columns