MySQL-error-Row size too large. The maximum row size for the used table type

2024-03-19 18:10
文章标签 mysql maximum error table type row database used large size

本文主要是介绍MySQL-error-Row size too large. The maximum row size for the used table type，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

问题描述：

The maximum row size for the used table type 这个报错很常见，请看下面的例子

案例1

mysql> create table test(a varchar(22000),b varchar(22000),c varchar(2000))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> show warnings;  ---->从这里看到MySQL做了转化，a列b列自动转化成了text
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1246 | Converting column 'a' from VARCHAR to TEXT |
| Note  | 1246 | Converting column 'b' from VARCHAR to TEXT |
+-------+------+--------------------------------------------+
2 rows in set (0.01 sec)mysql> alter table test ADD col22 varchar(20000) FIRST;
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如果varchar(N) 小一点的话，是可以成功的。mysql> alter table test ADD col22 varchar(2000) FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (`col22` varchar(2000) DEFAULT NULL,`a` mediumtext, ----》varchar自动转化为了text`b` mediumtext,`c` varchar(2000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

产生这个问题的原因是：

  1. VARCHAR（N）中的N指的是字符的长度。VARCHAR类型最大支持65535，单位是字节。这个MySQL自身的限制，通过参数无法解决。

  2. 此外需要注意的是，MySQL官方手册中定义的65535长度是指“”所有VARCHAR列的长度总和“”，如果列的长度总和超出这个长度，依然无法创建

建议如下：

  1. 检查您表中的所有列，将总和加起来，看是否超出varchar的限制，同时看是否有列类型的转化

  2. 在创建表时，需要对列的长度有一定的预估。

   2.1 一般情况下使用varchar(4)和varchar(10)保存’asdf’占用的空间都是一样的，但是更小的varchar(4)有着更好的性能。

   2.2 比如：MySQL建立索引时如果没有限制索引的大小，索引长度会默认采用的该字段的长度，也就是说varchar(10)建立的索引存储大小要比varchar(4)建立索引存储大小大的多，加载索引使用的内存也更多。

  1. 如果表中有大字段，建议将大字段进行拆分。这也是最优的做法。

案例2

案例2是比较诡异的，背景信息如下：

1. 执行alter table时报错：
mysql> ALTER TABLE `client_info` ADD COLUMN `client_info_ctime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';                                                       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
2. 在不同schema下的同样表结构是可以正常alter table的，唯独这张表不行
3.row_format=compact, innodb_file_format=antelope
4. 表结构如下：
CREATE TABLE `client_info` (`client_info_info` longblob NOT NULL,`client_info_intent` longblob NOT NULL,`client_info_certificate` longblob,`client_info_education` longblob NOT NULL,`client_info_experience` longblob NOT NULL,`client_info_internship` longblob NOT NULL,`client_info_project` longblob NOT NULL,`client_info_skill` longblob NOT NULL,`client_info_project1` longblob NOT NULL,`client_info_skill1` longblob NOT NULL,`client_info_skill21` longblob NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=217355 DEFAULT CHARSET=utf8;

分析解决

插入数据

mysql> insert into client_info values (repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('ab',95));
Query OK, 1 row affected (0.03 sec)mysql> insert into client_info values (repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('abcdefghij',410),repeat('ab',99));
Query OK, 1 row affected (0.02 sec)

进行DDL，报错

mysql> ALTER TABLE `client_info` ADD COLUMN `client_info_ctime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';                             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

原理分析

对于compact格式来说，如果表中有大字段，我们会将前768字节放在当前页中，剩余的部分放到off-page(行溢出)中，当我们在去修改表结构时候，所有字段的总和超出了8162，所以报错

也就是说，如果我们没有表中没有数据，或者说所有列保存的数据没有达到这个限制，也就不会出现这768字节的问题，也不会超出这8126。这也就是我们观察到，一个表可以正常修改，一个却不可以，空表也是可以添加字段的。本质上还是表中数据的问题，也是一个MySQL的问题，并不是AWS RDS特定的问题

InnoDB存储引擎表是索引组织的，即B+Tree的结构，这样每个页中至少应该有两条行记录（否则失去了B+Tree的意义，变成链表了）。因此，如果页中只能存放下一条记录，那么InnoDB存储引擎会自动将行数据存放到溢出页中

在这里插入图片描述

后续的操作及其步骤

  1. 修改参数组，将innodb_file_forma 修改为Barracuda
  2. alter table user_resume7 row_format=dynamic;
  3. 再次执行alter table
mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.03 sec)2. mysql> alter table user_resume7 row_format=dynamic;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 03. mysql> ALTER TABLE `client_info` ADD COLUMN `client_rinfo_ctime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改为dynamic是因为，dynamic默认将前20字节放在当前页，剩余部分放在off-page中，所以，理论上 8126/20=406列才能达到限制
在这里插入图片描述
推荐阅读

https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

这篇关于MySQL-error-Row size too large. The maximum row size for the used table type的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/826841。

相关文章

Mysql 中的多表连接和连接类型详解

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
阅读更多...
mysql重置root密码的完整步骤(适用于5.7和8.0)

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》：本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步：先停止mysql服务，一定要停止！方式一：通过命令行关闭mysql服务方式二：通过服务项关闭
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
mysql主从及遇到的问题解决

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql
阅读更多...
MySQL的索引失效的原因实例及解决方案

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6
阅读更多...
Linux下MySQL8.0.26安装教程

Linux下MySQL8.0.26安装教程

《Linux下MySQL8.0.26安装教程》文章详细介绍了如何在Linux系统上安装和配置MySQL,包括下载、解压、安装依赖、启动服务、获取默认密码、设置密码、支持远程登录以及创建表,感兴趣的朋友... 目录1.找到官网下载位置1.访问mysql存档2.下载社区版3.百度网盘中2.linux安装配置1.
阅读更多...
PostgreSQL如何用psql运行SQL文件

PostgreSQL如何用psql运行SQL文件

《PostgreSQL如何用psql运行SQL文件》文章介绍了两种运行预写好的SQL文件的方式：首先连接数据库后执行,或者直接通过psql命令执行,需要注意的是,文件路径在Linux系统中应使用斜杠/... 目录PostgreSQ编程L用psql运行SQL文件方式一方式二总结PostgreSQL用psql运
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2