MySQL Bugs: #50641: setting innodb_adaptive_hash

Bug #50641	setting innodb_adaptive_hash_index = ON actually disables it
Submitted:	26 Jan 2010 21:07	Modified:	26 Jan 2010 21:39
Reporter:	Gavin Towey	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.1.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
setting innodb_adaptive_hash_index = ON in the my.cnf disables adaptive the hash index.  Syntax was copied from a recent post on the innobase website.

How to repeat:
1. edit the my.cnf and set:
innodb_adaptive_hash_index = ON

2. restart mysql

3. SHOW GLOBAL VARIABLES LIKE 'innodb%';

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.44-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >SHOW GLOBAL VARIABLES LIKE 'innodb_ada%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_flushing   | ON    |
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
2 rows in set (0.00 sec)

mysql 5.1 >exit
Bye

C:\DBS>51

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.44-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >SHOW GLOBAL VARIABLES LIKE 'innodb_ada%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_flushing   | ON    |
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+
2 rows in set (0.00 sec)

Thank you for the bug report.

Jimmy, please also check what happens with innodb_adaptive_flushing 
and maybe other variables

same wrong setting when --innodb-adaptive-hash-index=ON is specified 
on the command line.

For these type of server parameter, user should specify 

"--innodb-adaptive-hash-index=1" instead of "--innodb-adaptive-hash-index=ON". I believe the default value "ON" in the documentation is a bit misleading. 

This is true for other type of server parameter such as:

innodb_adaptive_hash_index
innodb_stats_on_metadata
innodb_rollback_on_timeout

Thanks
Jimmy

The doc should mentioned this clearly as another option "innodb_use_legacy_cardinality_algorithm":

The default value of the variable is 1 (ON) ..... The variable can be set to 0 (OFF). 

Or more clearly, innodb_adaptive_flushing, innodb_adaptive_hash_index ... can be set to 'ON' or 'OFF' by setting the global variables to '0' and '1' respectively.

The same problem arises with --myisam-use-mmap=ON so it's either a Doc bug
or a server bug. I haven't rechecked the documentation but I remember that
reading it has left me with the conclusion that for Boolean options
I could use 1,ON, maybe even TRUE to set, 0,OFF, (?) FALSE to unset.
Of course I only used 0 or 1 so I never noticed it afterwards.
Actually I think it's a code bug, in combination with bug #50643,
the actual value assignment algorithm is hard to discern:
ON -> 0
-1 -> 1
I'd say everything except 0 or 1 should be reject with an option
sugarcoating of admissible values being /(0|1|on|off|true|false)/i
How about yes/no and their other language equivalents?

here is how the value got set to 0:

code stack:

setval
handle_options
test_plugin_options
plugin_init

In my_getopt.c:

setval()
{
    ....
    case GET_BOOL: /* If argument differs from 0, enable option, else disable */
      *((my_bool*) result_pos)= (my_bool) atoi(argument) != 0;
    ...
}

(gdb) p argument
$16 = 0x1b73010d "ON"
(gdb) p  atoi("ON")
$20 = 0

Please check whether this is expected behavior for boolean type variable. If so, please document it clearly.

BTW, run time set global variable with ON/OFF work well.

Thanks
Jimmy