{"action":"created","issue":{"url":"https://api.github.com/repos/airbnb/caravel/issues/1070","repository_url":"https://api.github.com/repos/airbnb/caravel","labels_url":"https://api.github.com/repos/airbnb/caravel/issues/1070/labels{/name}","comments_url":"https://api.github.com/repos/airbnb/caravel/issues/1070/comments","events_url":"https://api.github.com/repos/airbnb/caravel/issues/1070/events","html_url":"https://github.com/airbnb/caravel/issues/1070","id":175395549,"number":1070,"title":"problems running caravel with mysql metadata db","user":{"login":"dennisobrien","id":1168692,"avatar_url":"https://avatars.githubusercontent.com/u/1168692?v=3","gravatar_id":"","url":"https://api.github.com/users/dennisobrien","html_url":"https://github.com/dennisobrien","followers_url":"https://api.github.com/users/dennisobrien/followers","following_url":"https://api.github.com/users/dennisobrien/following{/other_user}","gists_url":"https://api.github.com/users/dennisobrien/gists{/gist_id}","starred_url":"https://api.github.com/users/dennisobrien/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dennisobrien/subscriptions","organizations_url":"https://api.github.com/users/dennisobrien/orgs","repos_url":"https://api.github.com/users/dennisobrien/repos","events_url":"https://api.github.com/users/dennisobrien/events{/privacy}","received_events_url":"https://api.github.com/users/dennisobrien/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/airbnb/caravel/labels/question","name":"question","color":"cc317c"}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":6,"created_at":"2016-09-07T02:03:14Z","updated_at":"2016-09-07T18:48:14Z","closed_at":"2016-09-07T08:17:31Z","body":"I'm running into two problems when trying to configure caravel to run with mysql as the metadata store.\r\n- The charts in the example dashboards show only the error message \"Please define at least one metric for your table\".\r\n- Some database interactions result in a stacktrace with the error message \"OperationalError: (pymysql.err.OperationalError) (1045, u\"Access denied for user 'mysqladmin'@'172.18.0.3' (using password: NO)\")\".\r\n\r\nI've tried to make this as reproducible as possible by running caravel and mysql as docker containers and using docker-compose to make the pieces work together.  The code is in this repo:\r\nhttps://github.com/dennisobrien/caravel-mysql-docker-example\r\n\r\nI have tested this first with caravel 0.10.0 and then built from source from the tag 'airbnb_prod.0.10.0.2'.\r\n\r\nThese errors do not occur when we change the metadata store to sqlite (by commenting out the environment variable SQLALCHEMY_DATABASE_URI lines in docker-compose.yml).\r\n\r\nSteps to reproduce:\r\n- Install docker and docker-compose.\r\n- Clone the repo above and cd into it.\r\n- To build caravel from source:\r\n    - `docker-compose up --force-recreate --build`\r\n    - Get a cup of coffee.\r\n- To install from a pypi release, uncomment and comment the sections in Dockerfile.\r\n- In a browser, open http://localhost:8088 and use the credentials in the `fabmanager create-admin` line in docker-entrypoint.sh.\r\n- Click on a dashboard and notice the error messages.\r\n- In 0.10.0:\r\n    - click Sources -> Databases -> SQL\r\n- In later versions\r\n    - click SQL Lab, the choose 'main' in the Database selector.\r\n- Notice the stacktrace in the server log.\r\n\r\n```\r\n2016-09-07 01:52:51,509:ERROR:root:(pymysql.err.OperationalError) (1045, u\"Access denied for user 'mysqladmin'@'172.18.0.3' (using password: NO)\")\r\nTraceback (most recent call last):\r\n  File \"/home/caravel/caravel/caravel/views.py\", line 66, in wraps\r\n    return f(self, *args, **kwargs)\r\n  File \"/usr/local/lib/python2.7/site-packages/Flask_AppBuilder-1.8.1-py2.7.egg/flask_appbuilder/security/decorators.py\", line 52, in wraps\r\n    return f(self, *args, **kwargs)\r\n  File \"/home/caravel/caravel/caravel/views.py\", line 1118, in tables\r\n    'tables': database.all_table_names(schema),\r\n  File \"/home/caravel/caravel/caravel/models.py\", line 445, in all_table_names\r\n    return sorted(self.inspector.get_table_names(schema))\r\n  File \"/home/caravel/caravel/caravel/models.py\", line 442, in inspector\r\n    return sqla.inspect(engine)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/inspection.py\", line 63, in inspect\r\n    ret = reg(subject)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py\", line 139, in _insp\r\n    return Inspector.from_engine(bind)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py\", line 135, in from_engine\r\n    return Inspector(bind)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py\", line 109, in __init__\r\n    bind.connect().close()\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py\", line 2018, in connect\r\n    return self._connection_cls(self, **kwargs)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py\", line 72, in __init__\r\n    if connection is not None else engine.raw_connection()\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py\", line 2104, in raw_connection\r\n    self.pool.unique_connection, _connection)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py\", line 2078, in _wrap_pool_connect\r\n    e, dialect, self)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py\", line 1405, in _handle_dbapi_exception_noconnection\r\n    exc_info\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py\", line 202, in raise_from_cause\r\n    reraise(type(exception), exception, tb=exc_tb, cause=cause)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py\", line 2074, in _wrap_pool_connect\r\n    return fn()\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 318, in unique_connection\r\n    return _ConnectionFairy._checkout(self)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 713, in _checkout\r\n    fairy = _ConnectionRecord.checkout(pool)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 480, in checkout\r\n    rec = pool._do_get()\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 1060, in _do_get\r\n    self._dec_overflow()\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py\", line 60, in __exit__\r\n    compat.reraise(exc_type, exc_value, exc_tb)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 1057, in _do_get\r\n    return self._create_connection()\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 323, in _create_connection\r\n    return _ConnectionRecord(self)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 449, in __init__\r\n    self.connection = self.__connect()\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/pool.py\", line 607, in __connect\r\n    connection = self.__pool._invoke_creator(self)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/strategies.py\", line 97, in connect\r\n    return dialect.connect(*cargs, **cparams)\r\n  File \"build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py\", line 385, in connect\r\n    return self.dbapi.connect(*cargs, **cparams)\r\n  File \"/usr/local/lib/python2.7/site-packages/pymysql/__init__.py\", line 90, in Connect\r\n    return Connection(*args, **kwargs)\r\n  File \"/usr/local/lib/python2.7/site-packages/pymysql/connections.py\", line 690, in __init__\r\n    self.connect()\r\n  File \"/usr/local/lib/python2.7/site-packages/pymysql/connections.py\", line 908, in connect\r\n    self._request_authentication()\r\n  File \"/usr/local/lib/python2.7/site-packages/pymysql/connections.py\", line 1116, in _request_authentication\r\n    auth_packet = self._read_packet()\r\n  File \"/usr/local/lib/python2.7/site-packages/pymysql/connections.py\", line 983, in _read_packet\r\n    packet.check_error()\r\n  File \"/usr/local/lib/python2.7/site-packages/pymysql/connections.py\", line 395, in check_error\r\n    err.raise_mysql_exception(self._data)\r\n  File \"/usr/local/lib/python2.7/site-packages/pymysql/err.py\", line 107, in raise_mysql_exception\r\n    raise errorclass(errno, errval)\r\nOperationalError: (pymysql.err.OperationalError) (1045, u\"Access denied for user 'mysqladmin'@'172.18.0.3' (using password: NO)\")\r\n```\r\n\r\nThere are a couple suspicious things here:\r\n- Why \"using password: NO\"?  There is a password specified in `SQLALCHEMY_DATABASE_URI`.\r\n- Why an IP address?  If the GRANT was done using the host name from `SQLALCHEMY_DATABASE_URI` this would be a name, not an IP.\r\n\r\nI'm happy to try any debugging ideas others might have.\r\n\r\nthanks,\r\nDennis\r\n"},"comment":{"url":"https://api.github.com/repos/airbnb/caravel/issues/comments/245378975","html_url":"https://github.com/airbnb/caravel/issues/1070#issuecomment-245378975","issue_url":"https://api.github.com/repos/airbnb/caravel/issues/1070","id":245378975,"user":{"login":"dennisobrien","id":1168692,"avatar_url":"https://avatars.githubusercontent.com/u/1168692?v=3","gravatar_id":"","url":"https://api.github.com/users/dennisobrien","html_url":"https://github.com/dennisobrien","followers_url":"https://api.github.com/users/dennisobrien/followers","following_url":"https://api.github.com/users/dennisobrien/following{/other_user}","gists_url":"https://api.github.com/users/dennisobrien/gists{/gist_id}","starred_url":"https://api.github.com/users/dennisobrien/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dennisobrien/subscriptions","organizations_url":"https://api.github.com/users/dennisobrien/orgs","repos_url":"https://api.github.com/users/dennisobrien/repos","events_url":"https://api.github.com/users/dennisobrien/events{/privacy}","received_events_url":"https://api.github.com/users/dennisobrien/received_events","type":"User","site_admin":false},"created_at":"2016-09-07T18:48:14Z","updated_at":"2016-09-07T18:48:14Z","body":"@bkyryliuk I tried 'mysql://mysqladmin:FIXME_12345@db:3306/db' as well as 'mysql://mysqladmin:FIXME_12345@db/db' and see the same symptoms.\r\n\r\nOne difference from the screenshot you included is that I have caravel and mysql on different machines.  In the case of this test using docker-compose, they are in running in different containers.  In the case of AWS, caravel is running in a container in ECS and mysql is an RDS, and I'm seeing this exact problem.  Originally I thought it was an AWS issue (or me bungling the RDS configuration) so I made this docker-compose set up to try to get a minimal reproducible case with mysql and caravel running on separate \"machines\".\r\n\r\nI'll continue to experiment but please continue to send along any ideas you'd like me to try."}}
{"actor":{"display_login":"dennisobrien"}}