Cannot set a global variable on MySQL

Question

I'm using MySQL in localhost (in ubuntu and also in windows). I want to set a global variable, and I have tried in all ways but even though I get an "ok" message from mysql, then when I do the "select @var" it allways says "NULL". I've tried:

set global var=17;
set @global.var=17;
set @@var=17;

Can anyone help me?

Thanks in advance.

ps: I have the SUPER privilege.

we can't create user-defined variables in global/session scope. — Angad Bansode, Oct 09 '19 at 06:48

spencer7593 · Accepted Answer · 2019-08-16T14:52:00.937

20

The variable name var does not reference a valid system variable. The GLOBAL and SESSION keywords in the SET statement are used for specifying the scope when setting MySQL system variables, not MySQL user variables.

Try for example:

SELECT @@global.net_read_timeout ;

SET GLOBAL net_read_timeout = 45 ;

SELECT @@global.net_read_timeout ;

http://dev.mysql.com/doc/refman/8.0/en/set-statement.html

http://dev.mysql.com/doc/refman/5.5/en/set-statement.html

edited Aug 16 '19 at 14:52

answered Nov 21 '13 at 02:56

spencer7593

106,611
15
112
140

2

So, is not possible to set and store permanently a variable with a any name you choose? – leonardo_palma Nov 21 '13 at 22:43
1

That is correct. the `SET GLOBAL` is for modifying predefined MySQL *system variables*. It's not used to set *user defined variables*. I don't have an explanation for MySQL responding with an "OK" message, rather than emitting an error. Perhaps there isn't a standard SQLSTATE that accurately represents the error, maybe it was just easier to return a success code; or, maybe it was a design decision regarding future upgrades, and some degree of backwards compatibility for deprecated and removed system variables. – spencer7593 Jul 21 '15 at 20:44
So say `SET GLOBAL SQL_MODE = ''` is the same as `SET @@SQL_MODE = ''` right? So the @'s kinda replace the global keyword if I'm not wrong, thanks. – Thielicious Jan 22 '20 at 08:23
@Thielicious: not quite. `@@sql_mode` is shorthand for `@@session.sql_mode`. The `sql_mode` system variable can be set at the GLOBAL and SESSION level. The session variable inherits the value of the corresponding global variable when the session is started. Some MySQL system variables are only global; while others, like `sql_mode`, are both global and session variables. – spencer7593 Jan 22 '20 at 16:45

Alois Heimer · Answer 2 · 2019-08-16T09:02:53.020

According to the MySQL 5.0 Reference Manual:

User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client session are automatically freed when that client exits.

You could consider using an extension like MySQL Global User Variables UDF (old archived link) to use global (persistent shared) variables.

Super Kai - Kazuya Ito · Answer 3 · 2022-09-11T16:09:07.450

On MySQL, you cannot create custom global or session system variables but can change existed global or session system variables as shown below:

SET GLOBAL max_connections = 1000;    -- Existed global system variable
SET SESSION sql_mode = 'TRADITIONAL'; -- Existed session system variable

And, you can create user-defined(custom) variables which are removed when you exit(log out) a session as shown below. User-defined variables exist only in the current session so they cannot be seen by other sessions unless you use performance_schema.user_variables_by_thread:

SET @first_name = 'John', @last_name = 'Smith';

Cannot set a global variable on MySQL

3 Answers3