python Mysql.connector doesn´t connect BUT mysql server is up and working

Question

This is the docker-compose file

version: '3.3'
services:
  mysql:
    image: mysql:latest
    restart: always
    environment:
      MYSQL_DATABASE: 'bot'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: '123'
      MYSQL_ROOT_PASSWORD: 'root'
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - mybot:/var/lib/mysql
  
  python:    
    restart: always
    build: .
    environment:
      MYSQL_DATABASE: 'bot'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: '123'
      MYSQL_ROOT_PASSWORD: 'root'
    volumes:
      - mybot:/usr/app
    command: tail -f /dev/null
volumes:
  mybot:

and Dockerfile

FROM python

user root
WORKDIR /usr/src
COPY requirements.txt ./
RUN apt-get update && apt-get install -y python3 python3-pip
RUN pip3 install -r requirements.txt
RUN mkdir -p new
COPY . ./new

Now, when I run sh terminal from python container, i had access to mysql database without problem (before that i manually installed default-mysql-server)

mysql -u user -p -u 172.X.X.X
mysql>

and also i am able to ping between containers with no errors, BUT when I run main.py I get the following error

mysql.connector.errors.InterfaceError: 2055: Lost connection to MySQL server at '172.X.X.X:3306', system error: 1 [SSL: TLSV1_ALERT_PROTOCOL_VERSION] tlsv1 alert protocol version (_ssl.c:1123)

main.py is connecting to mysql like this

self.mydb = mysql.connector.connect(host=params["host"],
                                            user=params["user"],
                                            password=params["pass"],
                                            database=params["database"])

and mysql my.cnf file has this custom lines

port = 3306
bind-address = 172.X.X.X

"outside" docker containers, everything works great.. Thank you!

Brazilein

Answer 1

This seems to be a bug in the mysql-connector that occurs in some versions.
The easiest way to workaround this is to switch to pymysql

Install pymysql

pip install pymysql

Simple select example:

import pymysql

connection = pymysql.connect(user='user', passwd='pass', host='host', database='database')
cursor = connection.cursor()
query = ("SELECT * FROM myTable")
cursor.execute(query)
for item in cursor:
    print item

Shamus

Answer 2

I've tested with the above Docker configuration and I was able to connect:

root@bf838ebaa060:/usr/src# pip list
Package                Version
---------------------- -------
mysql-connector-python 8.0.21
pip                    20.1.1
protobuf               3.12.2
setuptools             49.2.0
six                    1.15.0
wheel                  0.34.2

root@bf838ebaa060:/usr/src# cat test.py
import mysql.connector

config = {
    "host": "172.x.x.x",
    "port": 3306,
    "user": "root",
    "password": "root"
}

cnx = mysql.connector.connect(**config)
cur = cnx.cursor()
cur.execute("SELECT VERSION()")
res = cur.fetchone()
print(res)
cur.close()
cnx.close()

root@bf838ebaa060:/usr/src# python test.py
('8.0.21',)

Disclaimer: I'm the lead developer of MySQL Connector/Python. Please let me know if it still doesn't work for you.

Goode

That configuration works and give the same output, but using an older version of MySQL connector python give me the same error as before: Package Version ---------------------- ------- mysql-connector-python 8.0.5 pip 20.1.1 protobuf 3.12.2 setuptools 49.2.0 six 1.15.0 wheel 0.34.2 Thanks for your help! tell me if something is needed! – Brazilein

Version 8.0.5 is quite old, please upgrade (if you can). The latest release contains lots of improvements and bug fixes. – Goode

This was it, upgrading to latest version fixed the issue for me – Aerography