This post is just a reminder on how to find which settings have been set on MySQL Server.
If you have modified some settings from a configuration file or during runtime (persisted or not), these two queries will show you what are the values and how they were set. Even if the value is the same as the default (COMPILED) in MySQL, if you have set it somewhere you will be able to see where you did it.
Global Variables
First, let’s list all the GLOBAL variables that we have configured in our server:
SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE != 'COMPILED';
This is an example of the output:
Session Variables
And now the same query for the session variables:
SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.session_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE = 'DYNAMIC';
And an example:
You can also find some more info in this previous post. If you are interested in default values of different MySQL version, I also invite you to visit Tomita Mashiro‘s online tool : https://tmtm.github.io/mysql-params/
In case you submit bugs to MySQL, I invite you to also add the output of these two queries.
