MySQL and GROUP_CONCAT() maximum length

Question

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.

I'm very well aware that I can change the param group_concat_max_len to increase this limit:

SET SESSION group_concat_max_len = 1000000;

However, on the server I'm using, I can't change any param. Not by using the preceding query and not by editing any configuration file.

So my question is: Is there any other way to get the output of a multiple row query into a single string?

You seem to have chosen an answer already, but out of curiosity, why can't you use the `SET` statement to change a session variable? — Bill Karwin, Aug 21 '13 at 06:49
That's because the query I had to create was embedded in some rotten homemade php framework, and I wasn't allowed to edit any other part. The way this project was coded was really shameful. — ZeWaren, Aug 21 '13 at 08:58
i was wonder when using group_concat function my string were return break, i had no idea that this function return a limited number of char thanks buddy your question got me clear :) — MasoodRehman, Nov 17 '16 at 10:24

score 361 · Answer 1 · edited Dec 19 '14 at 13:12

361

SET SESSION group_concat_max_len = 1000000;

is a temporary, session-scope, setting. It only applies to the current session You should use it like this.

SET SESSION group_concat_max_len = 1000000;
select group_concat(column) from table group by column

You can do this even in sharing hosting, but when you use an other session, you need to repeat the SET SESSION command.

edited Dec 19 '14 at 13:12

O. Jones

81,279
15
96
133

answered Aug 21 '13 at 04:08

keatkeat

3,611
1
9
2

5

I preferred to use GLOBAL instead of SESSION: `SET GLOBAL group_concat_max_len=6999` to make the setting valid across queries – IcedDante Oct 07 '14 at 19:48
2

Rackspace and other cloud servers don't allow GLOBAL access. I try using jdbc.execute("SET SESSION group_concat_max_len = ..."); inside the Dao initialize method but as keatkeat has stated, this is only temporary. If anyone knows the right way to make this change permanently pls let me know – IcedDante Nov 18 '14 at 22:42

score 61 · Answer 2 · edited Mar 24 '15 at 21:58

61

The correct parameter to set the maximum length is:

SET @@group_concat_max_len = value_numeric;

value_numeric must be > 1024; by default the group_concat_max_len value is 1024.

edited Mar 24 '15 at 21:58

honk

7,217
11
62
65

answered Mar 24 '15 at 21:27

oscar

619
5
2

3

SET SESSION and SET GLOBAL didn't work on a certain server, but this did! thanks! – mfink Jun 19 '15 at 04:14
this worked while the other suggestions didn't @ **MySQL Server 5.1.41** (I know it's an old version) – low_rents Jan 28 '16 at 07:57
2

You can actually set `group_concat_max_len` to as low as **4**. ([mysql docs](http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len)). "`value_numeric` must be >= 4" is the case here. I actually used this to test what happens when you exceed the `group_concat_max_len` value. – Thomas F Nov 18 '16 at 18:15
Does that retain after mysql restart? – Ilia Sidorenko Sep 11 '17 at 08:49
1

I can confirm that this parameter is absolutely *NOT* reboot-proof : once mysql is restarted, property is resetted to 1024, so -1 for me – Frédéric Camblor Sep 29 '17 at 11:31
2

@NoWay you have to set the value in a configuration file (e.g. my.cnf) for the setting to apply on a restart of mysql. No `SET` query will affect settings after a restart. – Buttle Butkus Feb 13 '18 at 07:27

score 22 · Answer 3 · edited Aug 09 '18 at 13:55

22

Include this setting in xampp my.ini configuration file:

[mysqld]
group_concat_max_len = 1000000

Then restart xampp mysql

edited Aug 09 '18 at 13:55

agold

5,632
9
38
52

answered Nov 29 '17 at 14:58

Chinnadurai Ramalingam

229
2
3

score 10 · Answer 4 · answered Nov 26 '17 at 15:51

10

You can try this

SET GLOBAL group_concat_max_len = 1000000;

answered Nov 26 '17 at 15:51

Mohamed El Mrabet

538
4
14

I am running this is sqlyog client for my db, but it is not reflecting. But it seems to work when i run it through my java program – Jerry Nov 20 '18 at 06:48

score 5 · Answer 5 · edited Oct 31 '16 at 06:42

The correct syntax is mysql> SET @@global.group_concat_max_len = integer;
If you do not have the privileges to do this on the server where your database resides then use a query like:
mySQL="SET @@session.group_concat_max_len = 10000;"or a different value.
Next line:
SET objRS = objConn.Execute(mySQL) your variables may be different.
then
mySQL="SELECT GROUP_CONCAT(......);" etc
I use the last version since I do not have the privileges to change the default value of 1024 globally (using cPanel).
Hope this helps.

score 2 · Accepted Answer · answered Apr 11 '10 at 20:59

2

CREATE TABLE some_table (
  field1 int(11) NOT NULL AUTO_INCREMENT,
  field2 varchar(10) NOT NULL,
  field3 varchar(10) NOT NULL,
  PRIMARY KEY (`field1`)
);

INSERT INTO `some_table` (field1, field2, field3) VALUES
(1, 'text one', 'foo'),
(2, 'text two', 'bar'),
(3, 'text three', 'data'),
(4, 'text four', 'magic');

This query is a bit strange but it does not need another query to initialize the variable; and it can be embedded in a more complex query. It returns all the 'field2's separated by a semicolon.

SELECT result
FROM   (SELECT @result := '',
               (SELECT result
                FROM   (SELECT @result := CONCAT_WS(';', @result, field2) AS result,
                               LENGTH(@result)                            AS blength
                        FROM   some_table
                        ORDER  BY blength DESC
                        LIMIT  1) AS sub1) AS result) AS sub2;

answered Apr 11 '10 at 20:59

ZeWaren

3,482
2
18
19

1

This is a great answer, but doesn't quite finish the question - this is how to get a very long concat, but what about the grouping? Your query only returns one row, instead of one row per group. – Benubird Apr 24 '13 at 15:07
I remember that's what I was trying to do --getting the entire result set into a single string. – ZeWaren Apr 25 '13 at 09:01
10

@Benubird this is a very bad query. and by bad I mean terrible. the OP is doing a correlated subquery that has a subquery thats inside a subquery. if you were to examine that by data comparisons you would have 256 comparisons on his sample data set aka 4 rows.. now imagine if you have 1k rows thats 1 trillion comparisons. – John Ruddell Nov 07 '14 at 15:19
@JohnRuddell Yeah, it is. I can assure you this query is nowhere near inside a serious live system. At the time, I needed it for some kind of challenge/exercice. – ZeWaren Nov 09 '14 at 13:53
5

Ah gotcha.. I would recommend you make a note of that for other passers by... As this answer will be misleading :) interesting attempt though – John Ruddell Nov 09 '14 at 16:11
Nah.. I still prefer the group_concat() . I don't want to slow or "hurt" my database server with this query. – fedmich Aug 24 '15 at 09:39

score 2 · Answer 7 · answered Aug 03 '20 at 15:35

The short answer: the setting needs to be setup when the connection to the MySQL server is established. For example, if using MYSQLi / PHP, it will look something like this:

$ myConn = mysqli_init(); 
$ myConn->options(MYSQLI_INIT_COMMAND, 'SET SESSION group_concat_max_len = 1000000');

Therefore, if you are using a home-brewed framework, well, you need to look for the place in the code when the connection is establish and provide a sensible value.

I am still using Codeigniter 3 on 2020, so in this framework, the code to add is in the application/system/database/drivers/mysqli/mysqli_driver.php, the function is named db_connect();

public function db_connect($persistent = FALSE)
    {
        // Do we have a socket path?
        if ($this->hostname[0] === '/')
        {
            $hostname = NULL;
            $port = NULL;
            $socket = $this->hostname;
        }
        else
        {
            $hostname = ($persistent === TRUE)
                ? 'p:'.$this->hostname : $this->hostname;
            $port = empty($this->port) ? NULL : $this->port;
            $socket = NULL;
        }

        $client_flags = ($this->compress === TRUE) ? MYSQLI_CLIENT_COMPRESS : 0;
        $this->_mysqli = mysqli_init();

        $this->_mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10);
        $this->_mysqli->options(MYSQLI_INIT_COMMAND, 'SET SESSION group_concat_max_len = 1000000');

...
    }

MySQL and GROUP_CONCAT() maximum length

7 Answers7

Linked

Related