By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,704 Members | 1,122 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,704 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Aug 13 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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>

Aug 13 '08 #2

P: n/a
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>

Aug 13 '08 #3

P: n/a
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 <fred...@pythonware.comwrote:
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>
Aug 13 '08 #4

P: n/a
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

Aug 13 '08 #5

P: n/a
*** 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
--
Aug 13 '08 #6

P: n/a
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!
Aug 14 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.