MySQL: Replication and local variables

MySQL

Can I use local variables in a trigger if I expect it to replicate on a slave ?

I have a trigger that uses a local variable @userId that is set prior to the triggering query. So every time a certain query is run the variable @userId is set and the trigger can make use of it.

My question is, can I expect the bin log to work correctly if my binlog_format is STATEMENT ?

I know from the documentation that session variables will not work, but they do not mention local variables.

in statement-based replication, session variables are not replicated properly when used in statements that update tables.

Best Answer

I have a trigger that uses a local variable @userId that is set prior to the triggering query.

Local variables are variables defined on a compound-statement level with this compound statement scope using DECLARE variable statement explicitly. Their name's first symbol cannot be @.

User-defined variables are session scope variables defined implicitly (in SET or INSERT INTO statement) by the first 2 symbols of their names (first is @ and second is not).

So you use user-devined, not local, variables.

User-defined variables are ignored by the replication at all.

Can I use local variables in a trigger if I expect it to replicate on a slave?

The answer is "No". Use custom service table.

I know from the documentation that session variables will not work, but they do not mention local variables.

Session variables are system variables duplicates with a session scope. They (when exists) override system variables values. They are not local or user-defined, they are a separate type of variables. As for user-defined variables their first two symbols are @ and not @ - it shows that their values may be set by user with a session scope (like user-defined variables).

Related Question