python mysql.connector module uses wrong IP address

Question

I am having an issue when using python mysql.connector module in a script. When I run to connect to a docker container running mysql v8 from my script it tries to connect to the wrong IP address. Below is the sample function:

import mysql.connector
def connect():
    db =  mysql.connector.connect(
        host="172.17.0.2",
        user="user",
        passwd="password",
        auth_plugin='mysql_native_password')

When this is ran, the following error is produced: mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'user'@'172.17.0.1' (using password: YES)

Here is the IP of the docker container that I am trying to connect to

IPAddress": "172.17.0.2"

What really makes this interesting is that I can connect to the database just fine with the mysql.connector when using in an interactive python session. See below

Python 3.7.2 (default, Jan 16 2019, 19:49:22) 
[GCC 8.2.1 20181215 (Red Hat 8.2.1-6)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysql.connector
>>> db =  mysql.connector.connect(
... host="172.17.0.2",
... user="user",
... passwd="passwd",
... auth_plugin='mysql_native_password')
>>> mycursor =  db.cursor()
>>> mycursor.execute("show databases")
>>> for x in mycursor: print(x)
... 
('food',)
('information_schema',)

Here is the version of mysql-connector that I am running on Fedora 29

pip list |grep mysql-connector
mysql-connector-python 8.0.15

Here is docker version info as well

Client:
 Version:           18.09.2
 API version:       1.39
 Go version:        go1.10.6
 Git commit:        6247962
 Built:             Sun Feb 10 04:13:54 2019
 OS/Arch:           linux/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          18.09.2
  API version:      1.39 (minimum version 1.12)
  Go version:       go1.10.6
  Git commit:       6247962
  Built:            Sun Feb 10 03:47:25 2019
  OS/Arch:          linux/amd64
  Experimental:     false

Any thoughts on what would cause running this from a script to fail?

Are you running the script from a different Docker container, or from the host? — David Maze, Feb 27 '19 at 23:54
I am running the script from the host. I am a vim fan so was writing my scripts using vim and virtualenv from command line. I later downloaded pycharm and installed mysql-connector-python from pycharm's pip repo and was not able to reproduce this error. I am thinking it may have been an issue with the virtualenv? — tommy davison, Mar 02 '19 at 13:53

Mostafa Hussein · Accepted Answer · 2019-03-02T14:55:48.443

0

The script does not try to connect to wrong IP. The error message

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'user'@'172.17.0.1' (using password: YES)

Means that this user user which coming through this host 172.17.0.1 will not be able to connect to the database which you are trying to access at 172.17.0.2. What you need to do is to allow remote MySQL connection from the host 172.17.0.1 for this user user which will make the script able to work as expected.

Update: Not sure if that is related but the python script shows that the password is password and the console shows that the password is passwd. if the remote access is already allowed ensure that you typed the password correctly

edited Mar 02 '19 at 14:55

answered Feb 28 '19 at 00:43

Mostafa Hussein

11,063
3
36
61

But its interesting, there is no IP running at 172.17.0.1 so why would my script try and connect to a IP that isn't there? I'd also like to note, that I think there must have been an issue with the pip package that was downloaded in my virtualenv. I later downloaded pycharm and downloaded mysql-connector-python from pycharms repo's and was able to connect to the correct IP. – tommy davison Mar 02 '19 at 13:49
this ip `172.17.0.1` is the ip where you started your script (source ip ) not the target ip where you host the database – Mostafa Hussein Mar 02 '19 at 13:53
python script in here (172.17.0.1) ----> database in here (172.17.0.2). so the database need to authorize the source ip by allowing remote connections – Mostafa Hussein Mar 02 '19 at 13:54
if you executed `ifconfig` on the host machine you will notice that its ip is 172.17.0.1 written in the docker0 interface. so you are trying to connect from your host to the container through `docker0` – Mostafa Hussein Mar 02 '19 at 13:57
Understood with the docker networking portion. But the issue here still is the fact that an interactive session allows for the connection where a module does not. The mysql instance is allowing connections from anywhere for user. I can connnect to this DB with DBeaver as well. This issue was not related to remote access permissions. – tommy davison Mar 02 '19 at 14:40
I am not sure why the console connected without issues. are you running it from the same container ? Also you can verify if it's related to allow remote access by checking mysql itself the Users table. – Mostafa Hussein Mar 02 '19 at 14:42
The module and interactive sessions are being ran from my host machine, so no containers are being used to run the python module at this time. The remote access is allowed. See below: mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | root | % | | user | % | – tommy davison Mar 02 '19 at 14:51
@tommydavison I noticed something with your code and have updated the answer – Mostafa Hussein Mar 02 '19 at 14:56
1

good eye.... I really can't believe I didn't notice that. I changed the code in my module and am able to connect. Thank you for the extra set of eyes and trouble shooting this issue with me. – tommy davison Mar 02 '19 at 15:02

python mysql.connector module uses wrong IP address

1 Answers1