カテゴリー
Linux

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();
         });
     }
$

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

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

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

おわりに

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

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

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

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

以上です。

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

コメントを残す