ERROR 1118 (42000): Row size too large (> 8126).

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

最近在做数据库还原时候,遇到以下问题

ERROR 1118 (42000) at line 79532: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRnt row format, BLOB prefix of 768 bytes is stored inline.


解决思路

1,查资料

https://forums.mysql.com/read.php?22,632894,632894

问题1:

Hello All 

I have encounterd a problem on engine conversion from myisam to innodb, it shows error like: 

ERROR 1118 (42000): 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. 

To resolve following issue , i have change on my.cnf. 

my.cnf 
innodb_file_format = Barracuda 
innodb_file_per_table = 1 

and used on alter command. 
Alter table <table_name> engine=innodb ROW_FORMAT=DYNAMIC; 

It solves my issue but our team concern about the performance,security and possible error arise after following changes. 

So i reqest all of you , can any one please suggest me the better alternavite solution for current issue , and what will be the performance impact on this changes. 

thanks in advance


针对问题一的回答:

> performance,security and possible error arise 

No, no problem with any such things. 

To explain the changes: 

> innodb_file_format = Barracuda -- This is the latest format for storing data in InnoDB. The only drawback in the inability to migrate the table to an older version that does not support Barracuda. (This issue seems very unlikely.) 

> innodb_file_per_table = 1 -- This is preferred for "large" tables. It stores the data (and indexes) in a .ibd file for the table instead of in "ibdata1". It provides some benefits to future ALTERs. 

> Alter table <table_name> engine=innodb ROW_FORMAT=DYNAMIC; -- There are several "ROW_FORMATs" in InnoDB. This one is fine. 

> performance -- There is probably no noticeable difference in performance among all the options changed above. (One exception is ROW_FORMAT=COMPRESSED.) 

> please suggest me the better alternavite solution for current issue -- The alternative is to change the schema, possibly involving "vertically" partitioning the table into two tables, possibly "normalizing" some of the columns, possibly not blindly using too-big values in VARCHAR(...), etc. 

I say "possibly" because I don't see your SHOW CREATE TABLE, nor do I understand what impact the changes might have on the rest of the schema and application. 

You could provide SHOW CREATE TABLE for further advice, but I doubt if there is anything terribly significant to advise you on.




我的解决方法:

查看自己的设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show  GLOBAL  VARIABLES  LIKE  '%file_format%' ;
+ --------------------------+----------+
| Variable_name            | Value    |
+ --------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check |  ON        |
| innodb_file_format_max   | Antelope |
+ --------------------------+----------+
 
 
mysql> show variables  like  '%per_table%' ;
+ -----------------------+-------+
| Variable_name         | Value |
+ -----------------------+-------+
| innodb_file_per_table |  OFF    |
+ -----------------------+-------+
1 row  in  set  (0.03 sec)

修改参数:

1
2
3
4
innodb_file_format = Barracuda
innodb_file_per_table = 1 
or
set  GLOBAL  innodb_file_format =  'Barracuda' ;



检查修改后的结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show variables  like  '%per_table%' ;
+ -----------------------+-------+
| Variable_name         | Value |
+ -----------------------+-------+
| innodb_file_per_table |  ON     |
+ -----------------------+-------+
1 row  in  set  (0.00 sec)
 
 
mysql> show  GLOBAL  VARIABLES  LIKE  '%file_format%' ;
+ --------------------------+-----------+
| Variable_name            | Value     |
+ --------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check |  ON         |
| innodb_file_format_max   | Barracuda |
+ --------------------------+-----------+
rows  in  set  (0.00 sec)



此时暂时不需要执行 以下命令也可以成功

1
Alter  table  <table_name> engine=innodb ROW_FORMAT= DYNAMIC ;




      本文转自crazy_charles 51CTO博客,原文链接:http://blog.51cto.com/douya/1894891,如需转载请自行联系原作者



相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
12天前
|
XML C# 数据格式
The data at the root level is invalid. Line 1, position 1.
The data at the root level is invalid. Line 1, position 1.
31 0
|
7月前
|
存储 SQL 关系型数据库
【MySQL异常】Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNA
【MySQL异常】Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNA
64 0
|
12天前
【Simulink】报错:Size mismatch (size [2 x 1] ~= size [1 x 1]). The size to the left is the size of the l
【Simulink】报错:Size mismatch (size [2 x 1] ~= size [1 x 1]). The size to the left is the size of the l
|
SQL 存储 JSON
MySQL执行请求报错 Error: Row size too large (>8126)
最近遇到一个业务问题,在执行一个大的业务查询时会抛出异常报错,所以今天就总结一下 Row size too large (>8126) 报错的相关问题。
|
关系型数据库 MySQL 数据库
MySQL新增字段报错:ERROR 1118 -- Row size too large. The maximum row size for the used table type
MySQL新增字段报错:ERROR 1118 -- Row size too large. The maximum row size for the used table type
1501 0
|
12天前
|
关系型数据库 MySQL
mysql 5.5.62版本建表语句报错: Index column size too large. The maximum column size is 767 bytes
mysql 5.5.62版本建表语句报错: Index column size too large. The maximum column size is 767 bytes
|
12天前
|
关系型数据库 MySQL
MySQL【问题 02】报错 1709 - Index column size too large. The maximum column size is 767 bytes. 可能是最简单的方法
MySQL【问题 02】报错 1709 - Index column size too large. The maximum column size is 767 bytes. 可能是最简单的方法
80 0
|
9月前
|
索引
Result window is too large, from + size must be less than or equal to: [10000]
Result window is too large, from + size must be less than or equal to: [10000]
85 0
|
11月前
|
存储 安全 关系型数据库
Column length too big for column ‘remark‘ (max=65535)解决办法
Column length too big for column ‘remark‘ (max=65535)解决办法
158 0
|
程序员 算法框架/工具 Caffe
解决办法:error: 'size_t' does not name a type、unknown type name 'size_t'
解决办法:error: 'size_t' does not name a type、unknown type name 'size_t'
605 0