MySQL Bugs: #34782: CONCAT() fails when longblobs length's are bigger than max_allowed

Bug #34782	CONCAT() fails when longblobs length's are bigger than max_allowed_packet
Submitted:	24 Feb 2008 9:46	Modified:	27 Feb 2008 17:20
Reporter:	Hans Kejser Hansen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.32-Debian_7etch1-log	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any
Tags:	concat max_allowed_packet

Description:
I have a php script which update records in a longblob column, using CONCAT().

When the length of the data in the longblob column + string-to-be-concatted are bigger than max_allowed_packet, CONCAT() seems to retuns '' instead of the hole string.

Normally when when my sql statements are bigger than max_allowed_packet i got an error message, but not here.

How to repeat:
/* Example script in PHP */
/* max_allowed_packet = 16776192 */

mysql_query("TRUNCATE TABLE `hkh`");

/* Build up string to be concatted */
$bytes = '';
for ($i=0;$i<1000000;++$i) { $bytes .= 'X'; }

/* Insert 20 records */
for ($i=0; $i<20; ++$i)
{
   mysql_query("INSERT INTO `hkh` SET `data` = ''");
   $id = mysql_insert_id();

   /* Make each record have an different length */
   for ($j=0; $j<=$i; ++$j)
   {
      mysql_query("UPDATE `hkh` SET `data` = CONCAT(`data`, '".$bytes."') WHERE `id` = ".$id);
   }
}

mysql_query("SELECT id, LENGTH(`data`) FROM `hkh` ORDER BY `id`");
/* Outputs */
1 - 1000000
2 - 2000000
3 - 3000000
4 - 4000000
5 - 5000000
6 - 6000000
7 - 7000000
8 - 8000000
9 - 9000000
10 - 10000000
11 - 11000000
12 - 12000000
13 - 13000000
14 - 14000000
15 - 15000000
16 - 16000000     /* Goes wrong after here */
17 - 0
18 - 1000000
19 - 2000000
20 - 3000000

When running the same script on xp with MySQL - 5.0.21-community-nt and same max_alloed_packet value i got this result.

id - LENGTH(`
1 - 1000000  
2 - 2000000  
3 - 3000000  
4 - 4000000  
5 - 5000000  
6 - 6000000  
7 - 7000000  
8 - 8000000  
9 - 9000000  
10 - 10000000
11 - 11000000
12 - 12000000
13 - 13000000
14 - 14000000
15 - 15000000
16 - 16000000
17 - 16000000
18 - 16000000
19 - 16000000
20 - 16000000

Forgot to mention what the expected result should be. The string should be concatted or mysql should returns an error.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a at least, and inform about the results.

Upgraded my xp to 5.0.51a-community-nt, can't upgrade the debian server, it's on a web hotel.

When running the script on xp, i get the same result as before.

With mysql command line client I get a warning " Result of concat() was larger than max_allowed_packet (1048576) - truncated" which is correct.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please use function mysqli_get_warnings available in MySQL Improved Extension and "SHOW WARNINGS" query:

for ($j=0; $j<=$i; ++$j)
   {
      mysqli_query($link, "UPDATE `bug34782` SET `data` = CONCAT(`data`, '".$bytes."') WHERE `id` =last_insert_id()");
	  $class = mysqli_get_warnings($link);
	  if ($class && $class instanceof mysqli_warning) {
		  mysqli_query($link, "SHOW WARNINGS");
                  ........
	  }
   }

Verifying this report I discovered bug #34898 in C API.