Connecting MySQL and PostgreSQL into SQLAlchemy URI

[miloradkrstevski]

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

0.24

Expected results

Connection to db in MySQL and another db in PostgreSQL

Actual results

Can not connect either to MySQL or PostgreSQL db. The error on MySQL is: ERROR: {"error": "Connection failed!\n\nThe error message returned was:\nNo module named MySQLdb"}.

Can you please point me out:

1. What I need to have installed on Ubuntu and on Windows?
2. What is the correct syntax in connections since the ones provided in SqlAlchemy docs are givinig me error?
3. What is your suggestion on MySQL or PostgreSQL db for Superset?

Steps to reproduce

//

[xrmx]

The error is pretty clear and requirements for databases are in the documentation.

[miloradkrstevski]

@xrmx I followed the docs but can not still connect. Can you please point me out what to install (either in Ubuntu or Windows)? The syntax for (2) user / pass / db after the (1) mysql+mysqldb (2) scott:tiger@localhost/foo, is clear, but the (1) is the problem I can not solve.

[miloradkrstevski]

@mistercrunch Can you please help me on this issue?

[teewhyli]

@miloradkrstevski assuming your language of choice is python you need a python driver for mysql as superset uses SQLAlchemy to process SQL related queries in the back-end.

Do you have that python driver installed??

[miloradkrstevski]

@TianyangLi Yes, I will take for example Windows. I installed MySQL with all add-ons (https://dev.mysql.com/downloads/windows/installer/8.0.html), Python 2.7.15 x86 (https://www.python.org/downloads/release/python-2715/), Microsoft Visual C++ Compiler for Python 2.7 (https://www.microsoft.com/en-us/download/details.aspx?id=44266) and https://www.microsoft.com/en-us/download/details.aspx?id=48145).

With these installations, I still get an error either on mysql+mysqldb or mysql+mysqlconnector.

What should I do next? Thanks.

[teewhyli]

@miloradkrstevski

The error itself is a very straight forward fix. On ubuntu its as straight forward as:
sudo apt-get install python-mysqldb

If you have a alternative linux set-up that might just do it.
let me know how you go...not sure if running python 2.7 might have an affect.

[miloradkrstevski]

@TianyangLi Thanks a lot. I will reset Ubuntu installation and go with sudo apt-get install python-mysqldb and python 2.7. If this does not go well, I will try with python 3.xx.

Hopefully, something will work. Thanks again.

[miloradkrstevski]

@TianyangLi Does not work. Here is the info (from Windows): (in Ubuntu will try next)

Traceback (most recent call last):
File "C:\venv\lib\site-packages\superset\views\core.py", line 1691, in testconn
engine = create_engine(uri, connect_args=connect_args)
File "C:\venv\lib\site-packages\sqlalchemy\engine_init_.py", line 419, in create_engine
return strategy.create(*args, **kwargs)
File "C:\venv\lib\site-packages\sqlalchemy\engine\strategies.py", line 80, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File "C:\venv\lib\site-packages\sqlalchemy\dialects\mysql\mysqldb.py", line 102, in dbapi
return import('MySQLdb')
ImportError: No module named MySQLdb
2018-05-16 15:08:43,316:INFO:werkzeug:127.0.0.1 - - [16/May/2018 15:08:43] "POST /superset/testconn HTTP/1.1" 500 -

Something else comes to mind for a solution?

[xrmx]

@miloradkrstevski if you had read the documentation you'll have see that 1) windows is not supported, 2) the docs suggests another driver for mysql

[miloradkrstevski]

@xrmx Dude, I read the docs. And somehow it was working in Windows with some previous version of Superset and conn with MySQL. The thing is that I did not write it down what I have installed to work, but I know it was searching for solution pretty well, like now.

Now, I will test as @TianyangLi suggests and will find out. BTW, @xrmx, do you have any possible solution in mind?

Thanks.

[miloradkrstevski]

@TianyangLi I have tried with sudo apt-get install python-mysqldb, but still no success in Ubuntu either (@xrmx). It gives me error:

ERROR: {"error": "Connection failed!\n\nThe error message returned was:\nNo module named MySQLdb"}

And in the terminal it is the following info:

Traceback (most recent call last):
File "/home/miki/venv/local/lib/python2.7/site-packages/superset/views/core.py", line 1691, in testconn
engine = create_engine(uri, connect_args=connect_args)
File "/home/miki/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/init.py", line 419, in create_engine
return strategy.create(*args, **kwargs)
File "/home/miki/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 80, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File "/home/miki/venv/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 102, in dbapi
return import('MySQLdb')
ImportError: No module named MySQLdb

I am using Superset==0.24, because 0.25 (or latest) gives me problem with flask.

Anyone any idea? Thanks.

[mistercrunch]

pip install mysqlclient

[miloradkrstevski]

@mistercrunch When I try to install pip install mysqlclient, it goes me the following error:

Collecting mysqlclient
Downloading https://files.pythonhosted.org/packages/6f/86/bad31f1c1bb0cc99e88ca2adb7cb5c71f7a6540c1bb001480513de76a931/mysqlclient-1.3.12.tar.gz (89kB)
100% |████████████████████████████████| 92kB 735kB/s
Complete output from command python setup.py egg_info:
sh: 1: mysql_config: not found
Traceback (most recent call last):
File "", line 1, in
File "/tmp/pip-build-WdyxRX/mysqlclient/setup.py", line 17, in
metadata, options = get_config()
File "setup_posix.py", line 44, in get_config
libs = mysql_config("libs_r")
File "setup_posix.py", line 26, in mysql_config
raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found

----------------------------------------

Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-WdyxRX/mysqlclient/

What does this mean? Can anyone please explain me? I am novice in this and using only Superset. Thank you.

@mistercrunch Can you reopen my issue please so I can find solution? I really need it.

[mistercrunch]

Please research how to install mysqlclient on the os your own, usually installing the package for the mysql client (CLI) will install the required deps.

[miloradkrstevski]

Can anyone please point me out where I miss something because certainly there is something:

1. I used VMWare 12.1.1 to install Ubuntu 18.04 LTS. After installation I updated with the Software Updater.
2. Next, I am following the instructions on https://superset.apache.org/installation.html as follows:
   2.a. In terminal, I used: sudo apt-get install build-essential libssl-dev libffi-dev python-dev python-pip libsasl2-dev libldap2-dev. The installation went OK with no errors.
   2.b. In terminal, as regular admin user (no root, sudo su, sudo -s, sudo -i), I installed: pip install virtualenv. The installation went OK with no errors.
   2.c. In terminal, I used: sudo apt install virtualenv. The installation went OK with no errors.
   2.d. In terminal, I sued: virtualenv venv as regular admin user (no root, sudo su, sudo -s, sudo -i). The installation went OK with no errors.
   2.e. After, in terminal, I used . ./venv/bin/activate as regular admin user (no root, sudo su, sudo -s, sudo -i).
   2.f. In terminal, as regular admin user (no root, sudo su, sudo -s, sudo -i) and also in (venv), I used: pip install --upgrade setuptools pip, but the requirements are already up-to-date.
3. Afterwards, I started to install Superset. In teminal (venv), as regular admin user (no root, sudo su, sudo -s, sudo -i), I used: pip install superset==0.24. The installation went OK with no errors.
   3.a. In terminal (venv), as regular admin user (no root, sudo su, sudo -s, sudo -i), I used: fabmanager create-admin --app superset and created default admin user with password. The config went OK: > Recognized Database Authentications. Admin User admin created.
   3.b. Also as previous point 3.a. in terminal (venv) as this user, I config the following: superset db upgrade and it went OK.
   3.c. Afterwards, as previous point 3.b. in terminal (venv) as this user, I config the following: superset load_examples and this also went OK.
   3.d. The same as point 3.c. goes for config; superset init which also went OK.
4. Now, I started Superset, in terminal (venv) as regular admin user (no root, sudo su, sudo -s, sudo -i): superset runserver and it went OK, as follows below:

(venv) miki@ubuntu:~$ superset runserver
Starting server with command:
gunicorn -w 2 --timeout 60 -b 0.0.0.0:8088 --limit-request-line 0 --limit-request-field_size 0 superset:app

[2018-05-17 11:01:23 +0000] [16859] [INFO] Starting gunicorn 19.7.1
[2018-05-17 11:01:23 +0000] [16859] [INFO] Listening at: http://0.0.0.0:8088 (16859)
[2018-05-17 11:01:23 +0000] [16859] [INFO] Using worker: sync
[2018-05-17 11:01:23 +0000] [16863] [INFO] Booting worker with pid: 16863
[2018-05-17 11:01:23 +0000] [16864] [INFO] Booting worker with pid: 16864

5. Went to Firefox 60.0 on address http://localhost:8088/login/ and logged in with admin user. Everything is OK.
6. Tested with Upload CSV into sqlite default Superset db and the CSV was imported in sqlite (main) as follows: CSV file "2017.csv" uploaded to table "2017" in database "main" with SQLAlchemy URI: `sqlite:////home/miki/.superset/superset.db.`` From this I tested with some charts / slices and everything is OK.
7. Now, I logged out from Superset and stopped superset server and closed the terminal.
8. Afterwards, I installed MySQL server, as follows: sudo apt-get update - OK, sudo apt-get install mysql-server - OK, started mysql with sudo mysql -u root and run in mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'miki'; - OK (Query OK, 0 rows affected (0.00 sec)) and on the end mysql> \quit.
9. Next, I installed MySQL Workbench, as follows: sudo apt update && sudo apt upgrade - OK, sudo apt install mysql-workbench - OK. Started MySQL Workbench and created schema "miki".
10. Now, I installed the instruction from https://superset.apache.org/installation.html#database-dependencies for MySQL, as follows: pip install mysqlclient in terminal as regular admin user (no root, sudo su, sudo -s, sudo -i), but it gave me error:

miki@ubuntu:~$ pip install mysqlclient
Collecting mysqlclient
Downloading https://files.pythonhosted.org/packages/6f/86/bad31f1c1bb0cc99e88ca2adb7cb5c71f7a6540c1bb001480513de76a931/mysqlclient-1.3.12.tar.gz (89kB)
100% |████████████████████████████████| 92kB 615kB/s
Complete output from command python setup.py egg_info:
sh: 1: mysql_config: not found
Traceback (most recent call last):
File "", line 1, in
File "/tmp/pip-build-q2Ff6M/mysqlclient/setup.py", line 17, in
metadata, options = get_config()
File "setup_posix.py", line 44, in get_config
libs = mysql_config("libs_r")
File "setup_posix.py", line 26, in mysql_config
raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found

----------------------------------------

Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-q2Ff6M/mysqlclient/

10.a. I googled it: "Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-q2Ff6M/mysqlclient/" and did the following: pip install --upgrade setuptools - OK (and rerun pip install mysqlclient but still the same error), also tried as sudo su - but the same error.
10.b. Next, I tried sudo apt-get install python3.6-dev libmysqlclient-dev - OK and rerun pip install mysqlclient. Now it went OK as follows:

miki@ubuntu:~$ pip install mysqlclient
Collecting mysqlclient
Using cached https://files.pythonhosted.org/packages/6f/86/bad31f1c1bb0cc99e88ca2adb7cb5c71f7a6540c1bb001480513de76a931/mysqlclient-1.3.12.tar.gz
Building wheels for collected packages: mysqlclient
Running setup.py bdist_wheel for mysqlclient ... done
Stored in directory: /home/miki/.cache/pip/wheels/50/c7/31/81a516762c8e9324f2b1fdffc1e84b9f07224fe3707956f6e1
Successfully built mysqlclient
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.3.12

After I close the terminal.
11. Finally, I got to SQLAlchemy URI (first started terminal: sudo su, and run . ./venv/bin/activate and after superset runserver). Then went on Superset in Firefox.
12. In SQLAlchemy URI, I have tried the following: mysql+mysqldb://root:miki@localhost/miki and gives me error: "ERROR: {"error": "Connection failed!\n\nThe error message returned was:\nNo module named MySQLdb"}"
12.a. Also tried: mysql://root:miki@localhost/miki and mysql+mysqlconnector://root:miki@localhost/miki and the same error.
12.c Next, I tried all 3x connection details in point 12 and 12.a, but with port 3306 after @localhost:3306/miki, and the same error.

Can someone please point me out some solution or additional step to take into account because I tried everything and it does not work? Thank you.

[miloradkrstevski]

@mistercrunch Can you please reopen the issue so someone could help me? Anyone any solution: @xrmx , @TianyangLi , @mistercrunch , ...
The details are above this comment. Thank you.

[teewhyli]

@miloradkrstevski

Are you sure you're in the right virtual venv? have you deployed your venv in a python3 environment? its unclear from your steps..

python defaults 2.7. So its possible that you've installed python3-mysqldev library, but when you type 'pip install mysql' you're actually calling the wrong python version.

Or your superset is in a python2.7 venv and you're mysqldb is in a python3 one..

[miloradkrstevski]

Thank you very much @TianyangLi. That was the problem. I install all in python3, pip3, and venv is correct now. The connection to MySQL is established.

[sohelsarder]

My superset version info is :"version": "0.25.6" mysql version is : 5.7, python version info : python2.7
first of all SQLALCHEMY_DATABASE_URI would be like this
SQLALCHEMY_DATABASE_URI = 'mysql://{your mysql user name}:{your password }@localhost/{your db name}'

While I tried to use mysql as a meta data storage for superset after running
pip install mysqlclient
The first error I got: EnvironmentError: mysql_config not found
resolved this issue by running command: sudo apt-get install libmysqlclient-dev
then got error: ImportError: No module named MySQLdb
resolved this issue by running command: pip install MySQL-python

Then everything worked perfectly for me.

[bernarde01]

Thank you very much @TianyangLi. That was the problem. I install all in python3, pip3, and venv is correct now. The connection to MySQL is established.

Hi @sohelsarder. I'm sitting with the same error. I installed Superset via docker (as per the installation documantation). I get the "Unexpected error No module named 'MySQLdb'" problem when I try to connect a MySQL database. Can you offer any advice here?

Thanks

[bernarde01]

I just tried the install again, this time going through all the steps manually and not making use of docker. This solved the problem.

I think the issue with docker was that the mysqlclient installation wasn't in the correct virtual environment, however I do not know docker well enough to understand how this works exactly.

[bkarlson]

Folks, proper solution for Ubuntu:

cd /var/lib/superset
source venv/bin/activate
pip install mysqlclient

Done.

Explanation: superset runs in virtual environment and globally (system-wide) installed modules are not reachable from venv. Hence you'd need to dive into venv and install the module there.