Question :
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.
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).