MySQL view will not drop in Laravel 5.5 migration

Question

I am trying to write a PHPUnit test case in Laravel 5.5. I'd like to run migrations as part of that process. My users migration script actually creates a view, not a table. And it seems that all other normal tables get created and dropped just as expected. But the users view does not. Any ideas why? I can manually run the DROP statement just fine in PHPMyAdmin. Here is my migration script:

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{

public function up()
{
    DB::statement("
    CREATE VIEW users AS
    (
      SELECT
        PD.ID AS id,
        PCI.Username AS username,
        PCI.Username AS first_name
      FROM table1.Person PD
      LEFT JOIN table2.Contact PCI ON PCI.ID = PD.ID
    )
");

}

  public function down()
  {
    DB::statement("DROP VIEW users");
  }
}

user_5385008 · Answer

Yes, this is an open issue in the Laravel github. The proposed workaround which worked for me is to replace the CREATE VIEW in my migration file with CREATE OR REPLACE. So the full up() function will be:

public function up()
{
DB::statement("
CREATE OR REPLACE VIEW users AS
(
  SELECT
    PD.ID AS id,
    PCI.Username AS username,
    PCI.Username AS first_name
  FROM table1.Person PD
  LEFT JOIN table2.Contact PCI ON PCI.ID = PD.ID
)
");

}

MySQL view will not drop in Laravel 5.5 migration

1 Answers