I've tried EVERYTHING: 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)

Question

I have been trying to deploy a Streamlit app on Heroku and connect to a MySQL database. With a consistent error: InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)

Generally, I connect to the database the following way:

    mysql.connector.connect(host="localhost",user="****",
        password="****",
        database="**********"
        )

I've looked in my MySQL config file (my.ini file in my case) for the bind-address = 127.0.0.1, the file exists but the line isn't there.
I have tried restarting MySQL.
Grant privileges:

Varying in different scopes, trying on database or just generally.

GRANT ALL PRIVILEGES ON <databasename>.* TO '<username>'@'localhost' IDENTIFIED BY '<password>';

Creating a new user:

Again, varying in different scopes.

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

New admin account:

giving VERY wide scope

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION

Considered Windows Firewall

https://www.opendental.com/manual/port3306.html

I think the issue is something to do with the fact that there are no active connections (but I am unsure what to do):

C:\Users\madel>netstat -tnp

Active Connections

  Proto  Local Address          Foreign Address        State           Offload State

Further:

C:\Users\madel>netstat -tna | findstr LISTEN

-tna | findstr LISTEN
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:3306           0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:5040           0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:6646           0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:7680           0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:33060          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:49664          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:49665          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:49666          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:49667          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:49668          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:49674          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:53050          0.0.0.0:0              LISTENING       InHost
  TCP    0.0.0.0:57621          0.0.0.0:0              LISTENING       InHost
  TCP    127.0.0.1:28385        0.0.0.0:0              LISTENING       InHost
  TCP    127.0.0.1:28390        0.0.0.0:0              LISTENING       InHost
  TCP    127.0.0.1:49350        0.0.0.0:0              LISTENING       InHost
  TCP    127.0.0.1:49351        0.0.0.0:0              LISTENING       InHost
  TCP    192.168.0.6:139        0.0.0.0:0              LISTENING       InHost
  TCP    192.168.56.1:139       0.0.0.0:0              LISTENING       InHost
  TCP    [::]:135               [::]:0                 LISTENING       InHost
  TCP    [::]:445               [::]:0                 LISTENING       InHost
  TCP    [::]:3306              [::]:0                 LISTENING       InHost
  TCP    [::]:7680              [::]:0                 LISTENING       InHost
  TCP    [::]:33060             [::]:0                 LISTENING       InHost
  TCP    [::]:49664             [::]:0                 LISTENING       InHost
  TCP    [::]:49665             [::]:0                 LISTENING       InHost
  TCP    [::]:49666             [::]:0                 LISTENING       InHost
  TCP    [::]:49667             [::]:0                 LISTENING       InHost
  TCP    [::]:49668             [::]:0                 LISTENING       InHost
  TCP    [::]:49674             [::]:0                 LISTENING       InHost
  TCP    [::1]:49671            [::]:0                 LISTENING       InHost

Next step (I used my MySQL password here?? unsure)

C:\Users\madel>nc -v 127.0.0.1 3306
LAPTOP-2KQID6GI [127.0.0.1] 3306 (?) open
J
   →27
♂9aN↑
    ☻  ▀§          "K\↓sK8e¶▼>w caching_sha2_password ******
!  ☺ ä♦#08S01Got packets out of order

HALP!

Check if your mysql server is running properly `netstat -tulpn | grep LISTEN`, if its running with 127.0.0.1 and on correct port(this will give you local bind address on which the mysql server is running as along with port no. Try connecting to it to see if you are able to connect to it or not - `nc -v 127.0.0.1 PORT`. 111 means connection refused, so updating users/privileges would not help. — Jay, Dec 17 '21 at 17:56
Also check that the FireWall is not denying you access. That looks like a server not running type erro which can mean that the filewall is blocking access and making it look like the server is not running — RiggsFolly, Dec 17 '21 at 17:58
@Jay I am unable to do the 'u' or 'l' with netstat, these are the only options I see: https://www.ionos.com/digitalguide/server/tools/introduction-to-netstat/ — mk2080, Dec 17 '21 at 18:01
I dont know, do you have a firewall? Do you know which one? I dont know the answer to any of these questions — RiggsFolly, Dec 17 '21 at 18:06
Ok, so you have a mysql server listening on `0.0.0.0:3306`. Does `nc -v 127.0.0.1 3306` show connection succeeded or it says connection refused? — Jay, Dec 17 '21 at 18:13
if the port is listening in netstat, there must be a firewall blocking it. — Barmar, Dec 17 '21 at 18:14
@Jay on windows, so downloading and configuring netcat. One sec — mk2080, Dec 17 '21 at 18:20
@Barmar I went in and added a new inbound rule for connections over port 3306 (https://www.opendental.com/manual/port3306.html) — mk2080, Dec 17 '21 at 18:43
Ok, so you can also connect to your mysql server running locally, but your python program is getting `connection refused` when connecting to the same `127.0.0.1:3306`. What is your python program running as? I'm not familiar with the windows firewall, is there a way to run python program as sudo ;) ? — Jay, Dec 17 '21 at 18:45
So your terminal can access the mysql server but not your program. Might be an issue with the `localhost` binding. Replace `localhost` in your `mysql.connector.connect(host="localhost",user="****",` with `127.0.0.1` and it should start working without any new FW rules. — Jay, Dec 17 '21 at 18:47
Now, hang on. You said you're running on Heroku, right? That means your Python code is running on a computer somewhere out on the Internet. You can't connect to your desktop computer by using "localhost". "Localhost" means the computer that belongs to Heroku. You can't access a port on your Windows computer unless you have a public static IP address and port forwarding in your router. — Tim Roberts, Dec 17 '21 at 18:51
@Jay I tried replacing with `'127.0.0.1'` (and 127.0.0.1 for good measure because I'm desperate), still getting the same error. — mk2080, Dec 17 '21 at 18:58
@TimRoberts I connected Heroku to my github repository. I'm not sure I follow "unless you have a public static IP address and port forwarding in your router." do you have any suggestions of things to try? — mk2080, Dec 17 '21 at 18:59
If your MySQL database is running on your local computer, and your Python code is running on Heroku's computer, then isn't it clear those are two separate machines? "localhost" means Heroku's computer. It don't have any idea that you even HAVE a local computer, much less what it's IP address is. If you plan to run your code on a public server, then you need to have your database on a public database server. Heroku does offer Postgres services, but not MySQL. — Tim Roberts, Dec 17 '21 at 19:09
Think about it this way. Can you tell me how I can connect to your database from here at my desk? If you can't, then there's no way it can be reached from Heroku's computer. (Well, technically, Amazon's computers, since Heroku runs on AWS.) — Tim Roberts, Dec 17 '21 at 19:11
@TimRoberts Is that possible to configure MySQL to make it publically accessible? Or accessible to AWS? I was under the impression it was: 1) https://phoenixnap.com/kb/mysql-remote-connection 2) https://stackoverflow.com/questions/1420839/cant-connect-to-mysql-server-error-111 — mk2080, Dec 17 '21 at 19:16
You will need to install and configure mysql in Heroku to which your python program can connect to. https://roytuts.com/how-to-deploy-python-flask-mysql-based-application-in-heroku-cloud/ — Jay, Dec 17 '21 at 19:21
It's not a MySQL issue, it's a networking issue. There would need to be an IP address that goes to your Windows computer. If you're a home user, you may be able to configure your broadband router to forward incoming port 3306 to your Windows computer. You would then need to know your home's external IP address, and that may be dynamic. If you are on a corporate network, then there is virtually no chance your IT department would allow it. — Tim Roberts, Dec 17 '21 at 19:22
One of the possible reasons: You try to run the script with WSL shell but the MySQL server running in the Windows — Yalchin Mammadli, Jun 04 '22 at 02:35

I've tried EVERYTHING: 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)

0 Answers0