ERROR 1118 (42000): Row size too large (> 8126).

最近在做資料庫還原時候，遇到以下問題

ERROR 1118 (42000) at line 79532: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRnt row format, BLOB prefix of 768 bytes is stored inline.

解決思路

1，查資料

<a href="https://forums.mysql.com/read.php?22,632894,632894" target="_blank">https://forums.mysql.com/read.php?22,632894,632894</a>

問題1：

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 (&gt; 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 &lt;table_name&gt; 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

針對問題一的回答：

&gt; performance,security and possible error arise 

No, no problem with any such things. 

To explain the changes: 

&gt; 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.) 

&gt; 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. 

&gt; Alter table &lt;table_name&gt; engine=innodb ROW_FORMAT=DYNAMIC; -- There are several "ROW_FORMATs" in InnoDB. This one is fine. 

&gt; performance -- There is probably no noticeable difference in performance among all the options changed above. (One exception is ROW_FORMAT=COMPRESSED.) 

&gt; 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.

我的解決方法：

檢視自己的設定

<code>mysql&gt; show </code><code>GLOBAL</code> <code>VARIABLES </code><code>LIKE</code> <code>'%file_format%'</code><code>;</code>

<code>+</code><code>--------------------------+----------+</code>

<code>| Variable_name            | Value    |</code>

<code>| innodb_file_format       | Antelope |</code>

<code>| innodb_file_format_check | </code><code>ON</code>       <code>|</code>

<code>| innodb_file_format_max   | Antelope |</code>

<code>mysql&gt; show variables </code><code>like</code> <code>'%per_table%'</code><code>;</code>

<code>+</code><code>-----------------------+-------+</code>

<code>| Variable_name         | Value |</code>

<code>| innodb_file_per_table | </code><code>OFF</code>   <code>|</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.03 sec)</code>

修改參數：

<code>innodb_file_format = Barracuda</code>

<code>innodb_file_per_table = 1 </code>

<code>or</code>

<code>set</code> <code>GLOBAL</code> <code>innodb_file_format = </code><code>'Barracuda'</code><code>;</code>

檢查修改後的結果：

<code>| innodb_file_per_table | </code><code>ON</code>    <code>|</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>

<code>+</code><code>--------------------------+-----------+</code>

<code>| Variable_name            | Value     |</code>

<code>| innodb_file_format       | Barracuda |</code>

<code>| innodb_file_format_check | </code><code>ON</code>        <code>|</code>

<code>| innodb_file_format_max   | Barracuda |</code>

<code>3 </code><code>rows</code> <code>in</code> <code>set</code> <code>(0.00 sec)</code>

此時暫時不需要執行 以下指令也可以成功

<code>Alter</code> <code>table</code> <code>&lt;table_name&gt; engine=innodb ROW_FORMAT=</code><code>DYNAMIC</code><code>;</code>

<code></code>

      本文轉自crazy_charles 51CTO部落格，原文連結：http://blog.51cto.com/douya/1894891，如需轉載請自行聯系原作者