Results 1 to 9 of 9

Thread: MySQL problem configuring group_concat_max_len in my.cnf

  1. #1

    Default 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
    Code:
    [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
    Code:
    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
    Code:
    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
    Code:
    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

  2. #2
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,403
    Blog Entries
    2

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

    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/...n_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...concat_max_len

    HTH,
    TSU

  3. #3

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

    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?

  4. #4

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

    Problem solved after updating MySQL to latest version 5.6.25 - 2.6.2 via YaST.
    I start MySQL the usual way with
    Code:
    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
    Code:
    sudo systemctl start mysql
    group_concat_max_len value wouldn't change.
    But when I started it with
    Code:
    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.

  5. #5
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,403
    Blog Entries
    2

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

    Quote Originally Posted by suSUSEgar View Post
    Problem solved after updating MySQL to latest version 5.6.25 - 2.6.2 via YaST.
    I start MySQL the usual way with
    Code:
    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
    Code:
    sudo systemctl start mysql
    group_concat_max_len value wouldn't change.
    But when I started it with
    Code:
    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

  6. #6
    Join Date
    Oct 2011
    Location
    Germany (Ore Mountains)
    Posts
    428

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

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

    Code:
    /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:
    Code:
    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/mysqld (mysqld 10.0.20-MariaDB) starting as process 30
    610 ...
    


    Hendrik

  7. #7
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,403
    Blog Entries
    2

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

    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
    Code:
    /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
    Code:
    /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

  8. #8

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

    Quote Originally Posted by tsu2 View Post

    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've filed a bug at https://bugzilla.opensuse.org/show_bug.cgi?id=940143 referring to this thread.

  9. #9

    Default Re: MySQL problem configuring group_concat_max_len in my.cnf

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •