MySQL Text Column being truncated.

Question

I have a MySQL database in which I am storing a bunch of textual info into a Text field in the database. The columns is

Name: info
Type: text
Collation: utf8_general_ci

I had a user insert ~64kb of information into the field and it freaks out. It will truncate the last 3 characters off. Which in this case happen to be the ending to a tab so it screws up everything after it on the page. If I manually go into the database and remove a couple letters and add the back then next time I go to edit it those last 3 will be removed again.

phpmyadmin is stating that the field is to long and may not be editable. So I tried to edit it on my cms page and I still recieved the same result.

Are there any known problems with this much data in a single database text column in mysql? It does not seems like there should be.

Answer 1

I think the max size of a MySQL TEXT field is smaller than 2^16 bytes. Using UTF-8 encoding, this can mean even less characters. You could use MEDIUMTEXT for 2^24 or BIGTEXT for 2^32 bytes instead, see the MySQL-Docs for details.

Ah, and make sure to check your max_allowed_packet size.

Answer 2

There is this thing called documentation, saying that the storage required for datatypes is:

BLOB, TEXT  L + 2 bytes, where L <  2^16
MEDIUMBLOB, MEDIUMTEXT  L + 3 bytes, where L <  2^24
LONGBLOB, LONGTEXT  L + 4 bytes, where L <  2^32

Note, that 2^16 == 65536

Answer 3

My understanding is TEXT has a max value of 65,000 bytes and anything larger than that will be truncated. This would seem to explain your situation. You say your user is inputting around 64kb, could it be just over 65kb, by a few (three) bytes?

Consider using a BLOB.

Answer 4

You've got to remember that the TEXT has a Max of 65,535 characters. If your content exceeds 64K bytes it possible you are exceeding the limit of the field charaters. I suggest changing your column type to MEDIUMTEXT or LONGTEXT and see if that solves your problem.