MySQL Bugs: #29757: STRING FUNCTIONS WITH LIMITS

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

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

i was checking and only variable that have this values is MAX_ALLOWED_PACKET
is it limiting my max length?

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

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)

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

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

any work log? any bug closed? it's working with newers versions?

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

Bug#42680 has been made a duplicate of this one. Please review as needed.

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