MySQL Bugs: #39221: System variables can't be set in a SELECT

Bug #39221	System variables can't be set in a SELECT
Submitted:	3 Sep 2008 18:12	Modified:	4 Sep 2008 6:33
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.51a, all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
You can't set a system variable with SELECT := as normal.

They seem to be usable in every other way a normal variable can be used in expressions.

Why does this matter?  Because some read-write splitting systems send everything except SELECT to the write server, meaning you can't set session variables on the read server.  For example,

insert into foo; -- goes to the master
set @@sort_buffer_size := <big_value>; -- goes to the master
select ... order by <columns_requiring_big_filesort>; -- goes to the slave

If I could set the sessions variable with SELECT, I could get it to go to the slave.

How to repeat:
mysql> select @@sort_buffer_size := 1024;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 1024' at line 1

Thank you for the report.

This is documented behavior: http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html does not contain any word about possibility to change system variables using SELECT @@foo := 'bar'; statement, so I can verify this report only as feature request.