problems running caravel with mysql metadata db using mysql username and password #1070
Labels
!deprecated-label:bug
Deprecated label - Use #bug instead
Comments
|
It looks like you put a wrong sqlalchemy url. Please open a python shell and try to debug the issue by connecting with sqlalchemy to that url. Please reopen if it's not a configuration issue. |
|
There is a lot of evidence that the sqlalchemy url works.
I have also tried this with just 'mysql://' as well as with 'postgresql+psycopg2://'. Can you re-open this? I don't have the option to re-open. If you would like more specific information I would be happy to provide it. |
|
@dennisobrien - could you please attach screen shot of the database configuration that is failing? |
|
Hi @bkyryliuk Here's the view at http://localhost:8088/databaseview/edit/1 after clicking "Test Connection". It shows the sqlalchemy uri I am using. And after testing the connection, the Tables appear below: If I click on SQL Lab (in the latest tag) and select 'main', I see the error Here's the view at http://localhost:8088/caravel/dashboard/births/ which just shows another problem, possibly related to the above. The build flow goes like this:
|
|
@dennisobrien could you try to start the sqluri with mysql:// rather than mysql+pymysql://
|
|
@bkyryliuk I tried 'mysql://mysqladmin:FIXME_12345@db:3306/db' as well as 'mysql://mysqladmin:FIXME_12345@db/db' and see the same symptoms. One 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". I'll continue to experiment but please continue to send along any ideas you'd like me to try. |
|
The output of If I'm interpreting this correctly, the user 'mysqladmin' can access the database from any host (the '%' wildcard). So the issue with the OperationalError above is probably to do with "using password: NO" and not the IP address of the host. Is something removing the password from the sqlalchemy uri? I see places where the connection password is masked, but I doubt this is the issue. I did a dump of the ab_* tables, once from caravel running with mysql and another running with sqlite, just to see if any of the contents were different after the examples were loaded. I only see two differences:
mysql: sqlite: I'd be surprised if this caused a problem. |
|
@dennisobrien - are you familiar with ipython ? |
|
@bkyryliuk - I am familiar with ipython. It wasn't included in the docker image so I'm adding that now. (But since I'm still building from source from the tag 'airbnb_prod.0.10.0.2' it takes a while.) In the meantime, I connected to the container running caravel and launched python (not ipython) and can connect fine. There are some tables that are empty that I would not expect to be empty, but this might be related to the problems. I also added this debug script at the startup. It's using the sqlalchemy uri to read the tables. Let me know what you'd like to try with ipython. thanks, |
|
I just updated the mysql image to 5.7 (from 5.6) and I see some debug output that wasn't reported in the previous version.
|
|
@dennisobrien - let's try to import caravel and see if the app has the access in to the mysql: Please post here the stacktrace if you'll have some problems. |
|
@bkyryliuk -- Thanks for the debugging advice. Here's the output of the session. (Even though it reports version 0.10.0, this is build from the tag 'airbnb_prod.0.10.0.2'.) Inspect Set it anyway and follow the rest of your script: And just to be sure, this is the version of caravel |
|
I see similar stacktrace in #726 which was closed as a dupe of #596 If I follow the steps But again, I would expect this to break for everyone using a SQLALCHEMY_DATABASE_URI with a username and password. Here's an example using the code in And an example of the problem using the engine returned by |
|
Here's an ipython session that tries to mimic the functionality of caravel.views.Caravel.explore (the handler for the endpoint '/explore/<datasource_type>/<datasource_id>/'). In this case I was emulating the endpoint '/caravel/explore/table/3/?...' And I can verify that But if I query the db directly, I do see 'metrics' as a key in 'params'. |
|
That's pretty interesting, here is what I get: In the views file https://github.com/airbnb/caravel/blob/master/caravel/views.py#L486 when you save the database model this code is executed: I would expect to see |
|
Hmm. I don't see the X's in my uri. I am able to edit the database info, save it, reload and verify that it persists. I just updated the "Extra" json to test. |
Is the reason why you cannot access the db as db.password is not set. It means that Do you see any errors when you are saving the DB configuration in the UI ? |
|
I don't see any errors in the log around the time that And here's the record from the Also, does it matter that in the migration, no key is specified, while in the I'm not very familiar with Flask-AppBuilder and |
|
@bkyryliuk You've already helped me immensely so this is not a request, but if you want a reproducible test case, and already have docker and docker-compose installed, you can clone this repo and do a one-liner to bring up this stack. It's probably not as minimal as I could make it, but it is reproducible. Thanks again for all your help! |
dennisobrien
changed the title
|
@bkyryliuk - I just found that if I use the mysql 'root' user and configure it to have no password, these problems all go away.
So I think this bug is specific to using a mysql (or postgres) username and password in the SQLALCHEMY_DATABASE_URI. From your comments a couple days ago, I'm guessing you are using the mysql user with no password and that's why you have not been able to reproduce this problem. |
|
I'm also seeing this issue with postgres passwords. commenting out the |
|
@bkyryliuk - If you have any guidance on fixing this I'd be happy to submit a PR. At the moment, I'm confused on the intent of the code. Later in So calls to Since a recent commit 6aadc6e#diff-397111749c5081b0f26dfa6ac13430dcL896 the command I think previous to this commit it was failing silently here. So this is a good thing. Maybe the easiest fix is to move the password masking work from thanks, |
|
@dennisobrien as you already have a testcase in #1092, whatever will make it work would be an improvement on current state of affairs :) |
|
@dennisobrien - I'll have a chance to take a better look closer to the end of the week. We are using caravel with mysql and passwords in our production. Looks like the issue is that somehow |
xrmx
added
!deprecated-label:bug
|
Thanks for the fix @dennisobrien ! |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I'm running into two problems when trying to configure caravel to run with mysql as the metadata store.
I'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:
https://github.com/dennisobrien/caravel-mysql-docker-example
I have tested this first with caravel 0.10.0 and then built from source from the tag 'airbnb_prod.0.10.0.2'.
These 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).
Steps to reproduce:
docker-compose up --force-recreate --buildfabmanager create-adminline in docker-entrypoint.sh.There are a couple suspicious things here:
SQLALCHEMY_DATABASE_URI.SQLALCHEMY_DATABASE_URIthis would be a name, not an IP.I'm happy to try any debugging ideas others might have.
thanks,
Dennis
The text was updated successfully, but these errors were encountered: