4

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
Bulent
  • 3,307
  • 1
  • 14
  • 22

4 Answers4

8

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.

Tritol
  • 85
  • 1
  • 6
  • Thanks for your help. Your suggestion removed the error, but when I check databases, I didn't see tenant's database. Did manage to see created database? – Bulent Jul 15 '21 at 08:20
  • Yes, I used the commands as described at the end of this page: https://tenancyforlaravel.com/docs/v3/quickstart That created two new databases: tenantfoo and tenantbar. Then I connected successfully to both new databases with the same root/password credentials from the main database. – Tritol Jul 18 '21 at 04:47
  • On Laravel 9 with the corresponding Sail version i was able to simply connect as root without any errors (username: "root" / password: "password"). You may also use a third party mysql-client like Mysql Workbench and connect with these credentials. Then you are able do anything like creating additional databases. – lsblsb May 02 '22 at 11:01
1

Ran into the same problem, what worked for me was creating an init.sql file

GRANT ALL PRIVILEGES ON *.* TO 'sail'@'%';
FLUSH PRIVILEGES;

And adding this to the mysql entry:

volumes:
            - ./path/to/init.sql:/docker-entrypoint-initdb.d/init.sql
Mike
  • 11
  • 2
0

What worked for me:

I have PhpMyAdmin installed in my container and I able to login to to it using,

server: mysql
username: root
password: secret

After successful login, I navigated to the User Accounts page, selected sail and granted all the permission that was appropriate for my work and save.

This will resolve the issue of SQLSTATE[HY000] [1045] Access denied

BlackPearl
  • 2,532
  • 3
  • 33
  • 49
0

I asked this question long time ago. I had stopped working on that project. Now, I've started a new one, faced with the same issue. I came across with my own question while searching the solution. I found it, here it's:

You should execute the following commands on your teminal:

docker-compose exec mysql bash
mysql -u root -p
password: password
GRANT ALL PRIVILEGES ON *.* TO 'sail'@'%';
FLUSH PRIVILEGES;
EXIT;
exit
Bulent
  • 3,307
  • 1
  • 14
  • 22