| 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: | |
| 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 | ||
[6 Mar 2019 5:20]
Tsubasa Tanaka
[6 Mar 2019 5:48]
MySQL Verification Team
Hello Tanaka-San, Thank you for the report. regards, Umesh
[6 Mar 2019 5:51]
MySQL Verification Team
- 8.0.15
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.15 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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.00 sec)
mysql> system cat 94573/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "innodb_ft_aux_table" : { "Value" : "d1/t1" , "Metadata" : { "Timestamp" : 1551851247868393 , "User" : "root" , "Host" : "localhost" } } } }mysql>
mysql>
mysql>
-- restart will show the issue
2019-03-06T05:50:30.817660Z 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-06T05:50:30.817708Z 0 [ERROR] [MY-010175] [Server] Setting persistent options failed.
[23 Apr 2019 7:50]
Tsubasa Tanaka
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.
[7 May 2019 9:41]
Erlend Dahl
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.
[11 Sep 2019 9:59]
Ceri Williams
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.
[14 Nov 2019 11:39]
Ceri Williams
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)
[5 Feb 2020 15:42]
Erlend Dahl
Re-closing since the separate issue seems to have been fixed. Please file further issues as new bugs to avoid confusion.
