{"action":"created","issue":{"url":"https://api.github.com/repos/jupyterhub/jupyterhub/issues/1626","repository_url":"https://api.github.com/repos/jupyterhub/jupyterhub","labels_url":"https://api.github.com/repos/jupyterhub/jupyterhub/issues/1626/labels{/name}","comments_url":"https://api.github.com/repos/jupyterhub/jupyterhub/issues/1626/comments","events_url":"https://api.github.com/repos/jupyterhub/jupyterhub/issues/1626/events","html_url":"https://github.com/jupyterhub/jupyterhub/issues/1626","id":289957936,"number":1626,"title":"Jupyterhub is not resilient with external MySQL database","user":{"login":"ckbhatt","id":17678899,"avatar_url":"https://avatars1.githubusercontent.com/u/17678899?v=4","gravatar_id":"","url":"https://api.github.com/users/ckbhatt","html_url":"https://github.com/ckbhatt","followers_url":"https://api.github.com/users/ckbhatt/followers","following_url":"https://api.github.com/users/ckbhatt/following{/other_user}","gists_url":"https://api.github.com/users/ckbhatt/gists{/gist_id}","starred_url":"https://api.github.com/users/ckbhatt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ckbhatt/subscriptions","organizations_url":"https://api.github.com/users/ckbhatt/orgs","repos_url":"https://api.github.com/users/ckbhatt/repos","events_url":"https://api.github.com/users/ckbhatt/events{/privacy}","received_events_url":"https://api.github.com/users/ckbhatt/received_events","type":"User","site_admin":false},"labels":[{"id":106589228,"url":"https://api.github.com/repos/jupyterhub/jupyterhub/labels/question","name":"question","color":"Cfaef7","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2018-01-19T12:19:48Z","updated_at":"2018-03-02T11:48:05Z","closed_at":null,"author_association":"NONE","body":"We are using Jupyterhub with an external mariadb(open source fork of mysql) database. In the event of a temporary network failure, Jupyterhub gets stuck and do not recover.\r\n\r\n**How to reproduce the issue on mac**\r\n\r\n1. Install Mariadb locally\r\n`brew install mariadb`\r\n2. Remove from launchctl for ease of stop and start\r\n`launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist`\r\n3. Start mariadb\r\n`brew services start mariadb`\r\n4. Create a database named jh_test\r\n5. Create a user test/test in mac.\r\n6. Run jupyter hub locally with following config file.\r\n```\r\nc = get_config()\r\nc.Authenticator.whitelist = {'test'}\r\nc.Authenticator.admin_users = {'test'}\r\nc.JupyterHub.db_url = 'mysql+mysqlconnector://{}:{}@{}/{}{}'.format(\"root\",\"\",\"localhost\",\"jh_test\",\"\")\r\n```\r\n7. Login using test/test and start jupyter server.\r\n8. Stop mysql\r\n`brew services stop mariadb`\r\n9. Go to browser and click on Control Panel button. You'll get following exception:\r\n```\r\n[E 2018-01-19 04:14:19.920 JupyterHub web:1518] Exception in exception handler\r\n    Traceback (most recent call last):\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/tornado/web.py\", line 1516, in _execute\r\n        self._handle_request_exception(e)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/tornado/web.py\", line 1570, in _handle_request_exception\r\n        self.send_error(500, exc_info=sys.exc_info())\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/tornado/web.py\", line 1040, in send_error\r\n        self.finish()\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/jupyterhub/handlers/base.py\", line 105, in finish\r\n        super().finish(*args, **kwargs)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/tornado/web.py\", line 993, in finish\r\n        self._log()\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/tornado/web.py\", line 1540, in _log\r\n        self.application.log_request(self)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/tornado/web.py\", line 2053, in log_request\r\n        self.settings[\"log_function\"](handler)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/jupyterhub/log.py\", line 90, in log_request\r\n        user = handler.get_current_user()\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/jupyterhub/handlers/base.py\", line 229, in get_current_user\r\n        return self.get_current_user_cookie()\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/jupyterhub/handlers/base.py\", line 222, in get_current_user_cookie\r\n        return self._user_for_cookie(self.hub.cookie_name)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/jupyterhub/handlers/base.py\", line 206, in _user_for_cookie\r\n        u = self.db.query(orm.User).filter(orm.User.cookie_id==cookie_id).first()\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/query.py\", line 2755, in first\r\n        ret = list(self[0:1])\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/query.py\", line 2547, in __getitem__\r\n        return list(res)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/query.py\", line 2855, in __iter__\r\n        return self._execute_and_instances(context)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/query.py\", line 2878, in _execute_and_instances\r\n        result = conn.execute(querycontext.statement, self._params)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 945, in execute\r\n        return meth(self, multiparams, params)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/elements.py\", line 263, in _execute_on_connection\r\n        return connection._execute_clauseelement(self, multiparams, params)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 1053, in _execute_clauseelement\r\n        compiled_sql, distilled_params\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 1121, in _execute_context\r\n        None, None)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 1402, in _handle_dbapi_exception\r\n        exc_info\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py\", line 203, in raise_from_cause\r\n        reraise(type(exception), exception, tb=exc_tb, cause=cause)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py\", line 186, in reraise\r\n        raise value.with_traceback(tb)\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 1114, in _execute_context\r\n        conn = self._revalidate_connection()\r\n      File \"/Users/bhatt/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 424, in _revalidate_connection\r\n        \"Can't reconnect until invalid \"\r\n    sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'SELECT users.id AS users_id, users.name AS users_name, users.`admin` AS users_admin, users.last_activity AS users_last_activity, users.cookie_id AS users_cookie_id, users.state AS users_state, users.encrypted_auth_state AS users_encrypted_auth_state \\nFROM users \\nWHERE users.cookie_id = %(cookie_id_1)s \\n LIMIT %(param_1)s'] [parameters: [{}]]\r\n```\r\n10. Start mariadb again\r\n`brew services start mariadb`\r\n11. Refresh Jupyterhub page.\r\n**What you expected to happen**\r\nJupyterhub should reconnect with DB and work normally as network interruptions are common scenarios. In above case DB was shutdown to mimic network interruption. Results are same with a remote DB and actual n/w interruption.\r\n**What actually happens**\r\nJupyter hub keeps giving above exception and we need to restart hub for bringing it back to normal state.\r\n**Share what version of JupyterHub you are using**\r\n0.8.1\r\n"},"comment":{"url":"https://api.github.com/repos/jupyterhub/jupyterhub/issues/comments/369900830","html_url":"https://github.com/jupyterhub/jupyterhub/issues/1626#issuecomment-369900830","issue_url":"https://api.github.com/repos/jupyterhub/jupyterhub/issues/1626","id":369900830,"user":{"login":"minrk","id":151929,"avatar_url":"https://avatars1.githubusercontent.com/u/151929?v=4","gravatar_id":"","url":"https://api.github.com/users/minrk","html_url":"https://github.com/minrk","followers_url":"https://api.github.com/users/minrk/followers","following_url":"https://api.github.com/users/minrk/following{/other_user}","gists_url":"https://api.github.com/users/minrk/gists{/gist_id}","starred_url":"https://api.github.com/users/minrk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/minrk/subscriptions","organizations_url":"https://api.github.com/users/minrk/orgs","repos_url":"https://api.github.com/users/minrk/repos","events_url":"https://api.github.com/users/minrk/events{/privacy}","received_events_url":"https://api.github.com/users/minrk/received_events","type":"User","site_admin":false},"created_at":"2018-03-02T11:48:05Z","updated_at":"2018-03-02T11:48:05Z","author_association":"OWNER","body":"That would be great! We don't need to put every commit in a try/except, though, and we shouldn't try to do it that way. As long as we let the error raise and ensure that the rollback happens at the end of the Handler, we should be set. We already attempt to do that [here](https://github.com/jupyterhub/jupyterhub/blob/master/jupyterhub/handlers/base.py#L110), but it may not be firing in all cases. Some testing to ensure that rollback occurs correctly in one of these scenarios would be very helpful."}}
{"actor":{"display_login":"minrk"}}