[#MXS-1397] ReadWriteSplit's master connection can time out if session only issues read-only queries

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

SLEEP(5)

SLEEP(5)

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?

[MXS-1397] ReadWriteSplit's master connection can time out if session only issues read-only queries Created: 2017-09-07 Updated: 2020-08-25 Resolved: 2017-09-08
Status:	Closed
Project:	MariaDB MaxScale
Component/s:	readwritesplit
Affects Version/s:	2.1.6
Fix Version/s:	2.2.0

[MXS-1397] ReadWriteSplit's master connection can time out if session only issues read-only queries Created: 2017-09-07 Updated: 2020-08-25 Resolved: 2017-09-08