0

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.

Improve this question
5
  • 1
    the best choice - split the table
    – a_vlad
    Mar 16, 2017 at 10:11
  • I would have to re-write a lot of code to pull and insert info from and to the tables.
    – anjanesh
    Mar 16, 2017 at 10:20
  • Well, first of all you can define the engine on a per-table basis, you don't need a new instance. But you might face a new problem then: in contrast to InnoDB, MyISAM counts varchar(1000) as length 1000 (or 3000 with utf8), no matter if you put shorter string in it (but has a limit of 64k, so it might work). Anyway, I agree with vlad, you should try to redesing your table structure. MyISAM is an old format that should not be used anymore, and is removed in the newest MySQL version (8). I assume you cannot upgrade to 5.7?
    – Solarflare
    Mar 16, 2017 at 11:08
  • At present, cannot upgrade to 5.7, but what's new in 5.7 ?
    – anjanesh
    Mar 16, 2017 at 11:12
  • 5.7 defaults to Barracuda file format, which would allow you to use the dynamic row format. That is what is proposed in the error message. But you should set the format permanently in the server configuration (if you can do that in the private instance, that would be fine), it is risky to do it temporarily, as any further alter to the table (without setting the file format again) would revert that and truncate your data (without warning first).
    – Solarflare
    Mar 16, 2017 at 15:02

2 Answers 2

Reset to default
2

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 & 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.

Improve this answer
0
1

i faced the same issue and I resolved it by using text data type instead of using varchar, I know it is not the good practice and not a proper way to resolve this issue but it will work fine

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.