• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to increase group_concat_max_len....?

 
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
SET global.group_concat_max_len=4096;

or

SET session.group_concat_max_len=4096;
 
prabhu pandurangan
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@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
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm glad you both found the answer and posted it... sometimes the resolution is more subtle than expected.
 
Think of how stupid the average person is. And how half of them are stupider than that. But who reads this tiny ad?
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic