search

splitgraph / sgr

sgr (command line client for Splitgraph) and the splitgraph Python library
https://www.splitgraph.com
Other
325 stars 17 forks source link

sgr mount mysql_fdw won't connect to mysql db #809

Closed MaraScott closed 1 year ago

MaraScott commented 1 year ago

running 

sgr --verbosity DEBUG mount mysql_fdw schema_wp_demo -c splitgraph:splitgraph@127.0.0.1:3306 -o@- <<EOF
{
    "dbname": "splitgraph"
}
EOF

I get this 

error: Traceback (most recent call last):
error:   File "splitgraph/commandline/__init__.py", line 128, in invoke
error:   File "click/core.py", line 1259, in invoke
error:   File "click/core.py", line 1259, in invoke
error:   File "click/core.py", line 1066, in invoke
error:   File "click/core.py", line 610, in invoke
error:   File "splitgraph/commandline/mount.py", line 69, in _callback
error:   File "splitgraph/hooks/mount_handlers.py", line 66, in mount
error:   File "splitgraph/hooks/data_source/fdw.py", line 163, in mount
error:   File "splitgraph/hooks/data_source/fdw.py", line 179, in _create_foreign_tables
error:   File "splitgraph/hooks/data_source/fdw.py", line 572, in import_foreign_schema
error:   File "splitgraph/engine/postgres/psycopg.py", line 452, in run_sql
error: psycopg2.errors.FdwUnableToEstablishConnection: failed to connect to MySQL: Can't connect to MySQL server on '127.0.0.1:3306' (111)
error:

I tried different host : localhost, 127.0.0.1, 0.0.0.0 and I even open mysql access publicly, but nothing works.

mildbyte commented 1 year ago

It's likely because the engine is running in Docker and can't access your host loopback interface (if you're running MySQL on 127.0.0.1). Can you try running MySQL on 0.0.0.0 and using the Docker's bridge interface IP 172.17.0.1 in sgr mount? 

sgr --verbosity DEBUG mount mysql_fdw schema_wp_demo -c splitgraph:splitgraph@172.17.0.1:3306 -o@- <<EOF
{
    "dbname": "splitgraph"
}
EOF
MaraScott commented 1 year ago

Is it ment to work with mysql only or MariaDB is accepted too ?

mildbyte commented 1 year ago

MariaDB should work as well, though we don't explicitly test it in our integration tests (just MySQL).

MaraScott commented 1 year ago

then I got this log

error: Traceback (most recent call last):
error:   File "splitgraph/commandline/__init__.py", line 128, in invoke
error:   File "click/core.py", line 1259, in invoke
error:   File "click/core.py", line 1259, in invoke
error:   File "click/core.py", line 1066, in invoke
error:   File "click/core.py", line 610, in invoke
error:   File "splitgraph/commandline/mount.py", line 69, in _callback
error:   File "splitgraph/hooks/mount_handlers.py", line 66, in mount
error:   File "splitgraph/hooks/data_source/fdw.py", line 163, in mount
error:   File "splitgraph/hooks/data_source/fdw.py", line 179, in _create_foreign_tables
error:   File "splitgraph/hooks/data_source/fdw.py", line 572, in import_foreign_schema
error:   File "splitgraph/engine/postgres/psycopg.py", line 452, in run_sql
error: psycopg2.errors.FdwUnableToEstablishConnection: failed to connect to MySQL: Host '172.17.0.2' is not allowed to connect to this MariaDB server
error:
mildbyte commented 1 year ago

You might need to explicitly grant access in MariaDB to connect from this IP:

https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/#granting-user-connections-from-remote-hosts

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.17.0.2'     -- or the user you're trying to connect as
MaraScott commented 1 year ago

Hey, thanks a lot, it work as expected :) have a nice week end