Bug #94065 MySQL fails to startup when setting persist variable
Submitted: 25 Jan 14:41 Modified: 14 Mar 18:24
Reporter: yuhui wang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.14 OS:CentOS
Assigned to: CPU Architecture:x86
Tags: DEFAULT, innodb_buffer_pool_size, persist, restart

[25 Jan 14:41] yuhui wang
Description:
1.Start mysql using following configuration: 
* innodb_buffer_pool_instances > 1, for example innodb_buffer_pool_instances = 4; 
* innodb_buffer_pool_size > 1G(BUF_POOL_SIZE_THRESHOLD), for example innodb_buffer_pool_size  = 2G

2. set persist innodb_buffer_pool_size = default;

3. restart server

You will find mysql fails to startup because we have to set the variable that has been persisted to mysqld-auto.cnf in funcation Persisted_variables_cache::set_persist_options. In this case, setting innodb_buffer_pool_size will fail in variable checking stage(innodb_buffer_pool_size_validate), because innodb_buffer_pool_instances is bigger than 1 while default value of innodb_buffer_pool_size_validate is only 128MB which is much smaller than BUF_POOL_SIZE_THRESHOLD.

What is worst, mysql will reboot again and again until we kill mysqld_safe or remove mysqld-auto.cnf manually.

But if we do not use persist feature and only configure these variables(buffer_pool_size = 128MB and buffer_pool_instance = 4) in normal configuration file, mysql will startup successfully.

How to repeat:
1.Start mysql using following configuration: 
* innodb_buffer_pool_instances > 1, for example innodb_buffer_pool_instances = 4; 
* innodb_buffer_pool_size > 1G(BUF_POOL_SIZE_THRESHOLD), for example innodb_buffer_pool_size  = 2G

2. set persist innodb_buffer_pool_size = default;

3. restart server

Suggested fix:
Maybe we should not interrupt normal startup if some persisted variables fail to be set. We can just print a warning/error message to notice users.
[26 Jan 4:37] yuhui wang
After review codes carefully, I find this is not a bug of persisting variable feature, but a bug of innodb.
If we use 'set persist innodb_buffer_pool_size = 128*1024*1024' instead of 'set persist innodb_buffer_pool_size =default',  innodb will return an error and no variable will be persisted, thus everything is ok.
The root cause it that we will invoke innodb_buffer_pool_size_validate to check if the target value is valid(in this case, we will refuse to update if buf_pool_instance > 1 and buf_pool_size < 1G), but if we set to default value, we will skip checking(Ref: int set_var::check(THD *thd) in sql/set_var.cc, in this function we will return directly without checking if we are using set ... = default). 
Thus, in order to avoid changing the whole framework of variables setting. I think we can move the checking logic in innodb_buffer_pool_size_validate to innodb_buffer_pool_size_update which will be called under any situations.
[26 Jan 7:48] Umesh Shastry
Hello yuhui wang,

Thank you for the report.
Verified as described.

regards,
Umesh
[26 Jan 7:50] Umesh Shastry
test results

Attachment: 94065.results (application/octet-stream, text), 4.13 KiB.

[14 Mar 18:23] Daniel Price
Posted by developer:
 
Problem:
- Server is stuck in a boot-loop scenario because it is unable to
progress further due to the fact it ended up having at least one
of its variables set to an invalid value.
    
Cause:
- Setting a variable to its 'default' value does not go through
standard first-check-then-update-the-state mechanism but goes to
the update-the-state directly.
- Assumption is that 'default' values are never wrong, therefore
by-design they're never checked upon, but that may get us into
trouble whenever setting a variable depends on runtime circumstances
given at that moment (e.g. when validity of certain value depends on
a value of another variable).
- Previous assumption also holds for variables which are set through the
command-line options. They are not checked.
- Summarized, setting a variable to its 'default' value under certain
circumstances can get us into problems.
    
Fix:
- Always check the value of such dependent variables in their
update step.
[14 Mar 18:24] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.17 release, and here's the changelog entry:

The server failed to start with an innodb_buffer_pool_size=default
setting. The default value was not checked for compatibility with
dependent system variable settings.