qtpylib icon indicating copy to clipboard operation
qtpylib copied to clipboard

Published 20 hours ago • verified publisher icon ranaroussi

Dashboard Mysql connection lost

Open rashidtr opened this issue 7 years ago • 6 comments
trafficstars

Hello, Apparently, there is an issue with MySql connection while working with dashboard. While running this code:

# dashboard.py
from qtpylib.reports import Reports

class Dashboard(Reports):
    pass # we just need the name

if __name__ == "__main__":
    dashboard = Dashboard(
        port    = 5000,
        blotter = "MainBlotter"
    )
dashboard.run()

it runs only once without an error and then connection with mysql is lost. Here is the log from traceback:

>Traceback (most recent call last):
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 2309, in __call__
    return self.wsgi_app(environ, start_response)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 2295, in wsgi_app
    response = self.handle_exception(e)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 1741, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/qtpylib/reports.py", line 238, in trades
    trades = pd.read_sql(trades_query, self.dbconn)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/pandas/io/sql.py", line 381, in read_sql
    chunksize=chunksize)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/pandas/io/sql.py", line 1413, in read_query
    cursor = self.execute(*args)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/pandas/io/sql.py", line 1386, in execute
    raise_with_traceback(ex)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/pandas/compat/__init__.py", line 404, in raise_with_traceback
    raise exc.with_traceback(traceback)
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/pandas/io/sql.py", line 1382, in execute
    self.con.rollback()
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/pymysql/connections.py", line 431, in rollback
    self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")
  File "/media/lucy/hdd1/anaconda3/envs/qtpy/lib/python3.5/site-packages/pymysql/connections.py", line 745, in _execute_command
    raise err.InterfaceError("(0, '')") 
pandas.io.sql.DatabaseError: Execution failed on sql: SELECT * FROM trades WHERE exit_time IS NOT NULL
(2013, 'Lost connection to MySQL server during query')

rashidtr avatar Oct 30 '18 13:10 rashidtr

I get a similar error about the sql execution for trades (I assume it's normal because I have no trades on this database), but I don't lose my MySQL connection because of it

pandas.io.sql.DatabaseError: Execution failed on sql: SELECT * FROM trades WHERE exit_time IS NOT NULL

lionelyoung avatar Jan 21 '19 15:01 lionelyoung

Nevermind, it crashes for me too. I tried again, with --nopass and it lasted a bit longer. Not sure if related

I also took a trade and checked it again, still crashed: https://i.imgur.com/WwyXucE.png

I got this packet error, refs: https://github.com/PyMySQL/PyMySQL/issues/422


Traceback (most recent call last):
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 230
9, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 229
5, in wsgi_app
    response = self.handle_exception(e)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 174
1, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\_compat.py", line
 35, in reraise
    raise value
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 229
2, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 181
5, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 171
8, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\_compat.py", line
 35, in reraise
    raise value
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 181
3, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\flask\app.py", line 179
9, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "c:\users\wolfe\projects\wolfequant\qtpylib\qtpylib\report
s.py", line 219, in trades
    trades = pd.read_sql(trades_query, self.dbconn)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\pandas\io\sql.py", line
 381, in read_sql
    chunksize=chunksize)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\pandas\io\sql.py", line
 1413, in read_query
    cursor = self.execute(*args)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\pandas\io\sql.py", line
 1386, in execute
    raise_with_traceback(ex)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\pandas\compat\__init__.
py", line 404, in raise_with_traceback
    raise exc.with_traceback(traceback)
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\pandas\io\sql.py", line
 1382, in execute
    self.con.rollback()
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\pymysql\connections.py"
, line 429, in rollback
    self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")
  File "C:\Users\DemoUser\Envs\DemoEnv\lib\site-packages\pymysql\connections.py"
, line 750, in _execute_command
    raise err.InterfaceError("(0, '')")
pandas.io.sql.DatabaseError: Execution failed on sql: SELECT * FROM trades WHERE exit_time IS NOT NULL
(0, '')
unable to rollback

Versions:

  • Python 3.7.2
  • Flask 1.0.2
  • qtpylib 1.5.82

lionelyoung avatar Jan 22 '19 12:01 lionelyoung

i have same issue - any body resolved?

python dashboard.py --nopass

  • Serving Flask app "qtpylib.reports" (lazy loading)
  • Environment: production WARNING: Do not use the development server in a production environment. Use a production WSGI server instead.
  • Debug mode: on
  • Running on http://0.0.0.0:5000/ (Press CTRL+C to quit)
  • Restarting with stat
  • Debugger is active!
  • Debugger PIN: 306-847-646 127.0.0.1 - - [05/Feb/2019 15:28:39] "GET / HTTP/1.1" 200 - 127.0.0.1 - - [05/Feb/2019 15:28:39] "GET /positions HTTP/1.1" 500 - Traceback (most recent call last): File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 2309, in call return self.wsgi_app(environ, start_response) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 2295, in wsgi_app response = self.handle_exception(e) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 1741, in handle_exception reraise(exc_type, exc_value, tb) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/_compat.py", line 35, in reraise raise value File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 2292, in wsgi_app response = self.full_dispatch_request() File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 1815, in full_dispatch_request rv = self.handle_user_exception(e) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 1718, in handle_user_exception reraise(exc_type, exc_value, tb) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/_compat.py", line 35, in reraise raise value File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 1813, in full_dispatch_request rv = self.dispatch_request() File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/flask/app.py", line 1799, in dispatch_request return self.view_functionsrule.endpoint File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/qtpylib/reports.py", line 246, in positions trades = pd.read_sql(trades_query, self.dbconn) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/pandas/io/sql.py", line 380, in read_sql chunksize=chunksize) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/pandas/io/sql.py", line 1467, in read_query cursor = self.execute(*args) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/pandas/io/sql.py", line 1439, in execute raise_with_traceback(ex) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/pandas/compat/init.py", line 420, in raise_with_traceback raise exc.with_traceback(traceback) File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/pandas/io/sql.py", line 1434, in execute self.con.rollback() File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/pymysql/connections.py", line 429, in rollback self._execute_command(COMMAND.COM_QUERY, "ROLLBACK") File "/Users/githubbla/anaconda3/envs/qtpy/lib/python3.7/site-packages/pymysql/connections.py", line 750, in _execute_command raise err.InterfaceError("(0, '')") pandas.io.sql.DatabaseError: Execution failed on sql: SELECT * FROM trades WHERE exit_time IS NULL 'NoneType' object has no attribute 'read' unable to rollback 127.0.0.1 - - [05/Feb/2019 15:28:39] "GET /algos HTTP/1.1" 500 - 127.0.0.1 - - [05/Feb/2019 15:28:39] "GET /trades HTTP/1.1" 500

githubbla avatar Feb 05 '19 13:02 githubbla

Did some research. This error is due to the multi-threaded nature of a Flask application and the pymysql connection object is not thread safe. I modified the reports.py file to essentially create a new db conn for each request and errors have gone away.

Should I test and create a pull request @ranaroussi ?

michn avatar Sep 24 '19 21:09 michn

Hi @michn ,

I suppose, this pull did not yet happen?

It would be very nice if you could share your corrected code to the community anyhow!

Thanks in advance!

haimivan avatar Mar 24 '20 14:03 haimivan

@haimivan , you can temporary fix it by set threaded in flask like this to handle multiple clients using threads in a way compatible with old Flask versions app.run( debug=True, host=str(self.host), port=int(self.port), threaded=False, processes=3 ) Hope it help

sadekyo1712 avatar Feb 20 '21 05:02 sadekyo1712