Forums

Hello,

I have two databases on my server, a production db and a dev db.

The production db backs up every night and also updates the dev db with the latest batch of data.

I am trying to configure my local development environment to connect to the dev db. I have followed your advice on this page and installed MySQL Workbench. The server connects succesfully to PythonAnywhere via SSH.

However I am having issues configuring the connection within Django. In my dev.py settings file I have the following:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'myusername$dev',
        'USER': 'myusername',
        'PASSWORD': 'mypassword',
        'HOST': '127.0.0.1',
        'PORT': '3306',
    }
}

This unfortunately does not work and throws the following error: django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' (10061)"). Google hasn't been able to help me on this one.

Any ideas? Thanks

That all looks correct to me. Maybe double-check the password? Silly suggestion, I know... Also, grasping at straws now, maybe workbench and django can't work at the same time?

Password is definitely correct I'm afraid... There was a guy who had a similar issue however I found nothing useful from his StackOverflow question... Perhaps you might?

I understand little about databases however... But is it the case that when I connect to MySQL through the Workbench it makes it locally hosted?

I'm not sure what to make of that "locally hosted" question. one thing that SO post suggests is changing "127.0.0.1" to "localhost" and vice versa?

Hey Harry,

So I ended up installing Linux on a VirtualBox on my machine and managed to connect to the server without any issues. So it all works as intended.... Except....

Now I'm getting a exceeded the 'max_user_connections' resource (current value: 6) error whenever I connect on my dev environment. So whenever I connect and someone makes a change to the live database the website crashes with a 500 error and their work is lost!!! Not great.

Any ideas on what I can do to fix this? I know I should wait 5 minutes before trying again, but this issue has been recurring for a couple of days now.

You're leaving connections open: http://help.pythonanywhere.com/pages/ManagingDatabaseConnections/

@glenn, I had a look at that link but I'm not managing database connections myself and the Django configuration is standard (vanilla Django ORM), so I'm not really sure what's going on.

Any chance you could take a look? I know next to nothing about DB's and don't want to risk damaging my web app.

wait so firstly, why is your dev machine connecting to your live db?

some things to check: do you also have a bunch of django shells open? (maybe those hold open connections?)

can you give us more detail wrt what you are running in dev?

@conrad

wait so firstly, why is your dev machine connecting to your live db?

I setup two databases on the "databases" tab of my account. I've got a production database which my live web app is connected to, and I've got a dev database which is unused.

Whilst developing I'd like to have access to the most recent data, but I don't want to risk ruining my live/production database. So as a solution I have a script that populates the dev db with the latest batch of data every night from the production db.

And then I connect to the dev db through my dev environment using ssh. Is that bad?

do you also have a bunch of django shells open? (maybe those hold open connections?)

I usually always have one bash console open, I tend to close all the others with the exit command.

can you give us more detail wrt what you are running in dev?

Pretty simple, I've got a git copy of my project on the machine, which is a Linux Mint 18 os running on virtualbox. I open the terminal and run ssh -L 3306:username.mysql.pythonanywhere-services.com:3306 username@ssh.pythonanywhere.com which connects me to the db. I then open another terminal and run python manage.py runserver and it works! My settings.py is configured to access the dev database on my local machine, and the production database on pythonanywhere.

your setup sounds fine. Could it be that your daily script isn't closing it's connections?

If you open a db console from the pythonanywhere db tab, you can run a command like

SELECT * FROM information_schema.processlist;

To see what queries are being run/kept open

@conrad, thanks for your reply.

I have run the commands you suggested on both the production and dev consoles and they both return the same value:

+---------+-----------------+------------------+---------------------+---------+------+-----------+----------------------------------------------+
| ID      | USER            | HOST             | DB                  | COMMAND | TIME | STATE     | INFO                                         |
+---------+-----------------+------------------+---------------------+---------+------+-----------+----------------------------------------------+
| myId| NilaExpeditions | myHost | NilaExpeditions$dev | Query   |    0 | executing | SELECT * FROM information_schema.processlist |
+---------+-----------------+------------------+---------------------+---------+------+-----------+----------------------------------------------+
1 row in set (0.00 sec)

Also, I was going to test the connection, and I'm afraid that I may have locked myself out of my ssh, as I entered the incorrect password a whole bunch of times and now it's giving me a ssh: connect to host ssh.pythonanywhere.com port 22: Connection timed out error every time I try and connect! Oops!

Re: the SSH connection timeouts -- yes, that's probably because of the incorrect logins. As a security measure, if we get a bunch of failed passwords (I think six in a row) we temporarily block the IP address they came from for an hour. (As an aside, all of our servers get an order of a dozen dodgy login attempts a minute -- there are a lot of bad people out there!)

Re: the number of MySQL connections -- at the moment, our algorithm for working out how many concurrent MySQL connections you can have is number of web apps * number of workers per web app * 3. So that certainly should be enough for a Django app (which will normally keep one connection per worker per web app) plus some connections from your local machine.

Does the max_user_connections error happen as soon as you connect from your local machine? If not, how long does it take before it starts happening? It might be worth trying to trigger it, and then, when you're sure it's happening, run the command Conrad mentioned to see if you get something different back at that point.

OK I was able to login again (after carefully putting in my password!)

I believe I have 2 workers on 1 web app, so that would explain the max user count of 6.

Yes, as soon as I connect to the dev environment I get around 6-8 instances of the same error as the homepage is loading: django.db.utils.OperationalError: (1226, "User 'NilaExpeditions' has exceeded the 'max_user_connections' resource (current value: 6)")

Out of curiosity I decided to re-run that SQL statement you provided earlier whilst connected to the dev environment and here's what I got:

mysql> SELECT * FROM information_schema.processlist;
+---------+-----------------+------------------+---------------------+---------+------+-----------+----------------------------------------------+
| ID      | USER            | HOST             | DB                  | COMMAND | TIME | STATE     | INFO                                         |
+---------+-----------------+------------------+---------------------+---------+------+-----------+----------------------------------------------+
| 9381060 | NilaExpeditions | 10.0.0.6:50928   | NilaExpeditions$dev | Sleep   |  226 |           | NULL                                         |
| 9381061 | NilaExpeditions | 10.0.0.6:50930   | NilaExpeditions$dev | Sleep   |  226 |           | NULL                                         |
| 9381353 | NilaExpeditions | 10.0.0.150:59378 | NilaExpeditions$dev | Query   |    0 | executing | SELECT * FROM information_schema.processlist |
| 9381007 | NilaExpeditions | 10.0.0.6:50638   | NilaExpeditions$dev | Sleep   |  268 |           | NULL                                         |
| 9381056 | NilaExpeditions | 10.0.0.6:50920   | NilaExpeditions$dev | Sleep   |  226 |           | NULL                                         |
| 9381057 | NilaExpeditions | 10.0.0.6:50922   | NilaExpeditions$dev | Sleep   |  226 |           | NULL                                         |
+---------+-----------------+------------------+---------------------+---------+------+-----------+----------------------------------------------+
6 rows in set (0.00 sec)

Interesting! So what you're seeing there is five connections coming in through our server consoles-1 (10.0.0.6), and one from consoles-8 (10.0.0.150). The latter is the one where you ran the command. The connections through your SSH connection will show up as consoles-1, which does double duty as a console server and an SSH server.

Notably, there are no connections there from web servers -- though perhaps those have been closed, Django is normally set up to open a connection at the start of processing a request, then close it at the end -- so any connections from there would be short-lived.

Assuming you don't have lots of consoles open on the PythonAnywhere "Consoles" tab, that sounds very much like your dev environment is the culprit. I think something on your local machine is opening lots of connections and not closing them.

What are you running locally? Is it just Django? Or are you doing stuff with workbench too?

I see! Interesting.

I'm pretty much just connecting via ssh and running python manage.py runserver where my DB settings are configured exactly as I copied in the initial post.

I tried closing all consoles and refreshing the website but I'm still getting the same error (only twice this time). Interestingly enough, despite getting these errors the webpages load fine. It's only trying to write to the database that results in a 500 error.

Maybe django.db.backends.mysql is the culprit? Are there any viable alternatives that you know of?

Thanks

I doubt django.db.backends.mysql is the problem -- we use it for the database backend for the Django site that makes up PythonAnywhere's own main interface, and that's handling things fine.

What are you running on the Linux Mint machine? For example, what do you get if you run this command in a terminal?

ps auwf | grep -i python

.

nilaexpeditions    1998  6.0  0.9 135304 47968 pts/2    S+   21:46   0:00  \_ python manage.py runserver

nilaexpeditions    2008 12.1  1.2 220428 62776 pts/2    Sl+  21:46   0:01      \_ /home/ddiran/Desktop/nila/bin/python manage.py runserver

nilaexpeditions    1956  0.0  0.1  49052  5316 pts/1    S+   21:45   0:00  \_ ssh -L 3306:NilaExpeditions.mysql.pythonanywhere-services.com:3306 NilaExpeditions@ssh.pythonanywhere.com

nilaexpeditions    2014  0.0  0.0  14232   972 pts/0    S+   21:46   0:00  \_ grep --color=auto -i python

After connecting via ssh and running the server this is what the code you gave me returned

so here's another question- if you just have a runserver, but don't hit any endpoints/go to your local dev site, do the msql connections pile up anyways?

alternatively, if you experiment and say only go to the front page, vs only go to another url etc, does the mysql connection pile up for some endpoints but not others?

Hey @conrad, apologies for the delay in getting back to you. My Linux VM was corrupted so I simply cloned the project back into my windows 10 machine and am using Git Bash to connect via ssh now.

The problem still persists unfortunately. And I can no longer use ps auwf | grep -i python to check connections

I think it's pretty clear that the Django dev server is opening up a bunch of connections to the database. You could check that by listing your database connections before and after starting the dev server (and perhaps with some fast fingers during a request). You could try passing --nothreading to runserver to see if that helps.

Windows doesn't have ps, you'll have to use the process viewer.