Mysql – how many ‘text’ column a table could have in thesql? I have this error “Row size too large (> 8126).”

MySQL

I'm working on a application which is kinda complex to explain how it works. But this is the situation I'm in,

------------------------------------------------------------------------------
FIELD1 (TEXT) FIELD2(TEXT) FIELD3(TEXT) .........................FIELD70(TEXT)
------------------------------------------------------------------------------

POSSIBLE DATA SIZE FOR A SINGLE FIELD: around 500 characters.

I have around 20 tables in a single database like this.

I know it's too bad idea to have this much column in a single table. But I have to go with this since It's went too far and can not modify number of columns.

Now I got some error like, "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."

WHAT I HAVE TRIED:

  • I altered the table as ROW_FORMAT=COMPRESSED. = didn't work out
  • I altered the table as ROW_FORMAT=DYNAMIC. = didn't work out
  • innodb_file_format = Barracuda = didn't work out
  • I altered the column to VARCHAR(500) = didn't work out
  • I altered the column to TEXT,BLOG,MEDIUMBLOG = didn't work out

What could be the actual error? I've seen plenty of the answers for this problem bur they are telling these above methods that I've tried and failed. PLEASE HELP ME TO SOLVE THIS

Best Answer

The problem is the row size limit for InnoDB tables, in this links you can find some approaches to solve this:

http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitation/
InnoDB create table error: "Row size too large"

Related Question