Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Show the default value for a variable

I am using SET GLOBAL <variable> = <value> to modify dynamic settings in mysql and I am wondering if there is some way to get the default value for each variable? For instance, if I use the following:

SET GLOBAL max_connections = 1000;

and then list the variable using:

SHOW GLOBAL VARIABLES LIKE 'max_connections';

I can see the modified value 1000, but is there possible to get the default value for this system variable without checking the configuration files?

I am using mysql 5.7 on ubuntu 16.04.

like image 951
Cyclonecode Avatar asked Apr 21 '19 12:04

Cyclonecode


People also ask

What is the default value of variable?

Variables of any "Object" type (which includes all the classes you will write) have a default value of null. All member variables of a Newed object should be assigned a value by the objects constructor function.

How can we set default value to the variable?

You can set the default values for variables by adding ! default flag to the end of the variable value. It will not re-assign the value, if it is already assigned to the variable.

What is the default value of a variable in Python?

Python has a different way of representing syntax and default values for function arguments. Default values indicate that the function argument will take that value if no argument value is passed during the function call. The default value is assigned by using the assignment(=) operator of the form keywordname=value.

What is a default value example?

For instance, if a bank's interest rate for the current financial year is 8 percent, this may be set as the default value. This relieves the user from having to enter it repeatedly into the database.


5 Answers

In MySQL 5.7 you can use performance_schema to get the variables.

Before modifying or set you can select the variable to see the default value then modify.

Method #1

SELECT 
    VARIABLE_VALUE
FROM
    performance_schema.global_variables
WHERE
    VARIABLE_NAME = 'max_connections';

Output #1

| VARIABLE_VALUE |
| :------------- |
| 151            |

Method #2

If you are not sure the exact name of variable use like, it can used used also in above query too.

SHOW GLOBAL VARIABLES LIKE 'max_connect%';

Output #2

Variable_name      | Value
:----------------- | :----
max_connect_errors | 100  
max_connections    | 151  

Method #3

SELECT @@GLOBAL.max_connections;

Output #3

| @@GLOBAL.max_connections |
| -----------------------: |
|                      151 |

Refer here db-fiddle

Note: If you need to have a history kind of thing then you need to create a table to store those values before changing.

P.S. There is one more type of variables session. By replacing global to session those variables can be changed but it will affect only to the current session.

credits: @scaisedge, @richard

like image 107
James Avatar answered Oct 24 '22 14:10

James


From the manual:

To set a global system variable value to the compiled-in MySQL default value [...] set the variable to the value DEFAULT.

That means you can do this:

SET @@GLOBAL.max_connections = 1234;

/*
 * Proceed in this order
 * 1) Backup current value
 * 2) Reset to default
 * 3) Read the current value
 * 4) Restore the backup value if necesssary
 */

SET @oldvalue = @@GLOBAL.max_connections;
SET @@GLOBAL.max_connections = DEFAULT;
SET @defvalue = @@GLOBAL.max_connections;
SET @@GLOBAL.max_connections = @oldvalue;

SELECT @@GLOBAL.max_connections AS `current value`
     , @defvalue AS `default value`
-- 1234 and 151

The @oldvalue and @defvalue are user variables.

like image 37
Salman A Avatar answered Oct 24 '22 15:10

Salman A


Could be selecting from information_schema.GLOBAL_STATUS

select VARIABLE_VALUE 
from information_schema.GLOBAL_STATUS 
where VARIABLE_NAME = 'max_connections';
like image 41
ScaisEdge Avatar answered Oct 24 '22 13:10

ScaisEdge


This might not be ideal but if it were me trying to solve the problem I'd create my own table with all of the initial values and reference that when I needed.

CREATE TABLE 
   default_variables
SELECT 
   * 
FROM 
   information_schema.GLOBAL_STATUS;

This would require you to spin up a new DB installation that is the same version as the one you're currently using, but I think you could use this method to solve your problem.

Simply export the data and import it wherever you need it.

I don't currently see any built-in way to query the defaults.

like image 37
Richard Cagle Avatar answered Oct 24 '22 14:10

Richard Cagle


Have you tried using this

mysqld --verbose --help

based on https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html it will display compiled-in default.

To see specific variable you may use grep:

mysqld --verbose --help | grep -i 'max-connections'
like image 44
elomat Avatar answered Oct 24 '22 15:10

elomat