4

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?
  • What is the value of your max_allowed_packet variable? – Mr.Brownstone Jul 19 '15 at 22:12
  • SHOW VARIABLES LIKE 'max_allowed_packet'; returns 4194304. I'm running this on a hosted server so changing this in my.ini file is not possible. – Kristofer Gisslén Jul 20 '15 at 4:42
  • Can you consider including following within stored procedure itself and debug if it still outputs the correct length: SELECT sum(char_length(@votes_id)); – mysql_user Jul 20 '15 at 5:37
  • That actually only returns #1312 - PROCEDURE [database].GetVoteID can't return a result set in the given context. – Kristofer Gisslén Jul 20 '15 at 5:48
  • my bad. Please remove "@" SELECT sum(char_length(votes_id)); – mysql_user Jul 20 '15 at 6:12
1

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.

  • I tried this: SET SESSION group_concat_max_len = 100000; SET @eliminated_candidates := ( SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11))) FROM vote_final WHERE votes = 501 AND round_id = 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; within the procedure by calling it using CALL SessionTest(2,@votes_id); SELECT LENGTH(@votes_id); and it returns NULL. – Kristofer Gisslén Jul 20 '15 at 6:51
  • Place SELECT LENGTH(votes_id); right before your END in SP. (Also make sure your query is returning values, may be by running manually?) – mysql_user Jul 20 '15 at 7:11
  • SET SESSION group_concat_max_len = 100000; SET @eliminated_candidates := ( SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11))) FROM vote_final WHERE votes = 501 AND round_id = 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;SELECT LENGTH(votes_id); returns #1312 - PROCEDURE [database].GetVoteID can't return a result set in the given context (the query as itself returns values as it should) when being called using CALL SessionTest(2,@votes_id);. – Kristofer Gisslén Jul 20 '15 at 7:21
  • That error points me --> dba.stackexchange.com/questions/33986/… – mysql_user Jul 20 '15 at 7:44
  • This produces the length as expected: SET SESSION group_concat_max_len = 100000;SET @eliminated_candidates := (SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11)))FROM vote_final WHERE votes = 233 AND round_id = 3);SELECT LENGTH(GROUP_CONCAT(CONVERT(vote_id, CHAR(11)))) INTO votes_id FROM votes_copy WHERE FIND_IN_SET(vote_candidate_1, @eliminated_candidates) != 0;END. It still don't explain why I can't retrieve the string without it being truncated. – Kristofer Gisslén Jul 20 '15 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.