Bug #6370 concat return NULL when it shouldn't?

• Submitted: 1 Nov 2004 19:48
• Modified: 1 Nov 2004 22:12
• Reporter: Min Xu
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 4.0.21
• OS: Linux (linux tao 1.0)
• CPU Architecture: Any

[1 Nov 2004 19:48] Min Xu

Description:
with mysql 4.0.21, I have table with simple ID and a longblob fields,
I hit this strange problems. It seems when the longblob is larger than
around 1048003 bytes, I can add more bytes to it with concat. It is
either the problem with concat returning a NULL or some limit with
longblob. I though longblob can be much bigger than 100K.

see the output below

 select length(paper) from PaperStorage where paperId=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+---------------+
| length(paper) |
+---------------+
|       1048003 |
+---------------+
1 row in set (0.01 sec)

mysql> update PaperStorage set paper=concat(paper, "longgfdasjfdkaskfj;lasjfakjfkdasjfdjfasklongldflkasjfkdlsalonjhkjlhkjjkjjjkjkjkjlkjlkjlkjlljljjljljl878907r83uoieuqwiruwqo87r98eur5ceifkejfdsjflkjaoirqeur473875438urtfejwirjewlkjrewiur843895u50987454utjrjfjdskjfvvmckmvjefloiuropiwrefjdliujfpoweutreort39875r49365820840325uirjfeljfdjfvfkjvlksirjfegfdsakjflksjfksjfksrfieuwiruewrikfjdslkaireyrutiyqweuiryqwoiurykhsfdhsfhasflkdjsafhsakjfhsakjfhdaskjfhasdljhfjahdfaskfhsafdjheuiwryqlwehfsdhlfkjhaslkfhaksjfhalskhfdlskjhfalskdjfhlsakjhfqauhyreiuwyrqfhdaskjhflakshfweqiyurwqhjflkdasjhfliweuqireuwrdfjhalkhfdiyufweqoiryuewkhflasdkhflafhdiqwoifefkhdaslkfjhskfhdqoioIIIEIWUREIQUIKEKJRFLKSIAHFIOAUFHKJASLKHRFEIWUKJFDAHLKQUHEIFWUYFKHALSKFHDIURYEIWUHFKHASSIUEFEHIWUFLKDHKSJHKJHFDSKJHFKEWIUYIREUYRHSKFHLKSYIEUYWKHFKSDHFKSWIEYRKHFKSHAIUYFDIAS8UYRFKWEHRFKEDHSFKDHIWUERHFSDLKUFHIWUERWKHFKSDJHFDDKIRWEUKRWHFKDSHFIKUWEREWH") where paperId=6; select length(paper) from PaperStorage where paperId=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+---------------+
| length(paper) |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

How to repeat:
see the output above

Suggested fix:
can you suggest a workaround since I really need this to work.

[1 Nov 2004 20:49] Min Xu

A more repeatable test case:

create table bug_test ( id int, bigdata longblob );

insert into bug_test values (1, "test");

update bug_test set bigdata=concat(bigdata, space(1000000)) where id=1;

select length(bigdata) from bug_test;

update bug_test set bigdata=concat(bigdata, space(100000)) where id=1;

select length(bigdata) from bug_test;

[1 Nov 2004 21:43] MySQL Verification Team

What is the value of max_allowed_packet variable?

[1 Nov 2004 21:49] Min Xu

It is more than 200K I think. I could insert a longblob that is bigger than 200K.
But not with concat function.

[1 Nov 2004 21:52] MySQL Verification Team

max_allowed_packet is 1M by default. Check if you exceed this limit.

[1 Nov 2004 22:03] Min Xu

Thanks, it is working. This really should have print a error msg instead of silently set the field to NULL.

Thanks again!