Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
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
Attachments
Activity
Did you check INFORMATION_SCHEMA.SYSTEM_VARIABLES table?
https://mariadb.com/kb/en/mariadb/information-schema-system_variables-table/
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).
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.
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".
This seems added in mysql 8.0
http://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/
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 ...
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.
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.
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