mysql maximum row size

Question

I don't know why I'm having this odd behaviour on mysql 5.6.24 , can you help me ? do you think it's a bug

mysql -D database --default_character_set utf8 -e "ALTER TABLE abc_folder ADD COLUMN lev10 varchar(5000);"

ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

instead

 mysql -D database --default_character_set utf8 -e "ALTER TABLE abc_folder ADD COLUMN lev10 varchar(50000);"

In other words a bigger varchar() entry is accepted and correctly working. Does anybody know what is going on ?

I assume that somewhere the varchar() is turned into some type of text data type, but I can't find a reference to this in the documentation. — Gordon Linoff
– Gordon Linoff, Commented Apr 16, 2015 at 11:28
Please provide SHOW CREATE TABLE fnbl_folder. It may help with @GordonLinoff 's comment, and it may explain what happened. — Rick James
– Rick James, Commented Apr 21, 2015 at 2:35

Gary Olsson · Accepted Answer · 2015-04-22 13:02:22Z

This error means that in your table abc_folder, one single line would be bigger than 65535 bytes without columns of type TEXT or BLOBS. 65535 is the highest number which can be represented by an unsigned 16-bit binary number. And as stated here :

Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns:

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs

This means that internally, MySQL is probably using a 16 byte number to store the size of a row.

To correct your error, I would suggest changing VARCHAR(5000) into TEXT or to use multiple tables. Using TEXT if you want to impose a limit of 5000 bytes on your column, you should use a truncate function when inserting into the table. You can use substr for that.

INSERT INTO abc_folder (lev10) VALUES (SUBSTR('Some Text',0, 5000))

Collectives™ on Stack Overflow

mysql maximum row size

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related