MariaDB – What Are System Dynamics Variables on MariaDB?

mariadbMySQL

I'm a nweeby and i really didn't understand what is a Dynamic Variable. I didn't found on documentation. And really what is the concept of Dynamic Vatiables.

Best Answer

For the MySQL/MariaDB server "VARIABLES":

Dynamic: Yes -- You can SET the variable and immediately see the effect.
Dynamic: No -- You need to change the configuration file (my.cnf) and restart the server.

That covers most cases; there are a few that are more complicated. Also, there is some move toward making more of the Variables "dynamic", so you will see differences based on Version of MySQL/MariaDB.

Also, this is usually the history of the values:

When the server starts, the GLOBAL copy of the variables are set from my.cnf or formulas, etc.
When a user connects, the GLOBAL values are copied into SESSION copies.
If dynamic, SET variable_name = value changes the SESSION value.

SET GLOBAL ... will change the default value for all subsequent connections, not your current connection. There are exceptions to this...

Some VARIABLES do not distinguish between SESSION and GLOBAL. For example, since InnoDB's buffer_pool is shared among connections, most settings are GLOBAL; there is no separate value for each SESSION.

(Sorry if I digressed too much; it gets messy. But usually you don't need to worry about these issues.)

Related Solutions

MySQL – Difference Between Uninitialized Variables and NULL

There are two ways to set a variable from a query, and they behave differently, especially when no record is matched.

You can SELECT ... INTO the variable...

select first_name into @z from actor where actor_id = 999999;

Or you can SET it to the value returned by a scalar subquery.

set @z = (select first_name from actor where actor_id = 999999);

Illustrating the difference with the sample database...

mysql> use sakila;
Database changed

Our variable starts out NULL.

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT first_name INTO @z FROM actor WHERE actor_id = 1;
Query OK, 1 row affected (0.00 sec)

We matched a row and our variable is set.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

We try to select a record that doesn't exist:

mysql> select first_name into @z from actor where actor_id = 999999;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

So what's in our variable now?

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

I think this explains the confusion. When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.

On the other hand, if we use the other construct, the variable is reset when no record is matched.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

mysql> set @z = (select first_name from actor where actor_id = 999999);
Query OK, 0 rows affected (0.00 sec)

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>

Mysql – Why some of thesql system variables are readonly

innodb_buffer_pool_size

The InnoDB Storage Engine has too many moving parts to just allow the buffer pool to be resized on the fly because the buffer pool interacts with system tablespace (ibdata1) and the transactions logs (ib_logfile0, ib_logfile1).

log-bin

In spite of the MySQL Documentation, you can change the effect of log-bin using another variable called SQL_LOG_BIN. Look at the effects:

SESSION

If you run

SET SQL_LOG_BIN = 0;

this will disable your DB Session from recording binlog events.

If you run

SET GLOBAL SQL_LOG_BIN = 0;

this will disable all incoming DB Sessions from recording binlog events.

General Log / Slow Log

As a DBA, I am very glad that the general log and slow log are dynamic. Otherwise

how could do perform any log rotation ?
how to stop these logs before diskspace runs out ?

Here are some posts I made about doing log rotations

Nov 10, 2012 : Redirecting MySQL errors and slow logs into another database
Mar 30, 2012 : MySQL slow log configuration
Jun 30, 2011 : How do I output MySQL logs to syslog?

Datetime Format

mysqld is usually cognizant of timezone and internalization of datetime displays. I do not see a beneficial reason for shifting the datetime format on demand. This may actual cause problems, not so much with mysqld, but with PHP/Python/Ruby scripts that read dates from the MySQL instance and may misinterpret '04/12/2013' as April 12th when it should be Dec 4th. That would be developer's nightmare to deal with. Having datetime format a read-only variable simply provides a safety net against such a death-defying move whereas proper planning of the database and code interoperability with dates would be in order.