mysql修改大小限制,更改“ Mysql行大小太大”的限制

How can I change the limit

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.

Table:

id int(11) No

name text No

date date No

time time No

schedule int(11) No

category int(11) No

top_a varchar(255) No

top_b varchar(255) No

top_c varchar(255) No

top_d varchar(255) No

top_e varchar(255) No

top_f varchar(255) No

top_g varchar(255) No

top_h varchar(255) No

top_i varchar(255) No

top_j varchar(255) No

top_title_a varchar(255) No

top_title_b varchar(255) No

top_title_c varchar(255) No

top_title_d varchar(255) No

top_title_e varchar(255) No

top_title_f varchar(255) No

top_title_g varchar(255) No

top_title_h varchar(255) No

top_title_i varchar(255) No

top_title_j varchar(255) No

top_desc_a text No

top_desc_b text No

top_desc_c text No

top_desc_d text No

top_desc_e text No

top_desc_f text No

top_desc_g text No

top_desc_h text No

top_desc_i text No

top_desc_j text No

status int(11) No

admin_id int(11) No

解决方案

The question has been asked on serverfault too.

You may want to take a look at this article which explains a lot

about MySQL row sizes. It's important to note that even if you use

TEXT or BLOB fields, your row size could still be over 8K (limit for

InnoDB) because it stores the first 768 bytes for each field inline in

the page.

The simplest way to fix this is to use the Barracuda file format

with InnoDB. This basically gets rid of the problem altogether by

only storing the 20 byte pointer to the text data instead of storing

the first 768 bytes.

The method that worked for the OP there was:

Add the following to the my.cnf file under [mysqld] section.

innodb_file_per_table=1

innodb_file_format = Barracuda

ALTER the table to use ROW_FORMAT=COMPRESSED.

ALTER TABLE nombre_tabla

ENGINE=InnoDB

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8;

There is a possibility that the above still does not resolve your issues. It is a known (and verified) bug with the InnoDB engine, and a temporary fix for now is to fallback to MyISAM engine as temporary storage. So, in your my.cnf file:

internal_tmp_disk_storage_engine=MyISAM