MySQL Bugs: #105569: Dynamic setting variable innodb_buffer_pool_dump

Bug #105569	Dynamic setting variable innodb_buffer_pool_dump_now cannot take effect
Submitted:	15 Nov 2021 7:58	Modified:	29 Jun 2022 11:22
Reporter:	yi qian	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	8.0.25, 8.0.27, 5.7.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb_buffer_pool_dump_now; innodb_buffer_pool_load_now

Description:
When setting variable innodb_buffer_pool_dump_now or innodb_buffer_pool_load_now, the result shows that the setting  is successful. In fact, the setting  does not take effect.

How to repeat:
1. View the current value of the variable
mysql> show global variables like "innodb_buffer_pool_dump_now";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_buffer_pool_dump_now | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like "innodb_buffer_pool_load_now";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_buffer_pool_load_now | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)

2. Setting variables 
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;
Query OK, 0 rows affected (0.01 sec)

3. View the current value of the variable
mysql> show global variables like "innodb_buffer_pool_dump_now";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_buffer_pool_dump_now | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like "innodb_buffer_pool_load_now";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_buffer_pool_load_now | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)

Hello yi qian,

Thank you for the report and feedback.
Verified as described with 8.0.27 build.

regards,
Umesh

Posted by developer:
 
Hello Yi Qian,
Thanks for the report.
This variable is more like a trigger, a function, than it is a variable. There are other similar to this one and they work in the same way.
Please refer to https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html#innodb-preload-buf...
It explains how to run it, and the next to next section is, citing:

Displaying Buffer Pool Load Progress
To display progress when loading the buffer pool, issue the following statement:
  SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
If the operation has not yet started, “not started” is returned. If the operation is complete, the completion time is printed (e.g. Finished at 110505 12:23:24). If the operation is in progress, status information is provided (e.g. Loaded 123/22301 pages).

Therefore this is not a bug but an intended behavior.
However, we can improve the docs to state this fact explicitly on https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_l... and for other system variables with this behavior.

The following variable documentation was updated to reflect the behavior described above:

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_l...

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_d...

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_l...

The updated content should appear online soon.

Thank you for the bug report.