Description:
Nested string functions that could run completely server-side and output a small result string, return an incorrect truncated/max_allowed_packet exceeded warning and a NULL result.
"String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable."
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html
However, for nested functions where the combined result is smaller than the value of max_allowed_packet, the warning and NULL result are incorrect. In this case, both the SQL statement that is passed to the server and the output that is returned to the client are small.
"A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave."
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
How to repeat:
mysql>select md5(repeat('dsfkjdskvjfdivojfdoivjfd',504));
+---------------------------------------------+
| md5(repeat('dsfkjdskvjfdivojfdoivjfd',504)) |
+---------------------------------------------+
| 609cd46c6e874757d3102f57b3031112 |
+---------------------------------------------+
1 row in set (0.02 sec)
mysql>select md5(repeat('dsfkjdskvjfdivojfdoivjfd',504398959438544380));
+------------------------------------------------------------+
| md5(repeat('dsfkjdskvjfdivojfdoivjfd',504398959438544380)) |
+------------------------------------------------------------+
| NULL | <<<< NULL output
+------------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)
mysql>show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1301
Message: Result of repeat() was larger than max_allowed_packet (1048576) - truncated
1 row in set (0.00 sec)
mysql>select left(repeat('dsfkjdskvjfdivojfdoivjfd',504),5);
+------------------------------------------------+
| left(repeat('dsfkjdskvjfdivojfdoivjfd',504),5) |
+------------------------------------------------+
| dsfkj |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>select left(repeat('dsfkjdskvjfdivojfdoivjfd',509838942834),5);
+---------------------------------------------------------+
| left(repeat('dsfkjdskvjfdivojfdoivjfd',509838942834),5) |
+---------------------------------------------------------+
| NULL | <<<< NULL output
+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1301
Message: Result of repeat() was larger than max_allowed_packet (1048576) - truncated
1 row in set (0.00 sec)
Suggested fix:
If the SQL statement passed to the server is < max_allowed_packet and if the output of the server to the client is < max_allowed_packet, then a correct result should be returned and no warning should be issued.
