1SET @save = @@global.group_concat_max_len; 2DROP TABLE IF EXISTS t1; 3## Creating new table t1 ## 4CREATE TABLE t1 5( 6id INT NOT NULL AUTO_INCREMENT, 7PRIMARY KEY (id), 8rollno INT NOT NULL, 9name VARCHAR(30) 10); 11'#--------------------FN_DYNVARS_034_01-------------------------#' 12## Setting initial value of variable to 4 ## 13SET @@global.group_concat_max_len = 4; 14## Inserting some rows in table ## 15INSERT INTO t1(rollno, name) VALUES(1, 'Record_1'); 16INSERT INTO t1(rollno, name) VALUES(2, 'Record_2'); 17INSERT INTO t1(rollno, name) VALUES(1, 'Record_3'); 18INSERT INTO t1(rollno, name) VALUES(3, 'Record_4'); 19INSERT INTO t1(rollno, name) VALUES(1, 'Record_5'); 20INSERT INTO t1(rollno, name) VALUES(3, 'Record_6'); 21INSERT INTO t1(rollno, name) VALUES(4, 'Record_7'); 22INSERT INTO t1(rollno, name) VALUES(4, 'Record_8'); 23SELECT * FROM t1 ORDER BY id; 24id rollno name 251 1 Record_1 262 2 Record_2 273 1 Record_3 284 3 Record_4 295 1 Record_5 306 3 Record_6 317 4 Record_7 328 4 Record_8 33## Creating two new connections ## 34'#--------------------FN_DYNVARS_034_02-------------------------#' 35## Connecting with test_con1 ## 36## Accessing data and using group_concat on column whose value is greater than 4 ## 37SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 38id rollno GROUP_CONCAT(name) 391 1 Reco 402 2 Reco 414 3 Reco 427 4 Reco 43Warnings: 44Warning 1260 Row 1 was cut by GROUP_CONCAT() 45Warning 1260 Row 2 was cut by GROUP_CONCAT() 46Warning 1260 Row 3 was cut by GROUP_CONCAT() 47Warning 1260 Row 4 was cut by GROUP_CONCAT() 48## Changing session value of variable and verifying its behavior, ## 49## warning should come here ## 50SET @@session.group_concat_max_len = 10; 51SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 52id rollno GROUP_CONCAT(name) 531 1 Record_1,R 542 2 Record_2 554 3 Record_4,R 567 4 Record_7,R 57Warnings: 58Warning 1260 Row 2 was cut by GROUP_CONCAT() 59Warning 1260 Row 5 was cut by GROUP_CONCAT() 60Warning 1260 Row 7 was cut by GROUP_CONCAT() 61'#--------------------FN_DYNVARS_034_03-------------------------#' 62## Connecting with new connection test_con2 ## 63## Verifying initial value of variable. It should be 4 ## 64SELECT @@session.group_concat_max_len = 4; 65@@session.group_concat_max_len = 4 661 67## Setting session value of variable to 20 and verifying variable is concating ## 68## column's value to 20 or not ## 69SET @@session.group_concat_max_len = 20; 70## Verifying value of name column, it should not me more than 20 characters ## 71## Warning should come here ## 72SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 73id rollno GROUP_CONCAT(name) 741 1 Record_1,Record_3,Re 752 2 Record_2 764 3 Record_4,Record_6 777 4 Record_7,Record_8 78Warnings: 79Warning 1260 Row 3 was cut by GROUP_CONCAT() 80'#--------------------FN_DYNVARS_034_04-------------------------#' 81## Setting session value of variable to 26. No warning should appear here ## 82## because the value after concatination is less than 30 ## 83SET @@session.group_concat_max_len = 26; 84## Verifying value of name column, it should not give warning now ## 85SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 86id rollno GROUP_CONCAT(name) 871 1 Record_1,Record_3,Record_5 882 2 Record_2 894 3 Record_4,Record_6 907 4 Record_7,Record_8 91## Dropping table t1 ## 92DROP TABLE t1; 93## Disconnecting both the connection ## 94SET @@global.group_concat_max_len = @save; 95