apscheduler Lost connection to MySQL server during query

Question

I use apscheduler to execute regular job, and I got some error on it.

"Lost connection to MySQL server during query"

To find the answer, I try some test on it, and found out if my database(MySQL) "wait_timeout" is less than schedule interval time then this error occur. (sorry here I made some mistake...is less than...)

in the test:

my job setting

scheduler.add_job(period_job, 'interval', minutes=5, id='my_job_id')
my database setting

wait_timeout = 60

my test code

from apscheduler.schedulers.background import BackgroundScheduler
from flask import Flask
app = Flask(__name__)

scheduler = BackgroundScheduler({'apscheduler.jobstores.default': {
        'type': 'sqlalchemy',
        'url': 'mysql+pymysql://user:pass@url:3306/test_apscheduler?charset=utf8'
    },
        'apscheduler.executors.default': {
            'class': 'apscheduler.executors.pool:ThreadPoolExecutor',
            'max_workers': '20'
    },
        'apscheduler.executors.processpool': {
        'type': 'processpool',
        'max_workers': '5'
    },
        'apscheduler.job_defaults.coalesce': 'false',
        'apscheduler.job_defaults.max_instances': '3',
        'apscheduler.timezone': 'UTC',
    })

scheduler.start()


@app.route('/')
def hello_world():
    scheduler.add_job(period_job, 'interval', minutes=5, id='my_job_id')
    return 'Hello World!'


def period_job():
    print("hihi")


if __name__ == '__main__':
    app.run()

total error message:

Exception in thread APScheduler:
Traceback (most recent call last):
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_context context)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\default.py", line 450, in do_execute cursor.execute(statement, parameters)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\cursors.py", line 158, in execute result = self._query(query)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\cursors.py", line 308, in _query conn.query(q)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 820, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 1002, in _read_query_result result.read()
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 1285, in read first_packet = self.connection._read_packet()
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 946, in _read_packet packet_header = self._read_bytes(4)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 982, in _read_bytes 2013, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Python34\lib\threading.py", line 921, in _bootstrap_inner self.run()
  File "C:\Python34\lib\threading.py", line 869, in run self._target(*self._args, **self._kwargs)
  File "C:\Users\skuo\apshcduler\lib\site-packages\apscheduler\schedulers\blocking.py", line 27, in _main_loop wait_seconds = self._process_jobs()
  File "C:\Users\skuo\apshcduler\lib\site-packages\apscheduler\schedulers\base.py", line 801, in _process_jobs for job in jobstore.get_due_jobs(now):
  File "C:\Users\skuo\apshcduler\lib\site-packages\apscheduler\jobstores\sqlalchemy.py", line 69, in get_due_jobs return self._get_jobs(self.jobs_t.c.next_run_time <= timestamp)
  File "C:\Users\skuo\apshcduler\lib\site-packages\apscheduler\jobstores\sqlalchemy.py", line 131, in _get_jobs for row in self.engine.execute(selectable):
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\base.py", line 1991, in execute return connection.execute(statement, *multiparams, **params)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute return meth(self, multiparams, params)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement compiled_sql, distilled_params
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\base.py", line 1146, in _execute_context context)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception exc_info
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\util\compat.py", line 200, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\util\compat.py", line 183, in reraise raise value.with_traceback(tb)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_context context)
  File "C:\Users\skuo\apshcduler\lib\site-packages\sqlalchemy\engine\default.py", line 450, in do_execute cursor.execute(statement, parameters)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\cursors.py", line 158, in execute result = self._query(query)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\cursors.py", line 308, in _query conn.query(q)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 820, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 1002, in _read_query_result result.read()
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 1285, in read first_packet = self.connection._read_packet()
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 946, in _read_packet packet_header = self._read_bytes(4)
  File "C:\Users\skuo\apshcduler\lib\site-packages\pymysql\connections.py", line 982, in _read_bytes 2013, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: 'SELECT apscheduler_jobs.id, apscheduler_jobs.job_state \nFROM apscheduler_jobs \nWHERE apscheduler_jobs.next_run_time <= %(next_run_time_1)s ORDER BY apscheduler_jobs.next_run_time'] [parameters: {'next_run_time_1': 1457445220.361246}]

does anyone know what happened to this? and how to fix it?

what is the setting of interactive_timeout – Bernd Buffen Mar 08 '16 at 16:25 — Bernd Buffen, Mar 08 '16 at 16:25

score 0 · Answer 1 · answered Mar 08 '16 at 16:26

0

what is the setting of interactive_timeout ?

wait_timeout:

Description: Time in seconds that the server waits for a connection to become active before closing it. The session value is initialized when a thread starts up from either the global value, if the connection is non-interactive, or from the interactive_timeout value, if the connection is interactive.

answered Mar 08 '16 at 16:26

Bernd Buffen

14,525
2
24
39

thanks for reply. I check 'interactive_timeout' is 28800. And I set both variable(wait_timeout, interactive_timeout) to 60, but the same error still occur. – shan kuo Mar 09 '16 at 03:05
you must set both higher like 3600 (1h), Which version you use SELECT VERSION(); – Bernd Buffen Mar 09 '16 at 05:30
SELECT VERSION(); -> 5.5.46-0ubuntu0.14.04.2. And why these two timeout need set higher? – shan kuo Mar 09 '16 at 05:42
if your job connect to the db and do nothing with the DB a longer timer like a cron the DB will close the connection and if your job use it later it was closed – Bernd Buffen Mar 09 '16 at 05:46
ok, i see. I think maybe the error is apscheduler do not close the connection with database. Because when I use sqlalchemy, I use .close() function after query, and it's will give this connection back to connection pool. If next query activate and DB close, it will create new connection to DB. Your reply help me a lot! Thanks! – shan kuo Mar 09 '16 at 09:20

apscheduler Lost connection to MySQL server during query

1 Answers1