search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.04k stars 983 forks source link

client app OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query ([Errno 104] Connection reset by peer)') #1466

Open daweizhang opened 6 years ago

daweizhang commented 6 years ago

Hi renecannao , Thank for you support the proxysql for mysql server, but we use it , we have some issue , sometime app have LOST connection error. os version is centos 7.x, and proxysql is 1.4.7. our architecture is following:

python sqlalchemy (pool) ---> proxysql ----> mysql server

error log: File "./src/database/DatabaseMgr.py", line 238, in _query return self.engine.execute(sql, clause).fetchall() File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1752, in execute return connection.execute(statement, *multiparams, **params) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 721, in execute return self._execute_text(object, multiparams, params) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 870, in _execute_text statement, parameters File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context context) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in _handle_dbapi_exception exc_info File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context context) File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 146, in execute result = self._query(query) File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 296, in _query conn.query(q) File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 819, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1001, in _read_query_result result.read() File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1285, in read first_packet = self.connection._read_packet() File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 945, in _read_packet packet_header = self._read_bytes(4) File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 978, in _read_bytes "Lost connection to MySQL server during query (%s)" % (e,)) OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query ([Errno 104] Connection reset by peer)')

can you help me and provide some information to me ?

Thanks very much

daweizhang commented 6 years ago

add comments: sqlalchemy pool_recycle is set 1800s, proxysql mysq-wait_timeout is 25200000 ms (7hour) , so pool_recycle < mysq-wait_timeout

cedriclevarlet commented 6 years ago

Similar issue here on ubuntu 16.04 + python 3 & sqlalchemy (tested with mysqldb and pymysql engines) + proxysql 1.4.8 .

Worth noting that other clients do not seem to exhibit the same issue. Furthermore this only happens when writing data and not during read operations.

pondix commented 6 years ago

What is the wait_timeout set in your MySQL instances?

Also, are you connecting with engine = create_engine(url, pool_recycle=1).connect()?

daweizhang commented 6 years ago

@pondix Thank for your reply. our MySQL instance wait_timeout: mysql> show variables like 'wait_timeout' (8hours); +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec)

sqlalchemy engine create: engine = sqlalchemy.create_engine(connectionAddr, pool_size=poolSize, pool_recycle=1800)

proxysql mysql-wait_timeout: mysql> select * from global_variables where variable_name="mysql-wait_timeout" (7hour); +--------------------+----------------+ | variable_name | variable_value | +--------------------+----------------+ | mysql-wait_timeout | 25200000 | +--------------------+----------------+ 1 row in set (0.00 sec)

pondix commented 6 years ago

I believe there should be ERROR messages related to the "broken connections". I have put together a test script which may be reproducing the issue however I need to double check with Rene as well.

Kindly provide log files in the mean time to help speed up the troubleshooting process.

daweizhang commented 6 years ago

@pondix Thanks for your help, I also check proxysql.log, but I couldnot find any information, we tested proxysql from 2018-04-12 to 2018-04-16, because we found this error. 4/16 we downed proxysql . there is two type error logs in proxsql:

  1. MySQL_Session.cpp:2418:handler(): [ERROR] Unexpected packet from client, disconnecting the client
  2. MySQL_Thread.cpp:3080:process_data_on_data_stream(): [WARNING] Detected broken idle connection on for 1, I believe it was not rootcause , because our sql stmts is "SELECT * FROM devices WHERE row_key=%(row_key)s' {'row_key': 'b72e9fba-282b-11e8-a5f2-0a02aa007060'}", return result size is not more than 1M, but proxysql mysql-max_allowed_packet is 4M for 2, we have been down proxysql before this error happened , I think that it also was not rootcause please review our proxysql.log proxysql1.log (url)
eric-po commented 4 years ago

We had the same problem. my proxysql version is 1.4.11-6-g4e44db1. The proxysql log frequently reports "Detected broken idle connection". then,the backend is mysql. the informations were frequently reports: [Note] Aborted connection 1744627 to db: 'xxx_dbname' user: 'xxxx' host: '192.168.1.xx' (Got timeout reading communication packets)" we found out what it is enable that the proxysql settings of "mysql-multiplexing" in the end. just like this: ProxySQLAdmin> select * from global_variables where variable_name like '%mu%' order by 1; +-------------------------------------+----------------+ | variable_name | variable_value | +-------------------------------------+----------------+ | mysql-client_multi_statements | true | | mysql-connection_delay_multiplex_ms | 0 | | mysql-multiplexing | true | +-------------------------------------+----------------+

In addition, proxysql: "mysql-wait_timeout 3500" backend--mysql: "wait_timeout 3600"

it appers that the mysql have close the connection before proxysql close the connection.

then,we immediatly check the proxysql table of "runtime_mysql_query_rules". also,we found out that one's settings is wrong，we dont set "^SELECT @@" in the column of "match_digest",it is a mistake when we turn on "mysql-multiplexing".

So you should correctly configure the runtime_mysql_query_rules.match_digest when the "mysql-multiplexing" is "true". by the way, proxysql "fast_forward" isnt enable in my product environment. Hope this can help you.