Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
2.1.6
-
None
Description
When a ReadWriteSplit session executes a read-only query outside of a transaction, it will get routed to a slave. When this happens, MaxScale does not use the master connection at all. Therefore, if a session executes only read-only queries, then its master connection could be allowed to time out due to wait_timeout.
This can easily be reproduced with the following:
mysql -h 127.0.0.1 -u maxscale -ppassword <<EOF
|
CREATE DATABASE IF NOT EXISTS db1;
|
CREATE TABLE IF NOT EXISTS db1.tab (str varchar(50));
|
SET wait_timeout=10;
|
SELECT 1;
|
SELECT SLEEP(5);
|
SELECT 1;
|
SELECT SLEEP(5);
|
SELECT 1;
|
SELECT SLEEP(5);
|
INSERT INTO db1.tab (str) VALUES ('str');
|
EOF
|
Output:
[ec2-user@ip-172-30-0-106 ~]$ mysql -h 127.0.0.1 -u maxscale -ppassword <<EOF
|
> CREATE DATABASE IF NOT EXISTS db1;
|
> CREATE TABLE IF NOT EXISTS db1.tab (str varchar(50));
|
> SET wait_timeout=10;
|
> SELECT 1;
|
> SELECT SLEEP(5);
|
> SELECT 1;
|
> SELECT SLEEP(5);
|
> SELECT 1;
|
> SELECT SLEEP(5);
|
> INSERT INTO db1.tab (str) VALUES ('str');
|
> EOF
|
1
|
1
|
SLEEP(5)
|
0
|
1
|
1
|
SLEEP(5)
|
0
|
1
|
1
|
ERROR 2013 (HY000) at line 9: Lost connection to MySQL server during query
|
If this problem is encounted in a Java application, then the error is a bit different. I've also attached a Java program that can be used to reproduce the issue.
Output:
[ec2-user@ip-172-30-0-106 ~]$ export CLASSPATH='/home/ec2-user/mariadb-java-client-2.1.0.jar:.'
|
[ec2-user@ip-172-30-0-106 ~]$ javac ./TestMasterConnIdle.java
|
[ec2-user@ip-172-30-0-106 ~]$ java TestMasterConnIdle
|
Checking thread 10.
|
Checking thread 8.
|
Checking thread 11.
|
Checking thread 9.
|
Checking thread 10.
|
Checking thread 8.
|
Checking thread 11.
|
SQLException: (conn:2073) Could not send query: unexpected end of stream, read 0 bytes from 4
|
SQLState: 08
|
VendorError: 0
|
Checking thread 9.
|
Checking thread 10.
|
SQLException: (conn:2074) Could not send query: unexpected end of stream, read 0 bytes from 4
|
SQLState: 08
|
VendorError: 0
|
Checking thread 8.
|
SQLException: (conn:2072) Could not send query: unexpected end of stream, read 0 bytes from 4
|
SQLState: 08
|
VendorError: 0
|
Checking thread 9.
|
SQLException: (conn:2075) Could not send query: unexpected end of stream, read 0 bytes from 4
|
SQLState: 08
|
VendorError: 0
|
As a potential fix, maybe ReadWriteSplit should route a COM_PING to a session's master connection whenever a query is routed to a slave connection?