[MDEV-12684] Show what config file a sysvar got a value from Created: 2017-05-04  Updated: 2020-03-13  Resolved: 2019-10-14

Status: Closed
Project: MariaDB Server
Component/s: Variables
Fix Version/s: 10.5.0

Type: Task Priority: Critical
Reporter: Nilnandan Joshi Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None


 Description   

Extend INFORMATION_SCHEMA.SYSTEM_VARIABLES to show what .cnf file the value was read from. Note that included files (with !include and !includedir) should be shown, not just the master /etc/my.cnf.

Possible implementation, load_defaults() adds (may be on request only) special options in the argv list, like --file-name /my/cnf/path. And handle_options() uses that (comparing pointers, just like with --separator--).

This could go into GLOBAL_VALUE_ORIGIN column or into a new column, say, GLOBAL_VALUE_ORIGIN_INFO
which would have a file name for CONFIG, and, say, username for SQL.

Original description:

It's really difficult to check that MySQL/MariaDB is using my.cnf or not. Also we can't check that what are the default values of variables through mysql client. It would be really helpful if we can do like

1. Add 3rd column like "last updated by" (read cnf, set global, set session, default)
or
2. Make separate command like SHOW DEFAULT VARIABLES
or
3. Adding columns to information_schema.global_variables to check details

 Comments   
Comment by Will Fong [ 2017-05-04 ]

It would be helpful to get the filepath on where each setting was modified. This is helpful when there are duplicated settings, e.g. /etc/my.cnf and /etc/my.cnf.d/server.cnf

Comment by Sergei Golubchik [ 2017-05-04 ]

Did you check INFORMATION_SCHEMA.SYSTEM_VARIABLES table?
https://mariadb.com/kb/en/mariadb/information-schema-system_variables-table/

Comment by Elena Stepanova [ 2017-05-04 ]

The I_S table should answer at least some of those questions. I do however agree that it would be extremely useful to be able to see which cnf files the server used upon startup (even better if it shows which value it took from where, but even just a list of config files would be a big help).

Comment by Sergei Golubchik [ 2017-05-04 ]

Yes, this was part of the requests for the original I_S.SYSTEM_VARIABLES too. But it was quite difficult to implement.

Anyway, let's keep this as a task for extending I_S table.

Comment by Elena Stepanova [ 2017-05-04 ]

Maybe it would be simpler to implement a partial solution, like remembering which configs were read in which order, and storing it somewhere, be it in status, or in a variable, or in error log? Once a user has this list, it's fairly easy to find out manually which value is picked from which file. It won't be as shiny as reading it directly from I_S, but it will solve a lot of problems like "I've configured a variable but it isn't picked up".

Comment by Nilnandan Joshi [ 2017-11-15 ]

This seems added in mysql 8.0
http://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/

Comment by Hartmut Holzgraefe [ 2018-05-07 ]

I just had a look at the MySQL 8.0 implementation in P_S.VARIABLES_INFO,
and it seems to have almost everything I ever wished for:

  • shows by what mechanism a variable has been set
  • in case of option files: which file it was actually read from (resolving !include and !includedir)
  • in case of SQL level SET: when it was last changed, and by whom

The only thing missing from my wish list there would be an additional
VARIABLES_INFO_HISTORY table, to allow for tracking past changes ...

Comment by Ralf Gebhardt [ 2019-08-02 ]

Hi serg in one comment you wrote that this request is complex but the estimate is 2 days. Is 2 days a good estimate? The task is currently for 10.5, but not on our roadmap. It could be a good one if in the backlog for 10.5 if the 2 days are correct.

Comment by Sergei Golubchik [ 2019-08-05 ]

I think 2 days is about right

Comment by Sergei Golubchik [ 2019-09-30 ]

bar, please see https://github.com/MariaDB/server/compare/10.5...bb-10.5-serg

Comment by Alexander Barkov [ 2019-10-11 ]

Ok to push.

A proposal for tests:

Would be nice to run this query:

select * from information_schema.system_variables
 
where global_value_path is not null;

both for super-user and for non-privileged user.

Generated at Thu Feb 08 07:59:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.