InnoDB row Format (row_format) case A

Background Knowledge:
InnoDB storage engines, like most databases, records are stored as rows, which means that the page holds data for a row of rows in the table. In addition, MySQL on each page of the number of records stored there is a hard rule, at least 2 lines, up to 16KB/2-200, that is, 7992 lines.
Prior to InnoDB 1.0.X, the InnoDB storage engine provided the compact and redundant two formats for storing row record data. Redundant is a row record storage before the mysql5.0 version, which is still supported for compatibility with previous versions of the format, which is rarely used after 5.1, because the compact has a much better structure than it is, and the compact format consumes less disk space and backup time, redundant compared to A little bigger. The compact format is more suitable for most business scenarios.

At the beginning of the InnoDB 1.0.X version, a new file format was introduced,
Previously supported compact and redundant formats called Antelope file formats,
The newly introduced file format is called the Barracuda file format.
The Barracuda file format has two new row record formats: compressed and dynamic,
Also, the Barracuda file format includes all of the Antelope file formats.
This Barracuda file format supports 4 types of Row_format:

Redundant、Compact、Compressed、Dynamic

The Antelope file format supports only 2 types of Row_format:

Redundant、Compact

The parameter innodb_file_format is used to specify the file format, and the file format of the InnoDB storage engine currently in use can be viewed in the following way:

show variables like ‘innodb_file_format‘;

It's basically Barracuda.

The dynamic and compact are basically similar, but they are completely different in the processing of overflow data:

compact格式下，溢出列存储前768字节，而dynamic格式下，溢出的列只存储前20字节，一旦发生了行溢出，dynamic其实就存储一个指针，数据都放在溢出页里，dynamic代表将长字段(发生行溢出)完全off-page存储。

Row_format a case that throws an exception:
A few days ago, the production of MySQL encountered a problem, in the input data, the entire line of data is not recorded, reported the following error:

Cause:java.sql.SQLException: com.taobao.tddl.common.exception.TddlException:java.sql.SQLException:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Row size too large (> 8126). Changingsome columns to TEXT or BLOB or usingROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSEDmay help. In current row format,BLOB prefix of 768 bytes is stored inline.; nested exceptioniscom.ibatis.common.jdbc.exception.NestedSQLException:  

The table is a product detail table, there are more than 20 text fields, just hit a product, each field input data are very long,
And MySQL has a limit, a page (here pagesize is 16K) must save at least 2 rows, that is, the storage length of each row must be less than or equal to 8192, and so many TEXT fields, a row must be saved, that is, overflow occurs, that is, an overflow, Each column will still store the first 768 bytes (the table's Row_formart is compact), the field is more or more than 8192, so the error, plug in.
Finally, the row_format of the table is changed to dynamic to resolve. ALTER TABLE ... row_format=dynamic;

So, if you encounter some of the table text or VARCHAR large number of fields, and not good disassembly, you may need to consider the length of the column after overflow, if the length of the overflow column is still too large, you have to look at the table Row_format:

show table status like ‘%xxx%‘\G  

If necessary, set the fact to dynamic such as:

create table test(id int,name text,...... ) row_format=dynamic;或alter table test row_format=dynamic;

InnoDB row Format (row_format) case A