Tuesday 12 January 2021

Part 5: MariaDB

G'day:

Please note that I initially intended this to be a part of a single article, but by the time I had finished the first two sections, it was way too long for a single read, so I've split it into the following sections, each as their own article:

  1. Intro / Nginx
  2. PHP
  3. PHPUnit
  4. Tweaks I made to my Bash environment in my Docker containers
  5. MariaDB (this article)
  6. Installing Symfony
  7. Using Symfony
  8. Testing a simple web page built with Vue.js
  9. I mess up how I configure my Docker containers
  10. An article about moving files and changing configuration
  11. Setting up a Vue.js project and integrating some existing code into it
  12. Unit testing Vue.js components

As indicated: this is the fifth article in the series, and follows on from Part 4: Tweaks I made to my Bash environment in my Docker containers. It's probably best to go have a breeze through the earlier articles first just to contextualise things. Also as indicated in earlier articles: I'm a noob with all this stuff so this is basically a log of me working out how to get things working, rather than any sort of informed tutorial on the subject.

OK, let's get on with this MariaDB stuff.

Firstly; why am I using MariaDB instead of MySQL? Initially I started pottering around with MySQL on Docker for another piece of work I was doing, and I ran up against a show-stopper that doesn't seem to have a resolution. It's detailed on GitHub at "MySQL docker 5.7.6 and later fails to initialize database", and demonstrated here:

adam@DESKTOP-QV1A45U:~$ docker pull mysql
Using default tag: latest
latest: Pulling from library/mysql
6ec7b7d162b2: Pull complete
[...]
a369b92bfc99: Pull complete
Digest: sha256:365e891b22abd3336d65baefc475b4a9a1e29a01a7b6b5be04367fcc9f373bb7
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$ docker create --name mysql --expose 3306 -p 3306:3306 --interactive --volume /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123 --tty mysql
dfabbf24a7b76831cdb95d20302cc46587cfeb2f7b9f63ac2d907fb8505b07b8
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$ docker start --interactive mysql
2020-12-20 12:18:56+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.22-1debian10 started.
2020-12-20 12:18:56+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-20 12:18:56+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.22-1debian10 started.
2020-12-20 12:18:56+00:00 [Note] [Entrypoint]: Initializing database files
2020-12-20T12:18:56.174257Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.22) initializing of server in progress as process 46
2020-12-20T12:18:56.183150Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2020-12-20T12:18:56.186621Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-20T12:18:58.227909Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysqld: Cannot change permissions of the file 'ca.pem' (OS errno 1 - Operation not permitted)
2020-12-20T12:19:00.524436Z 0 [ERROR] [MY-010295] [Server] Could not set file permission for ca.pem
2020-12-20T12:19:00.524927Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.
2020-12-20T12:19:00.525836Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-12-20T12:19:02.414805Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22) MySQL Community Server - GPL.
adam@DESKTOP-QV1A45U:~$

I tried everything suggested in that thread, everything else I could find, and nothing improved the situation. However this entry on that issue page above looked like good advice:



So I just decided to run with MariaDB instead, and that worked perfectly:

adam@DESKTOP-QV1A45U:~$ docker create --name mariadb --expose 3306 -p 3306:3306 --interactive --volume /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123 --tty mariadb
Unable to find image 'mariadb:latest' locally
latest: Pulling from library/mariadb
da7391352a9b: Pull complete
[...]
a33f860b4aa6: Pull complete
Digest: sha256:cdc553f0515a8d41264f0855120874e86761f7c69407b5cfbe49283dc195bea8
Status: Downloaded newer image for mariadb:latest
bb2a4128911e52f2b16a25c4f994fe12eeec3c36a7e9e188cba2758522785522
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$ docker start mariadb
2020-12-20 12:27:15+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.8+maria~focal started.
2020-12-20 12:27:15+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-20 12:27:15+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 1:10.5.8+maria~focal started.
2020-12-20 12:27:15+00:00 [Note] [Entrypoint]: Initializing database files
[... bunch of stuff snipped ...]
mariadb
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$
adam@DESKTOP-QV1A45U:~$ docker exec -it mariadb mariadb --user=root --password=123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.8-MariaDB-1:10.5.8+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT @@VERSION;
+-------------------------------------+
| @@VERSION |
+-------------------------------------+
| 10.5.8-MariaDB-1:10.5.8+maria~focal |
+-------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

Now I don't doubt it's possible to get MySQL working in my environment, but… erm… shrug. I don't care. I just need a DB running. I'm not here to fart-arse around with DBs any more than absolutely necessary.

We should actually now back-up a bit. All that stuff above was done a while ago - although I replicated it just now for the sake of the notes above - and for my current exercise we're getting ahead of ourselves. I'm gonna start this exercise with a failing test (test/integration/DatabaseTest.php):

namespace adamCameron\fullStackExercise\test\integration;

use PHPUnit\Framework\TestCase;
use \PDO;

class DatabaseTest extends TestCase
{
    /** @coversNothing */
    public function testDatabaseVersion()
    {
        $connection = new PDO(
            'mysql:dbname=mysql;host=database.backend',
            'root',
            '123'
        );

        $statement = $connection->query("show variables where variable_name = 'innodb_version'");
        $statement->execute();

        $version = $statement->fetchAll();

        $this->assertCount(1, $version);
        $this->assertSame('10.5.8', $version[0]['Value']);
    }
}

Note: in a better world, I'd never have my user and password hard-coded there (see further down for where I address this), even in a test. And I'd not be using root (I don't address this one quite yet though). Also checking the version right down to the patch level is egregious, I know. Just checking for 10 would perhaps be better there.

This fails as one would expect:

root@5962e5abd527:/usr/share/fullstackExercise# vendor/bin/phpunit test/integration/
PHPUnit 9.5.0 by Sebastian Bergmann and contributors.

E                                                                   1 / 1 (100%)

Time: 00:00.245, Memory: 6.00 MB

There was 1 error:

1) adamCameron\fullStackExercise\test\integration\DatabaseTest::testDatabaseVersion
PDOException: SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known

/usr/share/fullstackExercise/test/integration/DatabaseTest.php:15

Caused by
PDOException: PDO::__construct(): php_network_getaddresses: getaddrinfo failed: Name or service not known

/usr/share/fullstackExercise/test/integration/DatabaseTest.php:15

ERRORS!
Tests: 1, Assertions: 0, Errors: 1.

Generating code coverage report in HTML format ... done [00:01.786]

Once we've installed the MariaDB container, got it up and running and networked it: this test should pass.

I'm back to taking Martin Pham's lead in the Docker config for all this (reminder, from his article "Symfony 5 development with Docker")

Here's the docker/mariadb/Dockerfile:

FROM mariadb:latest
CMD ["mysqld"]
EXPOSE 3306
Quick sidebar whilst I'm doing a final edit of this. If I'm testing for a specific version 10.5.8, should I perhaps be forcing that version here too? Hmmm… probably.

No surprises there. Next the stuff in docker/docker-compose.yml:

  mariadb:
    build:
      context: ./mariadb
    environment:
      - MYSQL_ROOT_PASSWORD=${DATABASE_ROOT_PASSWORD}
    ports:
      - "3306:3306"
    volumes:
      - ./mariadb/data:/var/lib/mysql
    stdin_open: true # docker run -i
    tty: true        # docker run -t
    networks:
      backend:
        aliases:
          - database.backend

One new thing for me here is the ${DATABASE_ROOT_PASSWORD}. Looking at Martin's set-up, he's also got a file docker/.env. Seems to me one can sling environment variables in there, and docker-compose picks them up automatically. So I've just got this (docker/.env):

DATABASE_ROOT_PASSWORD=123

I'll come back to this in a bit.

Also note that I'm configuring MariaDB to put its data in a volume back on the host machine. This is so the data persists when I shut the container down.

And now I should be able to start everything up, and it'll just work. Right?

adam@DESKTOP-QV1A45U:/mnt/c/src/fullstackExercise/docker$ docker-compose up --build --detach
Creating network "docker_backend" with driver "bridge"
Building nginx
[...]
Successfully built 7cb155649c3b
Successfully tagged docker_nginx:latest

Building php-fpm
[...]
Successfully built e483795cc006
Successfully tagged docker_php-fpm:latest

Building mariadb
[...]
Successfully built 1f05ad3e3ad3
Successfully tagged docker_mariadb:latest

Creating docker_mariadb_1 ... done
Creating docker_nginx_1 ... done
Creating docker_php-fpm_1 ... done

adam@DESKTOP-QV1A45U:/mnt/c/src/fullstackExercise/docker$

OK that's more promising than I expected. How about that integration test?

adam@DESKTOP-QV1A45U:/mnt/c/src/fullstackExercise/docker$ docker exec --interactive --tty docker_php-fpm_1 /bin/bash
root@4861480bcbad:/usr/share/fullstackExercise# vendor/bin/phpunit test/integration/
PHPUnit 9.5.0 by Sebastian Bergmann and contributors.

.                                                                   1 / 1 (100%)

Time: 00:00.200, Memory: 6.00 MB

OK (1 test, 2 assertions)

Generating code coverage report in HTML format ... done [00:01.215]
root@4861480bcbad:/usr/share/fullstackExercise#

Gasp (I actually did gasp a bit). Blimey. It only went and worked first time.

I'm intrigued by this .env stuff. I figured if I can set that root password in the .env file, then I can shift it out of my integration test, and just use the environment variable. So I've updated the php-fpm section of docker-compose.yml to also set than environment variable:

php-fpm:
  build:
    context: ./php-fpm
  environment:
    - DATABASE_ROOT_PASSWORD=${DATABASE_ROOT_PASSWORD}
  # etc

And update the integration test to use that:

$connection = new PDO(
    'mysql:dbname=mysql;host=database.backend',
    'root',
    $_ENV['DATABASE_ROOT_PASSWORD']
);

And test that's all OK:

adam@DESKTOP-QV1A45U:/mnt/c/src/fullstackExercise/docker$ docker-compose down --remove-orphans
[...] adam@DESKTOP-QV1A45U:/mnt/c/src/fullstackExercise/docker$ docker-compose up --build --detach
[...] adam@DESKTOP-QV1A45U:/mnt/c/src/fullstackExercise/docker$ docker exec --interactive --tty docker_php-fpm_1 /bin/bash
root@48dedafac625:/usr/share/fullstackExercise# env | grep DATABASE_ROOT_PASSWORD
DATABASE_ROOT_PASSWORD=123
root@48dedafac625:/usr/share/fullstackExercise# vendor/bin/phpunit test/integration/
PHPUnit 9.5.0 by Sebastian Bergmann and contributors.

.                                                                   1 / 1 (100%)

Time: 00:00.196, Memory: 6.00 MB

OK (1 test, 2 assertions)

Generating code coverage report in HTML format ... done [00:01.195]
root@48dedafac625:/usr/share/fullstackExercise#

Cool!

Right so that's all that done: it was very easy (thanks largely to Martin's guidance). Note that once I get underway with the app I'll have a specific database to use, and specific credentials to use against it; at that point I'll stop using the root credentials in that test. But for where we are now - just checking that the DB is up and networked and PHP can see it: the test I'm doing is fine.

That was quite a brief article. In the next one I'll get on to installing Symfony.

Righto.

--
Adam