mysql innodb Row size too large

 

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

问题原因

• One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.

• Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.

• Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.

• Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or lessdocumented in their wiki. [EDIT] Dead link - here looks better now

因为数据库引擎类型为innodb ,表中字段类型 多个数据类型varchar（n）

create table p2p_loan_claim_detail

(

   PROJECT_NO           varchar(50) not null comment '项目编号',

   BACKGROUND           varchar(512) comment '企业背景',

   OPER_STATE           varchar(512) comment '经营状况',

   RISK_MEASURE         varchar(512) comment '风险控制措施',

   GUARANTEE_OPINION    varchar(512) comment '担保机构意见',

   GUARANTEE_INFO       varchar(512) comment '担保信息',

   SAFEGUARD_PLAN       varchar(512) comment '风险保障方案',

   REMARK               varchar(512) comment '备注',

   CREATE_TIME          datetime comment '创建时间',

   UPDATE_TIME          datetime comment '更新时间',

   LITIGATION           varchar(512) comment '涉诉信息',

   SAFEGUARD_LEVEL      varchar(20) comment '风控评级',

   RENTER_INFO          varchar(512) comment '承租人信息',

   FINANCE_RENT_INFO    varchar(512) comment '融资租赁物信息',

   ORG_DESC             varchar(2000) comment '机构简介',

   CONTRACT_DESC        varchar(100) comment '应收账款基础合同',

   TRANS_CLAIM_AMOUNT   decimal(12,2) comment '转让收益权的应收账款金额（元）',

   CLAIM_DEADLINE       date comment '转让收益权的应收账款到期日',

   CHANNEL_OFFICE       varchar(128) comment '渠道办事处'),

 

改为：

 

create table

CREATE TABLE `p2p_loan_claim_detail_new` (
   `PROJECT_NO` varchar(50) NOT NULL COMMENT '项目编号',
   `BACKGROUND` text COMMENT '企业背景',
   `OPER_STATE` text COMMENT '经营状况',
   `RISK_MEASURE` text COMMENT '风险控制措施',
   `GUARANTEE_OPINION` text COMMENT '担保机构意见',
   `GUARANTEE_INFO` text COMMENT '担保信息',
   `SAFEGUARD_PLAN` text COMMENT '风险保障方案',
   `REMARK` text COMMENT '备注',
   `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间',
   `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
   `LITIGATION` text COMMENT '涉诉信息',
   `SAFEGUARD_LEVEL` varchar(20) DEFAULT NULL COMMENT '风控评级',
   `RENTER_INFO` text COMMENT '承租人信息',
   `FINANCE_RENT_INFO` text COMMENT '融资租赁物信息',
   `ORG_DESC` text COMMENT '机构简介',
   `CONTRACT_DESC` text COMMENT '应收账款基础合同',
   `TRANS_CLAIM_AMOUNT` decimal(12,2) DEFAULT NULL COMMENT '转让收益权的应收账款金额（元）',
   `CLAIM_DEADLINE` date DEFAULT NULL COMMENT '转让收益权的应收账款到期日',
   `CHANNEL_OFFICE` varchar(128) DEFAULT NULL COMMENT '渠道办事处')   row_format=compressed ;

 

SHOW VARIABLES LIKE '%innodb_file%';

 

SET  GLOBAL innodb_file_format=Barracuda;

这样就能插入数据了