MySQL data is too long for column occuring on LONGBLOB

Question

I have the following MySQL (version 5.1) table (InnoDB):

Username varchar(50)  
Sequence int(11)  
FileType varchar(10)  
Photo longblob -- here
PhotoSize int(11)  
Timestamp datetime

Attempting to insert a byte[] 96.7KB fails with the error:

Data Too Long For Column 'Photo' At Row 1

Inserting byte[] (size 37.2KB) works fine. I don't know what the tipping point is.

I googled it and many said the solution was to change it from BLOB to LONGBLOB. This didn't work. Others said changing to Max_Allowed_Packet to 16M (in the my.ini file) would solve the problem. This also didn't work.

Can anyone help me?

Answer 1

Even if they "can", databases are not made to stock binary files. It's more efficient to have in your table the path of the files on your server.

Answer 2

LONGBLOB

A BLOB column with a maximum length of 4,294,967,295 or 4GB (232 – 1) bytes. The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value.

Are you sure that yu have edited the right my.ini file? Did you check in your loaded configuration values? If you are using phpMyAdmin go to "Variables" tab and check "max allowed packet" value, default is 16,777,216, your shoudl be way more something like 1,000,000,000