DBI, BLOBS, MySQL

[Matt Lawrence]

Richard Huxton wrote:
> 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
>>     
>
> "If a TEXT column is indexed, index entry comparisons are space-padded
> at the end. This means that, if the index requires unique values,
> duplicate-key errors will occur for values that differ only in the
> number of trailing spaces. For example, if a table contains 'a', an
> attempt to store 'a ' causes a duplicate-key error"
>
> Genius. True genius.
>
>   
This reminds me of a slightly-related bug (feature?) with mysql and DBI, 
where data stored in a blob has trailing blanks removed on retrieval (if 
ChopBlanks is set, which it is by default).

This flummoxed me for an embarrassingly long time when I was 
investigating why a small proportion of our encrypted data couldn't be 
decrypted. I found to my surprise that if I grabbed the bad data myself 
and then decrypted it it was fine, but not if I used DBI. Only then did 
I spot that all the malformed data happened to end with 0x20. Duh!

Matt