環境
OS等:Windows 10 WSL上のUbuntuのDockerにsailで構築
Laravel:v8.83.3
PHP:v8.1.3
データベース:MySQL 8.0
ソースコードgitリポジトリ
リポジトリ:https://github.com/d-sazanami/laravel-jissenkaihatsu-v1
備考
開発環境として.envを参照する環境、PHPUnitのテストをする用に.env.testingを参照する環境を構築
開発環境、テストする環境は、異なるデータベースを参照するよう設定した(・・・はず)
現象
発端の現象は、testing環境でseederを実行したところ、SQLSTATE[HY000] [2002] Connection refusedのエラーが発生。(詳細は後述)
調べた(内容は後述)結果、発行されるSQLのクォーテーションでくくって欲しい部分がくくられずに発行されている。なお、マイグレーションはtesting環境でも成功しており、MySQLのデータベースを直接見に行くと、データベース、テーブル共に存在し、testing環境でアクセスするMySQLユーザーからも見える。
この現象は、testing環境のみ発生し、defaultの環境では発生しない。
testing環境でseederを実行したことの詳細
ソースコード
seeder
php
1<?php 2 3namespace Database\Seeders; 4 5use Illuminate\Database\Seeder; 6use Illuminate\Support\Facades\DB; 7 8class PersonSeeder extends Seeder 9{ 10 /** 11 * Run the database seeds. 12 * 13 * @return void 14 */ 15 public function run() 16 { 17 DB::table('people')->insert([ 18 'name' => '博麗霊夢', 19 'mail' => 'reimu@hakurei.com', 20 'age' => 16, 21 ]); 22 } 23}
env.testing
APP_ENV=testing APP_KEY=[.envと違うものを設定] APP_DEBUG=true APP_URL=http://localhost LOG_CHANNEL=stack LOG_LEVEL=debug DB_CONNECTION=mysql DB_HOST=mysql DB_PORT=3306 DB_DATABASE=laravel_app_test DB_USERNAME=sail DB_PASSWORD=sail ~(略)~
phpunit.xml
xml
1<?xml version="2.0" encoding="UTF-8"?> 2<phpunit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 3 xsi:noNamespaceSchemaLocation="./vendor/phpunit/phpunit/phpunit.xsd" 4 bootstrap="vendor/autoload.php" 5 colors="true" 6> 7 <testsuites> 8 <testsuite name="Unit"> 9 <directory suffix="Test.php">./tests/Unit</directory> 10 </testsuite> 11 <testsuite name="Feature"> 12 <directory suffix="Test.php">./tests/Feature</directory> 13 </testsuite> 14 </testsuites> 15 <coverage processUncoveredFiles="true"> 16 <include> 17 <directory suffix=".php">./app</directory> 18 </include> 19 </coverage> 20 <php> 21 <server name="APP_ENV" value="testing"/> 22 <server name="BCRYPT_ROUNDS" value="4"/> 23 <server name="CACHE_DRIVER" value="array"/> 24 <server name="DB_CONNECTION" value="testing"/> 25 <server name="DB_DATABASE" value="laravel_app_test"/> 26 <server name="DB_USERNAME" value="sail"/> 27 <server name="DB_PASSWORD" value="sail"/> 28 <server name="MAIL_MAILER" value="array"/> 29 <server name="QUEUE_CONNECTION" value="sync"/> 30 <server name="SESSION_DRIVER" value="array"/> 31 <server name="TELESCOPE_ENABLED" value="false"/> 32 </php> 33</phpunit>
config/database.php
php
1 'connections' => [ 2 3 'sqlite' => [ 4 'driver' => 'sqlite', 5 'url' => env('DATABASE_URL'), 6 'database' => env('DB_DATABASE', database_path('database.sqlite')), 7 'prefix' => '', 8 'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true), 9 ], 10 11 'mysql' => [ 12 'driver' => 'mysql', 13 'url' => env('DATABASE_URL'), 14 'host' => env('DB_HOST', '127.0.0.1'), 15 'port' => env('DB_PORT', '3306'), 16 'database' => env('DB_DATABASE', 'forge'), 17 'username' => env('DB_USERNAME', 'forge'), 18 'password' => env('DB_PASSWORD', ''), 19 'unix_socket' => env('DB_SOCKET', ''), 20 'charset' => 'utf8mb4', 21 'collation' => 'utf8mb4_unicode_ci', 22 'prefix' => '', 23 'prefix_indexes' => true, 24 'strict' => true, 25 'engine' => null, 26 'options' => extension_loaded('pdo_mysql') ? array_filter([ 27 PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'), 28 ]) : [], 29 ], 30 31 'pgsql' => [ 32 'driver' => 'pgsql', 33 'url' => env('DATABASE_URL'), 34 'host' => env('DB_HOST', '127.0.0.1'), 35 'port' => env('DB_PORT', '5432'), 36 'database' => env('DB_DATABASE', 'forge'), 37 'username' => env('DB_USERNAME', 'forge'), 38 'password' => env('DB_PASSWORD', ''), 39 'charset' => 'utf8', 40 'prefix' => '', 41 'prefix_indexes' => true, 42 'schema' => 'public', 43 'sslmode' => 'prefer', 44 ], 45 46 'sqlsrv' => [ 47 'driver' => 'sqlsrv', 48 'url' => env('DATABASE_URL'), 49 'host' => env('DB_HOST', 'localhost'), 50 'port' => env('DB_PORT', '1433'), 51 'database' => env('DB_DATABASE', 'forge'), 52 'username' => env('DB_USERNAME', 'forge'), 53 'password' => env('DB_PASSWORD', ''), 54 'charset' => 'utf8', 55 'prefix' => '', 56 'prefix_indexes' => true, 57 ], 58 59 'testing' => [ 60 'driver' => 'mysql', 61 'url' => env('DATABASE_URL'), 62 'host' => '127.0.0.1', 63 'port' => '3306', 64 'database' => 'laravel_app_test', 65 'username' => 'sail', 66 'password' => 'sail', 67 'unix_socket' => env('DB_SOCKET', ''), 68 'charset' => 'utf8mb4', 69 'collation' => 'utf8mb4_unicode_ci', 70 'prefix' => '', 71 'prefix_indexes' => true, 72 'strict' => true, 73 'engine' => null, 74 'options' => extension_loaded('pdo_mysql') ? array_filter([ 75 PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'), 76 ]) : [], 77 ] 78 79 ], 80
実行内容
bash
1# php artisan db:seed --database=testing --class=PersonSeeder 2 3 Illuminate\Database\QueryException 4 5 SQLSTATE[HY000] [2002] Connection refused (SQL: insert into `people` (`name`, `mail`, `age`) values (博麗霊夢, reimu@hakurei.com, 16)) 6 7 at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712 8 708▕ // If an exception occurs when attempting to run a query, 9we'll format the error 10 709▕ // message to include the bindings with SQL, which will make this exception a 11 710▕ // lot more helpful to the developer instead of just the database's errors. 12 711▕ catch (Exception $e) { 13 ➜ 712▕ throw new QueryException( 14 713▕ $query, $this->prepareBindings($bindings), $e 15 714▕ ); 16 715▕ } 17 716▕ } 18 19 +16 vendor frames 20 17 database/seeders/PersonSeeder.php:18 21 Illuminate\Database\Query\Builder::insert() 22 23 +22 vendor frames 24 40 artisan:37 25 Illuminate\Foundation\Console\Kernel::handle()
調べたこと
マイグレーション状態確認
bash
1# php artisan migrate:status 2+------+------------------------------------------------+-------+ 3| Ran? | Migration | Batch | 4+------+------------------------------------------------+-------+ 5| Yes | 2014_10_12_000000_create_users_table | 1 | 6| Yes | 2014_10_12_100000_create_password_resets_table | 1 | 7| Yes | 2019_08_19_000000_create_failed_jobs_table | 1 | 8| Yes | 2022_03_05_010816_create_people_table | 1 | 9| Yes | 2022_03_05_034018_add_age_to_people | 1 | 10| Yes | 2022_03_09_025533_create_jobs_table | 1 | 11+------+------------------------------------------------+-------+ 12 13# php artisan migrate:status --database=testing 14 15 Illuminate\Database\QueryException 16 17 SQLSTATE[HY000] [2002] Connection refused (SQL: select * from information_schema.tables where table_schema = laravel_app_test and table_name = migrations and table_type = 'BASE TABLE') 18 19 at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712 20 708▕ // If an exception occurs when attempting to run a query, we'll format the error 21 709▕ // message to include the bindings with SQL, which will make this exception a 22 710▕ // lot more helpful to the developer instead of just the database's errors. 23 711▕ catch (Exception $e) { 24 ➜ 712▕ throw new QueryException( 25 713▕ $query, $this->prepareBindings($bindings), $e 26 714▕ ); 27 715▕ } 28 716▕ } 29 30 +35 vendor frames 31 36 artisan:37 32 Illuminate\Foundation\Console\Kernel::handle()
MySQLの状態
sql
1mysql> select * from information_schema.tables where table_schema = 'laravel_app_test' and table_name = 'migrations' and table_type = 'BASE TABLE'; 2 3+---------------+------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ 4| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | 5+---------------+------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ 6| def | laravel_app_test | migrations | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 7 0 | 0 | 0 | NULL | 2022-03-10 05:56:37 | NULL | NULL | utf8mb4_unicode_ci | NULL | 8 | | 9+---------------+------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ 101 row in set (0.00 sec) 11
回答2件
あなたの回答
tips
プレビュー