Moving sqlite3 pytest tests to mysql

DISCLAIMER: I’m fully aware that it’s possible to successfully run pytest tests with mysql as a database. It’s most likely even possible to do so while running the tests in parallel. But it’s not something we managed to do in the time we were willing to spend on this task. The point of this article is to show you how this transformation worked (or didn’t work) for us, what we learnt along the way, and what are our future plans.

Why?

We have a fairly standard Django project setup. Our local environment runs on docker through docker-compose, where we have 4 separate services (app, db, nginx and redis). We use pytest for running tests, which execute inside a container that spawns for a single test run, and then exits. All of this is automated through the usage of Makefile commands.

As you may have guessed from the title, we use MySQL as our project database, both in production and for our local development. There’s nothing wrong with MySQL. It’s a mature and a perfectly fine solution for production databases. It gets the job done, it was never a bottleneck for us, and we don’t have strong reasons for abandoning it.

That being said, it’s not the database we decided to use for our testing environment. For that, we decided to use SQLite, or to be more specific, sqlite3. SQLite is an in-process database, meaning it doesn’t run as a separate process like the usual client/server database systems. Instead, it acts as a library that’s part of the main application’s address space. In our case, when the pytest binary is called to run the tests against a Python virtual machine, that virtual machine will also embed the SQLite library.

This means that SQLite can be pretty fast (as there’s no inter-process communication like in the other database systems), especially when used by low traffic applications, or by something fully controlled by us, like our test suite. As an added benefit, it’s easy to run SQLite with an in-memory database, making the test execution even faster.

Reading all of these benefits, you might wonder why did we even want to get rid of SQLite in our tests. The answer is that it doesn’t replicate our production environment as closely as a MySQL backed test suite would. Database management systems are incredibly complex, and there are a lot of areas where a code executing against one type of DBMS can behave differently when run against another type of DBMS. To minimise the chance of that happening, we decided to move our tests to use MySQL, especially as our project starts to grow in complexity.

First steps

The first thing we did was notice that the docker-compose-test.yml file already contains a db service, which runs MySQL. That enabled us to quickly test how fast things break by just commenting out the part of the Django settings where we specified the SQLite database:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "testdb.sqlite3",
    }
}

Since the test.py settings file inherits everything from local.py, this effectively means that the tests will use whichever database the local environment uses by default, which is MySQL. This was an ok attempt actually, since our db container did have the necessary env variables already set:

environment:
  - MYSQL_ROOT_PASSWORD=root
  - MYSQL_USER=test
  - MYSQL_PASSWORD=testpass
  - MYSQL_DATABASE=db
  - MYSQL_ROOT_HOST="%"

In an ideal world, this could’ve been the end of the story, and there’d be no need for this article. But what happened in reality was this error:

django.db.utils.OperationalError: (1044, "Access denied for user 'test'@'%' to database test_db")

After some googling and investigating, it turned out that it’s not enough to add your user to the MySQL container’s environment variables. Although that does create the user, that user doesn’t have permissions to do anything worthwhile, including creating a database. And since each pytest-django test run starts with the database being created, this was a problem.

The first hurdle

One naive solution would be to create the database manually before ever running the tests, and just avoid destroying it after the tests run. But the question is would the test user even have permissions to write to this database? Maybe it wouldn’t be able to run the migrations. Another issue would be running the tests in the Github PR pipeline. The database can’t be manually created in the pipeline, since the pipeline’s container gets destroyed after each run, and there’s no way to mount the database outside of the container (as far as we’re aware) so that it survives each run. Even if this was an option, it’s such an ugly solution that we’d never go with it. This meant we had to find a way to automatically give the test user enough permissions, so that it can create the database, run the migrations and do any other database reads/writes.

After some more investigation, we went with creating an entrypoint script that the MySQL server will run at startup. The script needed to grant the test user all the privileges it needs. The “script” is actually only two lines of code and looks like this:

GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'testpass';
FLUSH PRIVILEGES;

The other thing to do was to actually mount this script to the correct place in the MySQL container, so that it can get picked up and executed when the MySQL server starts. Luckily, this is very easy to do with docker-compose:

volumes:
  - ../config/db/privileges.sql:/docker-entrypoint-initdb.d/privileges.sql

After that, we brought down the containers and ran make test-run again. After a few seconds, the tests started executing, and the error message was gone. Unfortunately, a new one replaced it:

django.db.utils.OperationalError: (2002, "Can't connect to MySQL server on 'db' (115)")

What happened here? Well, it turns out that the container running the tests can spawn faster than the one running the MySQL server. We must’ve just forgotten to add the db service dependency to our test service:

depends_on:
  - db

It’s already there.

The second hurdle

How could that be? depends_on should mean that the service being dependent on should start before the service using the flag. If we scroll up and check the docker-compose service execution order:

Creating network "compose_default" with the default driver
Creating daas-test-db    ... done
Creating daas-test-redis ... done
Creating compose_test_run ... done

Seems to be ok. We spent some time investigating the issue and came to the conclusion that even though the MySQL container might be up, it doesn’t necessarily mean that the actual MySQL server is up and accepting connections. And checking the db container’s logs, it really does take quite some time from the moment that the container logs the first message, until the moment it’s actually ready to receive connections:

daas-db  | 2022-10-11 20:41:08+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.39-1.el7 started.
…
daas-db  | 2022-10-11T20:41:32.833400Z 0 [Note] /usr/sbin/mysqld: ready for connections.
daas-db  | Version: '5.7.39'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

More than 20 seconds. It’s no wonder that the test container managed to start and run the pytest command during that time. We need a way of knowing that the MySQL server is ready to accept connections before the pytest command even runs.

Turns out there’s a really handy program that comes bundled with the mysql-client called mysqladmin. This program enables us to try to connect to the MySQL server in question, before attempting to run the tests. If we don’t manage to connect, meaning that the server is not yet ready, we will sleep for some time and then try again. The final script looks like this:

#!/bin/bash
while ! mysqladmin ping -hdb -utest -ppassword --silent; do
    echo "Mysql db is unavailable, sleeping 5 seconds ..."
    sleep 5
done

What’s left to do is to integrate this script so that it runs before the pytest command. To make our lives easier, we added a new script that’s invoked by the make test-run, which looks like this:

#!/bin/bash
../config/test/wait_for_db.sh && pytest --ds=settings.test

It just starts the wait_for_db.sh script, which goes into executing the pytest tests as soon as it finishes.

The output now shows:

Creating compose_test_run ... done
Mysql db is unavailable, sleeping 5 seconds ...
Mysql db is unavailable, sleeping 5 seconds ...
mysqld is alive
======================================================= test session starts =======================================================
platform linux -- Python 3.8.13, pytest-6.2.5, py-1.11.0, pluggy-1.0.0
django: settings: settings.test (from option)
rootdir: /daas, configfile: pytest.ini
plugins: xdist-2.5.0, lazy-fixture-0.6.3, snapshottest-0.6.0, cov-4.0.0, mock-3.7.0, django-4.5.2, env-0.6.2, forked-1.4.0
collected 262 items
tests/e2e/test_swagger_apis.py ....                                                                                         [ 1%]
…

Success!

The final hurdle

The tests are now executing. Some of them fail, but most of them pass. After checking the failed ones, we notice weird errors like a pytest fixture trying to insert data longer than the maximum column length. We double checked the code, and it seems that we really did have a flaky logic in one of them. So far so good. Then we notice the same error in another test, but this time it doesn’t make sense.

We’re using the model bakery library for populating our database with random data. It’s a nice way of creating test fixtures without having to manually specify all the fields, foreign keys, etc. Model bakery shouldn’t try to insert something that’s longer than the maximum column length specified in the Django model, that would be a major bug in the library.

After checking the logs a bit more, we notice that the issue only happens when we override model bakery’s default values and try to insert something by ourselves, usually in Korean. For some reason, we get spaces between each of the Korean characters. Instead of 테스트, we’d get 테 스 트. This sometimes spills over the maximum column length, causing errors. We double checked that the MySQL database was created with the correct character sets and collations. Very strange. As a temporary hack, we changed those values to non Korean ones and went looking for other errors.

Unfortunately, other errors were far worse. They turned out to be non-deterministic. We ran the tests a couple of more times, each time we’d get slightly different results. Sometimes 7 tests would fail, sometimes 8, sometimes 9. Errors were either about a certain piece of data not existing in the database, or some uuids not matching. We try a couple of different solutions, we even try to run the tests in parallel (since this is how we run them with sqlite3, so it’s the ultimate goal), but that’s even worse.

Defeat

At this point, we’ve sunk quite some time into this task. There were some smaller issues we had that we didn’t even mention here. The experience as a whole was not smooth at all. At this point we had two options: push through this, spend more hours and days trying to get all the tests to pass, or, give up, document what we’ve learned, and move on with our lives.

We already had so many issues, and that’s without giving the parallel tests a fair chance. It’s only reasonable to assume that running the tests in parallel will mean even more issues. We started to think about the actual goals of this task. We tried to be rational and think about how many situations will we realistically have, in which an issue passed through the cracks just because a behaviour unit tested with SQLite, doesn’t behave the same with MySQL. We only thought of one similar situation that one of us had on a previous project, but even then it wasn’t a SQLite vs MySQL/PostgreSQL/SQLServer/etc. problem. In that case, the issue was only occurring on a multi-db setup, so having the same database in the testing environment didn’t help, because the tests were using only one database.

Maybe this is one of those situations when the time spent on an issue won’t justify the end results. We probably would’ve managed to fix the tests, one by one, day by day. But at what cost? Maybe it would have taken us two hours, but maybe it would have taken us three days. In the end, we agreed that this was a nice learning experience, both in engineering terms, but also in terms of knowing when to stop.

We’re planning to move to PostgreSQL in the future anyway, so maybe there will be a second part of this article.

TL;DR

• give your database user the necessary permissions by creating a simple sql script and mounting it to docker-entrypoint-initdb.d
• make sure your test container doesn’t start before the database is ready, if needed, use mysqladmin to ping the database until it’s ready
• pray for the best!