[MXS-969] use_sql_variables_in=master can break functionality of important session variables Created: 2016-11-14  Updated: 2016-11-16  Resolved: 2016-11-16

Status: Closed
Project: MariaDB MaxScale
Component/s: readwritesplit
Affects Version/s: 2.0.1
Fix Version/s: 2.0.2

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: markus makela
Resolution: Fixed Votes: 1
Labels: None

Sprint: 2016-22

 Description   

When use_sql_variables_in=master, the ReadWriteSplit router will route queries like the following only to the master:

SET SESSION wsrep_sync_wait=1;

In this specific case, it's very important that wsrep_sync_wait get set on the slaves. If the variable is only routed to the master, the application might see errors when queries don't return the expected results.

https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sync_wait

Possible fixes:

  • Only let use_sql_variables_in affect user-defined variables, not system variables.
  • Only let use_sql_variables_in affect queries that read variables, not queries that write variables with SET.

 Comments   
Comment by markus makela [ 2016-11-14 ]

This seems like a "bug" in readwritesplit and the query classifier. Both user variable modifications and global options are treated as the same type.

2016-11-14 23:04:26   [7]  info   : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE, stmt: SET SESSION wsrep_sync_wait=1 
2016-11-14 23:04:26   [7]  info   : Route query to master       127.0.0.1:3000 <
 
2016-11-14 23:04:38   [7]  info   : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE, stmt: SET @a =1 
2016-11-14 23:04:38   [7]  info   : Route query to master       127.0.0.1:3000 <

The latter query in the log excerpt should be detected as a modification to a user variable.

Comment by Valerii Kravchuk [ 2016-11-15 ]

Same approach should probably be applied to:

  • set names charset;

and any

  • set session system_variable = new_value;

as session connecting via MaxScale assumes these are set and should not care what server it gets relayed to, master or slave.

Comment by markus makela [ 2016-11-16 ]

The user variables are now properly detected and processed. The readwritesplit will send all user variable modifications and reads to the master. Normal system variable modifications are sent to all nodes.

Generated at Thu Feb 08 04:03:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.