skip to Main Content

According to docs:

Also, when the MySQL container is starting, it will ensure a database exists whose name matches the value of your DB_DATABASE environment variable.

My .env file looks like this.

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=

Yet when I try to run the migrations via sail artisan migrate, I get back this error:
SQLSTATE[HY000] [1049] Unknown database 'test' (SQL: select * from information_schema.tables where table_schema = test and table_name = migrations and table_type = 'BASE TABLE')

What I’ve tried:

  • Removing the docker container and images all together.
  • Running sail build --no-cache (to try and rebuild everything altogether)
  • When running sail shell, I went into MySQL and showed all databases. I could see the default laravel database there.

How do you tell sail to create the correct DB_DATABASE?

My docker-compose.yml:

# For more information: https://laravel.com/docs/sail
version: '3'
services:
    laravel.test:
        build:
            context: ./vendor/laravel/sail/runtimes/8.0
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-8.0/app
        ports:
            - '${APP_PORT:-80}:80'
        environment:
            WWWUSER: '${WWWUSER}'
            LARAVEL_SAIL: 1
        volumes:
            - '.:/var/www/html'
        networks:
            - sail
        depends_on:
            - mysql
            - redis
    mysql:
        image: 'mysql:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3306}:3306'
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
            MYSQL_DATABASE: '${DB_DATABASE}'
            MYSQL_USER: '${DB_USERNAME}'
            MYSQL_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
        volumes:
            - 'sailmysql:/var/lib/mysql'
        networks:
            - sail
    redis:
        image: 'redis:alpine'
        ports:
            - '${FORWARD_REDIS_PORT:-6379}:6379'
        volumes:
            - 'sailredis:/data'
        networks:
            - sail
    mailhog:
        image: 'mailhog/mailhog:latest'
        ports:
            - 1025:1025
            - 8025:8025
        networks:
            - sail
networks:
    sail:
        driver: bridge
volumes:
    sailmysql:
        driver: local
    sailredis:
        driver: local

2

Answers


  1. Case – Reusing an already created Docker volume

    If you stop Sail with sail down, the data volume remains on the Docker host without being deleted.

    When Sail is stopped, use sail down -v to delete the existing Docker volume data.

    First sail up, DB_DATABASE=forge

    When you first start Sail, a volume is created on the Docker host.

    grep DB_DATABASE .env
    DB_DATABASE=forge
    
    docker volume ls
    DRIVER    VOLUME NAME
    
    sail up -d
    Creating network "test_sail" with driver "bridge"
    Creating volume "test_sailmysql" with local driver
    Creating volume "test_sailredis" with local driver
    Creating test_mailhog_1 ... done
    Creating test_mysql_1   ... done
    Creating test_redis_1   ... done
    Creating test_laravel.test_1 ... done
    
    docker volume ls
    DRIVER    VOLUME NAME
    local     test_sailmysql
    local     test_sailredis
    
    sail mysql
    mysql> show databases;
    | forge              |
    

    However, when I exit Sail the Docker container is deleted but the volume is not deleted.

    sail down
    Stopping test_laravel.test_1 ... done
    Stopping test_mailhog_1      ... done
    Stopping test_redis_1        ... done
    Stopping test_mysql_1        ... done
    Removing test_laravel.test_1 ... done
    Removing test_mailhog_1      ... done
    Removing test_redis_1        ... done
    Removing test_mysql_1        ... done
    Removing network test_sail
    
    docker volume ls
    DRIVER    VOLUME NAME
    local     test_sailmysql
    local     test_sailredis
    

    Second sail up, DB_DATABASE=test

    If you start a second Sail in the same directory name, the already created Docker volume will be reused.

    grep DB_DATABASE .env
    DB_DATABASE=test
    
    sail up -d
    Creating network "test_sail" with driver "bridge"
    Creating test_mysql_1   ... done
    Creating test_redis_1   ... done
    Creating test_mailhog_1 ... done
    Creating test_laravel.test_1 ... done
    
    docker volume ls
    DRIVER    VOLUME NAME
    local     test_sailmysql
    local     test_sailredis
    

    Since data exists in test_sailmysql, which is the volume created in the first run, a new database creation task is not executed.

    sail mysql
      ERROR 1049 (42000): Unknown database 'test'
    
    sail artisan migrate
      IlluminateDatabaseQueryException
      SQLSTATE[HY000] [1049] Unknown database 'test' (SQL: select * from information_schema.tables where table_schema = test and table_name = migrations and table_type = 'BASE TABLE')
    

    Start after deleting the existing volume

    sail down -v
    ...
    Removing volume test_sailmysql
    Removing volume test_sailredis
    
    sail up -d
    ...
    Creating volume "test_sailmysql" with local driver
    Creating volume "test_sailredis" with local driver
    
    sail mysql
    mysql> show databases;
    | test               |
    
    sail artisan migrate
    Migration table created successfully.
    Migrating: 2014_10_12_000000_create_users_table
    Migrated:  2014_10_12_000000_create_users_table (214.30ms)
    Migrating: 2014_10_12_100000_create_password_resets_table
    Migrated:  2014_10_12_100000_create_password_resets_table (99.56ms)
    Migrating: 2019_08_19_000000_create_failed_jobs_table
    Migrated:  2019_08_19_000000_create_failed_jobs_table (151.61ms)
    

    sail down options

    sail down -h
    
        -v, --volumes           Remove named volumes declared in the `volumes`
                                section of the Compose file and anonymous volumes
                                attached to containers.
    
    Login or Signup to reply.
  2. Your problem is that you created your database without DB_PASSWORD set in your .env file and also tried to start it as root user.

    If you start your container with no password or with DB_USERNAME set to root you will get these errors when you run sail up respectively:

    // Set DB_USERNAME to root will not create the database but you can still query mysql
    bracing-mysql-1         | 2022-02-14 04:28:35+00:00 [ERROR] [Entrypoint]: MYSQL_USER="root", MYSQL_USER and MYSQL_PASSWORD are for configuring a regular user and cannot be used for the root user
    bracing-mysql-1         |     Remove MYSQL_USER="root" and use one of the following to control the root user password:
    bracing-mysql-1         |     - MYSQL_ROOT_PASSWORD
    bracing-mysql-1         |     - MYSQL_ALLOW_EMPTY_PASSWORD
    bracing-mysql-1         |     - MYSQL_RANDOM_ROOT_PASSWORD
    
    // Did not set DB_PASSWORD results in the user not being created
    bracing-mysql-1         | 2022-02-14 04:30:13+00:00 [Warn] [Entrypoint]: MYSQL_USER specified, but missing MYSQL_PASSWORD; MYSQL_USER will not be created
    

    The password error can be misleading because even if you set MYSQL_ALLOW_EMPTY_PASSWORD: 1 in your docker-compose.yml you still need to put a password in otherwise the user is not created.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search