Bug #20458 Concat string operator fail on large strings ( MySQL 5 only )
Submitted: 14 Jun 2006 11:52 Modified: 9 Aug 2006 13:55
Reporter: Kåre Høvik Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.x OS:Linux (Mandriva Cooker, i586)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[14 Jun 2006 11:52] Kåre Høvik
Concatinating large strings fail in MySQL 5. Strings in columns of datatype "longtext" are grown up to ca 560kB, but after that they seem to be shifted ( loosing the starting content ).

Tested on both Mandriva ( 5.0.22 ) and OS-X ( 5.0.19 )

How to repeat:
CREATE TABLE test_table(
id int(11) not null,
data longtext not null default '');
INSERT INTO test_table ( id, data ) values ( 1, 'kakemann' );
<repeat 25 times>
UPDATE test_table SET data=concat( data, '<insert 64kB string here>' ) WHERE id=1;
<end repeat>
[14 Jun 2006 12:37] Tonci Grgin
Hi Kåre, thanks for your bug report.
Verified as described by reporter on MySQL 5.0.23-debug bk build, Suse 10 using script provided: mysql -uroot test < insert.sql
MySQL 4.1.21-debug-log bk build on same Suse 10 host returns correct result.
[9 Aug 2006 10:13] Ramil Kalimullin
Hi Kåre,

could you paste a result of 
select @@max_allowed_packet;
here, please?
[9 Aug 2006 11:49] Kåre Høvik
mysql> select @@max_allowed_packet;
| @@max_allowed_packet |
|              1047552 |

The main reason for concatenating strings like in my example was to build strings longer than the maximum packet size allowed. However, while concatenating, it would not even build the string up to the allow packet size.
[9 Aug 2006 13:55] Ramil Kalimullin

trying to reproduce the problem I got a
"Result of concat() was larger than max_allowed_packet (1048576) - truncated"
warning exactly when the string exceeded the max_allowed_packet value, so I'll close this one as not a bug.

Thanks for your participation.
[17 Apr 2007 2:08] Dustin Oprea
I don't understand why this bug was closed.... The query being sent by the user doesn't exceed the maximum packet size, and as I understand it, max_allowed_packet limits the size of the query that is sent to MySQL. This limit is violated by the data produced internally in MySQL's logic, and not the actual user query... Therefore, I believe this feature is flawed.

The obvious workaround to this issue is to increase the value of max_allowed_packet, but this can not be done for the following two reasons:

o The MySQL server being used is located on a remote hosting server, whose configuration I can not change.
o The value of max_allowed_packet should be dynamic and able to be changed at runtime, except that MySQL 5.0.x has a bug that causes MySQL to ignore any changes to this setting.

Because of this 'feature', no one field of any record in the database may have a size greater than 1M.

I view this as an enormous failure of MySQL to meet my needs in storing data. I have seen this same bug reported for 4.0 and 4.1, and nothing has been changed.

What can be done, or what do you suggest I do?

Dustin Oprea
[18 Apr 2007 4:31] Ramil Kalimullin
Hi, Dustin!

It is a basic design constraint of the current server that the string-valued function may not return a value larger than max_allowed_packet.  Unfortunately, the only workaround is to increase the max_allowed_packet value; changing the server's behavior in this regard is not possible.  In MySQL 5.0, you can increase the per-session max_allowed_packet setting with:  set session max_allowed_packet=...

mysql> select version();
| version()    |
| 5.0.42-debug | 
1 row in set (0.00 sec)

mysql> create table b20458(a longtext not null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into b20458 values('something');
Query OK, 1 row affected (0.00 sec)

mysql> set session max_allowed_packet=1024*1024*50;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_allowed_packet;
| @@max_allowed_packet |
|             52428800 | 
1 row in set (0.00 sec)

mysql> update b20458 set a=concat("",repeat('z', 1024*1024*50));
Query OK, 1 row affected (1.54 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select length(a) from b20458;
| length(a) |
|  52428800 | 
1 row in set (0.08 sec)