Bug #80936 SET GLOBAL optimizer_switch behavior is counter-intuitive

• Submitted: 1 Apr 2016 18:44
• Modified: 4 Apr 2016 4:45
• Reporter: Jenni Snyder
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: MySQL 5.6
• OS: Any
• CPU Architecture: Any

[1 Apr 2016 18:44] Jenni Snyder

Description:
From the documentation:
https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html

It's not clear that SET GLOBAL optimizer_switch does not take effect in the current session, but rather all subsequent sessions. This can be confusing the operators setting the variable dynamically and then attempting to check their work.

mysql root@localhost ((none))> select @@version ;
+-----------------+
| @@version       |
+-----------------+
| 5.6.27-75.0-log |
+-----------------+
1 row in set (0.00 sec)

How to repeat:
# with GLOBAL
mysql root@localhost ((none))> set global optimizer_switch="use_index_extensions=on"
    -> ;
Query OK, 0 rows affected (0.00 sec)
 
mysql root@localhost ((none))> select @@optimizer_switch ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                                |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=off |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# without GLOBAL:
mysql root@localhost ((none))> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)
 
mysql root@localhost ((none))> select @@optimizer_switch ;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
changes to optimizer_switch using SET GLOBAL should take effect in the current, as well as future sessions.

[4 Apr 2016 4:45] MySQL Verification Team

Hello Jenni Snyder,

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on http://dev.mysql.com/doc/refman/5.6/en/set-statement.html

"If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement)."

Thanks,
Umesh