MySQL data type: Text,,, Erroring: Data Too Long

Question

I have a field as follows in MySQL: Type: Text Length: 0 Decimals: 0

And when I try to insert data around the size of 4 pages of MS Word, Coldfusion errors with: Data Too Long from the DB.

I thought TEXT data type was able to expand and handle this size of data? What am I missing and what can I do?

Answer 1

The type TEXT is limited to 2^16 bytes, or 65536 bytes. Try using the type LONGTEXT instead. It can hold values up to 2^32 bytes in length.

Answer 2

Text extracted from:

MySQL 5.1 Reference Manual :: 10 Data Types :: 10.1 Data Type Overview :: 10.1.3 Overview of String Types

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]  A TEXT column with a maximum length of 65,535 (2^16 – 1) characters. 

The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a two-byte length prefix that indicates the number of bytes in the value.

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.

I think you'd better use BLOB for that column.

MySQL 5.0 Reference Manual :: 10 Data Types :: 10.4 String Types :: 10.4.3 The BLOB and TEXT Types