Search code examples
mysqldockerapache-superset

Superset with Docker cannot add connection to external mysql

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

enter image description here

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
  1. 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

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

enter image description here

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..

enter image description here

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

enter image description here

Solution

  • 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

    1. service mysql start

    2. 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,

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

    2. 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

    1. 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)

    2. 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:[email protected]:3306/database_name'