Issue when creating the test database

[julien-lmnr]

Hi everyone!

When I run my tests, I encounter an error during the creation of the test database. Here is the error message:

Could not create database `app_test` for connection named default
An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'app'@'%' to database 'app_test'

Apparently, I don't have any syntax errors. I don't understand why this permission error occurs or how to fix it. Here is my MYSQL configuration in compose.yaml:

###> doctrine/doctrine-bundle ###
  database:
    image: mysql:${MYSQL_VERSION:-8.0.38}
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE:-app}
      MYSQL_RANDOM_ROOT_PASSWORD: "true"
      # You should definitely change the password in production
      MYSQL_PASSWORD: ${MYSQL_PASSWORD:-!ChangeMe!}
      MYSQL_USER: ${MYSQL_USER:-app}
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 5s
      retries: 5
      start_period: 60s
    volumes:
      - database_data:/var/lib/mysql:rw
      # You may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data!
      - ./docker/db/data:/var/lib/mysql:rw
###< doctrine/doctrine-bundle ###

Even when trying to create this database manually in the container, I get the same error...

Thank you in advance for your feedback. Have a great day!

[7-zete-7]

Hi @julien-lmnr!

Can you show entire your's compose.yaml, compose.override.yaml, compose.test.yaml (if it exists) files?

Also need to see your .env files (database DSN with app_test) to understand your environment.

[7-zete-7]

Also I see an issue in your database service configuration: the /var/lib/mysql volume target is mounted twice.

You need to leave only one of your mounting to /var/lib/mysql.

[julien-lmnr]

Hi @7-zete-7

Of course, here are my files:

# compose.yaml

services:
  php:
    image: ${IMAGES_PREFIX:-}app-php
    restart: unless-stopped
    environment:
      SERVER_NAME: ${SERVER_NAME:-localhost}, php:80
      MERCURE_PUBLISHER_JWT_KEY: ${CADDY_MERCURE_JWT_SECRET:-!ChangeThisMercureHubJWTSecretKey!}
      MERCURE_SUBSCRIBER_JWT_KEY: ${CADDY_MERCURE_JWT_SECRET:-!ChangeThisMercureHubJWTSecretKey!}
      TRUSTED_PROXIES: ${TRUSTED_PROXIES:-127.0.0.0/8,10.0.0.0/8,172.16.0.0/12,192.168.0.0/16}
      TRUSTED_HOSTS: ${TRUSTED_HOSTS:-^${SERVER_NAME:-example\.com|localhost}|php$$}
      DATABASE_URL: mysql://${MYSQL_USER:-app}:${MYSQL_PASSWORD:-!ChangeMe!}@database:3306/${MYSQL_DATABASE:-app}?serverVersion=${MYSQL_VERSION:-8.0.38}&charset=${MYSQL_CHARSET:-utf8mb4}
      MERCURE_URL: ${CADDY_MERCURE_URL:-http://php/.well-known/mercure}
      MERCURE_PUBLIC_URL: ${CADDY_MERCURE_PUBLIC_URL:-https://${SERVER_NAME:-localhost}/.well-known/mercure}
      MERCURE_JWT_SECRET: ${CADDY_MERCURE_JWT_SECRET:-!ChangeThisMercureHubJWTSecretKey!}
      # The two next lines can be removed after initial installation
      # SYMFONY_VERSION: ${SYMFONY_VERSION:-}
      # STABILITY: ${STABILITY:-stable}
    volumes:
      - caddy_data:/data
      - caddy_config:/config
    ports:
      # HTTP
      - target: 80
        published: ${HTTP_PORT:-80}
        protocol: tcp
      # HTTPS
      - target: 443
        published: ${HTTPS_PORT:-443}
        protocol: tcp
      # HTTP/3
      - target: 443
        published: ${HTTP3_PORT:-443}
        protocol: udp

# Mercure is installed as a Caddy module, prevent the Flex recipe from installing another service
###> symfony/mercure-bundle ###
###< symfony/mercure-bundle ###

###> doctrine/doctrine-bundle ###
  database:
    image: mysql:${MYSQL_VERSION:-8.0.38}
    environment:
      MYSQL_DATABASE: ${MYSQL_DATABASE:-app}
      # You should definitely change the password in production
      MYSQL_RANDOM_ROOT_PASSWORD: "true"
      MYSQL_PASSWORD: ${MYSQL_PASSWORD:-!ChangeMe!}
      MYSQL_USER: ${MYSQL_USER:-app}
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 5s
      retries: 5
      start_period: 60s
    volumes:
      - database_data:/var/lib/mysql:rw
      # You may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data!
      # - ./docker/db/data:/var/lib/mysql:rw
###< doctrine/doctrine-bundle ###

volumes:
  caddy_data:
  caddy_config:
###> symfony/mercure-bundle ###
  mercure_data:
  mercure_config:
###< symfony/mercure-bundle ###
###> doctrine/doctrine-bundle ###
  database_data:
###< doctrine/doctrine-bundle ###

# compose.override.yaml

# Development environment override
services:
  php:
    build:
      context: .
      target: frankenphp_dev
    volumes:
      - ./:/app
      - ./frankenphp/Caddyfile:/etc/caddy/Caddyfile:ro
      - ./frankenphp/conf.d/app.dev.ini:/usr/local/etc/php/conf.d/app.dev.ini:ro
      # If you develop on Mac or Windows you can remove the vendor/ directory
      #  from the bind-mount for better performance by enabling the next line:
      #- /app/vendor
    environment:
      #MERCURE_EXTRA_DIRECTIVES: demo
      # See https://xdebug.org/docs/all_settings#mode
      XDEBUG_MODE: "${XDEBUG_MODE:-off}"
    extra_hosts:
      # Ensure that host.docker.internal is correctly defined on Linux
      - host.docker.internal:host-gateway
    tty: true

###> symfony/mercure-bundle ###
###< symfony/mercure-bundle ###

###> doctrine/doctrine-bundle ###
  database:
    ports:
      - "3306:3306"
###< doctrine/doctrine-bundle ###

###> symfony/mailer ###
  mailer:
    image: axllent/mailpit
    restart: unless-stopped
    ports:
      - "1025:1025"
      - "8025:8025"
    environment:
      MP_SMTP_AUTH_ACCEPT_ANY: 1
      MP_SMTP_AUTH_ALLOW_INSECURE: 1
###< symfony/mailer ###

# compose.prod.yaml

# Production environment override
services:
  php:
    build:
      context: .
      target: frankenphp_prod
    environment:
      APP_SECRET: ${APP_SECRET}
      MERCURE_PUBLISHER_JWT_KEY: ${CADDY_MERCURE_JWT_SECRET}
      MERCURE_SUBSCRIBER_JWT_KEY: ${CADDY_MERCURE_JWT_SECRET}

In my .env file, for the database, I have this:

DATABASE_URL=mysql://${MYSQL_USER:-app}:${MYSQL_PASSWORD:-!ChangeMe!}@database:3306/${MYSQL_DATABASE:-app}?serverVersion=${MYSQL_VERSION:-8.0.38}&charset=${MYSQL_CHARSET:-utf8mb4}

Indeed, I fixed the /var/lib/mysql issue, but it doesn't change anything. I switched back to PostgreSQL to see if the test database could be created, and apparently, it works.

[7-zete-7]

I can't find any documentation for this. I can suggest a solution that I would use.

Seems like you need to create compose.test.yaml file

# compose.test.yaml

services:
  php:
    environment:
      APP_ENV: ${APP_ENV:-test}
      DATABASE_URL: mysql://${TEST_MYSQL_USER:-app_test}:${TEST_MYSQL_PASSWORD:-!ChangeMe!}@database:3306/${TEST_MYSQL_DATABASE:-app_test}?serverVersion=${TEST_MYSQL_VERSION:-8.0.38}&charset=${TEST_MYSQL_CHARSET:-utf8mb4}
    command: ["php", "bin/phpunit"]

  database:
    image: mysql:${TEST_MYSQL_VERSION:-8.0.38}
    environment:
      MYSQL_DATABASE: ${TEST_MYSQL_DATABASE:-app_test}
      # You should definitely change the password in production
      MYSQL_RANDOM_ROOT_PASSWORD: "true"
      MYSQL_PASSWORD: ${TEST_MYSQL_PASSWORD:-!ChangeMe!}
      MYSQL_USER: ${TEST_MYSQL_USER:-app_test}
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 5s
      retries: 5
      start_period: 60s
    volumes:
      - database_data:/var/lib/mysql:rw

Then start the test Docker Compose project via

docker compose -f compose.yaml -f compose.override.yaml -f compose.test.yaml run php

[julien-lmnr]

Thanks, I'll try your solution to see how it goes :smile:

[maxhelias]

This can be happen if you change the credentials after a docker image build.