Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Corrupted images after attempting to store PNG images as BLOBs inMySQL?

Reply
Thread Tools

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

 
 
Keith Hughitt
Guest
Posts: n/a
 
      08-13-2008
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
 
Reply With Quote
 
 
 
 
Fredrik Lundh
Guest
Posts: n/a
 
      08-13-2008
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>

 
Reply With Quote
 
 
 
 
Fredrik Lundh
Guest
Posts: n/a
 
      08-13-2008
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>

 
Reply With Quote
 
Keith Hughitt
Guest
Posts: n/a
 
      08-13-2008
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 Removed)> wrote:
> 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>


 
Reply With Quote
 
GHUM
Guest
Posts: n/a
 
      08-13-2008
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

 
Reply With Quote
 
Álvaro G. Vicario
Guest
Posts: n/a
 
      08-13-2008
*** 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
--
 
Reply With Quote
 
Aahz
Guest
Posts: n/a
 
      08-14-2008
In article <(E-Mail Removed)>,
Fredrik Lundh <(E-Mail Removed)> wrote:
>
>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 ((E-Mail Removed)) <*> http://www.pythoncraft.com/

Adopt A Process -- stop killing all your children!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
XP crashed after attempting to install a digital camera jcwinters Windows 64bit 1 07-13-2008 01:44 AM
ANN: PNG image CAPTCHA with PNG canvas available (SkimpyGimpy) aaronwmail-usenet@yahoo.com Python 0 04-18-2007 07:33 PM
ASP.Net bug when inserting Blobs into SQL Server? .Net Newbie ASP .Net 0 08-24-2004 09:09 PM
Problem with Blobs .Net Newbie ASP .Net 0 08-24-2004 09:05 PM
Reading Blobs From Oracle Ken ASP .Net 1 12-20-2003 03:21 AM



Advertisments