DBI, BLOBS, MySQL
matt.lawrence at virgin.net
Mon Jan 12 09:58:20 GMT 2009
Richard Huxton wrote:
> Paul Makepeace wrote:
>> What does 'show table files;' give you? You might have the wrong type
>> of blob/text.
> "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!
More information about the london.pm