mysql导入失败_MYSQL导入记录失败

1. 问题描述

今天往数据库的某张表(下面以table_name表示表名)中导入一条记录，报如下错误：

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

mysql有一个限制：不算blob类型的字段，其他字段的size大小之和不能超过8126个字节，而这张表字段很多，导致数据导入报错。

2. 解决方案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 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 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.

参考以上论述，实际操作如下：先查看mysql配置

mysql> show global variables like "%_file_format";

+--------------------+----------+

| Variable_name | Value |

+--------------------+----------+

| innodb_file_format | Antelope |

+--------------------+----------+

1 row in set (0.00 sec)

mysql> show global variables like "%_file_per_table";

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> show table status like 'table_name'\G;

*************************** 1. row ***************************

Name: table_name

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 16

Avg_row_length: 19456

Data_length: 311296

Max_data_length: 0

Index_length: 32768

Data_free: 18571329536

Auto_increment: NULL

Create_time: 2020-01-01 00:06:46

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

2. 修改文件格式

mysql> set global innodb_file_format=Barracuda;

Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_per_table=ON;

Query OK, 0 rows affected (0.00 sec)

同时，为防止myslq服务重启还原，还要修改my.cnf，在[mysqld]添加以下两行：

innodb_file_format = Barracuda

innodb_file_per_table = 1

3. 修改表的行格式

mysql> alter table table_name ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

4. 查看修改结果

mysql> show table status like "table_name"\G;

*************************** 1. row ***************************

Name: table_name

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 1

Avg_row_length: 98304

Data_length: 98304

Max_data_length: 0

Index_length: 32768

Data_free: 0

Auto_increment: NULL

Create_time: 2020-08-31 14:13:36

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options: row_format=DYNAMIC

Comment:

1 row in set (0.00 sec)

5. 再次导入数据，成功！

3. MYSQL行格式说明

3.1 innodb_file_formatAntelope(羚羊) Built-in-InnoDB(MySQL内置的InnoDB)支持文件格式的代号，有两种“数据表格式”(row_format)：Redundant(冗余)、Compact(紧凑) 。

Barracuda(梭子鱼)是InnoDB Plugin支持的文件格式，在原来的基础上新增了两种数据表格式的支持：Dynamic 和 Compressed 。

innodb_file_format在配置文件中指定，row_format则在创建数据表时指定 。

3.2 常见的行格式行格式的应用都是针对于表的，只有在创建表的时候才能使用row_format，在创建库的时候无法指定行格式。

表的行格式也是决定了这张表下面的物理存储方式，会影响查询和DML操作的性能。

常见的表行格式有以下4种：

(1)REDUNDANT

(2)COMPACT

(3)DYNAMIC

(4)COMPRESSED

各种行格式的特点及缺点：行格式紧凑的存储特性增强的可变长度色谱柱存储大索引键前缀支持压缩支持支持的表空间类型

REDUNDANT没有没有没有没有系统，每表文件，常规

COMPACT是没有没有没有系统，每表文件，常规

DYNAMIC是是是没有系统，每表文件，常规

COMPRESSED是是是是每表档案，一般

4. 参考文档