aggregate functions - MYSQL CONCAT MAX LENGTH

Question

Following this post: POST ABOUT CONCAT My problem is that i have many rows CONCAT into one row. For example if i have 10 rows with string around 50 chars, my query will show me only 6-7 of that rows or something like that. I searech in stack and google and i found that i can change CONCAT max length by command: SET group_concat_max_len := @@max_allowed_packet. What i am doing wrong?

EDIT: When i SHOW VARIABLES LIKE 'group_concat_max_len' it's shows me 1024. Mysql version 5.0.96-log. Tables type: MyISAM. Looks like it dont have any limits, i try to select simple varchar with 2000 chars, and it looks fine. I have 3 tables: 1st - Item with ItemID, 2nd - Descriptionpack with ItemID and DescriptionID, 3rd Description with DescriptionID.

Select
DISTINCT Item.ItemID as item
,GROUP_CONCAT(Description.DescriptionID) AS description
From Item 
LEFT OUTER JOIN descriptionpack
on Item.ItemID=descriptionpack.ItemID
LEFT OUTER JOIN description
on descriptionpack.descriptionID=description.descriptionID
GROUP BY item

EDIT2: I think i found the problem, i said my problem to my provider and they answer me this:

I reviewed your question with our hosting team. You wouldn't be able to change the global settings for that and other variables. However, you should be able to set that variable on a per session basis by setting it first, before other queries. Hope that helps.

So now the problem is, how to do that.

Answer 1

Presumably you're using GROUP_CONCAT(), not simple CONCAT().

The default value of the group_concat_max_len is 1024, which is a pretty small limit if you're building up big long concatenations.

To change it, use this command. I've set the length in this example to 100,000. You could set it to anything you need.

 SET SESSION group_concat_max_len = 100000;

The usual value for max_allowed_packet is one megabyte, which is likely more than you need.

group_concat_max_len itself has an effectively unlimited size. It's limited only by the unsigned word length of the platform: 2^32-1 on a 32-bit platform and 2^64-1 on a 64-bit platform.

If that still isn't enough for your application, it's time to take @eggyal's suggestion and rethink your approach.