CODEWITHSUNDEEP
mysqlblobmysql-connectormaxlength
johnmurphy01
johnmurphy01

Reputation: 31

MySQL 5.5 LONGBLOB Column Data too long for column 'x' at row 1

I have a column in my MySQL database that stores images as a byte array.

I am trying to update a row to insert a new image. The new image is 163K, and when I convert it to a byte[], the number of elements in the array is 167092. When I run the stored procedure that does the update, I get an error "Data too long for column 'x' at row 1. I already have an existing image in the database that has 8844 byte[] elements when converted.

The column datatype is LONGBLOB. From my understanding, I should have appox 4Gb to work with.

I have tried updating my my.ini file to make the MAX_ALLOWED_PACKETS=16M and I even tried 100M.

I am using the MySQL .NET Connector libraries to execute my stored procedures.

Does anyone have any ideas on how to fix this issue? I know I could store the image paths instead of storing the images directly into the database. But I would like to know how to solve my current issue and still store the images in the database first before trying to change my approach.

Upvotes: 0

Views: 4851

Answers (1)

8192K
8192K

Reputation: 5290

I've had exactly the same problem...

In my case I was passing the LONGBLOB via a TEXT parameter since I wanted to use CONCAT inside the stored procedure in order to create dynamic SQL.

The solution was simply to change TEXT into LONGTEXT. That's it :) That really took some time to figure out...

Hope I could help even after almost three years.

Upvotes: 1

Related Questions