It appears BLOB data is passed as a string literal on
an INSERT. There is a problem when MySQL's
character_set_client is set to something different from
the table/db/server's character set: the character set
translation is applied to the BLOB data before it is
inserted in the table.
For example, if character_set_client is set to utf8 but
the table is configured for latin1, random binary data
may get partially translated from utf8 to latin1, if
any of the bytes look like utf8 markers. When fetched
using a SELECT, the BLOB data retrieved is different.
Somehow, for the fetch, the character set translation
is not applied, therefore we end up with something
different than what was inserted.
What other mechanism does MySQLdb offer to insert BLOB
data to make it agnostic to character_set_client
settings? For this purpose, one needs to insert the
BLOB data using something other than a string literal.
Of course, it would be preferable if the MySQLdb driver
handled this transparently.
This problem was observed with MySQLdb-1.2 but I
believe it has been present for some time. MySQL
server is 4.1.12a (also tried 4.1.9). Python version
is 2.4.1 (also tried 2.4). Platform is Windows XP.