Corrupted images after attempting to store PNG images as BLOBs inMySQL?

[Keith Hughitt]

Hi all,

I've run into a strange error while trying to store some PNG images in
a MySQL database using MySQLdb. When I try to insert smaller images (<
64kb or so) everything seems to work fine. When I start trying to
insert larger images (~150kb), however, the images get corrupted along
the way.

The result is that only part of the image, e.g. the top 30% of the
image, is stored in the database, and the rest is simply transparent.
Furthermore, if I attempt to view the image in mysql-query-browser, it
does not display and simply states "Cannot display as image data,"
which seems to further suggest the idea that the data is being
corrupted somewhere along the way.

To store the image I'm using:

blob = open(img, 'rb').read()
sql = "INSERT INTO table VALUES('%s')" % (MySQLdb.escape_string(blob))

Anyone have any ideas?

Thanks,
Keith

[Fredrik Lundh]

Keith Hughitt wrote:

I've run into a strange error while trying to store some PNG images in
a MySQL database using MySQLdb. When I try to insert smaller images (<
64kb or so) everything seems to work fine. When I start trying to
insert larger images (~150kb), however, the images get corrupted along
the way.

The result is that only part of the image, e.g. the top 30% of the
image, is stored in the database, and the rest is simply transparent.
Furthermore, if I attempt to view the image in mysql-query-browser, it
does not display and simply states "Cannot display as image data,"
which seems to further suggest the idea that the data is being
corrupted somewhere along the way.

To store the image I'm using:

blob = open(img, 'rb').read()
sql = "INSERT INTO table VALUES('%s')" % (MySQLdb.escape_string(blob))

Ouch. Please use parameters instead of explicit escapes and string
formatting; Python's not PHP.

Anyone have any ideas?

Silently truncating or otherwise mangling columns is a standard MySQL
feature. What does the table definition look like?

</F>

[Fredrik Lundh]

Fredrik Lundh wrote:

Silently truncating or otherwise mangling columns is a standard MySQL
feature. What does the table definition look like?

Oh, you did write BLOB in the subject. BLOB columns hold 64k (minus 2
bytes for housekeeping), and excess data is discarded, by default:

"If strict SQL mode is not enabled and you assign a value to a BLOB or
TEXT column that exceeds the column's maximum length, the value is
truncated to fit and a warning is generated."

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Are you sure you *need* to use MySQL ;-)

</F>

[Keith Hughitt]

Thanks Fredrik.

That certainly explains things :) I also appreciate the suggestion on
coding guidelines: I'm
still becoming familiar with python. Originally we were not using the
database to store images,
but we started testing out storing images there as well as meta-data.
We may end up switching back
though for efficiency. For now though I think I will try mediumblob to
at least see if I can fix
the problem as things are.

Thanks and take care!
Keith

On Aug 13, 10:46 am, Fredrik Lundh <[e-mail]:

Fredrik Lundh wrote:

Silently truncating or otherwise mangling columns is a standard MySQL
feature. What does the table definition look like?

Oh, you did write BLOB in the subject. BLOB columns hold 64k (minus 2
bytes for housekeeping), and excess data is discarded, by default:

"If strict SQL mode is not enabled and you assign a value to a BLOB or
TEXT column that exceeds the column's maximum length, the value is
truncated to fit and a warning is generated."

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Are you sure you *need* to use MySQL ;-)

</F>

[GHUM]

Keith,

still becoming familiar with python. Originally we were not using the
database to store images,
but we started testing out storing images there as well as meta-data.

just a remark: I am using PostgreSQL to store BLOB-Data as there are
"Images", "PDFs", "Microsoft Office Files".

The system (application, network, database) is working quite okay up
to around 10 Megabytes per file. And that for around 7 years now.

The database itself comfortably works with up to 1Gig per object, just
the database drivers / network stack / bandwith / users patiance for
answers really gets... unreliable above 10 Meg per file.
Best wishes,

Harald

[=?iso-8859-1?Q?=C1lvaro?= G. Vicario]

*** Fredrik Lundh escribió/wrote (Wed, 13 Aug 2008 16:46:04 +0200):

Oh, you did write BLOB in the subject. BLOB columns hold 64k (minus 2
bytes for housekeeping), and excess data is discarded, by default:

"If strict SQL mode is not enabled and you assign a value to a BLOB or
TEXT column that exceeds the column's maximum length, the value is
truncated to fit and a warning is generated."

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Apart from this, MySQL features another delightful data truncator called
the "max_allowed_packet" server parameter:

http://dev.mysql.com/doc/refman/5.0/...too-large.html
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor en cubitos: http://www.demogracia.com
--

[Aahz]

In article <ma*************************************@python.or g>,
Fredrik Lundh <fr*****@pythonware.comwrote:

>
Ouch. Please use parameters instead of explicit escapes and string
formatting; Python's not PHP.

How would you recommend upgrading an application that is more than ten
years old and contains something like 100K lines of code?
--
Aahz (aa**@pythoncraft.com) <* http://www.pythoncraft.com/

Adopt A Process -- stop killing all your children!