MySQL connector problems w/ gevent

[David Hess]

Does anybody know the correct MySQL connector to use that is compatible with gevent? I'm using PyMySQL and I believe that I'm correctly accessing each connection from only one greenlet. However, it is running into some race problems:

AttributeError: 'NoneType' object has no attribute 'close'

Traceback (most recent call last):

  File "/home/therenow/flask/local/lib/python2.7/site-packages/gevent/greenlet.py", line 327, in run

    result = self._run(*self.args, **self.kwargs)

  File "backend/monitor.py", line 144, in participation_monitor

    for participation in Participant.query:

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2404, in __iter__

    return self._execute_and_instances(context)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2419, in _execute_and_instances

    result = conn.execute(querycontext.statement, self._params)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in execute

    return meth(self, multiparams, params)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement

    compiled_sql, distilled_params

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context

    context)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception

    exc_info

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context

    context)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute

    cursor.execute(statement, parameters)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/cursors.py", line 132, in execute

    result = self._query(query)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/cursors.py", line 271, in _query

    conn.query(q)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 726, in query

    self._affected_rows = self._read_query_result(unbuffered=unbuffered)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 861, in _read_query_result

    result.read()

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 1064, in read

    first_packet = self.connection._read_packet()

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 825, in _read_packet

    packet = packet_type(self)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 242, in __init__

    self._recv_packet(connection)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 248, in _recv_packet

    packet_header = connection._read_bytes(4)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 838, in _read_bytes

    2013, "Lost connection to MySQL server during query (%r)" % (e,))

OperationalError: (OperationalError) (2013, "Lost connection to MySQL server during query (error(9, 'File descriptor was closed in another greenlet'))") 'SELECT participants.id AS participants_id, participants.user_id AS participants_user_id, participants.event_id AS participants_event_id, participants.updated_at AS participants_updated_at \nFROM participants' ()

<Greenlet at 0x3542410: participation_monitor> failed with OperationalError

and

Traceback (most recent call last):

  File "/home/therenow/flask/local/lib/python2.7/site-packages/gevent/greenlet.py", line 327, in run

    result = self._run(*self.args, **self.kwargs)

  File "backend/monitor.py", line 144, in participation_monitor

    for participation in Participant.query:

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2404, in __iter__

    return self._execute_and_instances(context)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2419, in _execute_and_instances

    result = conn.execute(querycontext.statement, self._params)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in execute

    return meth(self, multiparams, params)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement

    compiled_sql, distilled_params

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context

    context)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1111, in _handle_dbapi_exception

    util.reraise(*exc_info)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context

    context)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute

    cursor.execute(statement, parameters)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/cursors.py", line 132, in execute

    result = self._query(query)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/cursors.py", line 271, in _query

    conn.query(q)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 726, in query

    self._affected_rows = self._read_query_result(unbuffered=unbuffered)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 861, in _read_query_result

    result.read()

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 1064, in read

    first_packet = self.connection._read_packet()

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 825, in _read_packet

    packet = packet_type(self)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 242, in __init__

    self._recv_packet(connection)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 248, in _recv_packet

    packet_header = connection._read_bytes(4)

  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 832, in _read_bytes

    data = self._rfile.read(num_bytes)

RuntimeError: reentrant call inside <_io.BufferedReader name=35>

<Greenlet at 0x2eaecd0: participation_monitor> failed with RuntimeError

The first one might be a simple bug but the second one is scary because that exception is coming from _io. There is C code in there that is using PyThread_get_thread_ident to check for reentrancy. There's no chance that gevent is patching that is there?

Does that mean that anything dependent on _io is not compatible with gevent?

Any help or insight is greatly appreciated.

Dave

[opensourcegeek]

Hi Dave,

Are you still running into this issue? I've been using pymysql for a while in production now - never came across that problem. If you have some code to show someone here might be able to help you.

[aaditya sood]

We've had issues under heavy load with gevent+postgresql+sqlalchemy. Using strategy='threadlocal' while creating the engine made them go away.

engine = create_engine(dburi,
                           echo=echo,
                           pool_recycle=3600,
                           convert_unicode=True,
                           pool_size=pool_size,
                           pool_timeout=pool_timeout,
                           strategy='threadlocal',
                           max_overflow=max_overflow)

engine.pool._use_threadlocal = True

 

See http://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#using-the-threadlocal-execution-strategy

We think it's related to the sqlalchemy pool mixing up connections between greenlets even after monkey patching.

HTH

Regards

--

Aaditya Sood

US: +1 (650) 784-0420

India: +91 9845100258

www.ideadevice.com

--
You received this message because you are subscribed to the Google Groups "gevent: coroutine-based Python network library" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gevent+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tony Wang]

Hi aaditya and David

Have you solved this error? I configured as aaditya said, the error still happened after few hours running.

SQLAlchemy==1.1.4 gevent==1.2.0 greenlet==0.4.11 PyMySQL==0.7.11

2017-07-15 10:59:15,688 INFO sqlalchemy.engine.threadlocal.TLEngine ROLLBACK
Exception during reset or similar

Traceback (most recent call last):

  File "/root/proxyserver/db/SqlHelper.py", line 133, in update
    updateNum = query.update(updatevalue)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3288, in update
    update_op.exec_()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1168, in exec_
    self._do_exec()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1323, in _do_exec
    mapper=self.mapper)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1107, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 971, in _connection_for_bind
    engine, execution_options)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 382, in _connection_for_bind
    self._assert_active()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 264, in _assert_active
    "This session is in 'prepared' state; no further "
sqlalchemy.exc.InvalidRequestError: This session is in 'prepared' state; no further SQL can be emitted within this transaction.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 687, in _finalize_fairy
    fairy._reset(pool)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 829, in _reset
    pool._dialect.do_rollback(self)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/dialects/mysql/base.py", line 1547, in do_rollback
    dbapi_connection.rollback()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 793, in rollback
    self._read_ok_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 767, in _read_ok_packet
    pkt = self._read_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 987, in _read_packet
    packet_header = self._read_bytes(4)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1021, in _read_bytes
    data = self._rfile.read(num_bytes)
RuntimeError: reentrant call inside <_io.BufferedReader name=29>

-2017-07-15 10:59:21,558 INFO sqlalchemy.engine.threadlocal.TLEngine ROLLBACK

Exception during reset or similar

Traceback (most recent call last):

  File "/root/proxyserver/db/SqlHelper.py", line 133, in update
    updateNum = query.update(updatevalue)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 3288, in update
    update_op.exec_()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1168, in exec_
    self._do_exec()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1323, in _do_exec
    mapper=self.mapper)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1107, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1396, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 469, in do_execute
    cursor.execute(statement, parameters)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 987, in _read_packet
    packet_header = self._read_bytes(4)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1021, in _read_bytes
    data = self._rfile.read(num_bytes)
RuntimeError: reentrant call inside <_io.BufferedReader name=27>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 687, in _finalize_fairy
    fairy._reset(pool)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 829, in _reset
    pool._dialect.do_rollback(self)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/sqlalchemy/dialects/mysql/base.py", line 1547, in do_rollback
    dbapi_connection.rollback()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 793, in rollback
    self._read_ok_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 767, in _read_ok_packet
    pkt = self._read_packet()
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 987, in _read_packet
    packet_header = self._read_bytes(4)
  File "/root/proxyserver/.env/lib/python3.5/site-packages/pymysql/connections.py", line 1021, in _read_bytes
    data = self._rfile.read(num_bytes)
RuntimeError: reentrant call inside <_io.BufferedReader name=27>

[Echo Zeng]

the same condition, the error still happened after few hours running. so i use semaphore to lock connection. can not find a better way.still watching .

在 2017年7月17日星期一 UTC+8下午7:39:23，Tony Wang写道：