Laravel 5.8 。 migrate が MySQL の場合は問題ないのに SQLite では失敗する現象の原因と修正

対処方法まとめ

マイグレーションファイルにて、メソッドチェーンで絡むの後にインデックスをつなげる場合は、インデックスのメソッドの引数を指定しない。
例えば、 $table->string('meta_key')->nullable()->index('meta_key'); ではなくて、 $table->string('meta_key')->nullable()->index(); とする。

エラー内容

root@6741121a1415:/var/www# vendor/bin/phpunit
PHPUnit 7.5.14 by Sebastian Bergmann and contributors.

E..                                                                 3 / 3 (100%)

Time: 684 ms, Memory: 20.00 MB

There was 1 error:

1) Tests\Feature\Admin\PostControllerTest::testExample
Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 index meta_key already exists (SQL: create index "meta_key" on "wp_postmeta" ("meta_key"))

/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:459
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:97
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:278
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:167
/var/www/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:237
/var/www/database/migrations/2019_06_02_062600_create_wp_postmeta_table.php:20
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:387
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:396
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:197
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:162
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:109
/var/www/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:71
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/vendor/symfony/console/Command/Command.php:255
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:198
/var/www/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:61
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/vendor/symfony/console/Command/Command.php:255
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/vendor/symfony/console/Application.php:921
/var/www/vendor/symfony/console/Application.php:273
/var/www/vendor/symfony/console/Application.php:149
/var/www/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Console/Application.php:182
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:275
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:136
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:220
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:56
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:55
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:18
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:105
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:72
/var/www/tests/Feature/Admin/PostControllerTest.php:19

Caused by
Doctrine\DBAL\Driver\PDOException: SQLSTATE[HY000]: General error: 1 index meta_key already exists

/var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:63
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:459
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:97
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:278
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:167
/var/www/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:237
/var/www/database/migrations/2019_06_02_062600_create_wp_postmeta_table.php:20
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:387
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:396
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:197
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:162
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:109
/var/www/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:71
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/vendor/symfony/console/Command/Command.php:255
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:198
/var/www/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:61
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/vendor/symfony/console/Command/Command.php:255
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/vendor/symfony/console/Application.php:921
/var/www/vendor/symfony/console/Application.php:273
/var/www/vendor/symfony/console/Application.php:149
/var/www/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Console/Application.php:182
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:275
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:136
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:220
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:56
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:55
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:18
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:105
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:72
/var/www/tests/Feature/Admin/PostControllerTest.php:19

Caused by
PDOException: SQLSTATE[HY000]: General error: 1 index meta_key already exists

/var/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:61
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
/var/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:459
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:97
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:278
/var/www/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:167
/var/www/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:237
/var/www/database/migrations/2019_06_02_062600_create_wp_postmeta_table.php:20
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:387
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:396
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:197
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:162
/var/www/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:109
/var/www/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:71
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/vendor/symfony/console/Command/Command.php:255
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:198
/var/www/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/FreshCommand.php:61
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34
/var/www/vendor/laravel/framework/src/Illuminate/Container/Container.php:576
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:183
/var/www/vendor/symfony/console/Command/Command.php:255
/var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php:170
/var/www/vendor/symfony/console/Application.php:921
/var/www/vendor/symfony/console/Application.php:273
/var/www/vendor/symfony/console/Application.php:149
/var/www/vendor/laravel/framework/src/Illuminate/Console/Application.php:90
/var/www/vendor/laravel/framework/src/Illuminate/Console/Application.php:182
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:275
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:136
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/PendingCommand.php:220
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/InteractsWithConsole.php:56
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:55
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/RefreshDatabase.php:18
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:105
/var/www/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:72
/var/www/tests/Feature/Admin/PostControllerTest.php:19

ERRORS!
Tests: 3, Assertions: 2, Errors: 1.
root@6741121a1415:/var/www#

原因

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 index meta_key already exists (SQL: create index "meta_key" on "wp_postmeta" ("meta_key")) がポイントです。

もうすでに meta_key インデックスが存在するので、作れないよ！と言っていますね。

ちなみに、 MySQL がデータベースの場合、何もエラーは起こりませんでした。

対処

SQLite では同じ名前のインデックスが作られれてしまうのがいけないので、名前を指定しないでインデックスを作成してやれば良さそうです。

実際、次のようにして、エラーは解消しました♪

なお、他にも今回の修正に該当する、 "カラム作成のメソッドチェーンに続けてインデックス作成のメソッドをかき、その際インデックス名を指定している" という箇所がありましたけれども、これはそのままとしました。

$ git diff database/migrations/
diff --git a/database/migrations/2019_06_02_062600_create_wp_commentmeta_table.php b/database/migrations/2019_06_02_062600_create_wp_commentmeta_table.php
index ad62d18..0d63930 100644
--- a/database/migrations/2019_06_02_062600_create_wp_commentmeta_table.php
+++ b/database/migrations/2019_06_02_062600_create_wp_commentmeta_table.php
@@ -15,7 +15,7 @@ class CreateWpCommentmetaTable extends Migration
         Schema::create('wp_commentmeta', function (Blueprint $table) {
             $table->bigInteger('meta_id', true)->unsigned();
             $table->bigInteger('comment_id')->unsigned()->default(0)->index('comment_id');
-            $table->string('meta_key')->nullable()->index('meta_key');
+            $table->string('meta_key')->nullable()->index();
             $table->longText('meta_value')->nullable();
         });
     }
diff --git a/database/migrations/2019_06_02_062600_create_wp_postmeta_table.php b/database/migrations/2019_06_02_062600_create_wp_postmeta_table.php
index 37ec412..12c5286 100644
--- a/database/migrations/2019_06_02_062600_create_wp_postmeta_table.php
+++ b/database/migrations/2019_06_02_062600_create_wp_postmeta_table.php
@@ -15,7 +15,7 @@ class CreateWpPostmetaTable extends Migration
         Schema::create('wp_postmeta', function (Blueprint $table) {
             $table->bigInteger('meta_id', true)->unsigned();
             $table->bigInteger('post_id')->unsigned()->default(0)->index('post_id');
-            $table->string('meta_key')->nullable()->index('meta_key');
+            $table->string('meta_key')->nullable()->index();
             $table->longText('meta_value')->nullable();
         });
     }
diff --git a/database/migrations/2019_06_02_062600_create_wp_termmeta_table.php b/database/migrations/2019_06_02_062600_create_wp_termmeta_table.php
index 6f7b7e5..b3695cc 100644
--- a/database/migrations/2019_06_02_062600_create_wp_termmeta_table.php
+++ b/database/migrations/2019_06_02_062600_create_wp_termmeta_table.php
@@ -15,7 +15,7 @@ class CreateWpTermmetaTable extends Migration
         Schema::create('wp_termmeta', function (Blueprint $table) {
             $table->bigInteger('meta_id', true)->unsigned();
             $table->bigInteger('term_id')->unsigned()->default(0)->index('term_id');
-            $table->string('meta_key')->nullable()->index('meta_key');
+            $table->string('meta_key')->nullable()->index();
             $table->longText('meta_value')->nullable();
         });
     }
diff --git a/database/migrations/2019_06_02_062600_create_wp_usermeta_table.php b/database/migrations/2019_06_02_062600_create_wp_usermeta_table.php
index 8ea5046..1a3a502 100644
--- a/database/migrations/2019_06_02_062600_create_wp_usermeta_table.php
+++ b/database/migrations/2019_06_02_062600_create_wp_usermeta_table.php
@@ -15,7 +15,7 @@ class CreateWpUsermetaTable extends Migration
         Schema::create('wp_usermeta', function (Blueprint $table) {
             $table->bigInteger('umeta_id', true)->unsigned();
             $table->bigInteger('user_id')->unsigned()->default(0)->index('user_id');
-            $table->string('meta_key')->nullable()->index('meta_key');
+            $table->string('meta_key')->nullable()->index();
             $table->longText('meta_value')->nullable();
         });
     }
$

捕捉。ドキュメントを見ると

インデックスデータベース：マイグレーション 5.8 Laravel

インデックス名の指定は、 unique メソッドの例はありますけれども、 index メソッドの説明はありません。。。

ソースコードを読んでみましたけれども、全く引数を指定しない場合に、どうなるのか、いまいちつかめません。。。

framework/Blueprint.php at 5.8 · laravel/framework

おわりに

もともと、 LaravelのテストでインメモリSQLiteを使う | Hypertext Candy がやりたかったのです。

なぜ Laravel のテストをインメモリ SQLite でやろうと思い至ったのかというと、お手本としてよく見ているリポジトリ phanan/koel: ? A personal music streaming server that works. で

https://github.com/phanan/koel/blob/master/phpunit.xml#L38

に SQLite とあって、何だろう ? と疑問に思ったのが始まりでした。

その途中での出来事が、今回の投稿となったのでした。

以上です。

対処方法まとめ

エラー内容

原因

対処

捕捉。ドキュメントを見ると

おわりに

関連

「Laravel 5.8 。 migrate が MySQL の場合は問題ないのに SQLite では失敗する現象の原因と修正」への1件の返信

Laravel 5.8。インメモリの SQLite で PHPUnit テストを行う設定 – oki2a24 へ返信するコメントをキャンセル