# 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，查资料

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 (> 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 <table_name> 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.

> performance,security and possible error arise

No, no problem with any such things.

To explain the changes:

> 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.)

> 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.

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

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

> 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.

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> show GLOBAL VARIABLES LIKE '%file_format%'; +--------------------------+----------+ | Variable_name            | Value    | +--------------------------+----------+ | innodb_file_format       | Antelope | | innodb_file_format_check | ON       | | innodb_file_format_max   | Antelope | +--------------------------+----------+     mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | OFF   | +-----------------------+-------+ 1 row in set (0.03 sec)

 1 2 3 4 innodb_file_format = Barracuda innodb_file_per_table = 1  or set GLOBAL innodb_file_format = 'Barracuda';

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | ON    | +-----------------------+-------+ 1 row in set (0.00 sec)     mysql> show GLOBAL VARIABLES LIKE '%file_format%'; +--------------------------+-----------+ | Variable_name            | Value     | +--------------------------+-----------+ | innodb_file_format       | Barracuda | | innodb_file_format_check | ON        | | innodb_file_format_max   | Barracuda | +--------------------------+-----------+ 3 rows in set (0.00 sec)

 1 Alter table  engine=innodb ROW_FORMAT=DYNAMIC;  

本文转自crazy_charles 51CTO博客，原文链接：http://blog.51cto.com/douya/1894891，如需转载请自行联系原作者

20330 0

29716 0

13839 0

22797 0

16894 0

20979 0

14914 0
+关注
21114

0

JS零基础入门教程（上册）