MySQL Bugs: #94065: MySQL fails to startup when setting persist variable

Bug #94065	MySQL fails to startup when setting persist variable
Submitted:	25 Jan 2019 14:41	Modified:	14 Mar 2019 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

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.

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.

Hello yuhui wang,

Thank you for the report.
Verified as described.

regards,
Umesh

test results

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

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.

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.