Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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, 2013 at 6: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, 2013 at 8: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, 2016 at 10:24
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.
                I preferred to use GLOBAL instead of SESSION: SET GLOBAL group_concat_max_len=6999 to make the setting valid across queries
– IcedDante
                Oct 7, 2014 at 19:48
                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, 2014 at 22:42
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.
                this worked while the other suggestions didn't @ MySQL Server 5.1.41 (I know it's an old version)
– low_rents
                Jan 28, 2016 at 7:57
                You can actually set group_concat_max_len to as low as 4.   (mysql docs). "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, 2016 at 18:15
                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, 2017 at 11:31
                @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, 2018 at 7:27
                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, 2018 at 6:48
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.

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.
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;
            $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');
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; 
                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, 2013 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, 2013 at 9:01
                @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 7, 2014 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 9, 2014 at 13:53
                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 9, 2014 at 16:11
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.