1drop table if exists t1; 2## Creating new table t1 ## 3CREATE TABLE t1 4( 5id INT NOT NULL auto_increment, 6PRIMARY KEY (id), 7name VARCHAR(30) 8); 9## Creating another table t2 ## 10CREATE TABLE t2 11( 12id INT NOT NULL auto_increment, 13PRIMARY KEY (id), 14name VARCHAR(30) 15); 16## Inserting records in both the tables ## 17INSERT INTO t1(name) VALUES('Record_1'); 18INSERT INTO t1(name) VALUES('Record_2'); 19INSERT INTO t1(name) VALUES('Record_3'); 20INSERT INTO t1(name) VALUES('Record_4'); 21INSERT INTO t1(name) VALUES('Record_5'); 22INSERT INTO t2(name) VALUES('Record_1_1'); 23INSERT INTO t2(name) VALUES('Record_2_1'); 24INSERT INTO t2(name) VALUES('Record_3_1'); 25INSERT INTO t2(name) VALUES('Record_4_1'); 26INSERT INTO t2(name) VALUES('Record_5_1'); 27'#--------------------FN_DYNVARS_079_01-------------------------#' 28## Setting max_join size value to 10 ## 29SET @@session.max_join_size=10; 30## This should work ## 31SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id; 32id name id name 331 Record_1 1 Record_1_1 342 Record_2 2 Record_2_1 353 Record_3 3 Record_3_1 364 Record_4 4 Record_4_1 375 Record_5 5 Record_5_1 38'#--------------------FN_DYNVARS_079_02-------------------------#' 39## Creating new connection test_con1 ## 40## Setting value of max_join_size ## 41SET @@session.max_join_size=8; 42## Since total joins are more than max_join_size value so error will occur ## 43SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id; 44ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay 45'#--------------------FN_DYNVARS_079_03-------------------------#' 46## Setting global value of variable ## 47SET @@global.max_join_size=8; 48## Creating and switching to new connection test_con2 ## 49## Verifying value of max_join_size ## 50SELECT @@global.max_join_size; 51@@global.max_join_size 528 53## Since total joins are more than max_join_size value so error will occur ## 54SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id; 55ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay 56## Dropping both the tables ## 57Drop table t1, t2; 58## Restoring values ## 59SET @@global.max_join_size = DEFAULT; 60SET @@session.max_join_size = DEFAULT; 61## Dropping connections ## 62