schedule2020-10-09

mysql8とlaravel(php7.4 pdo_mysql)でSQLSTATE[HY000] [2006] MySQL server has gone away

LaravelMySQLPHP

migrateしたら SQLSTATE[HY000] [2006] MySQL server has gone awayが出ました。

結論としては、認証方式mysql_native_passwordをcaching_sha2_passwordに変更したらMySQLに接続できるようになりました。環境と現象をまとめておきます。

環境

こちらで構築したDocker環境でこの現象が起きました。

構成とバージョンはこのようになってます。アプリ(php)とDB(mysql)のコンテナは分かれています。

php (php-fpm) 7.4.1
- composer 1.10.13
- nodejs v12.18.4
- npm 6.14.6
mysql 8.0
nginx 1.19.2

docekr-compose.ymlのmysqlの記述。

version: '3'

略

  db:
    image: mysql:8.0
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: database
      MYSQL_USER: docker
      MYSQL_PASSWORD: docker
      TZ: 'Asia/Tokyo'
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
    - ./docker/db/data:/var/lib/mysql
    - ./docker/db/my.cnf:/etc/mysql/conf.d/my.cnf
    - ./docker/db/sql:/docker-entrypoint-initdb.d
    ports:
    - 3306:3306

ここでデータベース、ユーザ、パスワードを設定しています。ホストOSのSQLクライアント(HeidiSQL)からはDB接続を確認できた。

エラー

# php artisan migrate

   Illuminate\Database\QueryException 

  SQLSTATE[HY000] [2006] MySQL server has gone away (SQL: select * from information_schema.tables where table_schema = database and table_name = migrations and table_type = 'BASE TABLE')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
    667▕         // If an exception occurs when attempting to run a query, we'll format the error
    668▕         // message to include the bindings with SQL, which will make this exception a
    669▕         // lot more helpful to the developer instead of just the database's errors.
    670▕         catch (Exception $e) {
  ➜ 671▕             throw new QueryException(
    672▕                 $query, $this->prepareBindings($bindings), $e
    673▕             );
    674▕         }
    675▕ 

      +37 vendor frames
  38  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

一応試した、composer dumpautoloadやphp artisan config:clearでは解消できなかった。

mysqlのユーザを確認

dbコンテナでmysqlのユーザを確認する。

$ docker-compose exec db bash

# mysql -u root -p
mysql: [Warning] World-writable config file '/etc/mysql/conf.d/my.cnf' is ignored.
Enter password: 

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| docker           | %         | mysql_native_password |
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

dockerとrootがあり、それぞれhostが%になっているためコンテナの外からもアクセスできる。（localhostはローカルだけ）

pluginが認証方式でmysql_native_passwordになっている。 caching_sha2_passwordはmysql8.0から追加された認証方式で、デフォルトである。

Dockerでビルドした際に5.7までのmysql_native_passwordに切り替わったようです。

認証方式の変更

ALTER USER '{ユーザ名}'@'{ホスト}' IDENTIFIED WITH {認証方式} BY '{パスワード}';で変更できる。

mysql> ALTER USER 'docker'@'%' IDENTIFIED WITH mysql_native_password BY 'docker';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| docker           | %         | caching_sha2_password |
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

caching_sha2_passwordに変更すると、接続できた！

$ php artisan migrate
Migration table created successfully.
...

なぜ認証方式によっては接続できないのか？

PHPのバージョンによって対応する方式が異なる。

MySQL8.0が登場する以前のPHPは、caching_sha2_passwordに対応していない。

PHP 7.1.16 より前のバージョン、もしくは PHP 7.2.4 より前の 7.2系の PHP では、 MySQL 8 サーバのデフォルトパスワードプラグインを mysql_native_password に設定するようにしてください。

http://0-oo.net/php-manual/ref.pdo-mysql.html

今回はPHP 7.4.1のpdo_mysqlを使っているためこれはOK。しかし、なぜか認証方式caching_sha2_passwordにする必要がある。

laravel

DB_CONNECTION=mysql
DB_HOST=db
DB_PORT=3306
DB_DATABASE=database
DB_USERNAME=docker
DB_PASSWORD=docker

その他考慮したことと確認方法

pdo_mysqlがインストールできていないのでは？

phpコンテナのDockerfileの記述。ビルドの際にpdo_mysqlをインストールしている。

# Install PHP Extensions
RUN apt install -y zlib1g-dev mariadb-client libzip-dev libonig-dev
RUN docker-php-ext-install -j$(nproc) zip pdo_mysql

pdo_mysqlがインストールできているか確認した。拡張を有効にするコマンドdocker-php-ext-enableを打ってみる。

$ docker-php-ext-enable pdo_mysql

warning: pdo_mysql (pdo_mysql.so) is already loaded!

インストール済で有効になっていました。

$ pecl search pdo_mysql
WARNING: channel "pecl.php.net" has updated its protocols, use "pecl channel-update pecl.php.net" to update
Retrieving data...0%
Matched packages, channel pecl.php.net:
=======================================
Package   Stable/(Latest) Local
PDO_MYSQL 1.0.2 (stable)        MySQL driver for PDO