User defined Variables

--001636498f2be5cdf604af5e8603
Content-Type: text/plain; charset=ISO-8859-1

I was wondering if there is a way to increase the size of user defined
variables ? I am not able to attribute a type to them and when I assign @sql
= 'a_text _field' , 'a_text_field' gets truncated if it exceeds the size of
@sql. This is not wholesome as I am using @sql in a subsequent prepare
statement and my procedure errors out with a sql syntax error. Any advice
for this would help.

thanks.

--001636498f2be5cdf604af5e8603--

Re: User defined Variables

In the last episode (Oct 15), Kailash R said:
> I was wondering if there is a way to increase the size of user defined
> variables ? I am not able to attribute a type to them and when I assign
> @sql = 'a_text _field' , 'a_text_field' gets truncated if it exceeds the
> size of @sql. This is not wholesome as I am using @sql in a subsequent
> prepare statement and my procedure errors out with a sql syntax error.
> Any advice for this would help.

Do you have example code that fails? Mysql variables are probably limited
to your server's max_packet value. I am able to create variables containing
long strings with no problems, at least:

mysql> set @a=repeat('a',1024*1024*10);
Query OK, 0 rows affected (0.95 sec)

mysql> select length(@a);
+------------+
| length(@a) |
+------------+
| 10485760 |
+------------+
1 row in set (0.05 sec)

mysql>

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: User defined Variables

--0022158df37346a11104af6d85d0
Content-Type: text/plain; charset=ISO-8859-1

Nice input Dan. Let me run some checks. My query is as follows:

select group_concat(Field1), field2, field3 from table1 group by field2,
field3 into str;
@sql = concat("select blah ... where field1 in ' ,str);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

So when the count(*) for the group by fields list exceeds a threshold (I
don't know what that value is) then @sql gets truncated because the
group_concat returns a big string. But I just ran your set
@a=repeat('a',1024*1024*10); query series to the heidiSQL connection to the
same environment but I it doesn't error out with the same problem. I suspect
server variables or what not .. not sure what.

Thanks for the feedback Dan I will post with more information.

Kailash.






On Sat, Oct 15, 2011 at 9:38 PM, Dan Nelson wrote:

> In the last episode (Oct 15), Kailash R said:
> > I was wondering if there is a way to increase the size of user defined
> > variables ? I am not able to attribute a type to them and when I assign
> > @sql = 'a_text _field' , 'a_text_field' gets truncated if it exceeds the
> > size of @sql. This is not wholesome as I am using @sql in a subsequent
> > prepare statement and my procedure errors out with a sql syntax error.
> > Any advice for this would help.
>
> Do you have example code that fails? Mysql variables are probably limited
> to your server's max_packet value. I am able to create variables
> containing
> long strings with no problems, at least:
>
> mysql> set @a=repeat('a',1024*1024*10);
> Query OK, 0 rows affected (0.95 sec)
>
> mysql> select length(@a);
> +------------+
> | length(@a) |
> +------------+
> | 10485760 |
> +------------+
> 1 row in set (0.05 sec)
>
> mysql>
>
> --
> Dan Nelson
> dnelson@allantgroup.com
>

--0022158df37346a11104af6d85d0--

Re: User defined Variables

In the last episode (Oct 16), Kailash R said:
> Nice input Dan. Let me run some checks. My query is as follows:
>
> select group_concat(Field1), field2, field3 from table1 group by field2, field3 into str;
> @sql = concat("select blah ... where field1 in ' ,str);
> prepare stmt from @sql;
> execute stmt;
> deallocate prepare stmt;

Did you maybe get a warning on your first SELECT statement? On a test table
of dictionary words:

mysql> select group_concat(word) from words into @a;
Query OK, 1 row affected, 1 warning (0.70 sec)

mysql> show warnings;
+---------+------+-----------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 146 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> select length(@a);
+------------+
| length(@a) |
+------------+
| 1024 |
+------------+
1 row in set (0.01 sec)


From the documentation, GROUP_CONCAT has a default 1024-byte limit:

http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.ht ml#function_group-concat

The result is truncated to the maximum length that is given by the
group_concat_max_len system variable, which has a default value of 1024.
The value can be set higher, although the effective maximum length of
the return value is constrained by the value of max_allowed_packet. The
syntax to change the value of group_concat_max_len at runtime is as
follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: User defined Variables

--0016e659f092b719b004af84dda0
Content-Type: text/plain; charset=ISO-8859-1

Thank you Dan. Great find !! That was the answer. group_concat was
truncating my results and it caused my prepared sql to error out. I change
the server variable dynamically whenever the 1260 warning is issued to
accommodate larger and larger strings for the group by operation and it
works well.
Thank you for answering my question. Please consider my question resolved.

Kailash.

On Sun, Oct 16, 2011 at 10:06 PM, Dan Nelson wrote:

> In the last episode (Oct 16), Kailash R said:
> > Nice input Dan. Let me run some checks. My query is as follows:
> >
> > select group_concat(Field1), field2, field3 from table1 group by field2,
> field3 into str;
> > @sql = concat("select blah ... where field1 in ' ,str);
> > prepare stmt from @sql;
> > execute stmt;
> > deallocate prepare stmt;
>
> Did you maybe get a warning on your first SELECT statement? On a test
> table
> of dictionary words:
>
> mysql> select group_concat(word) from words into @a;
> Query OK, 1 row affected, 1 warning (0.70 sec)
>
> mysql> show warnings;
> +---------+------+-----------------------------------+
> | Level | Code | Message |
> +---------+------+-----------------------------------+
> | Warning | 1260 | Row 146 was cut by GROUP_CONCAT() |
> +---------+------+-----------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> select length(@a);
> +------------+
> | length(@a) |
> +------------+
> | 1024 |
> +------------+
> 1 row in set (0.01 sec)
>
>
> From the documentation, GROUP_CONCAT has a default 1024-byte limit:
>
>
> http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.ht ml#function_group-concat
>
> The result is truncated to the maximum length that is given by the
> group_concat_max_len system variable, which has a default value of 1024.
> The value can be set higher, although the effective maximum length of
> the return value is constrained by the value of max_allowed_packet. The
> syntax to change the value of group_concat_max_len at runtime is as
> follows, where val is an unsigned integer:
>
> SET [GLOBAL | SESSION] group_concat_max_len = val;
>
> --
> Dan Nelson
> dnelson@allantgroup.com
>

--0016e659f092b719b004af84dda0--