MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:August 20 2002 2:34pm
Subject:Re: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)
View as plain text  
* Luc Foisy
> > -----Original Message-----
> > From: Roger Baklund [mailto:roger@stripped]
>
> > * Harald Fuchs
> > > In article <200208191407.g7JE71j12413@stripped>,
> > > Victoria Reznichenko <victoria.reznichenko@stripped> writes:
> > >
> > > > As you can see txt1 and txt2 contain text file ~ 8M
> > >
> > > > UPDATE tbl1 SET total=CONCAT(txt1,txt2) WHERE id=1;
> > >
> > > >  SELECT id, LENGTH(txt1), LENGTH(txt2), LENGTH(total) FROM tbl1;
> > > > +----+--------------+--------------+---------------+
> > > > | id | LENGTH(txt1) | LENGTH(txt2) | LENGTH(total) |
> > > > +----+--------------+--------------+---------------+
> > > > |  1 |      8390060 |      8390060 |             0 |
> > > > +----+--------------+--------------+---------------+
> > >
> > > > The same result.
> > > > MySQL inserts NULL in the total, because you can't store data more
> > > > than max_allowed_packet.
> > >
> > > Thanks for the clarification, Victoria.
> >
> > I'm sorry, but this is not very clear to me...
> >
> > The manual describes max_allowed_packet as "Max packetlength
> > to send/receive
> > from to server".
> >
> > Why are the columns transferred between server/client in the above
> > statement? Shouldn't  the entire UPDATE happen on the server side?
> >
>
> It probably does happen on the server side. But wouldn't it be
> smart to limit itself to something it knows it can't transfer later?

Maybe... :)

On the other hand:

1) The documentation on max_allowed_packet talks only about transferring
data, not storing. (This should be easy to fix.)

2) The mere existence of LONGBLOB and LONGTEXT suggests that fields of this
size could be inserted in a table. Again, a warning in the docs could be
very helpfull, but imo the types should not even be there, if they can not
be utilized.

3) How can the server know that the max_allowed_packet for _this_ connection
(the UPDATE'ing connection) isn't smaller than the max_allowed_packet value
for a future SELECT connection? (I could do the UPDATE ... CONCAT(... with
max_allowed_packet=1M, and later do selects with max_allowed_packet=16M)

4) We can of course insert more rows in a table than would be allowed to
receive with a single select statement, this is not an argument to prohibit
many _rows_ in a table. It's not the exact same thing, but I think it is
similar. I would prefer if mysql was not 'baby-sitting' for me... :)

5) It could be usefull in some cases, for instance when you store large xml
documents, you can extract relevant parts of the column, so that the packet
transferred to the client is smaller than max_allowed_packet.

The upper limit for max_allowed_packet has been lifted in 4.0.x, so this
will probably not be a problem in the future, but the docs should mention
this limitation for 3.23.

--
Roger

Thread
4.0.x bug with LONGTEXT?Harald Fuchs15 Aug
  • Re: 4.0.x bug with LONGTEXT?Victoria Reznichenko16 Aug
Re: 4.0.x bug with LONGTEXT?Harald Fuchs16 Aug
  • Re: Re: 4.0.x bug with LONGTEXT?Victoria Reznichenko19 Aug
Re: 4.0.x bug with LONGTEXT?Harald Fuchs20 Aug
  • max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Roger Baklund20 Aug
RE: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Luc Foisy20 Aug
  • Re: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Roger Baklund20 Aug
    • Re: Re: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Victoria Reznichenko21 Aug
      • Re: max_allowed_packet (was: Re: 4.0.x bug with LONGTEXT?)Roger Baklund21 Aug