group_concat_max_length system variable not changed even after changing my.cnf file

Question

I have currently installed MySQL version 5.6 installed on my local system. I want to permanently set the group_concat_max_length system variable to some value. When I do it using set global group_concat_max_len = 100000; command in the mysql terminal, the value is set but the value changes to default on restart. I have also changed my.cnf file and put it under [mysqld] header.

Can anyone suggest what might be the problem?

Edit: my.cnf

    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html

    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    [client]
    port        = 3306
    socket        = /var/run/mysqld/mysqld.sock

    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram

    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket        = /var/run/mysqld/mysqld.sock
    nice        = 0

    [mysqld]
    #
    # * Basic Settings
    #
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket        = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir        = /usr
    datadir        = /var/lib/mysql
    tmpdir        = /tmp
    lc-messages-dir    = /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    # bind-address        = 127.0.0.1


    #max_connections=200 (mysql connections)
    #
    # * Fine Tuning
    #
    key_buffer        = 16M
    max_allowed_packet    = 16M
    thread_stack        = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #

    innodb_buffer_pool_size = 8G
    innodb_flush_log_at_trx_commit = 2
    query_cache_size=268435456
    query_cache_type=1
    query_cache_limit=8388608
    group_concat_max_len=100000

    #query_cache_limit    = 1M
    #query_cache_size        = 16M

    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries    = /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id        = 1
    #log_bin            = /var/log/mysql/mysql-bin.log
    expire_logs_days    = 10
    max_binlog_size         = 100M
    #binlog_do_db        = include_database_name
    #binlog_ignore_db    = include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem



    [mysqldump]
    quick
    quote-names
    max_allowed_packet    = 16M

    [mysql]
    #no-auto-rehash    # faster start of mysql but no tab completition

    [isamchk]
    key_buffer        = 16M

    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/

mysql file in /etc/init.d/

    ...
    SELF=$(cd $(dirname $0); pwd -P)/$(basename $0)
    CONF=/etc/mysql/my.cnf
    MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"

    # priority can be overriden and "-s" adds output to stderr
    ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql -i"
    ...

After mysql restart:

    mysql> show variables like '%group_concat_max%';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | group_concat_max_len | 1024  |
    +----------------------+-------+
    1 row in set (0.00 sec)

Answer 1

The single line from your configuration file NEEDS to be listed as 6 lines because initialization can only deal with one variable per line, imho.

You will likely find that your query cache size of about 268M and query_cache_limit of about 8M would be more effective at 50M and 1M respectively. The reason being when one row is changed in a table, every cached query result for the updated table is marked invalid - i.e. cpu cycles are consumed to mark the cache results individually.

After restart, please confirm SHOW GLOBAL VARIABLES LIKE 'group_concat_max_length' has your intended value. It should be visible.