Professional Web Applications Themes

Problem on inserting data in a LONGBLOB field - MySQL

Hi all, I try to insert data in a LONGBLOB field. Usually it doesn't matter, but depending on which server I execute the query, it fails. Actually, all is OK while the file size doesn't exceed ~700 KB, above: one server execute the query, the other don't. So it seems that the problem comes from a low buffer size, but which one ? There you can find the configuration of the two servers : [url]http://www.point52.com/workingDB.pdf[/url] [url]http://www.point52.com/notWorkingDB.pdf[/url] Here's the query I try to execute (in reality, the file must be > ~700 KB in size) : UPDATE `doents` SET `doc_file`='xoxoxoxo...xoxoxoxo' WHERE ...

  1. #1

    Default Problem on inserting data in a LONGBLOB field

    Hi all,

    I try to insert data in a LONGBLOB field. Usually it doesn't matter,
    but depending on which server I execute the query, it fails.
    Actually, all is OK while the file size doesn't exceed ~700 KB, above:
    one server execute the query, the other don't.
    So it seems that the problem comes from a low buffer size, but which
    one ?

    There you can find the configuration of the two servers :
    [url]http://www.point52.com/workingDB.pdf[/url]
    [url]http://www.point52.com/notWorkingDB.pdf[/url]

    Here's the query I try to execute (in reality, the file must be > ~700
    KB in size) :

    UPDATE `doents` SET `doc_file`='xoxoxoxo...xoxoxoxo' WHERE `doc_id`
    = '1'

    Thank you in advance for your help

    --
    Alexandre Lahure
    Point 52

    Alexandre Lahure Guest

  2. #2

    Default Re: Problem on inserting data in a LONGBLOB field

    >From the docs.

    LONGBLOB

    A BLOB column with a maximum length of 4,294,967,295 or 4GB (232 - 1)
    bytes. The maximum effective (permitted) length of LONGBLOB columns
    depends on the configured maximum packet size in the client/server
    protocol and available memory.

    example: mysqld --set-variable=max_allowed_packet=1M

    and:
    Fix for BUG#4880, RSMD.getPrecision() returning 0 for non-numeric types
    (should return max length in chars for non-binary types, max length in
    bytes for binary types). This fix also fixes mapping of
    RSMD.getColumnType() and RSMD.getColumnTypeName() for the BLOB types
    based on the length sent from the server (the server doesn't
    distinguish between TINYBLOB, BLOB, MEDIUMBLOB or LONGBLOB at the
    network protocol level).

    onedbguru@firstdbasource.com Guest

  3. #3

    Default Re: Problem on inserting data in a LONGBLOB field

    Thank you,

    So, is there a way to change the value of "max_allowed_packet" on the
    fly or am I forced to do it at server launch ?

    Alexandre Lahure Guest

  4. #4

    Default Re: Problem on inserting data in a LONGBLOB field

    having just browsed through the docs, it appears to be a startup-time
    only parameter.

    [url]http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html[/url]

    onedbguru@firstdbasource.com Guest

  5. #5

    Default Re: Problem on inserting data in a LONGBLOB field

    Alexandre Lahure wrote:
    > Thank you,
    >
    > So, is there a way to change the value of "max_allowed_packet" on the
    > fly or am I forced to do it at server launch ?
    You can specify it as a client option too.
    See [url]http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html[/url]

    For example:

    shell$ mysql --max_allowed_packet=16M

    Or:

    mysql> SET GLOBAL MAX_ALLOWED_PACKET=16000000

    There's also a PHP programming example in the user comments on the doc
    page referenced above, posted by 'leo brown'.

    It's not clear what the largest value permitted is. Some places in the
    docs say 1GB
    ([url]http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html[/url]).

    But [url]http://dev.mysql.com/doc/internals/en/the-packet-header.html[/url] says
    the maximum packet length that can be encoded in the packet header is
    2^24, or 16MB. I can't guess why it's possible to set the value to
    something greater than 16MB on the server, if the protocol doesn't
    support it.

    The default value of the packet size is also unclear. This page:
    [url]http://dev.mysql.com/doc/refman/5.0/en/c.html[/url] says that the client's
    default packet size is 16MB, but the server's default is 1MB.

    I don't understand what it means for the server and the client to have
    different default packet size limits.

    Regards,
    Bill K.
    Bill Karwin Guest

  6. #6

    Default Re: Problem on inserting data in a LONGBLOB field

    Thank you for your answers,

    I couldn't assume to change the value of "max_allowed_packet"
    client-side, so I did it server-side where I could, and it works.
    It doesn't matter anymore since I know which variable is responsible of
    the limitation.

    Thank you all again

    Alexandre Lahure Guest

Similar Threads

  1. Replies: 2
    Last Post: March 8th, 11:57 AM
  2. Inserting images in a field?
    By Zlaxzzor in forum Macromedia Director Basics
    Replies: 4
    Last Post: May 4th, 10:28 PM
  3. Replies: 1
    Last Post: July 10th, 08:29 PM
  4. inserting into db from hidden field
    By Gary White in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 8th, 04:03 PM
  5. Problem while inserting data using sqlldr utility.
    By Shravana Kumar in forum Oracle Server
    Replies: 1
    Last Post: December 27th, 07:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139