WL#9720: SET PERSIST capture user, host and timestamp
Affects: Server-8.0 — Status: Complete
We have had good reception to SET PERSIST. One of the feedback requests has been that it would be useful to capture the user + a timestamp of the last configuration change. i.e. mysql> select * from performance_schema.variables_info where variable_name= 'max_connections'\G ********************************** VARIABLE_NAME: max_connections VARIABLE_SOURCE: PERSISTED VARIABLE_PATH: C:\..... MIN_VALUE: 1 MAX_VALUE: 100000 SET_TIME: 2016-10-12 12:01:01 SET_BY: morgan@localhost The names 'set_time' and 'set_by' are borrowed from the table SYS.sys_config. The data types are as follows: set_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, set_by VARCHAR(128) User Documentation ================== * https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html * https://dev.mysql.com/doc/refman/8.0/en/reset-persist.html * https://dev.mysql.com/doc/refman/8.0/en/persisted-variables-table.html * https://dev.mysql.com/doc/refman/8.0/en/variables-info-table.html * https://dev.mysql.com/doc/refman/8.0/en/using-system-variables.html
F1) New columns SET_TIME, SET_USER and SET_HOST will be added to performance_schema.variables_info table. F2) performance_schema.variables_info.SET_TIME column represents timestamp of when the variable was set. F3) performance_schema.variables_info.SET_USER column represents who (which user) has set this variable. F4) performance_schema.variables_info.SET_HOST column represents host on which this variable is set. F5) Successful execution of SET statement will update these columns accordingly. F6) For all variables which are not SET will have default values for these columns (SET_TIME, SET_USER and SET_HOST). F7) RESET PERSIST statement should have no effect on these new columns.
Three new columns will be added to performance_schema.variables_info table. SET_TIME - represents timestamp of when a configuration variable was changed. SET_USER - represents who has changed the configuration variable. SET_HOST - represents host on which the configuration variable was changed. New table definition will be as below: CREATE TABLE `variables_info` ( `VARIABLE_NAME` varchar(64) NOT NULL, `VARIABLE_SOURCE` enum('COMPILED','GLOBAL','SERVER','EXPLICIT','EXTRA','USER', 'LOGIN','COMMAND_LINE','PERSISTED','DYNAMIC') DEFAULT 'COMPILED', `VARIABLE_PATH` varchar(1024) DEFAULT NULL, `MIN_VALUE` varchar(64) DEFAULT NULL, `MAX_VALUE` varchar(64) DEFAULT NULL, `SET_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `SET_USER` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `SET_HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 Whenever a system variable is changed using SET statement performance_schema.variables_info table will get updated accordingly. Note: In case of proxy users, SET_USER column will be set to proxied user (user against which privilege checking is done).
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.