Frequent "MySQL server has gone away" errors

[krisskross]

The webserver frequently reports "MySQL server has gone away" errors. Usually this happens after the UI haven't been used for a few minutes. The mysql documentation indicate that it may be a timed out connection.

                          ____/ (  (    )   )  \___
                         /( (  (  )   _    ))  )   )\
                       ((     (   )(    )  )   (   )  )
                     ((/  ( _(   )   (   _) ) (  () )  )
                    ( (  ( (_)   ((    (   )  .((_ ) .  )_
                   ( (  )    (      (  )    )   ) . ) (   )
                  (  (   (  (   ) (  _  ( _) ).  ) . ) ) ( )
                  ( (  (   ) (  )   (  ))     ) _)(   )  )  )
                 ( (  ( \ ) (    (_  ( ) ( )  )   ) )  )) ( )
                  (  (   (  (   (_ ( ) ( _    )  ) (  )  )   )
                 ( (  ( (  (  )     (_  )  ) )  _)   ) _( ( )
                  ((  (   )(    (     _    )   _) _(_ (  (_ )
                   (_((__(_(__(( ( ( |  ) ) ) )_))__))_)___)
                   ((__)        \\||lll|l||///          \_))
                            (   /(/ (  )  ) )\   )
                          (    ( ( ( | | ) ) )\   )
                           (   /(| / ( )) ) ) )) )
                         (     ( ((((_(|)_)))))     )
                          (      ||\(|(|)|/||     )
                        (        |(||(||)||||        )
                          (     //|/l|||)|\\ \     )
                        (/ / //  /|//||||\\  \ \  \ _)
-------------------------------------------------------------------------------
Node: airflow01
-------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python2.7/dist-packages/flask_admin/base.py", line 68, in inner
    return self._run_view(f, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/flask_admin/base.py", line 359, in _run_view
    return fn(self, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/flask_login.py", line 755, in decorated_view
    return func(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/airflow/www/utils.py", line 101, in wrapper
    session.commit()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 801, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 392, in commit
    self._prepare_impl()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 372, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2019, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2137, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2101, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj
    mapper, table, insert)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 800, in _emit_insert_statements
    execute(statement, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorvalue
OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: u'INSERT INTO log (dttm, dag_id, task_id, event, execution_date, owner, extra) VALUES (%s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2016, 2, 29, 22, 39, 55, 135986), u'trackbox_metrics', u'LogLagCheck', 'log', datetime.datetime(2016, 2, 29, 18, 0), 'anonymous', "[('execution_date', u'2016-02-29T18:00:00'), ('task_id', u'LogLagCheck'), ('dag_id', u'tb_metrics')]")]

[griffinqiu]

+1
I always met this error.
The bomb and MySQL server has gone away

[bolkedebruin]

First option to look at is change some parameters related to timeout in /etc/my.cnf . See http://piwik.org/faq/troubleshooting/faq_183/ . And https://www.drupal.org/node/259580 .

For airflow we might need to look at how we do sessions (didn't really check) @mistercrunch http://stackoverflow.com/questions/6471549/avoiding-mysql-server-has-gone-away-on-infrequently-used-python-flask-server#6473271

[bolkedebruin]

@griffinqiu @krisskross did you have a look at the sql_alchemy_pool_recycle option? It defaults to 3600 and that might be too long in your case.

[mistercrunch]

You can also shut off connection pooling altogether by setting the size of the pool to 0.

[r39132]

Looking at http://dev.mysql.com/doc/refman/5.7/en/gone-away.html, there are many reasons this may be happening. Some of the reasons are possibly concerning :

• You can also encounter this error with applications that fork child processes, all of which try to use the same connection to the MySQL server. This can be avoided by using a separate connection for each child process.
• You can also get these errors if you send a query to the server that is incorrect or too large.
• You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.

I don't run MySQL. It would go good to isolate what is going on here for those running on MySQL. @krisskross any further details?

[krisskross]

We're not doing anything with the database, it's all airflow. We have other Java applications that pool connections to the same MySQL installation without problems, so this doesn't feel like a configuration issue with MySQL.

I'm not sure exactly how to set the pool size to 0? Is it the SQLALCHEMY_POOL_SIZE environment variable? http://flask-sqlalchemy.pocoo.org/2.1/config/#configuration-keys

[krisskross]

Not sure if helpful, but we run the webserver, scheduler and worker on the same machine.

[mistercrunch]

Oops actually it's dependent on this PR
#1021

I'll see if I can rebase and merge it now

[krisskross]

Sorry for the delay. Is there anything I can do to help?

[bolkedebruin]

Any update on this one? @krisskross in addition please verify that your mysql server is not being killed due to being out of memory. "Gone away" = loosing connection. table_cache, max_connections and memory all affect these. A clean install with default settings tuned to mysql and localexecutor can easily consume up to 8GB with a reasonable amount of dags getting loaded.

Please create a Jira issue if this persists and provide logging from syslog and mysql.