DBI, BLOBS, MySQL

[Christopher Jones]

On 8 Jan 2009, at 23:11, Peter Corlett wrote:
> On 8 Jan 2009, at 23:02, Christopher Jones wrote:
>> I'm having issues inserting files into a BLOB field in MySQL, using  
>> something similar to this;
> [...]
>> The problem is that files bigger than 64K are being truncated on  
>> their insert into the database (i.e. the maximum BLOB length in the  
>> database is 64K). The global max_allowed_packet variable is set to  
>> 16776192, so that doesn't seem to be the culprit, and I don't know  
>> of any other limitations in MySQL that would limit the size of the  
>> insert.
>
> That's correct. BLOBs are binary TEXT fields, and both have a limit  
> of 64kB. You want a MEDIUMBLOB or LONGBLOB instead, which have 16MB  
> and 4TB limits respectively.
>
> (Ah, the rusty razor blade that is MySQL claims another victim.)




On 8 Jan 2009, at 23:20, Paul Makepeace wrote:
> What does 'show table files;' give you? You might have the wrong type
> of blob/text.
>
> http://dev.mysql.com/doc/refman/5.0/en/blob.html



A cursory glance at the MySQL documentation does indeed point this  
out. And to think that I even considered it was possible that this  
could be a Perl/DBI/coding problem, when the sheer weight of previous  
circumstantial and incriminating evidence was enough to condemn MySQL  
from the start.

Thanks for your prompt, helpful and annoyingly accurate responses.


Chris.



"Damn these electric sex pants"