MySQL Bugs: #50643: innodb_adaptive_flushing/hash_index/ stats_on

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

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

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

Issue also observed with the innodb_stats_on_metadata. Seems to be affecting all 'on' / 'off' innodb variables

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

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.

switched the category to MySQL, please reassign

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

It looks like Jimmy's simultaneous update reset the category to the Plugin, 
fixing it

Posted by developer:
 
Fixed in 5.7.35, 8.0.25.

Boolean system variables could be assigned a negative value.

Posted by developer:
 
Fixed in 8.0.26, not 8.0.25.