OSX El Capitan sqlalchemy Can't connect to MySQL server on '127.0.0.1' (111)

The python in this app is running from a docker container. The container is running in network mode: "host".

I'm having trouble connecting to a MySQL database through the python package sqlalchemy. I get the following error.

OperationalError: (OperationalError) (2003, "Can't connect to MySQL server on '127.0.0.1' (111)") None None

The following terminal command works fine

mysql -h 127.0.0.1 -u my_user --password='password' db -e "SHOW TABLES;"
enter code here

Maybe sqlalchemy isn't using the correct configuration you say? I have printed out the configuration in a debug line right before the first connection to MySQL:

  print config.get('repository', 'host') // 127.0.0.1
  print config.get('repository', 'user') // my_user
  print config.get('repository', 'passwd') // password

Maybe that configuration still isn't making it into sqlalchemy? Lets print out the engine string

  engine = getUnaffiliatedEngine()
  print engine //Engine(mysql://my_user:***@127.0.0.1:3306)
  with engine.connect() as connection:
    for s in statements:
      if s.strip():
        connection.execute(s)

Maybe there's more than 1 version of mysql running? Only one process is running:

ps -ef | grep mysql

74 15459 1 0 10:14AM ?? 0:00.80 /usr/local/mysql/bin/mysqld --user=_mysql

Maybe that user doesn't have access to the database?

mysql> select User, Host from mysql.user;

+------+--------------------------+
| User | Host                     |
+------+--------------------------+
|my_user 127.0.0.1                |
| root | 127.0.0.1                |
| root | ::1                      |
|      | localhost                |
| root | localhost                |
+------+--------------------------+

Similarly 'show databases' shows that the db exists. I have granted all privs to that user for every database. I have flushed the privs too.

Maybe some kind of firewall rule is stopping the connection? I use little snitch, and the entire firewall has been taken down to test this.

I don't even know what else to debug at this point. Here is a reduced version of the python script throwing the connection error:

DSN_FORMAT = "mysql://%(user)s:%(passwd)s@%(host)s:%(port)s"

def getDSN():
  return DSN_FORMAT % dict(config.items("repository"))

def getUnaffiliatedEngine():
  return create_engine(getDSN())

def reset(offline=False):
  config.loadConfig()
  dbName = config.get('repository', 'db')
  print config.get('repository', 'host')
  print config.get('repository', 'user')
  print config.get('repository', 'passwd')

  resetDatabaseSQL = (
      "DROP DATABASE IF EXISTS %(database)s; "
      "CREATE DATABASE %(database)s;" % {"database": dbName})
  statements = resetDatabaseSQL.split(";")

  engine = getUnaffiliatedEngine()
  print engine
  with engine.connect() as connection:
    for s in statements:
      if s.strip():
        connection.execute(s)

Here is the output for tcpdump -i lo0 port 3306 :

11:44:41.224036 IP localhost.58797 > localhost.mysql: Flags [P.], seq 3915736486:3915736498, ack 2134634265, win 12519, options [nop,nop,TS val 980567261 ecr 980503692], length 12
11:44:41.224105 IP localhost.mysql > localhost.58797: Flags [.], ack 12, win 12737, options [nop,nop,TS val 980567261 ecr 980567261], length 0
11:44:41.224178 IP localhost.mysql > localhost.58797: Flags [P.], seq 1:19, ack 12, win 12737, options [nop,nop,TS val 980567261 ecr 980567261], length 18
11:44:41.224218 IP localhost.58797 > localhost.mysql: Flags [.], ack 19, win 12519, options [nop,nop,TS val 980567261 ecr 980567261], length 0
11:45:07.422776 IP localhost.58796 > localhost.mysql: Flags [P.], seq 2953728354:2953728366, ack 432872138, win 12483, options [nop,nop,TS val 980593366 ecr 980533534], length 12
11:45:07.422807 IP localhost.mysql > localhost.58796: Flags [.], ack 12, win 12729, options [nop,nop,TS val 980593366 ecr 980593366], length 0
11:45:07.422856 IP localhost.mysql > localhost.58796: Flags [P.], seq 1:19, ack 12, win 12729, options [nop,nop,TS val 980593366 ecr 980593366], length 18
11:45:07.422877 IP localhost.58796 > localhost.mysql: Flags [.], ack 19, win 12482, options [nop,nop,TS val 980593366 ecr 980593366], length 0

MySQL 5.6 OSX El Capitan 10.11.6

Solution

That last comment you wrote is very important and probably the reason of the problem.

I'm pretty sure that your Docker container has its own loopback interface with an address 127.0.0.1 that is different than your OSX loopback interface where you have running the MySQL.

I recommend you put your MySQL listening on an address that is visible from inside your container. You can debug that config easily making telnet ip_address 3306 from inside your container.