Bug #8835 max_allowed_packet on string functions applies to UPDATE

• Submitted: 27 Feb 2005 22:08
• Modified: 16 Sep 2006 10:54
• Reporter: Chris Padfield
• Status: Won't fix
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: All
• OS: Any (All)
• CPU Architecture: Any

[27 Feb 2005 22:08] Chris Padfield

Description:
The max_allowed_packet limit applied to strings also applies for UPDATE queries. This limit makes sense for SELECT queries where data is to be returned to client but not for UPDATE queries.

This prevents being able to store a large blob in the database using 
UPDATE blobs SET blob = CONCAT(blob, 'more data')

Thus effectivly limits the size of the column to the size of max_allowed_packet.

How to repeat:
Try to CONCAT 1MB of database to a column with 1MB of data in it and max_packet_size is 1MB.

Suggested fix:
Remove max_allowed_packet check on CONCAT (and perhaps other string functions) for UPDATE queries.

[16 Sep 2006 10:54] Valeriy Kravchuk

Thank you for a feature request. Note, that according to the manual (http://dev.mysql.com/doc/refman/5.1/en/memory-use.html):

" Each connection uses some thread-specific space. The following list indicates these and which variables control their size:

- A stack (default 192KB, variable thread_stack)

- A connection buffer (variable net_buffer_length)
    
- A result buffer (variable net_buffer_length)

The connection buffer and result buffer both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed."

So, even if it is not obvious from the name, max_allowed_packet is used to control the size of memory allocated for thread. CONCAT needs to allocate this memory, that's why this resriction applies to it also.