Fixed: "ERROR 1229 (HY000): Variable is a GLOBAL variable and should be set with SET GLOBAL" in MySQL
SET SESSION innodb_print_all_deadlocks = ON failed with ERROR 1229 (HY000): Variable 'innodb_print_all_deadlocks' is a GLOBAL variable and should be set with SET GLOBAL. The message tells you exactly what to do. There's also a catch, which is that it takes an administrator.
What I Was Doing
I wanted to log deadlocks on MySQL 9.7.4 in Docker, and, trying to keep the change small, I set it for my own session:
SET SESSION innodb_print_all_deadlocks = ON;
What I Expected
Query OK.
What I Got
ERROR 1229 (HY000): Variable 'innodb_print_all_deadlocks' is a GLOBAL variable and should be set with SET GLOBAL
What I Checked
The variable's scope. The InnoDB options page lists a scope for each one. This one says Global, while innodb_lock_wait_timeout says Global, Session.
Why It Happens
Deadlock logging applies to the whole server: there's one log, and no way for one session to have its own setting. So it has only one value, and you can't set it for a session.
The Fix
Do what the message says, from an administrator's account:
SET GLOBAL innodb_print_all_deadlocks = ON;
From an ordinary account, it fails with ERROR 1227.
After the Fix
Query OK, 0 rows affected (0.001 sec)
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Forgetting it affects everyone | The whole server logs deadlocks | That's what it's for, but know it |
| Forgetting it ends at a restart | It goes back to off | Use a startup option |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
SUPER or SYSTEM_VARIABLES_ADMIN | The account isn't an administrator | The fix |
is a read only variable | It can only be set at startup | ERROR 1238 |
is a SESSION variable and can't be used with SET GLOBAL | The opposite case | Use SET SESSION |
Conclusion
ERROR 1229 means the setting exists only for the whole server. Use SET GLOBAL, from an administrator's account.
Additional Resources
- Using System Variables — global and session scope.
