Initial DB migration fails on MySQL/MariaDB when running n8n in the Docker image

[leonboot]

Bug Description

Version tested: 1.76.1

When initially starting a n8n instance in Docker with a MySQL/MariaDB database backend, the initial setup fails on the database migrations that are being executed. Depending on the flavour (MySQL or MariaDB) and version (5.7, 8.0 or 8.4 for MySQL) the migration script fails on different points. Here are the last 3 lines of the migration script for each flavour:

MySQL 5.7.44:

Finished migration CreateTestRun1732549866705
Starting migration AddMockedNodesColumnToTestDefinition1733133775640
Migration "AddMockedNodesColumnToTestDefinition1733133775640" failed, error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('[]') NOT NULL' at line 1

MySQL 8.0.41:

Finished migration AddManagedColumnToCredentialsTable1734479635324
Starting migration AddStatsColumnsToTestRun1736172058779
Migration "AddStatsColumnsToTestRun1736172058779" failed, error: Column check constraint 'test_run_chk_1' references other column.

MySQL 8.4.4:

Finished migration MigrateTestDefinitionKeyToString1731582748663
Starting migration CreateTestMetricTable1732271325258
Migration "CreateTestMetricTable1732271325258" failed, error: Failed to add the foreign key constraint. Missing unique key for constraint 'FK_3a4e9cf37111ac3270e2469b475' in the referenced table 'test_definition'

MariaDB 10.11.10 and 11.4.4:

Finished migration RemoveSkipOwnerSetup1681134145997
Starting migration MigrateIntegerKeysToString1690000000001
Migration "MigrateIntegerKeysToString1690000000001" failed, error: Unknown collation: 'utf8mb4_0900_ai_ci'

To Reproduce

Given the following docker compose file:

services:

    n8n:
        image: n8nio/n8n:${N8N_VERSION}
        restart: unless-stopped
        environment:
            - GENERIC_TIMEZONE=UTC
            - N8N_ENFORCE_SETTINGS_FILE_PERMISSIONS=true
            - DB_TYPE=mysqldb
            - DB_MYSQLDB_DATABASE=${DB_NAME}
            - DB_MYSQLDB_HOST=database
            - DB_MYSQLDB_PORT=3306
            - DB_MYSQLDB_USER=${DB_USER}
            - DB_MYSQLDB_PASSWORD=${DB_PASSWORD}
        depends_on:
            database:
                condition: service_started

    database:
        image: ${DATABASE_IMAGE}
        restart: unless-stopped
        environment:
            MYSQL_RANDOM_ROOT_PASSWORD: "yes"
            MYSQL_DATABASE: ${DB_NAME}
            MYSQL_USER: ${DB_USER}
            MYSQL_PASSWORD: ${DB_PASSWORD}

And the following .env file:

N8N_VERSION=1.76.1
DATABASE_IMAGE=mysql:8.4

DB_NAME=n8n
DB_USER=n8n
DB_PASSWORD=n8n

Start the services by running docker compose up and watch the migrations start after the database has been set up (and n8n has already exited several times because of the database not being available, hence the restart: unless-stopped setting). The migrations will fail, no matter what database variant is being used.

Retry the docker compose up after changing the DATABASE_IMAGE value in the .env to either mysql:5.7, mysql:8.0, or mariadb:11.4.

Expected behavior

A running instance of n8n with a fully initialized database.

Operating System

N/A (Docker)

n8n Version

1.76.1

Node.js Version

N/A

Database

MySQL

Execution mode

main (default)

[Joffcom]

Hey @leonboot,

We have created an internal ticket to look into this which we will be tracking as "N8N-8227"

[Joffcom]

Hey @leonboot

I am not sure what the future of MySQL support looks like, I know we were planning to drop support for it and we have removed the options from our docs ready for this.

Is MySQL something you have to use or can you move to Postgres?

[leonboot]

@Joffcom thanks for your quick response! Postres is not our preferred database of choice as we lack the administration skills. We'll give it a go nonetheless!

FYI: I assumed n8n still had proper MySQL support because of the information in the README for the image on Docker Hub. If the official documentation does not mention MySQL support, it might be good to remove it from that README as well.