Laravel Sail rebuild default database

Question

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

Fatma

Make sure you're running sail artisan migrate instead of php artisan migrate – Triacid

What image are you running? Is it an official one or you have a Dockerfile? How are you starting the container? – Crambo

An official one, I haven't done any customization. Running it with: sail up. – Fatma

@Fatma Could you add contents of your docker-compose.yml? It should be in the root of your project. – Sedentary

Answer 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
  Illuminate\Database\QueryException
  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.

Household

Thanks! Deleting volumes fixed it for me. I'm sure that I encounter this error because to the instructions I've followed from multiple sources for learning how to use sail and executing commands multiple times when the setup is not yet completed. :) – Purge

You have to be aware of running sail config:clear too. 😅 – Jackijackie

Answer 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.

Tonsillotomy