Python MYSQL will not connect

Question

I have been helping troubleshoot a process on an old UBUNTU 16 server. We cannot migrate a site to a new server until we understand how it works as we need to recreate and troubleshoot.

Basically its a python3 script that connects to a MYSQL database and reads some data, and updates data under a certain condition. It is basically linked to ZOHO hence the need for this programming language.

The error has been diagonsed as a mysql connection error. Error 111 connection refused. The database is localhost

I have 100% verified the access for the username, password and database visibiity and that port 3306 is open. I can login using a MYSQL command on the CLU and show the database and its tables.

The connector is 100% pulling in the correct variables from the config.py as I have added a print(HOST, DBASE, USER, PASSWORD) just before the connector and verified they are correct.

I have also tried 127.0.0.1 instead of localhost. I have also (dont shoot me) put in the root username and password to remove any permissions issues

I am wondering if its possible to expand on the mysql error in python so I can get a very clear message as to WHAT part is causing an issue. Is there any command in MYSQL that could have been issued to specifically block python.

I have disabled UFW just in case, made no difference, and the general logs for MYSQL dont appear to contain anything useful

Exact error:

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Note: The server is Ubuntu 16, running MYSQL 5.7.26-0ubuntu0.16.04.1 with localhost access only. Python is 2.7.12

As noted this is an old server, that I want to scrap ASAP but until I can prove that the zoho connector is fixable, we cant proceed as its a blocker.

it a secret or why didn't you post the exact error message and code — nbk
– nbk, Commented Oct 11, 2021 at 16:46
please show our pyzhon code, this has nothing to do with mylsq or python, only your connection code is wrong — nbk
– nbk, Commented Oct 11, 2021 at 17:56

nbk · Accepted Answer · 2021-10-11 18:03:48Z

1

As you can see in theexample below.

the port is separate from the host

import mysql.connector

cnx = mysql.connector.connect(
    host='localhost',
    database='import_test',
    user='testuser',
    password='password***',
    port=3306
)

answered Oct 11, 2021 at 18:03

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MOLEDesign Over a year ago

Replacing database, user and password with verfified details (verified using phpmyadmin) I got mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused) Please note, i have another version of this server (a clone) where this worked fine

MOLEDesign Over a year ago

Let me expand. The server is virtual, We cloned it late last year and did a few security updates and this is now the live server. We are now, post covid, doing what we always wanted to do. If its any clue, we also cant TELNET to localhost on port 3306

nbk Over a year ago

this works in my local python 3.9.7 all libraries uptdate and a mysql 8.026 Mysql server

nbk Over a year ago

i don't beleive that you have a local mysql server running and it is quite unclear to what phpmyadmin or mysql.exe actually connects, but as you claim theay are working, chakcm the system on which python is runniong if there is a mysql server

MOLEDesign Over a year ago

This is a LAMP server running a Joomla instance, and a word press instance. Both up and running usinfg the localhost MYSQL service. The Python code connect to the Joomla database which has a custom table called ZoHo Sync. It uses the same username, database and password as the joomla instance to get data from the table

|

MOLEDesign · Accepted Answer · 2021-10-11 20:44:18Z

Turns out to be the most basic of all issues. On the old server we cloned from, everyting was open, zero lockdown including MYSQL listening to the world. On the new server, mysql had skip-networking was enabled.

Whereas the web instances were happily connecting directly to the socket, mysql was not activly listening on port 3306. I had changed my.cnf to enable networking but a different issue was stopping those changes taking place.

Once I had mysql working on port 3306 and I could see it listening, python now connected ok so some of the answers were correct. MYSQL was 'not working;' in a global sense.. HOWEVER this is a step backwards in terms of security. I did not want MYSQL open to the world

So I modified the code to use the unix socket of /var/run/mysqld/mysqld.sock, renenabled skip-networking and voila.. all is now fine with python, port 3306 is shutdown again and I have my first trial by fire of python complete

My config file defines DATABASE as local host, USER and PASSWORD but now has SOCKET as /var/run/mysqld/mysqld.sock and my code is

import mysql.connector

from models import ExportRecord

from config import HOST, USER, PASSWORD, DATABASE, SOCKET

def connect():
    return mysql.connector.connect(host=HOST, user=USER, passwd=PASSWORD, database=DATABASE, unix_socket=SOCKET)

Collectives™ on Stack Overflow

Python MYSQL will not connect

2 Answers 2

9 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Related