Row size too large (> 8126) I have a table with 156 columns and most of them are either VARCHAR(1000) or TEXT.

Now, when entering data into one such row, I get 


  mysql_error() = 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.


I am thinking of changing the table type to MyISAM.
Its on a shared server, but can setup a private MySQL instance.

From here :


  InnoDB implements transactions, foreign keys and relationship
  constraints, MyISAM does not.


I can still use JOINS etc on MyISAM, right ? I'm not implementing any other complex stuff on these tables.
5.5.14 and 5.6.3 and 5.7 have innodb_file_format=Barracuda as an option.

Prior to 5.7.7, do these:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout &amp; login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;


In 5.7.7 and after, most of that is defaulted as you need.

DYNAMIC is the format you need -- it leaves only 22 bytes in the main part of the row (which is limited to about 8KB), and puts the rest in overflow blocks.  This applies to VARCHAR, TEXT, VARBINARY and BLOB.  22*156 is well under 8K.  "When the row is too long, InnoDB chooses the longest columns for off-page storage."  That is, some of the shorter columns for a given row may still reside 'on-page'.

I think the "22" is a 2-byte length, plus a 20-byte "pointer" to the data.  That is, none of the data is stored 'on-page', and the 768 is not relevant.

Another possibility (but I don't recommend it) is to increase the default page size from 16KB to 32KB, thereby increasing the max from about 8K to about 16K.  (There is a 64KB page size, but the limit is still 16KB for each row.)

Also, COMPRESSED can be used instead of DYNAMIC, but again, I don't recommend it, especially before 5.7.  ("Compressed = Dynamic plus compression.")

Splitting the table ("vertical partitioning") is a good idea.

MyISAM is a bad idea.

Checking to see if you really need 1000 is a good idea.