Re: Bug in CONCAT function blocks input of strings longer than 1MB

Dmitry,

Dmitry.Kuznetsov@licr.org wrote:
>>Description:

Thank you for your bug report.

I was able to reproduce this BUG with much simpler test:

mysql> select isnull(concat(repeat('A', 1047552))), isnull(concat(repeat('A',
1047553)));
+--------------------------------------+-------------------- ------------------+
| isnull(concat(repeat('A', 1047552))) | isnull(concat(repeat('A', 1047553))) |
+--------------------------------------+-------------------- ------------------+
| 0 | 1 |
+--------------------------------------+-------------------- ------------------+


For strings Longer than 1047552 bytes CONCAT() returns NULL

Entered as: http://bugs.mysql.com/bug.php?id=119

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13894@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in CONCAT function blocks input of strings longer than 1MB

Dmitry,

Alexander Keremidarski wrote:
> Dmitry,


> For strings Longer than 1047552 bytes CONCAT() returns NULL

I should check things more thoroughly :)

The problem happens when string exceeds value of max_allowed_packet, by default it
is 1M

Set it to higher value and problem will disappear:

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16776192 |
+--------------------+----------+

mysql> select isnull(concat(repeat('A', 16776192))), isnull(concat(repeat('A',
16776193)));
+---------------------------------------+------------------- --------------------+
| isnull(concat(repeat('A', 16776192))) | isnull(concat(repeat('A', 16776193)))
|
+---------------------------------------+------------------- --------------------+
| 0 | 1
|
+---------------------------------------+------------------- --------------------



Below is result of your script with this setting:

./concat_bug.pl bad.seq
1144924 bytes uploaded, 1144924 are in DB


Case closed


--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13895@lists.mysql.com
To unsubscribe, e-mail

RE: Bug in CONCAT function blocks input of strings longer than 1 MB

Dmitry Kuznetsov writes:
> Alexander,
>
> Thanks a lot for your quick and comprehensive advice. Indeed, it helps.
>
> I would suggest to make CONCAT being dependent on a more obvious variable then 'max_allowed_packet', or, at least, describe this
> relationship in the manual... I think many academic people, nowadays working with genomic DNA sequences, might experience same
> problem. I would also suggest to expand LONGTEXT capability to 4Gb as soon as possible, this is a must to handle today's long
> genomic data. With such a capability, MySQL would be a real possibility against Oracle, for both academic and industrial
> researchers.
>
> Cheers,
> Dmitry.
>

I agree.

We should document it better, although not in CONCAT() function, but
in REPEAT() function, because that is where a check is made.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13899@lists.mysql.com
To unsubscribe, e-mail

RE: Bug in CONCAT function blocks input of strings longer than 1MB

At 20:56 +0200 3/4/03, Sinisa Milivojevic wrote:
>Dmitry Kuznetsov writes:
>> Alexander,
>>
>> Thanks a lot for your quick and comprehensive advice. Indeed, it helps.
>>
>> I would suggest to make CONCAT being dependent on a more obvious
>>variable then 'max_allowed_packet', or, at least, describe this
>> relationship in the manual... I think many academic people,
>>nowadays working with genomic DNA sequences, might experience same
>> problem. I would also suggest to expand LONGTEXT capability to 4Gb
>>as soon as possible, this is a must to handle today's long
>> genomic data. With such a capability, MySQL would be a real
>>possibility against Oracle, for both academic and industrial
>> researchers.
>>
>> Cheers,
>> Dmitry.
>>
>
>I agree.
>
>We should document it better, although not in CONCAT() function, but
>in REPEAT() function, because that is where a check is made.

Putting it in the REPEAT() section wouldn't explain anything to someone
who's using CONCAT().

The manual *already* documents this, and does so in the most logical
place: Right at the beginning of the section on string functions:


@node String functions, Numeric Functions, Non-typed Operators, Functions
@subsection String Functions

@findex string functions
@findex functions, string

String-valued functions return @code{NULL} if the length of the result would
be greater than the @code{max_allowed_packet} server parameter. @xref{Server
parameters}.


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13901@lists.mysql.com
To unsubscribe, e-mail

RE: Bug in CONCAT function blocks input of strings longer than 1 MB

Alexander,

Thanks a lot for your quick and comprehensive advice. Indeed, it helps.

I would suggest to make CONCAT being dependent on a more obvious variable then 'max_allowed_packet', or, at least, describe this
relationship in the manual... I think many academic people, nowadays working with genomic DNA sequences, might experience same
problem. I would also suggest to expand LONGTEXT capability to 4Gb as soon as possible, this is a must to handle today's long
genomic data. With such a capability, MySQL would be a real possibility against Oracle, for both academic and industrial
researchers.

Cheers,
Dmitry.


> -----Original Message-----
> From: Alexander Keremidarski [mailto:salle@mysql.com]
> Sent: Tuesday, March 04, 2003 6:36 PM
> To: Dmitry.Kuznetsov@licr.org
> Cc: bugs@lists.mysql.com
> Subject: Re: Bug in CONCAT function blocks input of strings
> longer than 1 MB
>
>
> Dmitry,
>
> Alexander Keremidarski wrote:
> > Dmitry,
>
>
> > For strings Longer than 1047552 bytes CONCAT() returns NULL
>
> I should check things more thoroughly :)
>
> The problem happens when string exceeds value of
> max_allowed_packet, by default it
> is 1M
>
> Set it to higher value and problem will disappear:
>
> mysql> show variables like 'max_allowed_packet';
> +--------------------+----------+
> | Variable_name | Value |
> +--------------------+----------+
> | max_allowed_packet | 16776192 |
> +--------------------+----------+
>
> mysql> select isnull(concat(repeat('A', 16776192))),
> mysql> isnull(concat(repeat('A',
> 16776193)));
> +---------------------------------------+------------------- --
------------------+
> | isnull(concat(repeat('A', 16776192))) | isnull(concat(repeat('A',
> | 16776193)))
> |
> +---------------------------------------+------------------- --
------------------+
> | 0 |
> 1
> |
> +---------------------------------------+------------------- --
----------
> +---------------------------------------+--------
>
>
>
> Below is result of your script with this setting:
>
> ./concat_bug.pl bad.seq
> 1144924 bytes uploaded, 1144924 are in DB
>
>
> Case closed
>
>
> --
> MySQL Conference and Expo 2003
> http://www.mysql.com/events/uc2003/index.html
> For technical
> support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
>
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com
>
>
>
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13898@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in CONCAT function blocks input of strings longer than 1MB

Dmitry,

Dmitry Kuznetsov wrote:
> Alexander,
>
> Thanks a lot for your quick and comprehensive advice. Indeed, it helps.
>
> I would suggest to make CONCAT being dependent on a more obvious variable then 'max_allowed_packet', or, at least, describe this
> relationship in the manual...

This is described in manual.

http://www.mysql.com/doc/en/String_functions.html

6.3.2 String Functions

String-valued functions return NULL if the length of the result would be greater
than the max_allowed_packet server parameter.



> I would also suggest to expand LONGTEXT capability to 4Gb as soon as possible,

Do you mean Up to 4Gb or Above 4Gb?

LONGTEXT can hold up to 4Gb - 1byte long bytes.


http://www.mysql.com/doc/en/Column_types.html

6.2 Column Types

.....

LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters.


> Cheers,
> Dmitry.

Nazdrave

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13914@lists.mysql.com
To unsubscribe, e-mail

RE: Bug in CONCAT function blocks input of strings longer than 1 MB

Alexander,

> > I would suggest to make CONCAT being dependent on a more obvious=20
> > variable then 'max_allowed_packet', or, at least, describe this=20
> > relationship in the manual...
>=20
> This is described in manual.

Excellent! Thanks!
I could suggest to describe a reason why it cannot be pushed higher =
64Mb, although in my.conf I tried to set it to 96Mb. Probably, a
constrain compiled in?


>> I would also suggest to expand LONGTEXT capability to 4Gb as soon as=20
>> possible,

>Do you mean Up to 4Gb or Above 4Gb?

Yes, UP to 4Gb, according to it's capacity (2^32 - 1)



>LONGTEXT can hold up to 4Gb - 1byte long bytes.

Currently, it cannot. Effectively, now it is downgraded to MEDIUMTEXT, =
(2^24 - 1), 16Mb.=20
Exactly in the reference you mentioned =
http://www.mysql.com/doc/en/Column_types.html , see:
"
.....
Note that because the server/client protocol and MyISAM tables has =
currently a limit of 16M per communication packet / table row,
you can't yet use this the whole range of this type.
....
"


I pushed 'max_allowed_packet' up to 64Mb, but still could not go =
through, as I got error 139 when I tried to enter stings longer 16
Mb:
....
DBD::mysql::st execute failed: Got error 139 from table handler=20
.....

Because of this, I had to declare this table as InnoDB type, and after =
all went OK.



>Nazdrave
:)) Thanks and all the best!

Dmitry.


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13918@lists.mysql.com
To unsubscribe, e-mail

RE: Bug in CONCAT function blocks input of strings longer than 1 MB

Dmitry Kuznetsov writes:
> Alexander,
>
> > > I would suggest to make CONCAT being dependent on a more obvious
> > > variable then 'max_allowed_packet', or, at least, describe this
> > > relationship in the manual...
> >
> > This is described in manual.
>
> Excellent! Thanks!
> I could suggest to describe a reason why it cannot be pushed higher 64Mb, although in my.conf I tried to set it to 96Mb. Probably, a
> constrain compiled in?
>
>
> >Nazdrave
> :)) Thanks and all the best!
>
> Dmitry.
>

Hi!

First make sure with:

mysqladmin var

That you have set value correctly.

With 3.23 you can not go higher, but with latest 4.0 you can go quite
high.

Do keep in mind that (if both client and server are on the same
computer), for 64Mb query MySQL will take approximately 400 Mb to
process it.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13922@lists.mysql.com
To unsubscribe, e-mail