Bug #50643 innodb_adaptive_flushing/hash_index/ stats_on_metadata can be set with neg vals
Submitted: 27 Jan 2010 1:50 Modified: 27 Jan 2010 1:52
Reporter: Omer Barnir (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[27 Jan 2010 1:50] Omer Barnir
Description:
innodb_adaptive_flushing and innodb_adaptive_hash_index can be set to 'ON' or 'OFF' by setting the global variables to '0' and '1' respectively.

Assigning other positive values returns an error. However assigning negative values sets the variables to 'ON'

How to repeat:
Start the server with innodb enabled.

Using the mysql client run the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.99-m3-debug-log Source distribution

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

mysql>
mysql>
mysql>
mysql> show variables like '%adaptive%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_flushing   | ON    |
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
2 rows in set (0.01 sec)

mysql> set @@global.innodb_adaptive_flushing=0;
Query OK, 0 rows affected (0.01 sec)

mysql> set @@global.innodb_adaptive_hash_index=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%adaptive%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_flushing   | OFF   |
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> set @@global.innodb_adaptive_flushing=2;
ERROR 1231 (42000): Variable 'innodb_adaptive_flushing' can't be set to the value of '2'
mysql> set @@global.innodb_adaptive_hash_index=3;
ERROR 1231 (42000): Variable 'innodb_adaptive_hash_index' can't be set to the value of '3'
mysql> set @@global.innodb_adaptive_hash_index=-3;  <-- Should have returned 
                                                        an error and not set
                                                        the variable
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%adaptive%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_flushing   | OFF   |
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> set @@global.innodb_adaptive_flushing=-200;   <-- Should have returned 
                                                        an error and not set
                                                        the variable
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%adaptive%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_flushing   | ON    |
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
2 rows in set (0.00 sec)

mysql>                                                                                               

Suggested fix:
Assigning negative values should return an error
[27 Jan 2010 15:19] Mikhail Izioumtchenko
plugin only and happens with 5.1 + Plugin as well, no need to use 5.5
so I changed the category/version accordingly. A small correction to Omar's note:
innodb_adaptive_flushing is Plugin only, innodb_adaptive_hash_index is
readonly in 5.1 InnoDB builtin
[28 Jan 2010 0:29] Omer Barnir
Issue also observed with the innodb_stats_on_metadata. Seems to be affecting all 'on' / 'off' innodb variables
[28 Jan 2010 7:58] Jimmy Yang
Well, this problem can be solved by adding validation functions to screen out invalid values. Only 1 and 0 are allowed for these options.

Thanks
Jimmy
[28 Jan 2010 12:53] Guilhem Bichot
Even though this is visible in 5.1 only with the InnoDB plugin, I think this is a MySQL bug; InnoDB just uses the plugin-variables API by having this in its code (ha_innodb.cc):
static MYSQL_SYSVAR_BOOL(adaptive_flushing, srv_adaptive_flushing,
  PLUGIN_VAR_NOCMDARG,
  "Attempt flushing dirty pages to avoid IO bursts at checkpoints.",
  NULL, NULL, TRUE);
and there is nothing wrong here.
In 5.1 bug shows up only in InnoDB because InnoDB is the only engine (except ibmdb2i) making use of this API.
But I could verify the same issue in next-mr-bugfixing with MyISAM (MyISAM uses the API in this version, which is not the case in 5.1):
mysql> select @@global.myisam_use_mmap;
+--------------------------+
| @@global.myisam_use_mmap |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> set @@global.myisam_use_mmap=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.myisam_use_mmap;
+--------------------------+
| @@global.myisam_use_mmap |
+--------------------------+
|                        1 |
So I suggest to re-classify this as a MySQL bug.
[28 Jan 2010 13:06] Mikhail Izioumtchenko
switched the category to MySQL, please reassign
[28 Jan 2010 13:07] Jimmy Yang
well, sure this can be fixed from mysql side, given this being generic and regression.

Otherwise, a validation function for the variable could catch the problem, and reject it as well. However, this would be variable specific. It would be good from mysql side.

Thanks
[28 Jan 2010 13:16] Mikhail Izioumtchenko
It looks like Jimmy's simultaneous update reset the category to the Plugin, 
fixing it