Superset with Docker cannot add connection to external mysql

Question

I am newbie on using Ubuntu, I am trying to install apache-superset and successfully installed it by using Docker by directly sudo docker pull apache/superset, but I am stuck at adding Database Connector to the running superset

As for my local database I am using MySQL and I happen to use SSH Tunnel in localhost to access it in server. So I think at Docker container perspective this must be an "external" databases

What I have tried:

1. I installed mysqlclient from pip3
2. By following this references: https://devopsheaven.com/docker/devops/add-host/link/2017/10/04/connect-external-services-from-docker-container.html

I tried to type: sudo docker run -it mysql -h 192.168.100.1 -P 33063 -u czjovan --password=mypw cz_payment_merged but then i get:

2021-03-04 11:34:53+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.23-1debian10 started.
2021-03-04 11:34:53+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
    command was: mysqld -h 192.168.100.1 -P 33063 -u czjovan --password=mypw cz_payment_merged --verbose --help --log-bin-index=/tmp/tmp.EV6L0jrspQ
    2021-03-04T11:34:53.402148Z 0 [ERROR] [MY-010124] [Server] Fatal error: Can't change to run as user 'czjovan' ;  Please check that the user exists!
2021-03-04T11:34:53.403355Z 0 [ERROR] [MY-010119] [Server] Aborting

3. By Following this also: From inside of a Docker container, how do I connect to the localhost of the machine?

I tried to type: sudo docker run --rm it --network=host mysql mysql -h 127.0.0.1 but got error 2003 (hy000) can't connect to mysql server on 127.0.0.1

4. I tried to add mysqlconnector to sqlalchemy uri, but the driver not found

I am not an expert by setting these, I lack of Docker mechanism.. appreciate it if anyone willingly to direct me how to step by step resolving this..

UPDATE-------------------------------------------------:

-> Following Mustafa Guler to add -p 3306:3306, the mysql container now starts..

but I still cannot add database in Superset, what should I do next?

Answer 1

You need to provide the IP of the docker host machine. If you're using default networking, use the static IP 172.17.0.1 for the host. Alternatively you can try host.docker.internal as the hostname.

Answer 2

You need to define user and pass as environment to create new user during start of mysql container. Maybe problem is user and pass MYSQL_USER, MYSQL_PASSWORD Also you do not expose 3306 port on docker run. Please use -p 3306:3306

Answer 3

I found out that installing Superset manually from scratch than using Superset from Docker Container are more a solution to me, since configuring Superset from docker manually can be a little cumbersome, what I did:

#-- Install Superset and MySQL Locally from Scratch ---#

1. sudo pip install apache-superset (
2. sudo apt install mysql-client-core-8.0
3. sudo apt install mysql-server
4. sudo pip install mysqlclient (for Superset Database Connectors)

#NOTE: there is also a package called 'superset' in pip, in my case i uninstalled this to ensure that only apache-superset is used

5. service mysql start

6. when mysql start, try to set password for the first time:

   sudo mysqladmin -u root password

then to test it: mysql -u root -p, enter a created password

if it can enter mysql normally, the password set is successful,

7. CREATE DATABASE superset (this will save all config that superset progress will be saved)

8. Editing config.py in apache superset, which usually located on /usr/local/lib/python3.8/dist-packages/superset/config.py (this depends on pip installation from no 1),

8a) Edit the sqlalchemy uri part in config.py, so it can connect to a local installed mysql in ubuntu, to something like 'mysql://root:@localhost:3306/superset'

8b) Ensure that the database part in config.py is superset, or the same name with database name created in MySQL

9. After all database, sqlalchemy uri, and database name is prepared, its good to go to follow with Superset configs stated from: https://superset.apache.org/docs/installation/installing-superset-from-scratch

   A) sudo superset db upgrade

   B) sudo superset fab create-admin

   C) sudo superset load examples

   D) sudo superset init

   E) sudo superset run -p 8088 --with-threads --reload --debugger (run this to start superset daily)

10. Run a Private SSH Tunnel Connecting to Database (Optional, in my case I use SSH Tunnel to access database)

And then I am able to add connection locally in Superset defining sqlalchemy uri

A) towards the SSH Tunnel mysql://(server_user):(server_password)@127.0.0.1:33063/(database_name) (this is outside from local MySQL)

B) towards Local Installed MySQL 'mysql://root:rootpwd@127.0.0.1:3306/database_name'

Answer 4

I will recommend to use docker than manual installation by pip. Package dependencies especially flask are real problems. Do not follow docker compose as beginner.

I had a problems using docker compose. Port is closed was annoying problem due to networking. Host.docker.internal doesn’t worked for me on Ubuntu 22 on Google cloud. I switched to manual installation and it was package versions hell on python 3.8. I would like to recommend to not follow official doc and use better approach with single docker image to start. Instead of running 5 containers by compose, run everything in one. Use official docker image, here image. Than modify docker file as follows to install custom db driver:

FROM apache/superset
USER root
RUN pip install mysqlclient
RUN pip install sqlalchemy-redshift
USER superset 

Second step is to build new image based on docker file description. To avoid networking problems start both containers on same network (superset, your db) easier is to use host network. I used this on Google cloud, example as follow:

docker run -d --network host --name superset supers

The same command to start container with your database. —network host. This solved my problems. More about in whole step to step tutorial: medium or here blog