MySQL backup freezing on active connection

[jerinpetergeorge]

I was trying to write some end-to-end tests for #360 (and hence helping to complete #369). During the time I tried to generate the backup of the test database (or all databases). But, it hangs to infinity

How Reproduce

# dbbackup.tests.test_db.settings.mysql

DEBUG = False
SECRET_KEY = "it's a secret to everyone"

INSTALLED_APPS = (
    'dbbackup',
    'dbbackup.tests.testapp',
)

DATABASES = {
    "default": {
        "NAME": "cloud",
        "USER": "root",
        "PASSWORD": "password",
        "HOST": "localhost",
        "ENGINE": "django.db.backends.mysql",
        "OPTIONS": {
            "charset": "utf8mb4"
        },
    }
}

# test class
from django.test import TestCase, tag
from django.core.management import call_command
from dbbackup.tests.testapp.models import CharModel


class PopulateTestData:
    @classmethod
    def setUpTestData(cls):
        super().setUpTestData()
        for char in "QWERTY":
            CharModel.objects.create(field=char)


@tag("db_test")
class TestDBCommands(PopulateTestData, TestCase):

    def test_backup(self):
        call_command("dbbackup", "-o", "test-bkp-mysql.dump")

# test_runner.py
import os
import django

os.environ.setdefault(
    "DJANGO_SETTINGS_MODULE",
    "dbbackup.tests.test_db.settings.mysql"
)
django.setup()


if __name__ == "__main__":
    from django.test.runner import DiscoverRunner

    test_runner = DiscoverRunner(
        keepdb=False,
        interactive=False,
        verbosity=3
    )
    test_runner.run_tests([
        "dbbackup.tests.test_db.test_db_command" # path to the specific test suite
    ])

Then, run the suite by python test_runner.py

Fix

Using the --single-transaction flag will solve this issue.

[jerinpetergeorge]

This test case may not be relevant to our application, but I have a strong feeling that --single-transaction is more appropriate during taking the backups since the dump is a snapshot of the databases at the instant the dump started, regardless of how long the dump takes.

[jonathan-s]

I'll take a look at this. ~~Do you happen to have any other sources that can verify a 'correct' approach for making backups using mysql?~~. That link to dba is exactly what I was looking for :)

[jerinpetergeorge]

MySQL support many storage engines and the --single-transaction flag is only applicable for the InnoDB engine. Fortunately, the InnoDB is the default storage engine and I don't think developers would go for any other engines while they are using Django.

So, I am thinking of making the --single-transaction flag an optional value which can be controlled by the settings variable and default to True.

Apart from that, I found the --oplog of mongodump command, which seemed to take the snapshots.

[jerinpetergeorge]

Any suggestion on this? @jonathan-s

[jonathan-s]

Hi @jerinpetergeorge, Thanks for asking again. I had missed the first message. Also well done on the research. Yes leaving single transaction as an optional flag sounds like the way to go.

This flag might not be used for the other databases, so if we try to use this flag with another database I would make sure that a warning is visible saying that this flag does nothing for that database.

[jonathan-s]

Also, I'm not sure how many actually uses mongo. It's an odd choice for django to begin with.

[jerinpetergeorge]

This flag might not be used for the other databases, so if we try to use this flag with another database I would make sure that a warning is visible saying that this flag does nothing for that database.

Sounds good to me.

Also, I'm not sure how many actually uses mongo. It's an odd choice for django to begin with

Yeah, you are right. IMO, let's leave things for Mongo now. We can do that later if someone facing the issue with Mongo.

[jonathan-s]

Yeah, you are right. IMO, let's leave things for Mongo now. We can do that later if someone facing the issue with Mongo.

Yes, that's the way to go.