I'm searching a way to insert this
SET GLOBAL group_concat_max_len=15000
in an existing SQL query such as the following:
SELECT *
FROM `Pages`
WHERE id =1
UNION SELECT 1 , 2, IF( 1 >0, SET GLOBAL group_concat_max_len=15000,'B' )
But I couldn't make it work because usually this query is executed on its own and I was wondering if some of you had any idea how to make it work
You can't use a SET statement inside an expression like you're doing, or even in a subquery. Execute the SET in one statement by itself. The value you set will affect subsequent statements.
By the way, are you aware that SET GLOBAL means the setting will affect all other MySQL connections on your server, and the setting will even persist after your current session is done, until you set it to something else? You might want to use SET SESSION if you only want the value to affect your current session.
SET SESSION command but I will use this one rather than SET GLOBAL Finally I separated the query with a ';' and it worked fine. I thought I could use it in the SQL query. THanks for the answer. BTW I was wondering what kind of rights do I need to create a query such as SET SESSION or SET GLOBAL?SET GLOBAL. You don't need any specific privilege to use SET SESSION, but of course you need privileges to do many of the statements for which you might want to change session variables.CI3 Example, you cant combine them, but you can execute a seperate query, such as:
public function myModelFunction($id){
//set mysql session variable
$this->db->query("SET @@group_concat_max_len = 2048;");
//actual query
$query= $this->db->query("").....
The maximum allowed result length in bytes for the GROUP_CONCAT() function. The default is 1024. mysql> show variables like 'group%';
You change the value of group_concat_max_len. Either in your config file or setting the session variable, e.g. "SET @@group_concat_max_len = 2048;'
You can execute them one after the other and I think that it will have the desired effect or separate the with a ; sign .
SET SESSION mentioned by Bill Karwin. Thanks for the answer.
For new user, while searching for solution on increasing group_concat_max_len i identified that in certain cases variable was not defined in my.cnf file. Nothing to worry about just addup this variable group_concat_max_len=XXXXX under heading [mysqld] in my.cnf file and restart mysql... you are DONE !!!
XXXXX = you desired value eg: 2048,4096 etc.
