aspose file tools*
The moose likes JDBC and the fly likes How to increase group_concat_max_len....? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to increase group_concat_max_len....?" Watch "How to increase group_concat_max_len....?" New topic
Author

How to increase group_concat_max_len....?

prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
Dear Members,

I tried to increase my MYSQL group_concat_max_len in linux machine, but for the same i customized the 'my.cnf' file in windows and increased the max_allowed_packet size and group_concat_max_len size it works fine.

But for the same i tried to do in linux machine, but the changes i made is not working, even after increasing the size it shows the default size of 1024.

Could you please locate me which file will mysql look for in linux machine for the server settings.

for group_concat_max_len and max_allowed_packet size...

Regards,
Prabhu.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
SET global.group_concat_max_len=4096;

or

SET session.group_concat_max_len=4096;
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
Hi Paul,

Thank you very much for sharing your ideas.

Will this be a permanent solution. Because while i tried using 'set global group_concat_max_len=1M' through command line, it is changed but once i restart mysql and check the size using 'SHOW VARIABLES LIKE 'group%'; it again shows the default value as '1024'.

Thats why im asking paul. Hope you would be clear. So i manually added these lines in my "/etc/my.cnf" file:

# Set max allowed packet size to 2 MB
max_allowed_packet=2M

# Set group concat max length to 1 MB
group_concat_max_len=1M


Regards,
Prabhu.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by prabhu rangan:
Hi Paul,

Thank you very much for sharing your ideas.

Will this be a permanent solution. Because while i tried using 'set global group_concat_max_len=1M' through command line, it is changed but once i restart mysql and check the size using 'SHOW VARIABLES LIKE 'group%'; it again shows the default value as '1024'.

Thats why im asking paul. Hope you would be clear. So i manually added these lines in my "/etc/my.cnf" file:

# Set max allowed packet size to 2 MB
max_allowed_packet=2M

# Set group concat max length to 1 MB
group_concat_max_len=1M


Regards,
Prabhu.


I believe the set command is only effective until you restart your database... my.cnf controls it when you restart.

Keep in mind when setting the value higher that the effective maximum length of the return value will be constrained by the value of the max_allowed_packet variable.
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
Hi Paul,

As you said if i do by 'SET' command it will be until restarting my mysql. So once if i restart my mysql; this settings will be set again to default. But i need a permanent solution to set my 'group_concat_max_len' and 'max_packet_length'.

I believe you that 'group_concat' depends on 'max_packet' thats why im setting max_packet to 2MB and group_concat to 1MB.

This works fine in WINDOWS by adding the belowlines to my 'my.cnf' file:
# Set max allowed packet size to 2 MB
max_allowed_packet=2M

# Set group concat max length to 1 MB
group_concat_max_len=1M

My issue is If i do the same in LINUX machine the changes is not taking places. I think LINUX machine is not looking for /etc/my.cnf' file for the configuration settings. I came to know that the LINUX machine depends on RAM size for settings also i have a 2GB RAM so which file will be looked for mysql configuration settings.

Please help me in this to resolve this issue.

Hope you could understand.

Regards,
PRabhu
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Prabhu,

I believe I understand the issue... maybe not the solution... it sounds very much like mysql is using a different my.cnf file... but it also sounds like you've done a lot of work to be sure that is not the problem.

I will see what I can find out and if I have time this weekend will partition a drive and install mysql and see if I can duplicate the issue... since the forecast is for 6-10 inches of snow for tomorrow... I may have plenty of time on my hands.

Paul
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
I should of asked what distro and which version of MySQL... with the latest version of Suse and MySQL 5.1, I wasn't able to duplicate this problem... at least not yet.
prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 132
@Camphell and All,

I solved this issue. In windows if we set the group_concat_length = 1M it assumes the byte value for it that is around 1048567 bytes. But in LINUX it does not support '1M', instead we should specify the exact 'number in bytes'. LINUX also reads the configuration from the /etc/my.ini/ file.

Thank you very much for your guidance.

Meet you all soon in further post.

Regards,
Prabhu.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
I'm glad you both found the answer and posted it... sometimes the resolution is more subtle than expected.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to increase group_concat_max_len....?