Search code examples
mysqldjangosocketsconnection

Trying to connect Django to MySQL. Connection error when migrating (Xampp, Linux)

I've been working for several weeks on Django. So far I have made 2 small projects. Now I tried to connect Django to the MySQL database.

  1. First, I started a new project.
  2. Then made a new app and updated the app in settings.py.
  3. After that I changed the database in the same file settings.py with my settings.
  4. Made a small model (in the file models.py).
  5. Installed all the necessary apps, (pip install) mysqlclient etc,
  6. Made the first migration successfully with python manage.py makemigrations So far so good!

Next, I tried to make the second migration (python manage.py migrate), as in the instructions.

Then the error message appears:

'error 111, can't connect to database'

Made some changes in the settings file, including SQL mode strict_trans_tables.

Now it shows me the error:

Can't connect to local MySQL server through socket ... Missing file .../mysql.sock.

I looked up the directory and there was no such file.

In the file my.cnf the filename and path is different!!! And exists! (It is using this existing file in my.cnf, but after executing the commang migrate it is searching for this file in a place, where it does not exist!)

To clarify things a little bit, the exact message of the error:

django.db.utils.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

There is no such file in the current directory (as I have checked).

my.cnf file contains the following path and filename: socket=/opt/lampp/var/mysql/mysql.sock There is a file in the current directory. MySQL works, as well.

What is going on and what to do?

=================================

Since my issue has been resolved, I how start thinking about what could cause such issue in the first place!

Apparently Django didn't know the path to the my.cnf file, even if it exists, so it had to be implemented manually from the settings.py file with the short code I have written below.

Recently I have been working on a similar projects, involving usage of javascript and node.js as a mean to create a website with access to database from mysql of the Xampp. I am using Linux for both projects, thus it might be related to the specifications of the system. Again in the node.js project I had to implement manually the path to the socked file, as follows:

const connection = mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    port: process.env.DB_PORT,
// THE PATH TO THE SOCKET FILE:
    socketPath: process.env.DB_SOCKET_PATH 
});

And thus I stated wondering why this issue has happened in the first place?? As I said, I could only suppose it might be related to the Linux version I am using (Linux Mint 20.3), or the way it was installed. When you start working with computers it is much easier to know what caused the problem, rather than only the correct solution.

Thank you very much in advance!

Solution

  • Finally the Django project was able to start using the MySQL database!!!

    The changes in the file setting.py are the same as before, as follows:

            'ENGINE': 'django.db.backends.mysql',
        'NAME': 'django_db',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '3306',

    the only thing that had to be done in addition to this, was to include the path of file my.cnf manually:

            'OPTIONS': {
            'read_default_file': '/opt/lampp/etc/my.cnf',
        },

    Obviously, this was the reason in the first place the path to the socket file to be mistaken.

    But why in the first place this error occurs, I don't know. Perhaps somewhere after the installation some other path was given, but I don't know where is this path, pointing to other directory of the socket file separately, or to other path for the my.cnf file.

    With the PostgreSQL database the connection was made so simply, it required only the 1st changes in the settings.py file.

    After that, additional migrations were made without any disturbance, as well as the superuser account.

    The source website from which I was able to use as a cheat sheet for the complete process: https://www.digitalocean.com/community/tutorials/how-to-create-a-django-app-and-connect-it-to-a-database