[MXS-78] "USE" statement gives unpredictable/unexpected results Created: 2015-04-01  Updated: 2015-04-14  Resolved: 2015-04-02

Status: Closed
Project: MariaDB MaxScale
Component/s: schemarouter
Affects Version/s: 1.1.0
Fix Version/s: 1.1.0

Type: Bug Priority: Blocker
Reporter: Kolbe Kegel (Inactive) Assignee: Timofey Turenko
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 6


 Description   

A literal "USE" statement sent from a client gives unexpected results when sent through schemarouter.

use DBI;
 
use Data::Dumper;
 
 
 
my $dbh = DBI->connect("DBI:mysql:host=127.0.0.1;port=$ARGV[0]", "kolbe", "kolbe");
 
 
 
foreach $i (1, 2) {
 
        $dbh->do("USE shard$i");
 
        print Dumper($dbh->selectrow_array("select database()"));
 
}

Connecting directly to the backend instances gives the expected results. shard1 only exists on the backend on port 3307. shard2 only exists on the backend on port 3308.

[skysql@max1 ~]$ perl mysqltest.pl 3307
 
$VAR1 = 'shard1';
 
DBD::mysql::db do failed: Unknown database 'shard2' at mysqltest.pl line 7.
 
$VAR1 = 'shard1';
 

[skysql@max1 ~]$ perl mysqltest.pl 3308
 
DBD::mysql::db do failed: Unknown database 'shard1' at mysqltest.pl line 7.
 
$VAR1 = undef;
 
$VAR1 = 'shard2';

But going through schemarouter gives unexpected and unpredictable behavior:

[skysql@max1 ~]$ perl mysqltest.pl 3310
 
$VAR1 = undef;
 
DBD::mysql::db do failed: Unknown database 'shard2' at mysqltest.pl line 7.
 
$VAR1 = 'shard2';
 
[skysql@max1 ~]$ perl mysqltest.pl 3310
 
$VAR1 = undef;
 
DBD::mysql::db do failed: Unknown database 'shard2' at mysqltest.pl line 7.
 
$VAR1 = 'shard2';
 
[skysql@max1 ~]$ perl mysqltest.pl 3310
 
DBD::mysql::db do failed: Unknown database 'shard1' at mysqltest.pl line 7.
 
$VAR1 = undef;
 
$VAR1 = 'shard2';
 
[skysql@max1 ~]$ perl mysqltest.pl 3310
 
DBD::mysql::db do failed: Unknown database 'shard1' at mysqltest.pl line 7.
 
$VAR1 = undef;
 
$VAR1 = 'shard2';
 
[skysql@max1 ~]$ perl mysqltest.pl 3310
 
DBD::mysql::db do failed: Unknown database 'shard1' at mysqltest.pl line 7.
 
$VAR1 = undef;
 
DBD::mysql::db do failed: Unknown database 'shard2' at mysqltest.pl line 7.
 
$VAR1 = 'shard2';



 Comments   
Comment by Kolbe Kegel (Inactive) [ 2015-04-01 ]

Here's the corresponding trace log:

2015-03-31 04:23:49   Servers and connection counts:
 
2015-03-31 04:23:49   MaxScale connections : 2 (2) in 	127.0.0.1:3308 RUNNING (only)
 
2015-03-31 04:23:49   MaxScale connections : 2 (2) in 	127.0.0.1:3307 RUNNING (only)
 
2015-03-31 04:23:49   Connected RUNNING (only) in 	127.0.0.1:3308
 
2015-03-31 04:23:49   Connected RUNNING (only) in 	127.0.0.1:3307
 
2015-03-31 04:23:49   Started Shard Router client session [72] for 'kolbe' from 127.0.0.1
 
2015-03-31 04:23:49   [72]  schemarouter: <mariadb2, information_schema>
 
2015-03-31 04:23:49   [72]  schemarouter: <mariadb2, mysql>
 
2015-03-31 04:23:49   [72]  schemarouter: <mariadb2, performance_schema>
 
2015-03-31 04:23:49   [72]  schemarouter: <mariadb2, shard2>
 
2015-03-31 04:23:49   [72]  schemarouter: <mariadb2, test>
 
2015-03-31 04:23:49   [72]  schemarouter: <mariadb1, shard1>
 
2015-03-31 04:23:49   [72]  Implicit COMMIT before executing the next command.
 
2015-03-31 04:23:49   [72]  > Cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE|QUERY_TYPE_ENABLE_AUTOCOMMIT|QUERY_TYPE_COMMIT, stmt: set autocommit=1 
2015-03-31 04:23:49   [72]  Session write, routing to all servers.
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:49   [72]  schemarouter: returning reply [OK] state [READY]  session [0x30809d0]
 
2015-03-31 04:23:49   [72]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard1 
2015-03-31 04:23:49   [72]  Session write, routing to all servers.
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:49   [72]  Backend 127.0.0.1:3308 already executing sescmd.
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:49   [72]  schemarouter: returning reply [OK] state [READY]  session [0x30809d0]
 
2015-03-31 04:23:49   [72]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:49   [72]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:49   [72]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:49   [72]  Backend 127.0.0.1:3308 processed reply and starts to execute active cursor.
 
2015-03-31 04:23:49   [72]  schemarouter: returning reply [RSET] state [READY]  session [0x30809d0]
 
2015-03-31 04:23:49   [72]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard2 
2015-03-31 04:23:49   [72]  Session write, routing to all servers.
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:49   [72]  schemarouter: returning reply [ERR] state [READY]  session [0x30809d0]
 
2015-03-31 04:23:49   [72]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:49   [72]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:49   [72]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:49   [72]  schemarouter: returning reply [RSET] state [READY]  session [0x30809d0]
 
2015-03-31 04:23:49   [72]  > Cmd: COM_QUIT, type: QUERY_TYPE_SESSION_WRITE, stmt:  
2015-03-31 04:23:49   [72]  Session write, routing to all servers.
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:49   [72]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:50   [72]  Stopped Shard Router client session [72]
 
2015-03-31 04:23:50   Servers and connection counts:
 
2015-03-31 04:23:50   MaxScale connections : 2 (2) in 	127.0.0.1:3308 RUNNING (only)
 
2015-03-31 04:23:50   MaxScale connections : 2 (2) in 	127.0.0.1:3307 RUNNING (only)
 
2015-03-31 04:23:50   Connected RUNNING (only) in 	127.0.0.1:3308
 
2015-03-31 04:23:50   Connected RUNNING (only) in 	127.0.0.1:3307
 
2015-03-31 04:23:50   Started Shard Router client session [73] for 'kolbe' from 127.0.0.1
 
2015-03-31 04:23:50   [73]  schemarouter: <mariadb1, information_schema>
 
2015-03-31 04:23:50   [73]  schemarouter: <mariadb1, mysql>
 
2015-03-31 04:23:50   [73]  schemarouter: <mariadb1, performance_schema>
 
2015-03-31 04:23:50   [73]  schemarouter: <mariadb1, shard1>
 
2015-03-31 04:23:50   [73]  schemarouter: <mariadb1, test>
 
2015-03-31 04:23:50   [73]  schemarouter: <mariadb2, shard2>
 
2015-03-31 04:23:50   [73]  Implicit COMMIT before executing the next command.
 
2015-03-31 04:23:50   [73]  > Cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE|QUERY_TYPE_ENABLE_AUTOCOMMIT|QUERY_TYPE_COMMIT, stmt: set autocommit=1 
2015-03-31 04:23:50   [73]  Session write, routing to all servers.
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:50   [73]  schemarouter: returning reply [OK] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:50   [73]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard1 
2015-03-31 04:23:50   [73]  Session write, routing to all servers.
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:50   [73]  Backend 127.0.0.1:3308 already executing sescmd.
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:50   [73]  schemarouter: returning reply [OK] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:50   [73]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:50   [73]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:50   [73]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:50   [73]  Backend 127.0.0.1:3308 processed reply and starts to execute active cursor.
 
2015-03-31 04:23:50   [73]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:50   [73]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard2 
2015-03-31 04:23:50   [73]  Session write, routing to all servers.
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:50   [73]  schemarouter: returning reply [ERR] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:50   [73]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:50   [73]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:50   [73]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:50   [73]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:50   [73]  > Cmd: COM_QUIT, type: QUERY_TYPE_SESSION_WRITE, stmt:  
2015-03-31 04:23:50   [73]  Session write, routing to all servers.
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:50   [73]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [73]  Stopped Shard Router client session [73]
 
2015-03-31 04:23:51   Servers and connection counts:
 
2015-03-31 04:23:51   MaxScale connections : 2 (2) in 	127.0.0.1:3308 RUNNING (only)
 
2015-03-31 04:23:51   MaxScale connections : 2 (2) in 	127.0.0.1:3307 RUNNING (only)
 
2015-03-31 04:23:51   Connected RUNNING (only) in 	127.0.0.1:3308
 
2015-03-31 04:23:51   Connected RUNNING (only) in 	127.0.0.1:3307
 
2015-03-31 04:23:51   Started Shard Router client session [74] for 'kolbe' from 127.0.0.1
 
2015-03-31 04:23:51   [74]  schemarouter: <mariadb1, information_schema>
 
2015-03-31 04:23:51   [74]  schemarouter: <mariadb1, mysql>
 
2015-03-31 04:23:51   [74]  schemarouter: <mariadb1, performance_schema>
 
2015-03-31 04:23:51   [74]  schemarouter: <mariadb1, shard1>
 
2015-03-31 04:23:51   [74]  schemarouter: <mariadb1, test>
 
2015-03-31 04:23:51   [74]  schemarouter: <mariadb2, shard2>
 
2015-03-31 04:23:51   [74]  Implicit COMMIT before executing the next command.
 
2015-03-31 04:23:51   [74]  > Cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE|QUERY_TYPE_ENABLE_AUTOCOMMIT|QUERY_TYPE_COMMIT, stmt: set autocommit=1 
2015-03-31 04:23:51   [74]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [74]  schemarouter: returning reply [OK] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:51   [74]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard1 
2015-03-31 04:23:51   [74]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [74]  Backend 127.0.0.1:3308 already executing sescmd.
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [74]  Backend 127.0.0.1:3308 processed reply and starts to execute active cursor.
 
2015-03-31 04:23:51   [74]  schemarouter: returning reply [ERR] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:51   [74]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:51   [74]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:51   [74]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:51   [74]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:51   [74]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard2 
2015-03-31 04:23:51   [74]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [74]  schemarouter: returning reply [OK] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:51   [74]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:51   [74]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:51   [74]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:51   [74]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8afc001750]
 
2015-03-31 04:23:51   [74]  > Cmd: COM_QUIT, type: QUERY_TYPE_SESSION_WRITE, stmt:  
2015-03-31 04:23:51   [74]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [74]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [74]  Stopped Shard Router client session [74]
 
2015-03-31 04:23:51   Servers and connection counts:
 
2015-03-31 04:23:51   MaxScale connections : 2 (2) in 	127.0.0.1:3308 RUNNING (only)
 
2015-03-31 04:23:51   MaxScale connections : 2 (2) in 	127.0.0.1:3307 RUNNING (only)
 
2015-03-31 04:23:51   Connected RUNNING (only) in 	127.0.0.1:3308
 
2015-03-31 04:23:51   Connected RUNNING (only) in 	127.0.0.1:3307
 
2015-03-31 04:23:51   Started Shard Router client session [75] for 'kolbe' from 127.0.0.1
 
2015-03-31 04:23:51   [75]  schemarouter: <mariadb2, information_schema>
 
2015-03-31 04:23:51   [75]  schemarouter: <mariadb2, mysql>
 
2015-03-31 04:23:51   [75]  schemarouter: <mariadb2, performance_schema>
 
2015-03-31 04:23:51   [75]  schemarouter: <mariadb2, shard2>
 
2015-03-31 04:23:51   [75]  schemarouter: <mariadb2, test>
 
2015-03-31 04:23:51   [75]  schemarouter: <mariadb1, shard1>
 
2015-03-31 04:23:51   [75]  Implicit COMMIT before executing the next command.
 
2015-03-31 04:23:51   [75]  > Cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE|QUERY_TYPE_ENABLE_AUTOCOMMIT|QUERY_TYPE_COMMIT, stmt: set autocommit=1 
2015-03-31 04:23:51   [75]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [75]  schemarouter: returning reply [OK] state [READY]  session [0x7f8b04000c30]
 
2015-03-31 04:23:51   [75]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard1 
2015-03-31 04:23:51   [75]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [75]  Backend 127.0.0.1:3308 already executing sescmd.
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [75]  Backend 127.0.0.1:3308 processed reply and starts to execute active cursor.
 
2015-03-31 04:23:51   [75]  schemarouter: returning reply [ERR] state [READY]  session [0x7f8b04000c30]
 
2015-03-31 04:23:51   [75]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:51   [75]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:51   [75]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:51   [75]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8b04000c30]
 
2015-03-31 04:23:51   [75]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard2 
2015-03-31 04:23:51   [75]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:51   [75]  Backend 127.0.0.1:3307 already executing sescmd.
 
2015-03-31 04:23:51   [75]  schemarouter: returning reply [OK] state [READY]  session [0x7f8b04000c30]
 
2015-03-31 04:23:51   [75]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:51   [75]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:51   [75]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:51   [75]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8b04000c30]
 
2015-03-31 04:23:51   [75]  > Cmd: COM_QUIT, type: QUERY_TYPE_SESSION_WRITE, stmt:  
2015-03-31 04:23:51   [75]  Session write, routing to all servers.
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:51   [75]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:52   [75]  Stopped Shard Router client session [75]
 
2015-03-31 04:23:52   Servers and connection counts:
 
2015-03-31 04:23:52   MaxScale connections : 2 (2) in 	127.0.0.1:3308 RUNNING (only)
 
2015-03-31 04:23:52   MaxScale connections : 2 (2) in 	127.0.0.1:3307 RUNNING (only)
 
2015-03-31 04:23:52   Connected RUNNING (only) in 	127.0.0.1:3308
 
2015-03-31 04:23:52   Connected RUNNING (only) in 	127.0.0.1:3307
 
2015-03-31 04:23:52   Started Shard Router client session [76] for 'kolbe' from 127.0.0.1
 
2015-03-31 04:23:52   [76]  schemarouter: <mariadb1, information_schema>
 
2015-03-31 04:23:52   [76]  schemarouter: <mariadb1, mysql>
 
2015-03-31 04:23:52   [76]  schemarouter: <mariadb1, performance_schema>
 
2015-03-31 04:23:52   [76]  schemarouter: <mariadb1, shard1>
 
2015-03-31 04:23:52   [76]  schemarouter: <mariadb1, test>
 
2015-03-31 04:23:52   [76]  schemarouter: <mariadb2, shard2>
 
2015-03-31 04:23:52   [76]  Implicit COMMIT before executing the next command.
 
2015-03-31 04:23:52   [76]  > Cmd: COM_QUERY, type: QUERY_TYPE_GSYSVAR_WRITE|QUERY_TYPE_ENABLE_AUTOCOMMIT|QUERY_TYPE_COMMIT, stmt: set autocommit=1 
2015-03-31 04:23:52   [76]  Session write, routing to all servers.
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:52   [76]  schemarouter: returning reply [OK] state [READY]  session [0x7f8af800c350]
 
2015-03-31 04:23:52   [76]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard1 
2015-03-31 04:23:52   [76]  Session write, routing to all servers.
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:52   [76]  Backend 127.0.0.1:3307 already executing sescmd.
 
2015-03-31 04:23:52   [76]  schemarouter: returning reply [ERR] state [READY]  session [0x7f8af800c350]
 
2015-03-31 04:23:52   [76]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:52   [76]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:52   [76]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:52   [76]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8af800c350]
 
2015-03-31 04:23:52   [76]  > Cmd: COM_QUERY, type: QUERY_TYPE_SESSION_WRITE, stmt: USE shard2 
2015-03-31 04:23:52   [76]  Session write, routing to all servers.
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:52   [76]  Backend 127.0.0.1:3307 already executing sescmd.
 
2015-03-31 04:23:52   [76]  Backend 127.0.0.1:3307 processed reply and starts to execute active cursor.
 
2015-03-31 04:23:52   [76]  Backend 127.0.0.1:3307 processed reply and starts to execute active cursor.
 
2015-03-31 04:23:52   [76]  schemarouter: returning reply [ERR] state [READY]  session [0x7f8af800c350]
 
2015-03-31 04:23:52   [76]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database() 
2015-03-31 04:23:52   [76]  schemarouter: Routing query to first available backend.
 
2015-03-31 04:23:52   [76]  Route query to 	127.0.0.1:3308 <
 
2015-03-31 04:23:52   [76]  schemarouter: returning reply [RSET] state [READY]  session [0x7f8af800c350]
 
2015-03-31 04:23:52   [76]  > Cmd: COM_QUIT, type: QUERY_TYPE_SESSION_WRITE, stmt:  
2015-03-31 04:23:52   [76]  Session write, routing to all servers.
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3308
 
2015-03-31 04:23:52   [76]  Route query to slave	127.0.0.1:3307 <
 
2015-03-31 04:23:53   [76]  Stopped Shard Router client session [76]

Comment by markus makela [ 2015-04-01 ]

Can you retest this with auth_all_servers=1 parameter for the service?

Comment by markus makela [ 2015-04-01 ]

If you can, please attach the MaxScale.cnf file.

Comment by Kolbe Kegel (Inactive) [ 2015-04-02 ]

This issue does still occur when using auth_all_servers=1.

Here's the relevant part of the config:

[mariadb1]
 
type=server
 
address=127.0.0.1
 
port=3307
 
protocol=MySQLBackend
 
 
 
[mariadb2]
 
type=server
 
address=127.0.0.1
 
port=3308
 
protocol=MySQLBackend
 
 
 
[Shard Router]
 
type=service
 
router=schemarouter
 
servers=mariadb1,mariadb2
 
user=maxuser
 
passwd=maxpass
 
auth_all_servers=1
 
 
 
[Shard Listener]
 
type=listener
 
service=Shard Router
 
protocol=MySQLClient
 
port=3310

Comment by markus makela [ 2015-04-13 ]

This was due to literal USE DATABASE statements not being classified as change database queries. They are now parsed and classified as a database change queries and treated the same way COM_INIT_DB packages are treated.

Comment by Timofey Turenko [ 2015-04-14 ]

test added to 'sharding' test case

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