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

K

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
 
F

Fredrik Lundh

Keith said:
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>
 
F

Fredrik Lundh

Fredrik said:
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>
 
K

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
 
G

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
 
Á

Álvaro 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/en/packet-too-large.html
 
A

Aahz

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top