php artisan migrate:rollback

Hello,

I'm trying to reinitialize the database and I'm hitting the following error when using the rollback or reset command.

[Doctrine\DBAL\Schema\SchemaException]                   
  There is no column with name 'user_id' on table 'cats'.

The schema is correct and the column is there, I can't figure out what I'm dooing wrong.

I'm using Laravel 5.1.

These are the 2 migrations involved.

<?php

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

class CreateCatsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('cats', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->date('date_of_birth');
            $table->integer('breed_id')->unsigned()->nullable();
            $table->timestamps();
            $table->foreign('breed_id')->references('id')->on('breeds');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('cats');
    }
}

I believe that the latest is causing the problem, but the question is why?!

<?php

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

class AddUserIdColumnToCats extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('cats', function (Blueprint $table)
        {
            $table->integer('user_id')->unsigned()->nullable();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('cats', function (Blueprint $table)
        {
            $table->dropForeign('cats_user_id_foreign');
            $table->dropColumn('user_id');
        });
    }
}

Any ideas what I'm I doing wrong?

bobbybouwmann

10 years ago

Level 88

It sounds to me that the column is already dropped. Are you sure you didn't fire the second migration already?

TGM

10 years ago

Best Answer

Level 1

Yes, the column is there.

sqlite> .schema fc_cats 
CREATE TABLE "fc_cats" ("id" integer not null primary key autoincre
ment, "name" varchar not null, "date_of_birth" date not null, "bree
d_id" integer null, "created_at" datetime not null, "updated_at" da
tetime not null, "user_id" integer null, foreign key("breed_id") re
ferences "fc_breeds"("id"));

Edit:

After some digging I found out that I need dbal to drop sqlite columns witch is very wird since I allready have dbal installed.

"doctrine/dbal": "^2.5"

Note: Before dropping columns from a SQLite database, you will need to add the doctrine/dbal dependency to your composer.json file and run the composer update command in your terminal to install the library.

according to laravel documentation.

And it seems that this is not the only migration that is causing problems.

Edit2:

Ok, so I found the answer to my question, sqlite does not support dropColumn and only a limited alter https://www.sqlite.org/faq.html#q11

1 like

mcbenton

10 years ago

Level 1

@TGM, nice catch! I updated my migrations that use dropColumn() to the following:

    public function down()
    {
        Schema::table('social', function (Blueprint $table) {
            if ('sqlite' !== env('DB_CONNECTION')) {
                $table->dropColumn('url');
                $table->dropColumn('username');
            }
        });
    }

Since I'm only using sqlite for running tests, and since in a typical rollback the whole table is going to get dropped eventually anyway, this should have any major impacts on the app as a whole, and now I don't get errors every time I try to run phpunit! Thanks!!!

Please or to participate in this conversation.