SHOW SETTINGS

[sanikolaev]

It would be convenient for the backup script and other purposes to be able to read current settings of the instance from inside it. What it can look like:

mysql> SHOW SETTINGS;  
 -------------------------- -----------------------------   
| Setting_name             | Value                       |  
 -------------------------- -----------------------------   
| searchd.listen           | 127.0.0.1:9315:mysql        |  
| searchd.listen           | 127.0.0.1:9316              |  
| data_dir                 | /home/snikolaev/data        |  
| pid_file                 | /home/snikolaev/9315.pid    |  
| log                      | /home/snikolaev/searchd.log |  
 -------------------------- -----------------------------   
5 rows in set (0.00 sec)  

i.e.:

• separator - .
• in case of multiple-values setting just duplicate the name
• resolve paths to absolute
• index and source shouldn't be in the list for the sake of security
• in addition to just mapping the config settings to the list it would be convenient to have a few other things:
  • configuration_file - absolute path to the configuration file
  • worker_pid - main process' pid file

[tomatolog]

should be fixed at 48c88cb there
added SHOW SETTINGS statement to get searchd config options to client along with configuration_file path and daemon worker_pid

[sanikolaev]

Output example:

mysql> show settings;
+------------------------+---------------------------------+
| Setting_name           | Value                           |
+------------------------+---------------------------------+
| configuration_file     | /home/snikolaev/configless.conf |
| worker_pid             | 5                               |
| listen                 | 127.0.0.1:9315:mysql            |
| data_dir               | /home/snikolaev/data            |
| pid_file               | /home/snikolaev/9315.pid        |
| log                    | /home/snikolaev/searchd.log     |
| query_log              | /home/snikolaev/query.log       |
| query_log_commands     | 1                               |
| not_terms_only_allowed | 1                               |
| binlog_path            | /home/snikolaev/data/binlog     |
| sphinxql_state         | /home/snikolaev/data/state.sql  |
+------------------------+---------------------------------+
11 rows in set (0.00 sec)

There are a few issues

1. another listen value is missing in the above output. The config is:

snikolaev@dev:~$ cat configless.conf
searchd {
   listen = 127.0.0.1:9315:mysql
   listen = 127.0.0.1:9316
...
}

2. common and indexer's values don't show up in the output:

snikolaev@dev:~$ cat settings.conf
searchd {
   listen = 127.0.0.1:9315:mysql
   listen = 127.0.0.1:9316
   data_dir = data
   pid_file = 9315.pid
   log = searchd.log
}

common {
   plugin_dir = /home/snikolaev
}

indexer {
   max_iops = 1
   on_file_field_error = skip_document
}

snikolaev@dev:~$ mysql -P9315 -h0 -e "show settings"
+--------------------+--------------------------------+
| Setting_name       | Value                          |
+--------------------+--------------------------------+
| configuration_file | /home/snikolaev/settings.conf  |
| worker_pid         | 5                              |
| listen             | 127.0.0.1:9315:mysql           |
| data_dir           | /home/snikolaev/data           |
| pid_file           | /home/snikolaev/9315.pid       |
| log                | /home/snikolaev/searchd.log    |
| binlog_path        | /home/snikolaev/data/binlog    |
| sphinxql_state     | /home/snikolaev/data/state.sql |
+--------------------+--------------------------------+

3. In the above output it's also seen that sphinxql_state is in the output, but it's not in the config. It's confusing since the idea of what's included in the output and what's not is not clear. If it's just a default which is output, then it's fine, it makes a lot of sense, but why don't we show all the other settings with their default values then?

4. adding searchd., common., indexer. suffixes would make it easier to recover a config from SHOW SETTINGS with a few lines script.

[sanikolaev]

In the above output it's also seen that sphinxql_state is in the output, but it's not in the config. It's confusing since the idea of what's included in the output and what's not is not clear. If it's just a default which is output, then it's fine, it makes a lot of sense, but why don't we show all the other settings with their default values then?

As discussed later it's better not to output all the settings with their default values by default since then it's difficult to distinguish default values from the values defined in config which may be important in some cases. But in some other cases it's important to see all the settings the instance is running with, for that we can implement SHOW SETTINGS OPTION format=all (the syntax is similar to SHOW THREADS OPTION format=all)

[githubmanticore]

➤ Stan commented:

I've just pushed fixes of points from this ticket but NOT the SHOW SETTINGS OPTION format=all as it needs another approach and could be done as another ticket if needed, ie

currently SHOW SETTINGS dumps lines from config file that was read on daemon load, it does not do any parsing, bounds checking and options value fixup.

However SHOW SETTINGS OPTION format=all will read actual values stored in daemon (after config got parsed, value bounds fixup or different kind of corrections) and need different printers for different kind of values.

[tomatolog]

should be done at a75173a

[githubmanticore]

➤ Sergey Nikolaev commented:

Reopening since there's a bug: empty binlog_path = results in smth like this:

mysql> show settings; 
+---------------------+--------------------------------+ 
| Setting_name        | Value                          | 
+---------------------+--------------------------------+ 
| configuration_file  | /home/snikolaev/min.conf       | 
| worker_pid          | 2929941                        | 
| searchd.listen      | 127.0.0.1:9315:mysql41         | 
| searchd.log         | /home/snikolaev/sphinx_min.log | 
| searchd.pid_file    | /home/snikolaev/9315.pid       | 
| searchd.binlog_path | /home/snikolaev/               | 

i.e. non-empty searchd.binlog_path which makes it impossible to distinguish from binlog_path = /home/snikolaev/

[githubmanticore]

➤ Stan commented:

should be fixed at d8c16a0