Change limit for “Mysql Row size too large”

Question

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 

Answer 1

Set followings on your my.cnf file and restart mysql server.

innodb_strict_mode = 0

Answer 2

I ran into this problem recently and solved it a different way. If you are running MySQL version 5.6.20 there is a known bug in the system. See MySQL docs

Important Due to Bug #69477, redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. To address this bug, a patch introduced in MySQL 5.6.20 limits the size of redo log BLOB writes to 10% of the redo log file size. As a result of this limit, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields).

In my situation the offending blob table was around 16MB. Thus, the way I solved it was by adding a line to my.cnf that ensured I had at least 10x that amount and then some:

innodb_log_file_size = 256M

Answer 3

I had the same issue, this solved it for me:

ALTER TABLE `my_table` ROW_FORMAT=DYNAMIC;

From MYSQL Documentation:

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but this new format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.

Answer 4

I would like to share an awesome answer, it might be helpful. Credits Bill Karwin see here https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

They vary by InnoDB file format.At present there are 2 formats called Antelope and Barracuda.

The central tablespace file (ibdata1) is always in Antelope format. If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.

Basic points:

1. One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.

2. Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.

3. Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.

4. Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or less documented in their wiki.

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.

Answer 5

If you can switch the ENGINE and use MyISAM instead of InnoDB, that should help:

ENGINE=MyISAM

There are two caveats with MyISAM (arguably more):

1. You can't use transactions.
2. You can't use foreign key constraints.

Answer 6

I also encountered the same problem. I solve the problem by executing the following sql:

ALTER ${table} ROW_FORMAT=COMPRESSED;

But, I think u should know about the Row Storage.
There are two kinds of columns: variable-length column(such as VARCHAR, VARBINARY, and BLOB and TEXT types) and fixed-length column. They are stored in different types of pages.

Variable-length columns are an exception to this rule. Columns such as BLOB and VARCHAR that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages. We call such columns off-page columns. The values of these columns are stored in singly-linked lists of overflow pages, and each such column has its own list of one or more overflow pages. In some cases, all or a prefix of the long column value is stored in the B-tree, to avoid wasting storage and eliminating the need to read a separate page.

and when purpose of setting ROW_FORMAT is

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page.

Wanna know more about DYNAMIC and COMPRESSED Row Formats