Django Connection Problems on Remote MySQL Database

Outline

An interesting problem reared its head today regarding the inability of my Django setup to connect to a remote MySQL server. I have just migrated a Django site I am in the middle of working on from a single physical server to two virtual machines (VMs) running on a free copy of VMWare’s ESXi server; one Linux (call it VM1) running MySQL and the other Linux VM (call it VM2)  running Python and Apache.

Here’s what happened…

Stages of Set Up – on VM1

The first thing I did was to set up the MySQL user permissions up on VM1 to accept requests from VM2 on the databases required by the Django application. Next I ran the query SHOW VARIABLES LIKE '%skip_net' to check that the ’skip_networking’ variable was set to ‘OFF’.

mysql> SHOW VARIABLES LIKE '%skip_net%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

I then checked that the section [mysqld] in the file at /etc/my.cnf looked something like the following:

...
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
#bind-address=127.0.0.1
...

You may note above that the bind-address variable is commented out! Next to check that the MySQL server was actually listening to requests on port 3306 (standard port on MySQL) by doing the following:

[user@raven ~]# netstat -an | grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN

Cool so far. On to VM 2 then…

Setting up on VM2

First thing I did here was to connect to the MySQL server on the command line, from VM2 to VM1:

[user@lapwing ~]$ mysql -uuser -h192.168.100.81 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.67 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

All good here then – next to see if Django could see the database by using the interactive console, python manage.py shell, and trying out the models – thus hopefully proving that the connection settings provided in the settings.py file were correct :

Python 2.5.2 (r252:60911, Sep 30 2008, 15:41:38)
[GCC 4.3.2 20080917 (Red Hat 4.3.2-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from site1.items.models import Item
>>> Item.objects.all()
[<Item: Teak Toilet Seat>,...(more items here)]
>>>

Well I was quite pleased at this point as we seemed to be getting somewhere. I then fired up a browser and immediately ran into dificulties, getting the message:

Caught an exception while rendering: (2003,
  "Can't connect to MySQL server on '192.168.100.81' (13)")

Original Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/django/template/debug.py", line 71,
  in render_node result = node.render(context)
  File "/usr/lib/python2.5/site-packages/django/template/defaulttags.py", line 244,
  in render if (value and not ifnot) or (ifnot and not value):
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 191,
  in __nonzero__ iter(self).next()
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 185,
  in _result_iter self._fill_cache()
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 618,
  in _fill_cache self._result_cache.append(self._iter.next())
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 275,
  in iterator for row in self.query.results_iter():
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 206,
  in results_iter for rows in self.execute_sql(MULTI):
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 1733,
  in execute_sql cursor = self.connection.cursor()
  File "/usr/lib/python2.5/site-packages/django/db/backends/__init__.py",line 56,
  in cursor cursor = self._cursor(settings)
  File "/usr/lib/python2.5/site-packages/django/db/backends/mysql/base.py", line 262,
  in _cursor self.connection = Database.connect(**kwargs)
  File "/usr/lib/python2.5/site-packages/MySQLdb/__init__.py", line 74,
  in Connect return Connection(*args, **kwargs)
  File "/usr/lib/python2.5/site-packages/MySQLdb/connections.py", line 169,
  in __init__ super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (2003, "Can't connect to MySQL server on '192.168.100.81' (13)")

The clue here was the error code (13) on the first and last lines which in MySQL parlance meant permission denied. After much thinking and head scratching I had the problem solved.

SELinux, SELinux, SELinux

How many times have people been caught out by this one – many I am sure, and I am not the exception to the rule either! There was a couple of things happening here which got me confused for a while:

• The MySQL server was able to listen on port 3306
• Connection to the database on VM1 was possible on the command line from VM2
• Database permissions were all correct for the user required
• Connection settings were fine in the settings.py file for the project
• The python interactive console connected to and fetched the objects using the models provided
• However the connection failed when running over HTTP

What was to blame – well the heading of this paragraph says it all… Time to check and see what SELinux was up to…

[user@lapwing ~]$ getenforce
Enforcing

Yes, SELinux was in enforce mode – a schoolboy level error to have forgotten to check that! An easy solution to this problem in the end – simply ask SELinux to allow httpd to connect to resources over the network.

[root@lapwing ~]# setsebool -P httpd_can_network_connect=1

There was need to reboot VM2 – SELinux should enforce the policy immediately. Voilà – got the site working!