MySQL problem configuring group_concat_max_len in my.cnf

I can’t seem to change MySQL group_concat_max_len variable no matter what.

After searching my whole harddrive, i found 2 configuration files: /etc/my.cnf and /usr/my.cnf

In both files I already put this


[mysqld]
max_allowed_packet=100M
group_concat_max_len=2048

I even created new file /root/.my.cnf and put those lines.
Then I restarted the service using

sudo systemctl restart mysql

Running query SHOW VARIABLES from a database admin tool still showing that group_concat_max_len = 4, which is the default. But strangely,
max_allowed_packet = 104857600, which is the 100M value that I put in my.cnf.

If I run

sudo mysqld --verbose --help

from terminal, it shows that group_concat_max_len is 2048 and max_allowed_packet is 104857600, which is what I want.

If I run

sudo mysqld --print-defaults

it says
mysqld would have been started with the following arguments:
–innodb_file_format=Barracuda --innodb_file_per_table=ON --server-id=1 --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES --max_allowed_packet=100M --group_concat_max_len=2048

So why in the world if I start or restart mysql, group_concat_max_len keeps turning back to 4, while other variable can be modified without problem ? How do I change group_concat_max_len value ?
I prefer a permanent change rather than occasionaly running SET SESSION or SET GLOBAL

I’m running OpenSUSE 13.2 i586 with kernel 3.16.7-21-desktop and MySQL 5.6.25 installed from OpenSUSE repository. In YaST install/remove software, the installed packages are:
mysql-community-server 5.6.25-2.3.1
mysql-community-server-client 5.6.25-2.3.1
mysql-community-server-errormessages 5.6.25-2.3.1

Really appreciate any help

Purely speculating, but based partly because you didn’t say you looked up the current value in your system,
From the MySQL online documentation
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

Possibly of additional interest, but not a problem in this case, the min/max parameters for this setting for both 32-bit and 64-bit
https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len

HTH,
TSU

Same Problem here on OpenSuse 13.1 (64bit) using MySQL 5.6.12, for Linux (x86_64) while it seems to work on MySQL 5.6.25, for Linux (x86_64) on another machine.

Today I realized, that the startup methods on the two machines differed.
On the first machine mysqld is a subprocess of
“/bin/bash /usr/lib/mysql/mysql-systemd-helper start default”,
while on the second machine it is a subprocess of
“/bin/sh /usr/bin/mysqld_safe --mysqld=mysqld --user=mysql --pid-file=/var/run/mysql/mysqld.pid --socket=/var/run/mysql/mysql.sock --datadir=/var/lib/mysql”.

Further on the first machine “–group_concat_max_len=32768” and other options from my.cnf are passed as command line arguments to mysqld.
While on the second machine only some very basic options (–basedir, --datadir, --plugin-dir, --user, --socket, --pid-file and --log-error) are passed. So better don’t trust in what --print-defaults says.

Can you please post your process structure for the mysqld startup?

Problem solved after updating MySQL to latest version 5.6.25 - 2.6.2 via YaST.
I start MySQL the usual way with

sudo systemctl start mysql

via terminal and my settings in /etc/my.cnf work, including group_concat_max_len.

@Chris78g
Strange indeed.
When I started MySQL with

sudo systemctl start mysql

group_concat_max_len value wouldn’t change.
But when I started it with

sudo mysqld_safe --user=mysql &

it worked, the value was as exactly as I set in /etc/my.cnf. I used to start MySQL this way untill I update it.

You can probably modify your mysql.service Unit file to pass the command that works so that both work the same way.

Would be nice if you can also submit a bug at https://bugzilla.opensuse.org describing what you found. Someone will take a look at that (my guess upstream so would affect many distros not just openSUSE) and if you’ve found something that needs to be fixed, they’ll take care of it.

TSU

I think, (one of) the problem(s) is in the mysql-systemd-helper script:

/usr/sbin/mysqld --user=mysql --group=mysql ...

There is no option “group” and I guess mysqld expands this to the only option starting with “group” → “group_concat_max_len”.
Then the value “mysql” gets converted - fault tolerant - to “0” and this shows in the logfiles:

Jul 30 21:28:58 linux-yb52.local **mysql**-systemd-helper[30597]: Waiting for MySQL to start 
Jul 30 21:28:58 linux-yb52.local **mysql**-systemd-helper[30596]: 150730 21:28:58 [Warning] option 'group_concat_max_len': unsigned value 0 adjusted to 4 
Jul 30 21:28:59 linux-yb52.local **mysql**-systemd-helper[30596]: 150730 21:28:58 [Note] /usr/sbin/**mysql**d (**mysql**d 10.0.20-MariaDB) starting as process 30
610 ...


Hendrik

I forgot to mention in my previous post…

If you decide you’d like to try modifying a systemd Unit file, if you haven’t done this before there is a “proper” way to do this…

By default, all systemd default Unit files exist in the “system” or “user” subdirectories of

/usr/lib/systemd

Never modify these original files.

But, you can copy any existing Unit file (or create a new Unit file) in the corresponding subdirectory of

/etc/systemd

If a Unit file in this second tree has the same name of an original Unit file, then the file in /etc/systemd will over-ride the original file.

This makes it easy to undo any modifications you make by simply deleting the file you created and updates should not over-write your creations.

TSU

I’ve filed a bug at 940143 – depending on the startup method (systemctl or mysqld_safe) mysqld is started with or w/o --group_concat_max_len option referring to this thread.

As far as I can see, the problem seems to have vanished on our machines.

Referring to
/var/log/zypp/history
the problem appeared with
2015-07-10 01:32:42|install|mysql-community-server|5.6.25-7.4.1
and disappeared with
2015-07-27 01:23:45|install|mysql-community-server|5.6.25-7.7.2
here.