Changing group_concat_max_len

Hi Experts,

I have noticed in my Mysql query, where I use GROUP_CONCAT I do not get all expected records as per my syntax below. With little bit of research I noticed that this has limit of 1024 bytes, which can be increased.

I am wondering where and how can I increase this, and what is the maximum? Can the maximum be unlimited?

SELECT locs.id, name, default_time, default_vehicle, 

GROUP_CONCAT(DISTINCT assoc.agent_id ORDER BY agents.fName, agents.lName) AS agent_ids 

FROM locations AS locs 
INNER JOIN location_agent_assoc AS assoc ON locs.id = assoc.location_id 
INNER JOIN agents ON assoc.agent_id = agents.id 

WHERE locs.archived = 0 AND agents.archived = 0 
GROUP BY locs.id, name, default_time, default_vehicle 

ORDER BY locs.name

Open in new window


Any help will be greatly appreciated.
APD TorontoSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
you can issue set global or session group_concat_max_len= as part of your mysql query to make its effect immediate. global sets it for all sessions, session only increases it for the session where the set command is ran.
You can also add it into /etc/my.cnf file so it will be set when mysql starts next time.
0
APD TorontoSoftware DeveloperAuthor Commented:
I cannot find etc/my.cnf
0
arnoldCommented:
oh, missed that you are on windows, you need to find etc/my.ini by following where you installed the mysql server
<Drive_letter>:\program files (x86)\mysql server\
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

APD TorontoSoftware DeveloperAuthor Commented:
I changed it,. but it does not seem to help. How can I check if the change is in effect?
0
APD TorontoSoftware DeveloperAuthor Commented:
Also, do I need a semi-column after my statement:

group_concat_max_len = 18446744073709547520;
0
PortletPaulfreelancerCommented:
https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len

what is the maximum?
   32-bit platforms 4294967295
   64-bit platforms 18446744073709547520

Can the maximum be unlimited?
   no

do I need a semi-colon after my statement:
   yes
0
arnoldCommented:
yes, set global group_concat_max/-length=18446744073709547520;

are you sure your current one is low?
which my.ini did you use from the examples?
0
APD TorontoSoftware DeveloperAuthor Commented:
This is my my.ini

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
group_concat_max_len = 18446744073709547520;
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

Open in new window


it is located in C:\Program Files\MySQL\MySQL Server 5.6\my.ini. I found this by locating my-default.ini, and copied it to my.ini in the same directory and adding the group_concat_max_len = 18446744073709547520; line. However, my query still does not return all expected records with group_concat.

Is there a way I can check what is the maximum lenght applied to the group_concat?
0
APD TorontoSoftware DeveloperAuthor Commented:
UPDATE: even though my OS is x64, I checked my phpInfo() and the architecture is x86, so I changed the value to 4294967295, restarted my server (physically), but same result with truncated data.
0
APD TorontoSoftware DeveloperAuthor Commented:
I found it in phpMyadmin, I assume this change permanent?
0
APD TorontoSoftware DeveloperAuthor Commented:
When I changed the variable in phpMyAdmin, my query worked.

However, when I restart my server the variable is back to 1024, and my query is again truncated. How can I make it a permanent change?
0
arnoldCommented:
run the command set global you also need to update your my.ini to make it permanent everytime the system boots or the service is restarted.
You can add a directive to your query as well.

run
set global group_concat_max_len=3423543543;
this will last until the server is rebooted or the service is restarted.

If you can not alter the my.ini, add the set global directive before you run the query in question.
This way whenever the need to run this query comes up, the set global directive is run first.
0
APD TorontoSoftware DeveloperAuthor Commented:
If I add that before my query that would mean I need to give the user admin privileges, which I don't want to do.

There must be a way that I can set this permanently on Windows. What about a batch file that I can add somewhere in startup?

As well, every time I change my C:\Program Files\MySQL\MySQL Server 5.6\my.ini file I restart MySQL with my services in Control panel without doing a physical server restart, then refresh phpMyAdmin to see if the effect had taken place, which has not.  Is this a sufficient restart or do I need to physically restart?
0
arnoldCommented:
placement of the directive is important.
you can have the set session group_concat_max_length=18923423; precede the query you run.
it does not need admin rights.
group_concat_max_length=4543543345

you are placing the above within the
[mysqld]
group_concat_max_length=18890320

optional examples deals with setting an init-file.......
https://bugs.mysql.com/bug.php?id=74037
0
APD TorontoSoftware DeveloperAuthor Commented:
Ok, this is strange... I changed my query to

SET SESSION group_concat_max_len=3423543543; SELECT locs.id, name, default_time, default_vehicle, GROUP_CONCAT(DISTINCT assoc.agent_id ORDER BY agents.fName, agents.lName) AS agent_ids FROM locations AS locs INNER JOIN location_agent_assoc AS assoc ON locs.id = assoc.location_id INNER JOIN agents ON assoc.agent_id = agents.id WHERE locs.archived = 0 AND agents.archived = 0 GROUP BY locs.id, name, default_time, default_vehicle ORDER BY locs.name

Open in new window


...but I still get truncated results.

As well, under phpMyAdmin logged in as root, did the following code under the SQL window

SET SESSION group_concat_max_len=3423543543;

...Went under variables, group_concat_max_len still showing 1024. How can this be?

Finally, is it not possible using mysqld -uroot -pXXXX to set this? If so, as I am simply put that in a batch file and done?
0
PortletPaulfreelancerCommented:
borrowing a very simple example from here I just did this. Notice the change in result
    SELECT DISTINCT 
    parent_id, GROUP_CONCAT(DISTINCT child_id ORDER BY child_id) AS child_id_list
    FROM group_c
    group by parent_id
    ORDER BY parent_id;

    | parent_id | child_id_list |
    |-----------|---------------|
    |         1 |       1,2,3,4 |
    |         2 |         1,4,6 |
    |         3 |           1,2 |
    |         4 |             1 |
    |         5 |             0 |

Open in new window

    SET SESSION group_concat_max_len=6;
    
    SELECT DISTINCT 
    parent_id, GROUP_CONCAT(DISTINCT child_id ORDER BY child_id) AS child_id_list
    FROM group_c
    group by parent_id
    ORDER BY parent_id;

    | parent_id | child_id_list |
    |-----------|---------------|
    |         1 |        1,2,3, |
    |         2 |         1,4,6 |
    |         3 |           1,2 |
    |         4 |             1 |
    |         5 |             0 |

Open in new window


see: http://sqlfiddle.com/#!9/c4ecc/2

I suggest you try a similar small example to see if you can control that length.

sample
CREATE TABLE `group_c` (
`parent_id` int(11) DEFAULT NULL,
`child_id` int(11) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO group_c(parent_id, child_id)
VALUES (1,1),(1,2),(1,3),(2,1),(2,4),(1,4),(2,6),(3,1),(3,2),(4,1),(4,1),(1,1),(5,0);

Open in new window

0
APD TorontoSoftware DeveloperAuthor Commented:
Taking Paul's advice, I see that your example in SQL Fiddle works well.

I also set my max len to 5, just to visually see the difference. In doing so, I did notice 1 thing that I missed yesterday...

I'm now running:
SET SESSION group_concat_max_len=5; /*3423543543;*/

SELECT locs.id, name, default_time, default_vehicle, GROUP_CONCAT(DISTINCT assoc.agent_id ORDER BY agents.fName, agents.lName) AS agent_ids FROM locations AS locs INNER JOIN location_agent_assoc AS assoc ON locs.id = assoc.location_id INNER JOIN agents ON assoc.agent_id = agents.id WHERE locs.archived = 0 AND agents.archived = 0 GROUP BY locs.id, name, default_time, default_vehicle ORDER BY locs.name

Open in new window


in phpMyAdmin, I just see #Rows: 231, any my query is edited as follows:
SET SESSION group_concat_max_len=5;# MySQL returned an empty result set (i.e. zero rows). /*3423543543;*/ SELECT locs.id, name, default_time, default_vehicle, GROUP_CONCAT(DISTINCT assoc.agent_id ORDER BY agents.fName, agents.lName) AS agent_ids FROM locations AS locs INNER JOIN location_agent_assoc AS assoc ON locs.id = assoc.location_id INNER JOIN agents ON assoc.agent_id = agents.id WHERE locs.archived = 0 AND agents.archived = 0 GROUP BY locs.id, name, default_time, default_vehicle ORDER BY locs.name # Rows: 231 

Open in new window


However, I do not also see the 231 Rows, but if I remove the SET statement, I do see my 231 rows, but not with max len of 5.  This is also happening with my PHP code, where with SET, I get no rows returned.

So my question now is, why with the SET statement, I don't see my rows.
0
arnoldCommented:
you seem to have an answer to your question.  You should open a separate question for the followup.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoSoftware DeveloperAuthor Commented:
OK.
0
APD TorontoSoftware DeveloperAuthor Commented:
Thank you both!
0
arnoldCommented:
Your choice for my.ini is too limited, other than what you added everything else is a comment.  There should be a my-*.ini

Within phpmyadmin, there is a suggested changes on DB/index,etc.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.