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

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} [] []

About this issue

Original URL
State: closed
Created 11 years ago
Reactions: 14
Comments: 21 (7 by maintainers)

Commits related to this issue

database migration: 2019_02_23_222617_add_hidpi.php: fixup SQLite doesn't support multiple calls to dropColumn / renameColumn in a single modification. see https://github.com/laravel/framework/issues/... — committed to ttys3/Lychee by deleted user 5 years ago
database migration: 2019_02_23_222617_add_hidpi.php: fixup SQLite doesn't support multiple calls to dropColumn / renameColumn in a single modification. see https://github.com/laravel/framework/issues/... — committed to LycheeOrg/Lychee by deleted user 5 years ago
Fixed SQLite issue with multiple drop statements - See: https://github.com/laravel/framework/issues/2979 — committed to artkonekt/user by fulopattila122 5 years ago
Fix migrations to support SQLite for testing [WEB-347] https://github.com/laravel/framework/issues/2979 — committed to art-institute-of-chicago/data-aggregator by IllyaMoskvin 5 years ago
Fix drop column issue Ref: https://github.com/laravel/framework/issues/2979 — committed to nctu-software-testing/db-project by s911415 4 years ago

Most upvoted comments

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']);
});

+146

edwinheij on Jun 21, 2016

I would do them in two separate operations.

+66

taylorotwell on Dec 19, 2013

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.

+64

i-bajrai on Apr 23, 2016

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

+54

lucacri on Oct 2, 2015

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

+22

ryanrca on May 9, 2016

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

+19

viezel on Apr 20, 2017

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.

Xethron on Aug 11, 2017

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!

bruno-rodrigues on Jun 30, 2016

+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

dancourse on Aug 12, 2015

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.

yathit on Jan 16, 2018

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]);
    });
});

cwilby on Mar 12, 2020

This still bites my team a lot.

MrRio on Jul 9, 2018

Hot tip that I figured out from @rienheuver’s response - this error applies to both dropping columns and renaming columns. You’ll need to drop columns using array notation as above, and rename columns in separate Schema::table calls:

AbrahamBrookes on Jan 16, 2023

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?

rienheuver on May 23, 2018