“ Mysql 行大小太大”的更改限制

我怎样才能改变限制

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

表:

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
304532 次浏览
小开

这个问题也在 服务器故障上提出过。

你可能想看看 这篇文章，它解释了很多东西 关于 MySQL 行大小 文本或 BLOB 字段，您的行大小可能仍然超过8K (限制为 中每个字段的前768个字节 那一页。

解决这个问题的最简单方法是使用梭鱼文件格式 这基本上可以完全解决这个问题 只存储指向文本数据的20字节指针，而不存储 前768字节。

对 OP 有效的方法是:

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

    innodb_file_per_table=1
innodb_file_format = Barracuda

  2. ALTER the table to use 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) bug with the InnoDB engine, and a temporary fix for now is to fallback to MyISAM engine as temporary storage. So, in your my.cnf file:

internal_tmp_disk_storage_engine=MyISAM
小开

我想分享一个很棒的答案，可能会有帮助

它们因 InnoDB 文件格式的不同而不同。目前有2种格式称为羚羊和梭鱼。

中央表空间文件(ibdata1)始终采用羚羊格式。如果使用每表一个文件，可以通过在 my.cnf 中设置 inodb _ file _ format = Barracuda 来使单个文件使用 Barracuda 格式。

基本要点:

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

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

  3. 可变大小的数据类型如 VARCHAR、 TEXT、 BLOB 存储在溢出页上，所以它们不会完全计入行大小限制。在 Antelope 中，除了存储在溢出页上之外，还有多达768字节的这种列存储在主数据页上。Barracuda 支持动态行格式，因此它可能只在主数据页上存储一个20字节的指针。

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

梭子鱼还支持 ROW _ FORMAT = COMPRESSED，以进一步提高溢出数据的存储效率。

我还必须指出，我从未见过一个设计良好的表超过行大小限制。这是一个强烈的“代码味道”，您正在违反第一规范形式的重复组条件。

小开

我最近碰到了这个问题，用另一种方法解决了它。如果您正在运行 MySQL 5.6.20版，那么系统中有一个已知的 bug。参见 MySQL 文档

很重要 由于 Bug # 69477，对于大型外部存储的 BLOB 字段的重做日志写操作可能会覆盖最多的内容 为了解决这个问题，MySQL 5.6.20中引入了一个补丁来限制重做的大小 Log BLOB 写入重做日志文件大小的10% 行中的最大 BLOB 数据大小的10倍以上 表加上其他可变长度字段的长度(VARCHAR、 VARBINARY 和 TEXT 类型字段)。

在我的情况下，冒犯的 blob 表大约是16MB。因此，我解决这个问题的方法是在 my.cnf 中添加一行代码，以确保至少有10倍于这个数字，然后再增加一些:

innodb_log_file_size = 256M

小开

如果你能切换到 ENGINE，使用 MyISAM 而不是 InnoDB，那应该会有帮助:

ENGINE=MyISAM

对于 MyISAM，有两点需要注意(可以说更多) :

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

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

innodb_strict_mode=0
小开

我也遇到了同样的问题，我通过执行以下 sql 来解决这个问题:

ALTER ${table} ROW_FORMAT=COMPRESSED;

但是，我想你应该知道 行存储。
有两种类型的列: 可变长度列可变长度列(例如 VARCHAR、 VARBINARY、 BLOB 和 TEXT 类型)和 定长柱定长柱定长柱。它们存储在不同类型的页中。

可变长度列是此规则的一个例外。诸如 BLOB 和 VARCHAR 之类的列太长，无法放在 B 树页面上，这些列存储在单独分配的磁盘页面上，称为溢出页面。我们称这种栏目为页外栏目。这些列的值存储在溢出页的单链接列表中，并且每个此类列都有自己的一个或多个溢出页列表。在某些情况下，长列值的全部或前缀存储在 B 树中，以避免浪费存储空间，并消除读取单独页面的需要。

以及设置 ROW _ FORMAT 的目的是什么时候

当使用 ROW _ formAT = DYNAMIC 或 ROW _ formAT = COMPRESSED 创建表时，InnoDB 可以在页外完全存储长的可变长度列值(对于 VARCHAR、 VARBINARY、 BLOB 和 TEXT 类型) ，聚集索引记录只包含指向溢出页的20字节指针。

想知道更多关于 动态和压缩行格式的信息

小开

其他的回答解决了问题。我将解决根本原因: 差的模式设计。

不要在列之间显示数组。这里有3 * 10列，应该在一个新表(加上 id等)中将其转换为10行，每行3列

您的 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

您的额外表(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

对于每个 id，Top中将有10行(或更少? 或更多?)。

这样可以消除原来的问题，并清除模式。(这不是“正常化”，正如一些评论所讨论的那样。)

把 没有切换到 MyISAM，它正在消失。
不用担心 ROW_FORMAT。

您需要更改代码以执行 JOIN并处理多行而不是多列。

小开

我在试图从另一台服务器恢复备份的 mysql 数据库时遇到了这个问题。 对我来说，解决这个问题的方法是在 my.conf 中添加某些设置(如上面的问题所示) ，并额外修改 sql 备份文件:

第一步: 在 my.conf 中添加或编辑以下代码行:

innodb_page_size=32K
innodb_file_format=Barracuda
innodb_file_per_table=1

步骤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;

上面的重要更改是 ROW _ FORMAT = DYNAMIC;(原来的 sql 备份文件中没有包含它)

帮助我解决这个问题的来源: MariaDB 和 InnoDB MySQL 行大小过大

小开

花了几个小时之后，我找到了解决方案: 只需在 MySQL 管理员中运行以下 SQL 语句，就可以将表转换成 MyISAM:

USE db_name;
ALTER TABLE table_name ENGINE=MYISAM;
小开

我也有同样的问题，这个解决了我的问题:

ALTER TABLE `my_table` ROW_FORMAT=DYNAMIC;

来自 MYSQL 文件:

DYNAMIC 行格式维护存储整个 行，如果符合的话(COMPACT 和 REDUNDANT 也是如此) 格式) ，但是这种新格式避免了填充 B 树的问题 具有大量长列数据字节的节点 格式的基本思想是，如果长数据值的一部分是 存储在页外，通常最有效的方法是存储所有 使用 DYNAMIC 格式，较短的列可能会 保留在 B 树节点中，使溢出页数最小化 对于任何给定行都需要。

小开

如果在有许多列的 SELECT 上发生这种情况，原因可能是 mysql 正在创建一个临时表。如果这个表太大以至于无法放入内存中，它将使用其默认的临时表格式(InnoDB)将其存储在磁盘上。在这种情况下，应用 InnoDB 大小限制。

然后你有4个选择:

  1. 如另一篇文章中所述，更改 inodb 行大小限制 需要重新初始化服务器。
  2. 更改查询以包含更少的列或避免导致它创建临时表(即删除 条款)。
  3. 将 Max _ heap _ table _ size更改为大，以便结果适合于内存，而不需要写入磁盘。

  4. 将默认的临时表格格式改为 MYISAM，这就是我所做的:

    internal_tmp_disk_storage_engine=MYISAM

Restart mysql, query works.

小开

这里有一个简单的建议:

在用10.3.17-MariaDB 从 Debian 9升级到 Debian 10之后，我发现 Joomla 数据库有一些错误:

[警告] InnoDB: 无法在表 database.table中添加字段 field，因为添加后，行大小为8742，大于索引叶页上记录的最大允许大小(8126)。

以防万一，我准备好了 在/etc/mysql/mariadb.con.d/50-server. cnf 中，inodb _ default _ row _ format = DYNAMIC (不管怎样，这都是违约)

然后，我使用 phpmyadmin 为 Joomla 数据库中的所有表运行“ Optimize table”。 我认为 phpmyadmin 在这个过程中所做的餐桌娱乐有所帮助。 如果您碰巧安装了 phpmyadmin，只需要点击几下即可。

小开

我在 AWS RDS 上使用 MySQL 5.6。

innodb_file_per_table=1
innodb_file_format = Barracuda

为了使参数组更改生效，我必须重新启动 DB 实例。

另外，不支持 ROW _ FORMAT = COMPRESSED。

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

InnoDB 表(适用于数据库页中本地存储的数据)的最大行大小为4KB、8KB、16KB 和32KB 的 略少于半页

对于16kb 的页面(默认值) ，我们可以计算:

Slightly less than half a page 8126 / Number of bytes to threshold for overflow 767 = 10.59 fields of 767 bytes maximum

基本上，你可以最大化一行:

  • 11个 varchar 字段 > 767个字符(latin1 = 1 byte per char)或
  • 11个 varchar 字段 > 255个字符(mysql 上的 utf-8 = 每个 char 3字节)。

请记住，只有当字段大于767字节时，它才会溢出到溢出页。如果767字节的字段太多，它将崩溃(超过 max row _ size)。不常用于 latin1，但是如果开发人员不小心的话，使用 utf-8是很有可能的。

对于这种情况，我认为您可以将 inodb _ page _ size 提高到32kb。

在 my.cnf:

innodb_page_size=32K

参考文献:

小开

您需要对 my.ini文件做一些更改

把这个添加到[ mysqld ]下面

innodb_strict_mode=0

更新这两条线路

innodb_log_file_size=256M
innodb_log_buffer_size=256M

innodb_strict_mode: 当它被启用时，某些 InnoDB 警告会变成错误。

参考资料: https://mariadb.com/kb/en/innodb-strict-mode/

innodb_log_file_size和 innodb_log_buffer_size需要增加大小。

小开

当我毁掉我的 Laravel Homestead (流浪汉)盒子，重新开始的时候，我一直遇到这个问题。

  1. 从命令行 homestead ssh将 SSH 放入框中

  2. 转到 my.cnf 文件 sudo vi /etc/mysql/my.cnf

  3. 将下面几行添加到文件底部(在! include 下面)

    [mysqld]

    innodb_log_file_size=512M

    innodb_strict_mode=0

  4. 将更改保存到 my.cnf，然后重新加载 MYSQL sudo service mysql restart

小开

神秘5.6台:

执行以下 SQL COMMANDS:

Mysql > SET GLOBAL innodb_file_format=Barracuda;
Mysql > ALTER TABLE `name_of_my_table_here` ENGINE=InnoDB ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8;
小开

为了解决这个问题，您必须在 phpmyadmin 中将 inodb 更改为 myisam。

enter image description here


提交答案