一 问题描述
同事反馈,通过maxscale执行sql的时候,报错。
sql内容:
SELECT
*,
@rownum := @rownum + 1 AS r
FROM
(SELECT
'0dca79c35b' AS id
FROM
DUAL) T
报错:
SQL Error (1064): Routing query to backend failed. See the error log for further detail
maxscale日志报错:
2021-07-12 15:39:05 warning: (244392) The query can't be routed to all backend servers because it includes SELECT and SQL variable modifications which is not supported. Set use_sql_variables_in=master or split the query to two, where SQL variable modifications are done in the first and the SELECT in the second one.
二 解决办法
修改maxscale配置文件,在读写分离服务配置下面,添加一行配置:
use_sql_variables_in=master
示例:
#重启maxscale
systemctl restart maxscale