1118 ошибка mysql

The key parameter is: innodb_page_size

Support for 32k and 64k page sizes was added in MySQL 5.7. For both 32k and 64k page sizes, the maximum row length is approximately 16000 bytes.

The trick is that this parameter can be only changed during the INITIALIZATION of the mysql service instance, so it does not have any affect if you change this parameter after the instance is already initialized (the very first run of the instance).

innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.6.1, “InnoDB Startup Configuration”.

So if you do not change this value in my.ini before initialization, the default value will be 16K, which will have row size limit of ~8K. Thats why the error comes up.

If you increase the innodb_page_size, the innodb_log_buffer_size must be also increased. Set it at least to 16M. Also if the ROW_FORMAT is set to COMPRESSED you cannot increase innodb_page_size to 32k, or 64K. It should be DYNAMIC (default in 5.7).

ROW_FORMAT=COMPRESSED is not supported when innodb_page_size is set to 32KB or 64KB. For innodb_page_size=32k, extent size is 2MB. For innodb_page_size=64k, extent size is 4MB. innodb_log_buffer_size should be set to at least 16M (the default) when using 32k or 64k page sizes.

Furthermore the innodb_buffer_pool_size should be increased from 128M to 512M at least, otherwise you will get an error on initialization of the instance (I do not have the exact error).

After this, the row size error gone.

The problem with this is that you have to create a new MySql instance, and migrate data to your new DataBase instance, from old one.

Parameters that I changed and works (after creating a new instance and initialized with the my.ini that is first modified with these settings):

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

All the settings and descriptions in which I found the solution can be found here:

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

Hope this helps!

Regards!

Источник

You need to change the data type from VARCHAR to TEXT which will overcome the row limitation in MySQL.

The query below creates an error due to row limitation:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;

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

But when you change it to TEXT datatype it works. In the following MyISAM example, changing a column to TEXT avoids the 65,535-byte row size limit and permits the operation to succeed because BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size.

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

Further reading

Источник

Столкнулся с такой ошибкой:

OperationalError: (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.')

В интернете почитал варианты решения. Сделал:

innodb_file_format = Barracuda
innodb_file_per_table = ON

Перезапустил сервер

mysql> show global variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

Делаю:

mysql> ALTER TABLE market_offer ROW_FORMAT=DYNAMIC;

В итоге отваливается с ошибкой:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Подскажите, как можно её решить?

Структура таблицы:

CREATE TABLE `market_offer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `company_id` int(11) DEFAULT NULL,
  `rubrica_id` int(11) DEFAULT NULL,
  `category_id` int(11) unsigned DEFAULT NULL,
  `artno` varchar(50) DEFAULT NULL,
  `pub_date` datetime NOT NULL,
  `keyword` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL DEFAULT '',
  `title` varchar(300) NOT NULL,
  `tizer` text NOT NULL,
  `quantity` smallint(5) unsigned NOT NULL DEFAULT '0',
  `unit` char(3) NOT NULL DEFAULT 'PCE',
  `price` decimal(13,2) NOT NULL,
  `currency` char(3) NOT NULL DEFAULT 'RUR',
  `picture` varchar(100) NOT NULL,
  `pictureurl` varchar(500) NOT NULL,
  `url` varchar(500) NOT NULL,
  `text` text NOT NULL,
  `info` longtext NOT NULL,
  `lastmodified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `rubrica_id` (`rubrica_id`),
  KEY `company_id` (`company_id`),
  KEY `user_id` (`user_id`),
  KEY `category_id` (`category_id`),
  KEY `category_pubdate` (`category_id`,`pub_date`),
  KEY `rubrica_title` (`rubrica_id`,`title`(255)),
  KEY `market_offer_e01be369` (`is_active`),
  KEY `lastmodified` (`lastmodified`),
  KEY `market_offer_c4ebad10` (`lastmodified`),
  KEY `picture` (`picture`),
  CONSTRAINT `market_offer_ibfk_8` FOREIGN KEY (`category_id`) REFERENCES `market_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_id_refs_id_3cfc06ff` FOREIGN KEY (`user_id`) REFERENCES `users_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14943895749 DEFAULT CHARSET=utf8

Источник

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:

  1. 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.
  2. Use a TEXT field instead of a large VARCHAR field.
  3. If both of the above solutions are not possible, then split the table into multiple tables.
  4. 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

Источник

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.

Contents

  1. Example of the Problem
  2. Root Cause of the Problem
  3. Checking Existing Tables for the Problem
  4. Finding All Tables That Currently Have the Problem
  5. 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
  6. 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.

Источник

Понравилась статья? Поделить с друзьями:
  • 1117 ошибка устройства ввода вывода при форматировании флешки
  • 1117 ошибка тойота
  • 1117 ошибка камминз газель некст
  • 111606 ошибка опель зафира б
  • 1116 приус ошибка