MySQL Error 1118 : Row Size too large ( > 8126)

簡單說 :

Innodb 下, 如果 innodb_page_size 設定為 16KB
table 每一個 row 最大的 length(size) 為 8126 bytes  ( 大約是 16KB /2 )

(注意： mysql 限制 row size 為 65536，但是每種 engine 還會有自己的限制)
(ref : https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)
(Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.)



固定欄位的大小就是固定大小
但是變動欄位大小 (blog / text / varchar) 等，在不同的 innodb row_format 下有不同的計算方法

compact :
變動欄位會用 768 bytes, 超過的會放在另一個 page
如果有太多的 column (例如11個 blob)，那很容易就超過 8126 bytes，就會出現上述的訊息

dynamic / compress
變動欄位會用 20 bytes，超過的會放在另一個page



解決方法 :

1. 改用 dynamic / compress row_format

這需要啟用 innodb barracuda file-format
同時重新 create table(alter table)
row format可以用 show table status 查看



2. 增加 page_size

提高 innodb_page_size
show variables like "innodb_page_size"

ref :
 http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. For an InnoDB page size of 64KB, the maximum row length is about 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html



3. 把欄位拆到其他 table


4. 把很多個 blob/text 合併，在應用層再去分離





ref :

https://www.percona.com/blog/2011/04/07/innodb-row-size-limitation/

Innodb has a limit for the maximum row size, which is slightly less than half a database page (the actual math is 16k-(page header + page trailer)/2. For the default page size of 16kb. this sets an ~8000 bytes limit on row size (8126 as reported by my test installation). This limit is a consequence of InnoDB storing two rows on a page. If you’re using compression, however, you can store a single row on a page.
If your row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns for off-page storage.

In these cases, the first 768 bytes of each variable length column is stored locally, and the rest is stored outside of the page 

It’s worth mentioning that this limit applies to the byte-size of values, not the character-size. This means if you insert a 500 character string with all multi-byte characters into a VARCHAR(500) column, that value will also be chosen for off page storage. This means it is possible that you hit this error after converting from a native language character set to utf8, as it can increase the size dramatically.

If you have more than 10 variable length columns, and each exceeds 768 bytes, then you’ll have at least 8448 bytes for local storage, not counting other fixed length columns. This exceeds the limit and therefore you get the error.

http://blog.opskumu.com/mysql-blob.html
非常詳細的說明


http://wubx.net/varchar-vs-text/

- text 是要要进overflow存储。 也是对于text字段，不会和行数据存在一起。但原则上不会全部overflow ,
会有768字节和原始的行存储在一块，多于768的行会存在和行相同的Page或是其它Page上。

- varchar 在MySQL内部属于从blob发展出来的一个结构，在早期版本中innobase中，也是768字节以后进行overfolw存储。

- 对于Innodb-plugin后： 对于变长字段处理都是20Byte后进行overflow存储
（在新的row_format下：dynimic compress） 

http://blog.sina.com.cn/s/blog_8e9cceee0101k65j.html

【导致问题的原因】
总结了下原因是因为mysql-innodb是按照page存储数据的，每个page max size是16k，然后每个page两行数据，所以每行最大8k数据。如果你的字段是blob之类的话，会存储在page之外的溢出区里。

但是innodb默认的approach（羚羊）存储格式会把每个blob字段的前864个字节存储在page里，所以你的blob超过一定数量的话，单行大小就会超过8k，所以就报错了

【解决思路】

解决方式是使用innodb的Barracuda（梭鱼） 存储格式

这种格式对blob字段的处理方式是在page里头只存储一个20byte大小的指针，其它全存在溢出区，所以你轻易超不了8k