0

I am coding a php-angularjs webapp and I am now creating a revision system with procedure and triggers on insert/update.

after the login I need to do:

$sql = 'SET @id_user := ' . (int)$_SESSION['user']['id_user'].';';

it works well if I select it (returns the user id as expected) but later on (1 min later) after a few queries and pages visited, when I update the customer table everything is good except the @user_id var which is null

Question: Is there a way I can set the var for my full session to avoid repeating the SET @var every time I need to log the modifications?

(it also needs to think that I have multiple admins)

Spaceman Spiff
  • 934
  • 1
  • 14
  • 32
LucJOB
  • 5
  • 2
  • Very strange logic. Every user would update this variable. Also, it sets only session value. I think that you cannot create and keep one connection for each user. – sectus Mar 05 '15 at 14:35
  • i tough it was user specific variable like a php session var, isn't it? – LucJOB Mar 05 '15 at 14:41
  • It's database session variable. If you have no persistent connection this variable exists only for single page loading. – sectus Mar 05 '15 at 14:43
  • that's what i suspected. So, what would be the best design approach to create a simple revision then? – LucJOB Mar 05 '15 at 14:46
  • I recommend you to use redis.io. and to manage logged in users in redis. – 4EACH Mar 05 '15 at 17:28

1 Answers1

2

User-defined variables are session-bound in MySQL. So, they exist in context of single connection.

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.

If you will reuse same connection, then by definition, variables will be same (so, "shared"). But if your request initializes different connections, then you will not be able to access variables from one session in another.

As a conclusion - user-defined variables were never intended for state transition, it is unsafe and, actually, a side-effect to rely on them even across several queries in context of one session. They might be used to "tune" your query (let's say, enumeration-related issues as sequences are absent in MySQL), but I would not recommend to use them for state transfer.

Alma Do
  • 37,009
  • 9
  • 76
  • 105