[MXS-1414] About Presistent Connection Mysql Gone away Created: 2017-09-14  Updated: 2017-09-14  Resolved: 2017-09-14

Status: Closed
Project: MariaDB MaxScale
Component/s: readconnroute, readwritesplit
Affects Version/s: 2.1.5
Fix Version/s: 2.1.8

Type: Bug Priority: Major
Reporter: make Yuan Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: maxscale
Environment:

CentOS release 6.7 (Final)

Issue Links:
Duplicate
duplicates MXS-1396 Persistent connections hang with Perc... Closed

 Description   

When I Started Persistent Connections. I got a lot of feedback from phper about
MySQL server has gone away, and I Try persistmaxtime bigger or a smaller one , But
Same situation . and I was think , When a connection is found, the connection in the pool has not timed out，next second，The backend connection has freed this connection cause this situation ??? and When I close Persistent Connections is ok ~

About php log

2017/09/14 10:37:17 192.168.37.146 seller.juanpi.com/goods/needClick02/sa_id/137
 
ERR: 2006:MySQL server has gone away
 
 [ SQL语句 ] : SELECT u_uid as uid,u_username as username,u_email as email,u_mobile as mobile,u_password as password,u_status as isrank,u_avatar as pic,u_regtime as addtime,u_token as token FROM js_member.js_users_10 WHERE ( u_uid = 23800579 ) LIMIT 1  
ERR: 2006:MySQL server has gone away
 
 [ SQL语句 ] : SELECT sum(sd_trade_money) as money FROM js_settlement_old.js_settlementpop_detail WHERE ( sd_uid = '23800579' ) AND ( sd_trade_status IN (2,3) ) AND ( sd_trade_type IN (9,12,16) ) AND ( sd_trade_money > 0 ) LIMIT 1  
ERR: 2006:MySQL server has gone away
 
 [ SQL语句 ] : SELECT `bi_is_overseas` FROM js_business.js_business_info WHERE ( `uid` = '23800579' ) LIMIT 1  
ERR: 2006:MySQL server has gone away
 
 [ SQL语句 ] : SELECT * FROM js_scm.js_storage_agreement WHERE ( `sa_id` = 137 ) AND ( `sa_company_id` = '23800579' ) LIMIT 1  
ERR: 2006:MySQL server has gone away
 
 [ SQL语句 ] : SELECT `bc_addtime`,`ba_endtime` FROM js_business.js_business_accept WHERE ( `ba_uid` = '23800579' ) AND ( `ba_type` = 2 ) AND ( `ba_sa_id` = null ) AND ( `ba_endtime` > 1505356414 ) LIMIT 1

part of maxscale.cnf

[server1]
 
type=server
 
address=192.168.143.233
 
port=3310
 
protocol=MySQLBackend
 
#persistpoolmax=100
 
#persistmaxtime=28800
 
serv_weight=2
 
 
 
[server2]
 
type=server
 
address=192.168.143.233
 
port=3309
 
protocol=MySQLBackend
 
serv_weight=1
 
#persistpoolmax=100
 
#persistmaxtime=28800
 
 
 
[LoadBlance Service]
 
type=service
 
router=readconnroute
 
servers=server2,server1
 
user=maxopt
 
passwd=06B65119699B323CEAC99134D694B4CE
 
router_options=slave
 
#filters=DBfirewall
 
 
 
 
 
[RW test]
 
type=service
 
router=readwritesplit
 
servers=server1,server2
 
user=maxopt
 
passwd=06B65119699B323CEAC99134D694B4CE
 
max_slave_connections=100%
 
max_slave_replication_lag= 100
 
router_options=slave_selection_criteria=LEAST_ROUTER_CONNECTIONS
 
router_options=master_failure_mode=fail_on_write,retry_failed_reads=false
 
filters=Hint
 
 
 
[Read-Only Listener]
 
type=listener
 
service=LoadBlance Service
 
address=0.0.0.0  
protocol=MySQLClient
 
port=4008
 
 
 
[Read-Write Listener]
 
type=listener
 
service=RW test
 
address=0.0.0.0 
protocol=MySQLClient
 
port=4006



 Comments   
Comment by markus makela [ 2017-09-14 ]

Please try upgrading to MaxScale 2.1.7 and see if the problem goes away. MXS-1351, MXS-1375 and MXS-1366 are all fixed in 2.1.7 and these bugs could explain the failures.

Comment by make Yuan [ 2017-09-14 ]

And I update maxscale to maxscale-2.1.7, start with same maxscale.cnf
repetitive execution “ mysql -u juanpi -P 4006 -h 192.168.143.233 -pjuanpi make -e "select * from student limit 1" at shell , also can tips " Lost connection"

LIKE this:

[root@sz-4PG5S62-143 log]# mysql -u juanpi -P 4008 -h xxx -pxxx make -e "select * from student limit 1"
Warning: Using a password on the command line interface can be insecure.
----------------------------------+
| id | name | name2 | name3 |
----------------------------------+
| 18 | wrHAQ6anr3PqV1k0cByZ | NULL | NULL |
----------------------------------+
[root@sz-4PG5S62-143 log]# mysql -u juanpi -P 4008 -h xxx -pxxx make -e "select * from student limit 1"
Warning: Using a password on the command line interface can be insecure.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

Comment by make Yuan [ 2017-09-14 ]

maxscale log

2017-09-14 15:30:53.414 info : (126) [readwritesplit] > Autocommit: [enabled], trx is [not open], cmd: (0x03) MYSQL_COM_QUERY, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@version_comment limit 1
2017-09-14 15:30:53.414 info : (126) [readwritesplit] Route query to slave [192.168.143.233]:3310 <
2017-09-14 15:30:53.414 info : (126) [MySQLBackend] Sent COM_CHANGE_USER
2017-09-14 15:30:53.414 error : (126) [MySQLBackend] Received AuthSwitchRequest to 'mysql_native_password' when 'mysql_native_password' was expected
2017-09-14 15:30:53.414 info : (126) [readwritesplit] > Autocommit: [enabled], trx is [not open], cmd: (0x03) MYSQL_COM_QUERY, type: QUERY_TYPE_READ, stmt: select * from student limit 1
2017-09-14 15:30:53.414 info : (126) [readwritesplit] Route query to master [192.168.143.233]:3309 <
2017-09-14 15:30:53.414 info : (126) [MySQLBackend] Sent COM_CHANGE_USER
2017-09-14 15:30:53.414 error : (126) [MySQLBackend] Received AuthSwitchRequest to 'mysql_native_password' when 'mysql_native_password' was expected
2017-09-14 15:30:53.414 info : Stopped RW test client session [126]

Comment by markus makela [ 2017-09-14 ]

OK, then you are suffering from MXS-1396. It will be fixed in 2.1.8.

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