Increasing MySql Innodb Row length to avoid Error 139

I'm creating a table in MySql (Innodb engine) with more than 15 TEXT datatype columns. After table creation, I'm trying to insert a row in to this table with more than 500 characters in all the columns. While doing so, mysql returns the following error,

[Error Code: 1030, SQL State: HY000] Got error 139 from storage engine

Upon searching, I found that there is a row length limitation of 8000 bytes in mysql. I wanted to know if this limit can be configured (by a parameter or even compiling the code) 开发者_StackOverflow社区to the desired level. I see some links talking about innodb plugin where this is resolved but I couldn't get a clear idea on that. I'm trying this in windows.

Any help on this is greatly appreciated.

Thanks, Ashok.

There is a requirement in InnoDB that one page (16k) must contain at least two records. If you subtract the overhead you'll get that a near 8k per record limit.

BLOB/TEXT types can be longer and InnoDB handles them different way depending on row format.

For compact COMPACT the rule is following. If a record is longer than ~8k then the page stores first 768 bytes of a BLOB field(s). The remaining part of the field is stored in a chain of external pages. With 15 BLOB fields 500 bytes each + other overhead you exceed limit of ~8k per record.

DYNAMIC format was optimized for BLOBs. The limit is same, ~8k per record. But if a record exceeds the limit only a reference to the external page (it's 20 bytes) is stored. The BLOB value is stored in external pages only. So one record may contain more BLOB fields.

DYNAMIC rows format is available only in Barracuda file format. Don't forget to enable it in my.cnf:

innodb_file_per_table=ON
innodb_file_format=Barracuda

To solve your problem you have to create the table in DYNAMIC format.

CREATE TABLE testtext 
  ( 
     id     INTEGER, 
     text1  TEXT, 
     text2  TEXT, 
     text3  TEXT, 
     text4  TEXT, 
     text5  TEXT, 
     text6  TEXT, 
     text7  TEXT, 
     text8  TEXT, 
     text9  TEXT, 
     text10 TEXT, 
     text11 TEXT, 
     text12 TEXT, 
     text13 TEXT, 
     text14 TEXT, 
     text15 TEXT, 
     text16 TEXT, 
     text17 TEXT, 
     text18 TEXT, 
     text19 TEXT, 
     text20 TEXT, 
     text21 TEXT, 
     PRIMARY KEY (id) 
  ) 
engine=innodb 
row_format=dynamic; 

It looks like there are multiple possible solutions to this type of problem. I found this link provided both good information and some good tangible options: http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitation/.

However, if you know from the outset that you are going to be creating a structure like this, you should re-think your approach saving data. Perhaps your purpose would be more effectively served by creating a table where each TEXT-N field is it's own row in the database and related rows are indicated by a shared 'rowid' or similar...

It solved my problem by changing engine to MyISAM, so thought of sharing the same,

Execute below sql query:

ALTER TABLE `table_name` ENGINE = MYISAM

PS: please go through link below : http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html