0

I have a mysql database running on a localhost (ubuntu 16.04). On the same host I have a docker container in which a python script is running. This script has to connect to the mysqldb on the local host. As was described in these posts (post1, post2) I set bind-address=0.0.0.0 for my local database and found the ip-address of my local host and used it in my python script to connect to the database, but it did not work. Below I show my set-up and how I run the docker container. My python script (analysis.py) looks as follows:

import pandas as pd
import sqlalchemy as db

def find_max_age():
   cnx = db.create_engine('mysql+mysqlconnector://root:password@172.17.0.1:3306/datasets')
   cnx_res = db.create_engine('mysql+mysqlconnector://root:password@172.17.0.1:3306/results')
   df = pd.read_sql("select * from test_table", cnx)
   idx = df['age'].idxmax() == df.index
   df_res = df[idx]

   df_res.to_sql('max_age4', con=cnx_res, index=False)


if __name__ == '__main__':
   find_max_age()

My Dockerfile looks as follwos:

FROM python:2.7-slim
EXPOSE 80 3306
WORKDIR /app
COPY requirements.txt /app
RUN pip install -r requirements.txt
COPY analysis.py /app
CMD python analysis.py

Finally, the requirements.txt looks like

mysql-connector-python
sqlalchemy
pandas

I build the docker image as follows:

docker build -t max_age_app .

Then I start the container using this image as follows:

docker run -d max_age_app:latest

The container exits with exit code 1 and when I take a look at the respective log of the container I find the following error in it:

> Traceback (most recent call last):
  File "analysis.py", line 24, in <module>
    find_max_age()
  File "analysis.py", line 11, in find_max_age
    df = pd.read_sql("select * from test_table", cnx)
  File "/usr/local/lib/python2.7/site-packages/pandas/io/sql.py", line 397, in read_sql
    chunksize=chunksize)
  File "/usr/local/lib/python2.7/site-packages/pandas/io/sql.py", line 1063, in read_query
    result = self.execute(*args)
  File "/usr/local/lib/python2.7/site-packages/pandas/io/sql.py", line 954, in execute
    return self.connectable.execute(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2074, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 400, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 788, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 529, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1190, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 347, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 474, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 671, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/__init__.py", line 172, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 78, in __init__
    self.connect(**kwargs)
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/abstracts.py", line 731, in connect
    self._open_connection()
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 179, in _open_connection
    sqlstate=exc.sqlstate)
sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 2003 (HY000): Can't connect to MySQL server on '172.17.0.1' (111) (Background on this error at: http://sqlalche.me/e/4xp6)

In order to determine the ip of the localhost I used ifconfig command, which yielded something like this:

docker0   Link encap:Ethernet  HWaddr 02:42:a2:a6:d7:ff  
          inet addr:172.17.0.1 

enp0s3    Link encap:Ethernet  HWaddr 08:00:27:bb:7e:b5  
          inet addr:10.0.2.15 

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1

So I tried 172.17.0.1 in order to connect to the local database from within the container but it did not work.

Do I have to match any ports between the container and the localhost via the -p option when I start the container?

I would appreciate any help.

Riko
  • 155
  • 1
  • 3
  • 10

2 Answers2

0

You shouldn't EXPOSE the port 3306 on the container, since the MySQL server is listening outside it, on the localhost. I suspect it's a networking issue, so try to see if the localhost has the interface with the address 172.17.0.1 and if you can reach this address from inside your container (ex. try docker exec -ti _your_container_name /bin/sh and then try ping 172.17.0.1). You should also check the mysql logs to see if you have some error reported there.

Ass3mbler
  • 3,855
  • 2
  • 20
  • 18
  • I removed EXPOSE 3306 from the Dokerfile(I thought this was the default port for mysql),but it did not help though. Does port 80 has to be exposed, it is probably irrelevant?Since the container exits almost immediately after I start it I had to use `docker run -ti max_age_app /bin/bash` in order to get inside it.Then I pinged the ip and it worked. In order to determine the ip on the localhost I used ifconfig, there were actually another id-address, I checked it as well.It pinged but the container still could not establish the connection. In mysql log I were only old errors – Riko Jan 05 '19 at 01:29
  • if you try netstat -nap | grep "3306" on localhost do you see the mysqld process binded? – Ass3mbler Jan 05 '19 at 01:38
  • I see the following output (not sure what it means): `sudo netstat -nap | grep "3306" tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 19319/mysqld tcp 1 0 127.0.0.1:60474 127.0.0.1:3306 CLOSE_WAIT 22935/python` – Riko Jan 05 '19 at 01:43
  • It seems that there was a connection. Do you have any other python process connecting to your mysqld server? – Ass3mbler Jan 06 '19 at 00:47
  • i think there was indeed a connection, but the docker container was not authorized to access the database, so each time the python script from the container tried to connect the database refused the access. the problem is now solved as I described in my answer. anyways thanks for your hints! – Riko Jan 06 '19 at 15:12
0

In order to solve this problem one has to correctly configure the mysql database on the localhost. In addition to what was discussed above one has to do the following steps:

  • Find the IP-address of your docker container with the python app. You can use docker inspect <container_name>.
  • Create in your local mysql database a new user (and a respective password) for that IP-address. Info on how to do it you can find in this post. How to set password policies in mysql is described here.

Afterwards, simply starting the container with the command docker run -d max_age_app will suffice for the python script to write the data into the database on the localhost.

Riko
  • 155
  • 1
  • 3
  • 10