Some limitations of the "MySQL" Governor degree

Today the development of the import data in the time to report an error:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. Some columns to TEXT or BLOBs

Cause: The imported data row length is found to be long. Official documents are resolved as follows:

1. vertical table field split or large large field merge (maximum number of characters is not more than 768, business merge + split), divide your table into small ones. If one table contain more than of text colums, and the data contain is a little bit long. This error is thrown out.

2. Modify the table's storage engine, modify InnoDB to MyISAM.

3. Modify the Row_format to compressed or dynamic, of course, the premise must be guaranteed Innodb_file_format =barracuda.

But why do the explanations above appear?

A limitation that is found by the query as InnoDB:

We know that the INNODB page block size defaults to 16KB, the table data is stored in B-tree node's page block, but if the table row of data length more than 16k, this time there will be a row overflow, overflow of the row is stored in another place, The page that holds the overflow data is called Uncompresse blob page.

InnoDB uses clustered index to store data, that is, B + tree structure, so there are at least two rows of data in each page block, otherwise it loses the meaning of B + tree (only one piece of data per page, the whole tree becomes a doubly linked list), so that the maximum length of a row of data is limited to 8k.

When the inserted row of data cannot be stored in a data page block, in order to ensure that the page can hold at least two rows of data, InnoDB will automatically part of the data overflow to another page, a portion of the data will be stored in the data page block, the size of the column is the first 768 bytes, and then there is offset point to overflow page.

As mentioned above, the first 768 bytes of the large large segment are stored in the data page block, then if there are 10 large segments (such as varchar (TEXT,BLOB), the same as varchar storage of the first 768 bytes), also more than one row of data 8k limit (10*768< 8000,11*768>8000). If the inserted value exceeds 8000 bytes, an error (BLOB or text is the same):

The test is as follows:

Table structure:

Inserting 10 columns of data (10*768<8000) can be inserted:

Insert 11 column data (11*768>8000) Error:

Text data type test:

BLOB data type test:

Once you understand what's going on, you can solve the problem, reduce the number of fields in varchar (1000), or change the storage engine to MyISAM, or increase the size of the page_size: Change to 32k,64k. Because the demand is not good change, the size of the database page block changes need to change the source code, understand that the table to query the main, update very little, so instead of MyISAM storage engine:

You can see that the MyISAM storage engine is not subject to this limitation.

As can be seen from the above, in the MySQL InnoDB Storage Engine table received page block size, data in the form of B + tree organization of data, resulting in a single row of data can not exceed 8k, thus affecting the table large number of data type Varchar,text,blob limit, in the 16k page block size, It is best not to exceed 10, and you need to be aware of this limitation in table design.

In the InnoDB plugin version, MySQL introduced a new file format: Barracuda, Barracuda; There are two new row records in the file format: compressed,dynamic, the two-clock format is completely row-overflow for BLOB data, Use only 20 bytes in the data page to point to the overflow page.

Antelope is the innodb-base file format, Barracude is the file format introduced after Innodb-plugin, and Barracude file format is also supported. The difference between the two is:

File format

1. Antelope (innodb-base)

   Support for row formats
   Row_format=compact
   Row_format=redundant
   Characteristics
   The difference between the compact and the redumdant is the difference between the first and the remaining content.
   The compact storage format is headed for a non-null, variable-length list of field lengths, and the redundant storage format is the first part of a field-length offset list (each field occupies a byte length and its corresponding displacement). In Antelope for variable-length fields, less than 768 bytes are not overflow page storage, and in some cases result set IO is reduced.

2. Barracuda (Innodb-plugin)

   Support for row formats
   Row_format=dynamic
   Row_format=compressed
   Characteristics
   The two are mainly functional differences. In addition, the difference between the variable-length field in the line and the antelope is that only 20 bytes are saved, and the other overflow page is stored. In addition, these two need to open innodb\_file\_per\_table=1 (this feature is useful for some optimizations)

Note:

One thing to note here is that if you want to use compression, you must first use the Innodb_file_format =barracuda format, otherwise it will not work.

Reference:

InnoDB row Storage and row Formats

Limitations of large characters in InnoDB

Antelope and Barracuda Differences

Some limitations of the "MySQL" Governor degree