更改“Mysql Row size too large”的限制

Change limit for "Mysql Row size too large"

提问by Lasha Kurt

How can I change the limit

如何更改限制

Row size too large (> 8126).Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSEDmay help. In current row format, BLOBprefix of 768 bytes is stored inline.

行大小太大 (> 8126)。将某些列更改为 TEXT 或 BLOB 或使用ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED可能会有所帮助。在当前行格式中，BLOB内联存储 768 字节的前缀。

Table:

桌子：

id  int(11) No       
name    text    No       
date    date    No       
time    time    No       
schedule    int(11) No       
category    int(11) No       
top_a   varchar(255)    No       
top_b   varchar(255)    No       
top_c   varchar(255)    No       
top_d   varchar(255)    No       
top_e   varchar(255)    No       
top_f   varchar(255)    No       
top_g   varchar(255)    No       
top_h   varchar(255)    No       
top_i   varchar(255)    No       
top_j   varchar(255)    No       
top_title_a varchar(255)    No       
top_title_b varchar(255)    No       
top_title_c varchar(255)    No       
top_title_d varchar(255)    No       
top_title_e varchar(255)    No       
top_title_f varchar(255)    No       
top_title_g varchar(255)    No       
top_title_h varchar(255)    No       
top_title_i varchar(255)    No       
top_title_j varchar(255)    No       
top_desc_a  text    No       
top_desc_b  text    No       
top_desc_c  text    No       
top_desc_d  text    No       
top_desc_e  text    No       
top_desc_f  text    No       
top_desc_g  text    No       
top_desc_h  text    No       
top_desc_i  text    No       
top_desc_j  text    No       
status  int(11) No       
admin_id    int(11) No 

回答by hjpotter92

The question has been asked on serverfaulttoo.

在serverfault 上也有人问过这个问题。

You may want to take a look at this articlewhich explains a lot about MySQL row sizes. It's important to note that even if you use TEXT or BLOB fields, your row size could still be over 8K (limit for InnoDB) because it stores the first 768 bytes for each field inline in the page.

The simplest way to fix this is to use the Barracuda file formatwith InnoDB. This basically gets rid of the problem altogether by only storing the 20 byte pointer to the text data instead of storing the first 768 bytes.

你可能想看看这篇文章，它解释了很多关于 MySQL 行大小的信息。请务必注意，即使您使用 TEXT 或 BLOB 字段，您的行大小仍可能超过 8K（InnoDB 的限制），因为它为页面中的每个内联字段存储前 768 个字节。

解决此问题的最简单方法是在 InnoDB 中使用Barracuda 文件格式。通过只存储指向文本数据的 20 字节指针而不是存储前 768 字节，这基本上完全摆脱了这个问题。

---

---

The method that worked for the OP there was:

适用于 OP 的方法是：

1. Add the following to the my.cnffile under [mysqld]section.

   innodb_file_per_table=1
   innodb_file_format = Barracuda
   

2. ALTERthe table to use ROW_FORMAT=COMPRESSED.

   ALTER TABLE nombre_tabla
       ENGINE=InnoDB
       ROW_FORMAT=COMPRESSED 
       KEY_BLOCK_SIZE=8;
   

1. 将以下内容添加到部分my.cnf下的文件中[mysqld]。

   innodb_file_per_table=1
   innodb_file_format = Barracuda
   

2. ALTER要使用的表ROW_FORMAT=COMPRESSED。

   ALTER TABLE nombre_tabla
       ENGINE=InnoDB
       ROW_FORMAT=COMPRESSED 
       KEY_BLOCK_SIZE=8;
   

---

---

There is a possibility that the above still does not resolve your issues. It is a known (and verified) bugwith the InnoDBengine, and a temporary fix for now is to fallback to MyISAMengine as temporary storage. So, in your my.cnffile:

有可能以上仍然不能解决您的问题。这是InnoDB引擎的一个已知（并已验证）错误，目前的临时修复是回退到 MyISAM引擎作为临时存储。因此，在您的文件中：my.cnf

internal_tmp_disk_storage_engine=MyISAM

回答by Colin

I ran into this problem recently and solved it a different way. If you are running MySQL version 5.6.20 there is a known bug in the system. See MySQL docs

我最近遇到了这个问题，并以不同的方式解决了它。如果您运行的是 MySQL 5.6.20 版，则系统中存在一个已知错误。查看MySQL 文档

Important Due to Bug #69477, redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. To address this bug, a patch introduced in MySQL 5.6.20 limits the size of redo log BLOB writes to 10% of the redo log file size. As a result of this limit, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields).

重要 由于错误 #69477，外部存储的大型 BLOB 字段的重做日志写入可能会覆盖最近的检查点。为解决此错误，MySQL 5.6.20 中引入的补丁将重做日志 BLOB 写入的大小限制为重做日志文件大小的 10%。由于此限制，innodb_log_file_size 应设置为大于表行中发现的最大 BLOB 数据大小加上其他可变长度字段（VARCHAR、VARBINARY 和 TEXT 类型字段）长度的 10 倍的值。

In my situation the offending blob table was around 16MB. Thus, the way I solved it was by adding a line to my.cnf that ensured I had at least 10x that amount and then some:

在我的情况下，有问题的 blob 表大约为 16MB。因此，我解决它的方法是在 my.cnf 中添加一行，以确保我至少有 10 倍的数量，然后是一些：

innodb_log_file_size = 256M

innodb_log_file_size = 256M

回答by phoenix

If you can switch the ENGINE and use MyISAM instead of InnoDB, that should help:

如果您可以切换 ENGINE 并使用 MyISAM 而不是 InnoDB，那应该会有所帮助：

ENGINE=MyISAM

ENGINE=MyISAM

There are two caveats with MyISAM (arguably more):

MyISAM 有两个警告（可以说更多）：

1. You can't use transactions.
2. You can't use foreign key constraints.

1. 您不能使用事务。
2. 您不能使用外键约束。

回答by Karl

Set followings on your my.cnf file and restart mysql server.

在 my.cnf 文件上设置以下内容并重新启动 mysql 服务器。

innodb_strict_mode = 0

回答by Aman Chhabra

I would like to share an awesome answer, it might be helpful. Credits Bill Karwin see here https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

我想分享一个很棒的答案，它可能会有所帮助。积分比尔卡文见这里https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

They vary by InnoDB file format.At present there are 2 formats called Antelope and Barracuda.

它们因 InnoDB 文件格式而异。目前有 2 种格式，称为 Antelope 和 Barracuda。

The central tablespace file (ibdata1) is always in Antelope format. If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.

中央表空间文件 (ibdata1) 始终采用 Antelope 格式。如果使用 file-per-table，则可以通过在 my.cnf 中设置 innodb_file_format=Barracuda 来使单个文件使用 Barracuda 格式。

Basic points:

基本要点：

1. One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.

2. Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.

3. Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.

4. Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or less documented in their wiki.

1. 一页 16KB 的 InnoDB 数据必须至少包含两行数据。此外，每个页面都有一个页眉和一个页脚，其中包含页面校验和和日志序列号等。这就是每行小于 8KB 的限制。

2. 固定大小的数据类型（如 INTEGER、DATE、FLOAT、CHAR）存储在此主数据页上，并计入行大小限制。

3. 可变大小的数据类型（如 VARCHAR、TEXT、BLOB）存储在溢出页上，因此它们不完全计入行大小限制。在 Antelope 中，除了存储在溢出页面上之外，最多 768 字节的此类列存储在主数据页面上。Barracuda 支持动态行格式，因此它可能仅在主数据页上存储 20 字节的指针。

4. 可变大小数据类型也以 1 个或更多字节为前缀来编码长度。InnoDB 行格式也有一个字段偏移数组。所以在他们的维基中或多或少地记录了一个内部结构。

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

Barracuda 还支持 ROW_FORMAT=COMPRESSED 以进一步提高溢出数据的存储效率。

I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.

我还必须评论说，我从未见过设计良好的表超出行大小限制。您违反了第一范式的重复组条件，这是一种强烈的“代码气味”。

回答by multimediaxp

I had the same issue, this solved it for me:

我有同样的问题，这为我解决了：

ALTER TABLE `my_table` ROW_FORMAT=DYNAMIC;

From MYSQL Documentation:

来自 MYSQL文档：

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but this new format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.

DYNAMIC 行格式保持了在索引节点中存储整行（如果合适的话）的效率（就像 COMPACT 和 REDUNDANT 格式一样），但是这种新格式避免了用大量数据字节填充 B 树节点的问题长列。DYNAMIC 格式基于这样的想法：如果长数据值的一部分存储在页外，通常将所有值存储在页外是最有效的。对于 DYNAMIC 格式，较短的列可能会保留在 B 树节点中，从而最大限度地减少任何给定行所需的溢出页数。

回答by Iftikhar Khan

After spending hours I have found the solution: just run the following SQL in your MySQL admin to convert the table to MyISAM:

花了几个小时后，我找到了解决方案：只需在 MySQL 管理员中运行以下 SQL 即可将表转换为 MyISAM：

USE db_name;
ALTER TABLE table_name ENGINE=MYISAM;

回答by matyas

I ran into this issue when I was trying to restore a backed up mysql database from a different server. What solved this issue for me was adding certain settings to my.conf (like in the questions above) and additionally changing the sql backup file:

当我尝试从不同的服务器恢复备份的 mysql 数据库时遇到了这个问题。为我解决这个问题的是向 my.conf 添加某些设置（如上面的问题）并另外更改 sql 备份文件：

Step 1:add or edit the following lines in my.conf:

第 1 步：在 my.conf 中添加或编辑以下几行：

innodb_page_size=32K
innodb_file_format=Barracuda
innodb_file_per_table=1

Step 2add ROW_FORMAT=DYNAMIC to the table create statement in the sql backup file for the table that is causing this error:

步骤 2将 ROW_FORMAT=DYNAMIC 添加到导致此错误的表的 sql 备份文件中的 table create 语句：

DROP TABLE IF EXISTS `problematic_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `problematic_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC;

the important change above is ROW_FORMAT=DYNAMIC;(that was not included in the orignal sql backup file)

上面的重要变化是ROW_FORMAT=DYNAMIC; （未包含在原始 sql 备份文件中）

source that helped me to resolve this issue: MariaDB and InnoDB MySQL Row size too large

帮助我解决此问题的来源：MariaDB 和 InnoDB MySQL Row size too large

回答by Rick James

The other answers address the question asked. I will address the underlying cause: poor schema design.

其他答案解决了提出的问题。我将解决根本原因：糟糕的架构设计。

Do not splay an array across columns. Here you have 3*10 columns that should be turned into 10 rows of 3 columns in a new table (plus id, etc)

不要跨列展开数组。这里有 3*10 列，应该在新表中变成 10 行 3 列（加id号等）

Your Maintable would have only

你的Main桌子只有

id  int(11) No       
name    text    No       
date    date    No       
time    time    No       
schedule    int(11) No       
category    int(11) No       
status  int(11) No       
admin_id    int(11) No 

Your extra table (Top) would have

您的额外表 ( Top) 将有

id  int(11) No          -- for joining to Main
seq TINYINT UNSIGNED    -- containing 1..10
img   varchar(255)    No       
title varchar(255)    No       
desc  text    No    
PRIMARY KEY(id, seq)    -- so you can easily find the 10 top_titles

There would be 10 (or fewer? or more?) rows in Topfor each id.

Top每个将有 10（或更少？或更多？）行id。

This eliminates your original problem, and cleans up the schema. (This is not "normalization", as debated in some of the Comments.)

这消除了您的原始问题，并清理了架构。（这不是“规范化”，正如一些评论中所争论的那样。）

Do notswitch to MyISAM; it is going away.
Don't worry about ROW_FORMAT.

难道不转的MyISAM; 它正在消失。
别担心ROW_FORMAT。

You will need to change your code to do the JOINand to handle multiple rows instead of multiple columns.

您将需要更改代码来执行JOIN和处理多行而不是多列。

回答by Mihir Kagrana

I am using MySQL 5.6 on AWS RDS. I updated following in parameter group.

我在 AWS RDS 上使用 MySQL 5.6。我在参数组中更新了以下内容。

innodb_file_per_table=1
innodb_file_format = Barracuda

I had to reboot DB instance for parameter group changes to be in effect.

我必须重新启动数据库实例才能使参数组更改生效。

Also, ROW_FORMAT=COMPRESSED was not supported. I used DYNAMIC as below and it worked fine.

此外，不支持 ROW_FORMAT=COMPRESSED。我使用 DYNAMIC 如下，它工作正常。

ALTER TABLE nombre_tabla ENGINE=InnoDB ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8