SET user defined variable in mysql return null?

Question

When I created a user defined variable using

SET @a =10;

also i checked

SET @a := 10;

the above query executed successfully. while accessing the variable it gives me NULL value instead of 10. I accessed the defined variables using this query

SELECT @a;

Works fine for me. Which version of MySQL are you using? Where are you executing the queries? — Naltharial
– Naltharial, Commented Feb 6, 2012 at 12:05
Using the latter syntax: SET @a := 10; it just works for me. Variables are tied to a tcp connection, are you trying everything during the same session? — Evert
– Evert, Commented Feb 6, 2012 at 12:06
You should set and access user variable in the same session. ...SET @a=10 and SET @a:=10 are equal. — Devart
– Devart, Commented Feb 6, 2012 at 12:07
the version i use is MySQL 5.1.49-1ubuntu8.1,i am executing this in mysql query browser — Sam Arul Raj T
– Sam Arul Raj T, Commented Feb 6, 2012 at 12:08
Try to run 'SELECT CONNECTION_ID();' query after 'SET @a= 10;' and after 'SELECT @a;' to check that session it the same. — Devart
– Devart, Commented Feb 6, 2012 at 15:42

D Mac · Accepted Answer · 2012-03-12 19:36:11Z

2

The only way this can happen (in a client session) - and the way it happens for me from time to time - is you get bit by a short timeout on the client connection. It goes like this:

mysql> set @a = 10;

mysql> [wait for N+1 minutes, where N is the client timeout]

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

You have to initialize your variables and use them within a contiguous client session. When the session goes away, you lose all your variables.

The other explanation, as pointed out by others in the comments, is that the commands are hitting the server from different connections; your problem might not be a timeout, but that you are originating the "SET ..." and "SELECT ..." commands in different connections. User variables are not shared across different connections.

edited Mar 12, 2012 at 19:36

answered Mar 12, 2012 at 19:01

D Mac

3,8191 gold badge27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SET user defined variable in mysql return null?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related