Bug #29757 STRING FUNCTIONS WITH LIMITS
Submitted: 12 Jul 2007 13:56 Modified: 1 Nov 2011 12:14
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[12 Jul 2007 13:56] Roberto Spadim
Description:
hello i was working with BLOB columns AND string functions
i got a problem with too big strings, see:

select 
length(repeat('a',2096128)),
length(substring(  repeat('a',2096128) ,1,2096128)  )

if i set repeat to 2096128+1 i get a 0 length string
i think that's a limit on string functions, but i didn't found any documentation about it in mysql docs

(in my application repeat('a',2096128) is a blob field with a coded string in any format (today HEXADECIMAL 8 bytes integers) )

windows XP 32bit get the same error but with max repeat size of 1048576

How to repeat:
select 
length(repeat('a',2096128)),
length(substring(  repeat('a',2096128) ,1,2096128)  )

works!

select 
length(repeat('a',2096129)),
length(substring(  repeat('a',2096128) ,1,2096129)  )

don't work :(

i'm using atlhon 64 3000+ (not dualcore) on linux 64bits, 

windows XP 32bit get the same error but with max repeat size of 1048576

Suggested fix:
maybe document this limits on some where in docs
i think that's a limit in libc i didn't check mysql source code to know more about it, but it's somethink like it, since 1048576 is:
10000 00000000 00000000 in binary and 2096128 is:
11111 11111100 00000000 in binary 

i don't know if it's a libc limit, i think that's
[12 Jul 2007 14:01] Roberto Spadim
i was checking and only variable that have this values is MAX_ALLOWED_PACKET
is it limiting my max length?
[12 Jul 2007 14:04] Roberto Spadim
on 64 bits machine:

set MAX_ALLOWED_PACKET=4096128;
select 
length(repeat('a',4096001))

this return 0 length
it's very strange since if i change
MAX_ALLOWED_PACKET=4096128; to MAX_ALLOWED_PACKET=4096129;
the same error continue, only with 4097024 the function return ok
[12 Jul 2007 14:14] Godofredo Miguel Solorzano
Thank you for the bug report. What is the output you got? I got the below
with --max_allowed_packet=16M on Windows 64-bit.

c:\dev\5.1>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.21-beta-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select
    -> length(repeat('a',2096128)),
    -> length(substring(  repeat('a',2096128) ,1,2096128)  );
+-----------------------------+-------------------------------------------------------+
| length(repeat('a',2096128)) | length(substring(  repeat('a',2096128) ,1,2096128)  ) |
+-----------------------------+-------------------------------------------------------+
|                     2096128 |                                               2096128 |
+-----------------------------+-------------------------------------------------------+
1 row in set (0.06 sec)

mysql> select
    -> length(repeat('a',2096129)),
    -> length(substring(  repeat('a',2096128) ,1,2096129)  );
+-----------------------------+-------------------------------------------------------+
| length(repeat('a',2096129)) | length(substring(  repeat('a',2096128) ,1,2096129)  ) |
+-----------------------------+-------------------------------------------------------+
|                     2096129 |                                               2096128 |
+-----------------------------+-------------------------------------------------------+
1 row in set (0.06 sec)
[12 Jul 2007 18:06] Roberto Spadim
i found the problem, max_allowed_packet set the max length for strings, i think that's wrong, since
i'm returning LENGTH( very big string ), and the length() result just have about 10 bytes (if it's a string, maybe less if you send it as integer of 4 bytes)

manual should show this limit of string functions

you windows-64bits works ok on 32-bits  too, the problem is the variable that's small to return a length() function of 10 bytes
[13 Jul 2007 6:35] Sveta Smirnova
Thank you for the report.

Verified as described:

=====mysql-5.1=====
=====bug29757=====
select 
length(repeat('a',2096129)),
length(substring(  repeat('a',2096128) ,1,2096129)  );
length(repeat('a',2096129))     length(substring(  repeat('a',2096128) ,1,2096129)  )
NULL    NULL
Warnings:
Warning 1301    Result of repeat() was larger than max_allowed_packet (1048576) - truncated
Warning 1301    Result of repeat() was larger than max_allowed_packet (1048576) - truncated
[21 Mar 2011 4:10] Roberto Spadim
any work log? any bug closed? it's working with newers versions?
[5 Apr 2011 6:06] Roberto Spadim
don't work with mysql 5.5
maybe internal functions (not mysql result) could be executed with more memory limit, and mysql result should execute with max_allowed_packet
[1 Nov 2011 4:48] Roel Van de Paar
Bug#42680 has been made a duplicate of this one. Please review as needed.
[1 Nov 2011 12:14] Roberto Spadim
nice, could we add another variable ?
for example string could make work internally with > max_allowed_packet and return a small value to user?
i don[t know how mysql internally do this, but could this work? or add some info at documents about string functions limits?

the duplicate is nice for me, i think that's the same problem