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 <dnel...@allantgroup.com> 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
> dnel...@allantgroup.com
>
