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

本文主要是介绍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)

产生这个问题的原因是：

VARCHAR（N）中的N指的是字符的长度。VARCHAR类型最大支持65535，单位是字节。这个MySQL自身的限制，通过参数无法解决。
此外需要注意的是，MySQL官方手册中定义的65535长度是指“”所有VARCHAR列的长度总和“”，如果列的长度总和超出这个长度，依然无法创建

建议如下：

检查您表中的所有列，将总和加起来，看是否超出varchar的限制，同时看是否有列类型的转化
在创建表时，需要对列的长度有一定的预估。

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

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

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

案例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存储引擎会自动将行数据存放到溢出页中

在这里插入图片描述

后续的操作及其步骤

修改参数组，将innodb_file_forma 修改为Barracuda
alter table user_resume7 row_format=dynamic;
再次执行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列才能达到限制
在这里插入图片描述
推荐阅读