Skip to main content

MySQL and MariaDB

MySQL and MariaDB are for a site whose database is a separate service, where that is what your hosting offers. The site treats the two alike. This page is for whoever sets that up. If one server with a disk is all you have, SQLite is simpler. Read Choose a database first: there is no tool to move a site between databases later.

Settings​

VariableWhat it doesDefault
DB_PROVIDERmysql chooses MySQL or MariaDB. mariadb is accepted too and does exactly the same.sqlite
DATABASE_URLThe address of the database, with the user and password in it.none, required
DATABASE_SSLtrue encrypts the connection.false
DB_AUTO_MIGRATECreate missing tables at every start.true
DB_PROVIDER=mysql
DATABASE_URL=mysql://choir:a-long-password@db.example.org:3306/choir
DATABASE_SSL=true

The address has the form mysql://USER:PASSWORD@HOST:PORT/DATABASE, for MariaDB as well. If the password contains characters such as @, :, /, # or %, write them percent-encoded (@ is %40), or choose a password of letters and digits.

The address contains the password, so treat it as a secret. DATABASE_URL_FILE reads it from a file: Keep secrets in files.

The schema file for these databases is headed "MySQL 8 / MariaDB 10.5+". The site does not check the version it connects to.

Create the database and its user​

The site needs one empty database and a user with full rights on it. It creates its own tables. As an administrator:

CREATE DATABASE choir CHARACTER SET utf8mb4;
CREATE USER 'choir'@'%' IDENTIFIED BY 'a-long-password';
GRANT ALL PRIVILEGES ON choir.* TO 'choir'@'%';

Narrow '%' to the address the site connects from if you can. The user has to be able to create tables, at every start and after every update, so do not give the site a user that can only read and write rows.

Every table is created as InnoDB with the utf8mb4 character set, whatever the database's own default is.

How the site connects​

  • Times are UTC. The site stores and reads times as plain text in UTC. It does not change the time zone of the database server, so keep the server itself on UTC, as the official Docker images are. On a server set to another time zone, the times the database fills in by itself would be in that zone.
  • One statement at a time. The connection has multiple statements switched off. The schema file is sent one statement after another.
  • A pool of connections. There is no setting for its size. The driver's own default applies, which is ten at most for each copy of the site.
  • No table prefix. The tables have fixed names. Give the site a database of its own.

Encryption: DATABASE_SSL​

DATABASE_SSL=true encrypts the connection. Most managed databases require it.

It does not check the server's certificate. The connection is protected against someone listening, but not against someone standing in for your database server. There is no setting for a certificate authority file.

In Docker Compose​

docker-compose.yml in the source uses PostgreSQL, with a MariaDB service (mariadb:11) commented out beside it. To use it:

  1. Comment out the db service and remove db from the depends_on of the app service.

  2. Remove the # from the mysql service, and from mysql-data: under volumes: at the foot of the file.

  3. In the app service, change two lines:

    DB_PROVIDER: mysql
    DATABASE_URL: mysql://${MYSQL_USER}:${MYSQL_PASSWORD}@mysql:3306/${MYSQL_DATABASE}

  4. Add mysql to the depends_on of the app service, with condition: service_healthy.

  5. Set the three values in .env. The file's own defaults for the user and database are choir, but DATABASE_URL above has no defaults, so set all three:

    MYSQL_USER=choir
    MYSQL_PASSWORD=change-me
    MYSQL_DATABASE=choir

The service has MARIADB_RANDOM_ROOT_PASSWORD set, so the root password is random and printed once in that container's log at first start.

Check it​

Start the site and look for:

[db] schema is up to date (mysql)

The line says mysql or mariadb, whichever you wrote in DB_PROVIDER. Then list the tables. There should be 36:

mysql -h db.example.org -u choir -p choir -e 'SHOW TABLES'

Backups are yours​

The update does not back this database up

Before a one-click update the site makes a safety copy of a SQLite database only. A MySQL or MariaDB database is not copied, by the update or by anything else in the site. Set up mysqldump, mariadb-dump or your provider's backups before the site holds anything you would miss: Back up PostgreSQL, MySQL, S3 storage and Cloudflare.

If something goes wrong​

The site applies its schema at start, so a database it cannot reach stops it. The driver's own error follows [server] failed to start: in the log.

What you seeCause
DATABASE_URL is needed for DB_PROVIDER=mysql among the [config] linesDATABASE_URL is not set. This check is not made when DB_PROVIDER is written mariadb.
ECONNREFUSED, ENOTFOUND or a timeoutThe host or port is wrong, or a firewall is in the way.
Access denied for user …The user or password is wrong, the user may not connect from this address, or a special character in the password is not percent-encoded.
Unknown database '…'The database named at the end of the address does not exist.
CREATE command denied to user …The user may not create tables. Grant it all privileges on the database.