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