Axeia's avatar
Level 1

View not getting removed in down function

Hello,

When running php artisan migrate:fresh --seed I get the error that the view already exists.

migrating: 2018_10_07_175757_add_triggers_and_views

   Illuminate\Database\QueryException  : SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'eggfriends_count' already exists (SQL: CREATE VIEW eggfriends_count AS
           SELECT trainers.name, count(*) as eggfriends_count                                                                                                                                                                                                                 
             FROM eggfriends                                                                                                                                                                                                                                                  
             JOIN trainers ON accepter = name OR requester = name                                                                                                                                                                                                             
            WHERE accepted = true                                                                                                                                                                                                                                             
         GROUP BY trainers.name                                                                                                                                                                                                                                               
        )                                                                                                                                                                                                                                                                     

  at /home/Server/web/eggfriends/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   PDOException::("SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'eggfriends_count' already exists")
      /home/Server/web/eggfriends/vendor/laravel/framework/src/Illuminate/Database/Connection.php:507

  2   PDO::exec("CREATE VIEW eggfriends_count AS
           SELECT trainers.name, count(*) as eggfriends_count
             FROM eggfriends
             JOIN trainers ON accepter = name OR requester = name
            WHERE accepted = true
         GROUP BY trainers.name
        ")
      /home/Server/web/eggfriends/vendor/laravel/framework/src/Illuminate/Database/Connection.php:507

  Please use the argument -v to see more details.

I don't understand why however as the down function of this class is as follows:

public function down()
    {
        DB::unprepared('DROP TRIGGER insert_trainer');
        DB::unprepared('DROP TRIGGER delete_trainer');
        DB::unprepared('DROP VIEW eggfriends_count');
    }

After posting this whilst looking at the file names in my Seeder folder I realised this might be due to the Class and/or Filename? Perphaps Laravel assumes that if there's no "Create" in their names that there won't be a reason to run the down method?

[edit] Change the class to CreateViewsAndAddTriggersTable and the file to 2018_10_07_175757_create_views_and_add_triggers_table.php to no avail. So much for that theory.

Changing unprepared to statement also changed nothing.

0 likes
1 reply
Snapey's avatar
Snapey
Best Answer
Level 122

I don't think fresh runs any migration down methods.

The migrate:fresh command will drop all tables from the database and then execute the migrate command:

It probably just does that, drops the tables and does not drop any views.

You could create a migration file with an earlier filename that the others and in the up method, drop the view and triggers.

Please or to participate in this conversation.