Mysql – When does a MySQL session end

group-concatenationMySQLsession

I have a stored procedure as described below.

DROP PROCEDURE `GetVoteID`//    
CREATE DEFINER=`u1037413_manager`@`%` PROCEDURE `GetVoteID`(IN minimum_votes INT(11), IN current_round INT(11), INOUT votes_id TEXT)
BEGIN
    SET SESSION group_concat_max_len = 100000;
    SET @eliminated_candidates := (
        SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11)))
        FROM vote_final
        WHERE votes = minimum_votes
        AND round_id = current_round
    );
    SELECT GROUP_CONCAT(CONVERT(vote_id, CHAR(11))) INTO votes_id
    FROM votes_copy
    WHERE FIND_IN_SET(vote_candidate_1, @eliminated_candidates) != 0;
END

Since I'm using a group_concat that produces quite long strings in both the set-statement and the select-statement I need to change the sessions group_concat_max_len to 100000 chars. But this is where I run into problems. Using this method I've figured out that SET SESSION group_concat_max_len = 100000; really never changes the limit. When the length of @votes_id reaches 1,024 it's content is truncated – even though I've changed the limit to 100,000.

CALL GetVoteID(501,3,@votes_id);
SELECT sum(char_length(@votes_id));

This leaves me with two questions.

  1. When does this session actually end? At the end of the stored procedure or at the end of each statement within the procedure?
  2. What am I doing wrong with the group_concat_max_len?

Best Answer

To answer your questions:

  1. Session ends when session (with mysql) ends and not when stored procedure ends! So the setting of group concat you did appears correct.

I simulated your scenario and it worked well for me!

MySQL Version: 5.6.24-72.2-log

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS session_test $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE session_test (INOUT str TEXT)
    -> BEGIN
    -> show session variables like 'group_concat_max_len';
    -> set session group_concat_max_len = 10000;
    -> show session variables like 'group_concat_max_len';
    -> select group_concat(ts1) into str from t1;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call session_test(@myvar);
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.01 sec)

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.01 sec)

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select length(@myvar);
+----------------+
| length(@myvar) |
+----------------+
|          10000 |
+----------------+
1 row in set (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 501 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

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

You see the last warning cause my group concat length went beyond 10K but that's OK!

  1. You're not doing anything wrong with the variable as such it appears fine to me!

Are you sure you have group concat length of 10k? Please try to exec the select statement from procedure.

Related Question