[FIXED] How To Create A New Database For Each Tenant on Laravel Sail, Docker

Issue

I'm developing a multi-tenant app first time by using Laravel 8, Tenancy For Laravel on Docker and WSL2. My issue occures when I try to create a tenant. I want to create a new database for each tenant, but creating database trows the following error:

SQLSTATE[42000]:
Syntax error or access violation:
1044 Access denied for user 'sail'@'%' to database 'tenant-a6d0813b-a546-428d-859a-15095724fb73'
(SQL: CREATE DATABASE `tenant-a6d0813b-a546-428d-859a-15095724fb73` CHARACTER SET `utf8mb4` COLLATE `utf8mb4_unicode_ci`) 

I don't even know where should I start from because I'm pretty new on Docker, WLS and Laravel Sail. Before I moved this project to Docker, I was able to create databases on Xammp, but not anymore.

docker-compose.yml file is as it's shipped by Laravel exept the part that phpmyadmin is included:

phpmyadmin:
   image: phpmyadmin
   restart: always
   ports:
       - 8080:80
   environment:
       - PMA_HOST=mysql
   depends_on:
       - mysql
   networks:
       - sail

Solution

I ran into the exact same problem today.

The problem seems to be the db user 'sail' set in the .env file. It doesn't have the required privileges to create a database.

Opening the mysql console with the sail mysql command and trying to create a database with CREATE DATABASE test; causes the same error.

Changing the paramater in the .env file to:

DB_USERNAME=root

and rebuilding the docker container caused an error in the docker creation log and therefore mysql not running at all.

Logging into docker mysql as root and trying to set new privileges to the 'sail' user didn't help either.

What worked for me in the end was building the application with the standard

DB_USERNAME=sail

starting the application with sail up -d and then changing the .env file to

DB_USERNAME=root

After that I reset the config with sail artisan config:clear.

Answered By - Tritol

Answer Checked By - Marilyn (PHPFixing Volunteer)