MySQL Bugs: #94573: SET PERSIST_ONLY can set incorrect value to innodb_ft_aux

Bug #94573	SET PERSIST_ONLY can set incorrect value to innodb_ft_aux_table
Submitted:	6 Mar 2019 5:20	Modified:	5 Feb 2020 15:42
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	8.0.15, 8.0.17	OS:	CentOS (7.5)
Assigned to:		CPU Architecture:	x86
Tags:	MY-011268

Description:
SET GLOBAL innodb_ft_aux_table validates argument table exists or not like this.

mysql> SET GLOBAL innodb_ft_aux_table = 'd1/t1'; -- SET GLOBAL fails because d1.t1 does not exist
ERROR 1231 (42000): Variable 'innodb_ft_aux_table' can't be set to the value of 'd1/t1'

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

But SET PERSIST doesn't validate argument and incorrect value of innodb_ft_aux_table can raid on mysqld-auto.cnf.

How to repeat:
$ docker run -d --name=mysql80 -e MYSQL_ROOT_PASSWORD="" -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_HOST
="%" mysql/mysql-server:8.0

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE d1.t1; -- No such table exits
ERROR 1049 (42000): Unknown database 'd1'

mysql> SET GLOBAL innodb_ft_aux_table = 'd1/t1'; -- SET GLOBAL fails because d1.t1 does not exist
ERROR 1231 (42000): Variable 'innodb_ft_aux_table' can't be set to the value of 'd1/t1'

mysql> SET PERSIST innodb_ft_aux_table = 'd1/t1'; -- SET PERSIST fails too
ERROR 1231 (42000): Variable 'innodb_ft_aux_table' can't be set to the value of 'd1/t1'

mysql> SET PERSIST_ONLY innodb_ft_aux_table = 'd1/t1'; -- But SET PERSIST_ONLY doesn't validate d1.t1 exists or not
Query OK, 0 rows affected (0.01 sec)

Incorrect variable is in mysqld-auto.cnf, and then mysqld can't start after restart.

$ docker restart mysql80
$ docker logs mysql80
..
[Entrypoint] MySQL Docker Image 8.0.15-1.1.10
[Entrypoint] Starting MySQL 8.0.15-1.1.10
2019-03-06T04:53:53.181914Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 1
2019-03-06T04:53:53.570265Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-03-06T04:53:53.594240Z 5 [ERROR] [MY-011268] [Server] Configuring persisted options failed: "Variable 'innodb_ft_aux_table' can't be set to the value of 'd1/t1'".
2019-03-06T04:53:53.594279Z 0 [ERROR] [MY-010175] [Server] Setting persistent options failed.

Suggested fix:
innodb_ft_aux_table (or is there any variable such as "intended for diagnostic purposes and can only be set at runtime" ?) should validate argument within SET PERSIST statement.

In this case, SET PERSIST should call innodb_internal_table_validate 
 function.
(mysql-8.0.15/storage/innobase/handler/ha_innodb.cc Line 18973)

Hello Tanaka-San,

Thank you for the report.

regards,
Umesh

I found `default_collation_for_utf8mb4` affected this problem.

mysql80 10> SET PERSIST default_collation_for_utf8mb4 = 99;
ERROR 3721 (HY000): Invalid default collation cp1250_polish_ci: utf8mb4_0900_ai_ci or utf8mb4_general_ci expected

mysql80 10> SET PERSIST_ONLY default_collation_for_utf8mb4 = 99;
Query OK, 0 rows affected (0.00 sec)

mysql80 10> SELECT * FROM performance_schema.persisted_variables;
+-------------------------------+----------------+
| VARIABLE_NAME                 | VARIABLE_VALUE |
+-------------------------------+----------------+
| default_collation_for_utf8mb4 | 99             |
+-------------------------------+----------------+
1 row in set (0.00 sec)

mysql80 10> RESTART;
Query OK, 0 rows affected (0.00 sec)

2019-04-23T07:49:49.801325Z 5 [ERROR] [MY-011268] [Server] Configuring persisted options failed: "Unknown collation: '99'".
2019-04-23T07:49:49.801364Z 0 [ERROR] [MY-010175] [Server] Setting persistent options failed.

Posted by developer: Bharathy X Satish

https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

Like PERSIST, PERSIST_ONLY writes the variable setting to mysqld-auto.cnf.
However, unlike PERSIST, PERSIST_ONLY does not modify the global variable
runtime value. This makes PERSIST_ONLY suitable for configuring read-only
system variables that can be set only at server startup.

This kind of behavior with PERSIST_ONLY is expected. Whenever end user wants
to persist a value by not changing the current runtime value then user needs
to use PERSIST_ONLY. If user want to change runtime value of a variable and
persist it to cnf file then user is expected to use PERSIST so that a
validation happens before changing the runtime value.

This is not a bug and closing it.

I disagree that this is not a bug, as you can prevent the server from starting with this issue.

mysql> set persist_only super_read_only = 1;                                                                                                                                   
Query OK, 0 rows affected (0.00 sec)                                                                                             
                    
mysql> system grep -F super  /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "super_read_only" : { "Value" : "1" , "Metadata" : { "Timestamp" : 1568195690463960 , "User" : "root" , "Host" : "localhost" } } } }

mysql> select @@version, @@version_comment;                                                                        
+-----------+------------------------------+                                                                                                                                                                      
| @@version | @@version_comment            |
+-----------+------------------------------+                                                                       
| 8.0.16    | MySQL Community Server - GPL |                                                      
+-----------+------------------------------+                                                                                                                                                                      
1 row in set (0.00 sec)                                      

A restart/start with this config would produce an error that causes termination:

2019-09-11T09:55:49.224826Z 5 [ERROR] [MY-011268] [Server] Configuring persisted options failed: "Variable 'super_read_only' can't be set to the value of '1'".
2019-09-11T09:55:49.224852Z 0 [ERROR] [MY-010175] [Server] Setting persistent options failed.

Regarding my previous comment, Kamil Holubicki investigated the issue and considered it was a separate issue due to it relating specifically to the handling of booleans (https://bugs.mysql.com/bug.php?id=96848)

Re-closing since the separate issue seems to have been fixed.

Please file further issues as new bugs to avoid confusion.