Framework: [Bug] Schema builder dropColumn doesn't work with sqlite when droping multiple columns

[nexces]

Laravel version: 4.0.9

Migration 1:

<?php

use Illuminate\Database\Migrations\Migration;

class BaseTable extends Migration {

        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
            Schema::create('foo', function ($table) {
                $table->increments('id');
                $table->string('foo');
                $table->string('bar');
                $table->string('baz');
            });
        }

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

}

Migration 2:

<?php

use Illuminate\Database\Migrations\Migration;

class Extending extends Migration {

        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
            Schema::table('foo', function ($table) {
                $table->dropColumn('bar');
                $table->dropColumn('baz');
            });
        }

        /**
         * Reverse the migrations.
         *
         * @return void
         */
        public function down()
        {
            Schema::table('foo', function ($table) {
                $table->string('bar');
                $table->string('baz');
            });
        }

}

Log after attempted migration:

[2013-12-17 12:25:17] log.ERROR: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 no such column: bar' in /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:331
Stack trace:
#0 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php(331): PDO->prepare('CREATE TEMPORAR...')
#1 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php(521): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\SQLiteConnection), 'CREATE TEMPORAR...', Array)
#2 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php(332): Illuminate\Database\Connection->run('CREATE TEMPORAR...', Array, Object(Closure))
#3 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(63): Illuminate\Database\Connection->statement('CREATE TEMPORAR...')
#4 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(150): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\SQLiteConnection), Object(Illuminate\Database\Schema\Grammars\SQLiteGrammar))
#5 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(75): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#6 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(209): Illuminate\Database\Schema\Builder->table('foo', Object(Closure))
#7 /tmp/test/test/app/database/migrations/2013_12_17_121124_extending.php(17): Illuminate\Support\Facades\Facade::__callStatic('table', Array)
#8 /tmp/test/test/app/database/migrations/2013_12_17_121124_extending.php(17): Illuminate\Support\Facades\Schema::table('foo', Object(Closure))
#9 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(137): Extending->up()
#10 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(113): Illuminate\Database\Migrations\Migrator->runUp('2013_12_17_1211...', 1, false)
#11 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(84): Illuminate\Database\Migrations\Migrator->runMigrationList(Array, false)
#12 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php(67): Illuminate\Database\Migrations\Migrator->run('/tmp/test/test/...', false)
#13 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Console/Command.php(108): Illuminate\Database\Console\Migrations\MigrateCommand->fire()
#14 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(244): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#15 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Console/Command.php(96): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Application.php(897): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Application.php(191): Symfony\Component\Console\Application->doRunCommand(Object(Illuminate\Database\Console\Migrations\MigrateCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Application.php(121): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /tmp/test/test/artisan(59): Symfony\Component\Console\Application->run()
#20 {main}

Next exception 'Exception' with message 'SQLSTATE[HY000]: General error: 1 no such column: bar (SQL: CREATE TEMPORARY TABLE __temp__foo AS SELECT id, foo, bar FROM foo) (Bindings: array (
))' in /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:556
Stack trace:
#0 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php(529): Illuminate\Database\Connection->handleQueryException(Object(PDOException), 'CREATE TEMPORAR...', Array)
#1 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php(332): Illuminate\Database\Connection->run('CREATE TEMPORAR...', Array, Object(Closure))
#2 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(63): Illuminate\Database\Connection->statement('CREATE TEMPORAR...')
#3 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(150): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\SQLiteConnection), Object(Illuminate\Database\Schema\Grammars\SQLiteGrammar))
#4 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(75): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#5 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(209): Illuminate\Database\Schema\Builder->table('foo', Object(Closure))
#6 /tmp/test/test/app/database/migrations/2013_12_17_121124_extending.php(17): Illuminate\Support\Facades\Facade::__callStatic('table', Array)
#7 /tmp/test/test/app/database/migrations/2013_12_17_121124_extending.php(17): Illuminate\Support\Facades\Schema::table('foo', Object(Closure))
#8 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(137): Extending->up()
#9 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(113): Illuminate\Database\Migrations\Migrator->runUp('2013_12_17_1211...', 1, false)
#10 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(84): Illuminate\Database\Migrations\Migrator->runMigrationList(Array, false)
#11 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php(67): Illuminate\Database\Migrations\Migrator->run('/tmp/test/test/...', false)
#12 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Console/Command.php(108): Illuminate\Database\Console\Migrations\MigrateCommand->fire()
#13 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(244): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#14 /tmp/test/test/vendor/laravel/framework/src/Illuminate/Console/Command.php(96): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#15 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Application.php(897): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Application.php(191): Symfony\Component\Console\Application->doRunCommand(Object(Illuminate\Database\Console\Migrations\MigrateCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /tmp/test/test/vendor/symfony/console/Symfony/Component/Console/Application.php(121): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /tmp/test/test/artisan(59): Symfony\Component\Console\Application->run()
#19 {main} [] []

[taylorotwell]

I would do them in two separate operations.

[dancourse]

+1 for this being a bug. I've got a migration script which works fine with MySQL in homestead, but for sqlite if I use a rename and a dropColumn or multiple dropColumn calls then I get.

SQLSTATE[HY000]: General error: 1 no such column: XXX (SQL: CREATE TEMPORARY TABLE

This appears to be a bug as I believe there is no mention in the documentation (http://laravel.com/docs/5.0/schema#dropping-columns) about using 2 separate operations if to drop multiple columns or mixing rename and dropColumn in one migration being a problem

Laravel 5.0
Homestead

[martinssipenko]

@taylorotwell Are there any plans to for supporting many dropColumn operation in one DB migration script?

[lucacri]

I spent the last 2 hours trying to debug this error. If we are not going to support multiple dropColumn in the same statement, then we should add it in the documentation. I'm sure a lot of people are hitting this roadblock

[i-bajrai]

Using a SQLite database.

Does not work:
Schema::table('box_products', function ($table) {
$table->dropColumn('weight');
$table->dropColumn('multiplier');
$table->dropColumn('shopify_product_id');
$table->dropColumn('name');
$table->dropColumn('price');
});

Works:
Schema::table('box_products', function ($table) {
$table->dropColumn('weight');
});
Schema::table('box_products', function ($table) {
$table->dropColumn('multiplier');
});
Schema::table('box_products', function ($table) {
$table->dropColumn('shopify_product_id');
});
Schema::table('box_products', function ($table) {
$table->dropColumn('name');
});
Schema::table('box_products', function ($table) {
$table->dropColumn('price');
});

The calls are all in one file.

[ryanrca]

I just spent 2 hours troubleshooting this exact problem. This is horrendous! and painful.

[edwinheij]

Just tested, this is also working with SQLite (by using an Array)

Schema::table('box_products', function ($table) {
    $table->dropColumn(['weight', 'multiplier', 'shopify_product_id', 'name', 'price']);
});

[bruno-rodrigues]

Had this problem when running PHPUnit Test. It always break in the second test run.

Changing the migration from:

Schema::table('vehicles', function (Blueprint $table) {
     $table->dropForeign('vehicles_manufacturer_id_foreign');
     $table->dropForeign('vehicles_model_id_foreign');
     $table->dropColumn('manufacturer_id');
     $table->dropColumn('model_id');
});

to:

Schema::table('vehicles', function (Blueprint $table) {
            $table->dropForeign('vehicles_manufacturer_id_foreign');
            $table->dropForeign('vehicles_model_id_foreign');
            $table->dropColumn(['manufacturer_id', 'model_id']);
        });

Solved it!

[viezel]

its 2017 and I still have this issue. Can't we fix it please?

[mkarnicki]

@viezel I hit the same problem, but check this right here - just above. I guess this is fixed with the docs. You should use ['this', 'syntax'] to drop columns.

[MrRio]

I'm having this issue also when switching to SQLite for my tests

[antoniofiore]

Laravel 5.4, running Php Unit Test

I'm simply dropping a field in migration:

        Schema::table('impressions', function (Blueprint $table) {
            $table->dropColumn('url_request');
        });

->
Class 'Doctrine\DBAL\Driver\PDOSqlite\Driver' not found

[Xethron]

Just had some issues with this myself... If this is not going to be "fixed", at least give a proper error message like: "Multiple dropColumn calls not supported in a single operation. Please use Array syntax or multiple operations". This would help people who get stuck and not sure whats broken.

[MrRio]

@Xethron Agreed

[yathit]

A good example of un-symmetric API design.

When creating column, the syntax is create one-by-one for each column, but dropping columns is in array.

Debugging this issue is difficult for the fact that error message is not helpful.

[rienheuver]

Trying to get my testing environment setup for sqlite for faster migrations and I run into this as well.
I'm doing a more complex than regular change of a column which involves creating a new column, seeding it with the current data, then dropping the old column and renaming the new one to the old name.

$table->dropColumn('delta');
$table->renameColumn('delta_temp', 'delta');

This cannot be summarized with array notation, but it breaks like this issue addresses. Using two Schema:: blocks seems like a hacky solution. Can this please be addressed?

[LipeTuga]

It's not a great solution, but for this problem i set on my phpunit.xml the "app_env" a value "testing", on the migration i have:

    if(env('APP_ENV') != 'testing')
    {
        Schema::table('categories', function (Blueprint $table) {
            $table->dropColumn(['form_data']);
        });
    }

On the future this can give some problems.But you can keep working and wait for a proprer solution for this bug.

[MrRio]

This still bites my team a lot.

[berkaygure]

If you using prefix don't use in sqlite. It's work for me.

[cwilby]

Here's how I solved for multiple sequential rename operations.

collect([
    ['old_a', 'new_a'],
    ['old_b', 'new_b'],
    ['old_c', 'new_c']
])->map(function ($old_new) {
    Schema::table('thetable', function (Blueprint $table) use ($old_new) {
       $table->renameColumn($old_new[0], $old_new[1]);
    });
});