I have mysql and apache superset setup on dockers and connected by a bridge network, what will theSQLAlchemy URI be?

Question

I pulled the official superset image:

git clone https://github.com/apache/incubator-superset.git

then added the MYSQL Client to requirements.txt

cd incubator-superset
touch ./docker/requirements-local.txt
echo "mysqlclient==1.4.6" >> ./docker/requirements-local.txt
docker-compose build --force-rm
docker-compose up -d

After which I made the MYSQL Container

docker run --detach --network="incubator-superset_default" --name=vedasupersetmysql --env="MYSQL_ROOT_PASSWORD=vedashri" --publish 6603:3306 mysql

Then connected Mysql to the Superset Bridge.

The bridge network is as follows:

docker inspect incubator-superset_default
[
    {
        "Name": "incubator-superset_default",
        "Id": "56db7b47ecf0867a2461dddb1219c64c1def8cd603fc9668d80338a477d77fdb",
        "Created": "2020-12-08T07:38:47.94934583Z",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": null,
            "Config": [
                {
                    "Subnet": "172.19.0.0/16",
                    "Gateway": "172.19.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": true,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {
            "07a6e0d5d87ea3ccb353fa20a3562d8f59b00d2b7ce827f791ae3c8eca1621cc": {
                "Name": "superset_db",
                "EndpointID": "0dd4781290c67e3e202912cad576830eddb0139cb71fd348019298b245bc4756",
                "MacAddress": "02:42:ac:13:00:02",
                "IPv4Address": "172.19.0.2/16",
                "IPv6Address": ""
            },
            "096a98f22688107a689aa156fcaf003e8aaae30bdc3c7bc6fc08824209592a44": {
                "Name": "superset_worker",
                "EndpointID": "54614854caebcd9afd111fb67778c7c6fd7dd29fdc9c51c19acde641a9552e66",
                "MacAddress": "02:42:ac:13:00:05",
                "IPv4Address": "172.19.0.5/16",
                "IPv6Address": ""
            },
            "34e7fe6417b109fb9af458559e20ce1eaed1dc3b7d195efc2150019025393341": {
                "Name": "superset_init",
                "EndpointID": "49c580b22298237e51607ffa9fec56a7cf155065766b2d75fecdd8d91d024da7",
                "MacAddress": "02:42:ac:13:00:06",
                "IPv4Address": "172.19.0.6/16",
                "IPv6Address": ""
            },
            "5716e0e644230beef6b6cdf7945f3e8be908d7e9295eea5b1e5379495817c4d8": {
                "Name": "superset_app",
                "EndpointID": "bf22dab0714501cc003b1fa69334c871db6bade8816724779fca8eb81ad7089d",
                "MacAddress": "02:42:ac:13:00:04",
                "IPv4Address": "172.19.0.4/16",
                "IPv6Address": ""
            },
            "b09d2808853c54f66145ac43bfc38d4968d28d9870e2ce320982dd60968462d5": {
                "Name": "superset_node",
                "EndpointID": "70f00c6e0ebf54b7d3dfad1bb8e989bc9425c920593082362d8b282bcd913c5d",
                "MacAddress": "02:42:ac:13:00:07",
                "IPv4Address": "172.19.0.7/16",
                "IPv6Address": ""
            },
            "d08f8a2b090425904ea2bdc7a23b050a1327ccfe0e0b50360b2945ea39a07172": {
                "Name": "superset_cache",
                "EndpointID": "350fd18662e5c7c2a2d8a563c41513a62995dbe790dcbf4f08097f6395c720b1",
                "MacAddress": "02:42:ac:13:00:03",
                "IPv4Address": "172.19.0.3/16",
                "IPv6Address": ""
            },
            "e21469db533ad7a92b50c787a7aa026e939e4cf6d616e3e6bc895a64407c1eb7": {
                "Name": "vedasupersetmysql",
                "EndpointID": "d658c0224d070664f918644584460f93db573435c426c8d4246dcf03f993a434",
                "MacAddress": "02:42:ac:13:00:08",
                "IPv4Address": "172.19.0.8/16",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {
            "com.docker.compose.network": "default",
            "com.docker.compose.project": "incubator-superset",
            "com.docker.compose.version": "1.26.0"
        }
    }
]

How should I form the SQLAlchemy URI? I have tried mysql://user:password@8088:6603/database-name

But it shows connection error, when I enter this URI.

If there is any related documentation, that would also help.

Answer 1

The issue was not related to superset or network. You configured the right network but haven't enabled default-authentication-plugin on MySQL docker images. Due to this error showed on the console was

 Plugin caching_sha2_password could not be loaded:

To reproduce:

from sqlalchemy import create_engine
engine = create_engine('mysql://root:[email protected]/mysql')
engine.connect()

error logs:

   sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1045, 'Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory')
    (Background on this error at: http://sqlalche.me/e/13/e3q8)

To resolve the issue:

Create MySQL image with default-authentication-plugin

docker run --detach --network="incubator-superset_default" --name=mysql --env="MYSQL_ROOT_PASSWORD=sample" --publish 3306:3306 mysql --default-authentication-plugin=mysql_native_password 

Superset already has a User-defined bridge network, so you can use both formats

mysql://root:sample@mysql/mysql
mysql://root:[email protected]/mysql