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

Discussion in 'Python' started by Keith Hughitt, Aug 13, 2008.

  1. 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
    Keith Hughitt, Aug 13, 2008
    #1
    1. Advertising

  2. Re: Corrupted images after attempting to store PNG images as BLOBsin MySQL?

    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, Aug 13, 2008
    #2
    1. Advertising

  3. Re: Corrupted images after attempting to store PNG images as BLOBsin MySQL?

    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>
    Fredrik Lundh, Aug 13, 2008
    #3
  4. 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 <> 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>
    Keith Hughitt, Aug 13, 2008
    #4
  5. Keith Hughitt

    GHUM Guest

    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
    GHUM, Aug 13, 2008
    #5
  6. Re: Corrupted images after attempting to store PNG images as BLOBs in MySQL?

    *** 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


    --
    -- 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
    --
    Álvaro G. Vicario, Aug 13, 2008
    #6
  7. Keith Hughitt

    Aahz Guest

    Upgrading to DB-API (was Re: Corrupted images ...)

    In article <>,
    Fredrik Lundh <> 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 () <*> http://www.pythoncraft.com/

    Adopt A Process -- stop killing all your children!
    Aahz, Aug 14, 2008
    #7
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jan Liße
    Replies:
    3
    Views:
    795
    Suresh
    Feb 20, 2004
  2. William Payne

    String corrupted after throw

    William Payne, Mar 7, 2004, in forum: C++
    Replies:
    2
    Views:
    397
    Alf P. Steinbach
    Mar 8, 2004
  3. Replies:
    0
    Views:
    459
  4. lightning
    Replies:
    1
    Views:
    647
    Daniel Pitts
    Sep 28, 2009
  5. Replies:
    5
    Views:
    133
Loading...

Share This Page