Symfony6项目创建数据库遇SQLSTATE[HY000][1045]权限拒绝错误求助

搭建Symfony6实战教育项目时，执行数据库创建命令报错：

Could not create database for connection named default An exception occurred in the driver: SQLSTATE[HY000] [1045] Access denied 'root'@'localhost'

已完成基础操作：

• 安装Symfony依赖包
• 安装并运行Docker
• 使用项目预配置的docker-compose.yml、.env、doctrine.yaml文件

尝试的两种数据库配置方案 #

方案一：通过.env配置DATABASE_URL #

测试了多种serverVersion参数组合：

# .env
DATABASE_URL="mysql://root:root@127.0.0.1:3306/symf6-hands-on?serverVersion=mariadb-10.8.3&charset=utf8mb4"
# 或
DATABASE_URL="mysql://root:root@127.0.0.1:3306/symf6-hands-on?serverVersion=10.8.3-MariaDB-1:10.8.3+maria~jammy&charset=utf8mb4"
# 或
DATABASE_URL="mysql://root:root@127.0.0.1:3306/symf6-hands-on?serverVersion=10.8.3-MariaDB-1:10.8.3+maria~jammy&charset=utf8mb4&sslmode=disable"

对应doctrine.yaml配置：

# doctrine.yaml 
dbal:
    url: '%env(resolve:DATABASE_URL)%'
    server_version: '10.8.3'

方案二：直接在doctrine.yaml中配置参数 #

# doctrine.yaml 
dbal:
    #url: '%env(resolve:DATABASE_URL)%'
    server_version: '10.8.3'
    driver: pdo_mysql
    host: 127.0.0.1
    port: 3306
    dbname: symf6-hands-on
    user: root
    password: root

已验证的信息 #

容器内MariaDB版本 #

执行命令进入容器：

$ docker exec -it symfony6-hands-on-mysql-1 mysql -u root -p

查询版本结果：

MariaDB [(none)]> select version();
+-------------------------------------+
| version()                           |
+-------------------------------------+
| 10.8.3-MariaDB-1:10.8.3+maria~jammy |
+-------------------------------------+

Docker配置 #

两种方案均使用以下docker-compose.yml配置：

# docker-compose.yml
mysql:
    external_links:
      - "host.docker.internal:localhost"
    image: mariadb:10.8.3
    # Mac M1环境下已开启
    platform: linux/arm64/v8
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - 3306:3306

执行的创建命令：symfony console doctrine:database:create 或 bin/console doctrine:database:create

本地环境参数 #

• Mac M1 Ventura 13.1
• PHP 8.2.1
• Symfony 6.1.12

执行bin/console about输出：

-------------------- --------------------------------- 
  Symfony                                               
 -------------------- --------------------------------- 
  Version              6.1.12          ...                 
 -------------------- --------------------------------- 
  Kernel                                                
 -------------------- --------------------------------- 
  Type                 App\Kernel                       
  Environment          dev                              
  Debug                true                             
  Charset              UTF-8           ...               
 -------------------- --------------------------------- 
  PHP                                                   
 -------------------- --------------------------------- 
  Version              8.2.1                            
  Architecture         64 bits                          
  Intl locale          en_US_POSIX                      
  Timezone             UTC (2023-02-16T09:50:54+00:00)  
  OPcache              true                             
  APCu                 false                            
  Xdebug               false                            
 -------------------- --------------------------------- 

更新操作：尝试授予root权限 #

执行以下授权命令：

GRANT ALL PRIVILEGES ON {{MYSQL_DATABASE_CM}}.* TO '{{MYSQL_USERNAME_CM}}'@'%';
# 以及
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
# 以及
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
# 以及
GRANT ALL  ON *.* TO 'root'@'localhost';

权限查询结果：

MariaDB [(none)]> SHOW GRANTS FOR 'root'@'%';

| Grants for root@%                                                                                                              
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION 
|1 row in set (0.001 sec)

# 或
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `root`.* TO `root`@`localhost`                                                                                |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------+

更新内容2：Docker容器运行状态 #

symfony6-hands-on % docker ps
CONTAINER ID   IMAGE                    COMMAND                  CREATED      STATUS      PORTS                                            NAMES
bfcac67ebb6c   mariadb:10.8.3           "docker-entrypoint.s…"   5 days ago   Up 2 days   0.0.0.0:3306->3306/tcp                           symfony6-hands-on-mysql-1
2bb1ad5ba56d   adminer                  "entrypoint.sh php -…"   6 days ago   Up 2 days   0.0.0.0:8080->8080/tcp                           symfony6-hands-on-adminer-1
cb9ea779c0bb   schickling/mailcatcher   "sh -c 'mailcatcher …"   6 days ago   Up 2 days   0.0.0.0:1025->1025/tcp, 0.0.0.0:1080->1080/tcp   symfony6-hands-on-mailer-1

内容的提问来源于stack exchange，提问作者Olex Nikonov