SQL HY000 error when seeding phpunit tests

Hi, I am trying to seed an sqlite in-memory database for use in phpunit tests. in tests/TestCase.php I have a prepareForTests() method with two artisan calls:

protected function prepareForTests()
{
    Artisan::call('migrate');
    Artisan::call('db:seed');
}

The call to db:seed seems to be causing this error:

PDOException: SQLSTATE[HY000]: General error: 1 near "SET": syntax error

Any ideas what might cause this?

DarkRoast

11 years ago

Level 8

I think this was caused by a difference between sqlite and mysql syntax when disabling foreign keys.

Before each seed is executed I am turning off the foreign key checks with:

DB::statement('SET FOREIGN_KEY_CHECKS = 0');

This works fine when using mysql, however the sqlite documentation uses a different syntax: https://www.sqlite.org/foreignkeys.html#fk_enable so I guess I have a couple of options - somehow check which engine is running then run the specific statement for that engine, write separate seeder files, or just use mysql as the test db.

Are there any other options? What would you choose?

1 like

yichen

11 years ago

Level 1

Oh man I got this error too. Any ideas on how to fix this?

alatui

10 years ago

Level 1

I edited my database/seeds/DatabaseSeeder file:

<?php
use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Model;
class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Model::unguard();
        $this->setFKCheckOff();

        $this->call(LessonsTableSeeder::class);
        $this->call(UsersTableSeeder::class);
        $this->call(TagsTableSeeder::class);
        $this->call(LessonTagTableSeeder::class);

        $this->setFKCheckOn();
        Model::reguard();
    }


    private function setFKCheckOff() {
        switch(DB::getDriverName()) {
            case 'mysql':
                DB::statement('SET FOREIGN_KEY_CHECKS=0');
                break;
            case 'sqlite':
                DB::statement('PRAGMA foreign_keys = OFF');
                break;
        }
    }

    private function setFKCheckOn() {
        switch(DB::getDriverName()) {
            case 'mysql':
                DB::statement('SET FOREIGN_KEY_CHECKS=1');
                break;
            case 'sqlite':
                DB::statement('PRAGMA foreign_keys = ON');
                break;
        }
    }

}

9 likes

fta

3 years ago

Level 1

@alatui This code Working bro. Thanks

J_shelfwood

8 years ago

Level 13

thanks @alatui , saved my setup!

vipmaa

5 years ago

Level 2

You can let Laravel set the statment by using Schema::disableForeignKeyConstraints();

Please or to participate in this conversation.