3
.. _configuration_variables:
8
Variables reflect the running configuration of Drizzle.
9
Variables allow you to configure (or reconfigure) certain values at runtime,
10
whereas :ref:`configuration_options` configure Drizzle at startup and cannot
11
be changed without restarting Drizzle.
13
Each variable has three aspects:
15
1. Corresponding option
19
Most variables have a corresponding option which
20
initialize the variable. For example, :option:`--datadir` corresponds to
21
and initializes the variable ``datadir``. The variable name is derived
22
from the option name by stripping the option's leading ``--`` and changing
23
all ``-`` (hyphens) and ``.`` (periods) to ``_`` (underscores).
25
Dynamic variables can be changed at runtime and the change takes affect
26
immediately. Most variables, however, are static which means that they
27
only reflect the value of their corresponding option. To change a static
28
variable, you must change its corresponding option, then restart Drizzle.
29
Certain variables are static and do not have a corresponding option because
30
they are purely informational, like ``version`` and ``version_comment``.
32
All variables are global, but each connection receives a copy of all global
33
variables called session variables. A connection can change dynamic session
34
variables without affecting the rest of the server or other sessions, or it
35
can potentially change dynamic global variables which affects all connections.
36
Changes to dynamic session variables are lost when the connection is closed,
37
and changes to dynamic global variables remain in affect until changed again.
39
.. note:: Configuration variables and :ref:`user_defined_variables` are different. :ref:`user_defined_variables` do not affect the configuration of Drizzle, and they are always dynamic, session variables.
41
.. _setting_variables:
46
The ``SET`` command sets global and session variables:
50
-- Set global variable
51
SET GLOBAL variable=value;
53
-- Set sesion variable
55
SET SESSION variable=value
57
If setting the variable succeeds, the command finishes silently like:
61
drizzle> SET SESSION max_allowed_packet=10485760;
62
Query OK, 0 rows affected (0.001475 sec)
64
Else, an error occurs if the variable cannot be changed:
68
drizzle> SET tmpdir="/tmp";
69
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable
71
.. _querying_variables:
76
The ``DATA_DICTIONARY.GLOBAL_VARIABLES`` and
77
``DATA_DICTIONARY.SESSION_VARIABLES`` are views for querying the
78
global and session variables respectively. For example:
82
drizzle> SELECT * FROM DATA_DICTIONARY.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'max%';
83
+--------------------------+----------------+
84
| VARIABLE_NAME | VARIABLE_VALUE |
85
+--------------------------+----------------+
86
| max_allowed_packet | 67108864 |
87
| max_error_count | 64 |
88
| max_heap_table_size | 16777216 |
89
| max_join_size | 2147483647 |
90
| max_length_for_sort_data | 1024 |
91
| max_seeks_for_key | 4294967295 |
92
| max_sort_length | 1024 |
93
| max_write_lock_count | -1 |
94
+--------------------------+----------------+