Sqlite General error near "auto_increment"

Are you using the ->increments(...) or ->bigIncrements(...) methods on your migrations?

If you can, please paste your migration over here.

If you are using the helper methods from the Blueprint class the specific syntax for each supported DB might be handled correctly. I use both MySQL and SQLite in several projects and never faced this issue.

@marufalom you should have the migration as any other:

$table->bigIncrements('id');

Laravel will handle that for you. I believe you are failing to do so, because you are manually trying to add an id when you create the resource. Can you please share your usage of the factory, and the factory itself?

I use SQLite in memory database for testing, and haven't had any issue with the id yet.

This is how my migration looks like:

Schema::create('teachers', function (Blueprint $table) {
                $table->increments('id');
                $table->string('name', 100);

Factory:

$factory->define(Teacher::class, function (Faker $faker) {
    return [
        'name' => $faker->name
    ];
});

Yea, i've also used mysql and sqlite before didn't have this issue.

Note: I am using Laravel 5.8

Weird... Does the error occur only when running the factories? Or while migrating?

Which trait do you use in your tests: RefreshDatabase , DatabaseMigrations or DatabaseTransactions?

Do you have any .env.testing file? If so please paste here, also with you can you paste your phpunit.xml file?

If you can please also paste the test case which is failing.

This might help if you are using the RefreshDatabase trait:

https://github.com/laravel-doctrine/orm/issues/275#issuecomment-419294409

I am using RefreshDatabase trait, DatabaseMigrations also gives me same error.

env.testing

APP_ENV=testing
APP_DEBUG=true
APP_KEY=someimportantrandomstring
DB_DEFAULT=sqlite_testing
APP_URL=http://localhost

DB_CONNECTION=sqlite
DB_DATABASE=database/testing.sqlite

config/database.php

  'sqlite_testing' => [
            'driver'        => 'sqlite',
            'database'  => storage_path() . '/testing.sqlite',
            'prefix'        => '',
        ],

testcase:

public function user_can_visit_teachers_page()
    {
        $teacher = factory('App\Models\Teacher')->create();

        $response = $this->get(route('teachers.index', $teacher->id));

        $response->assertStatus(200);
    }

phpunit.xml

    <php>
        <env name="APP_ENV" value="testing"/>
        <env name="CACHE_DRIVER" value="array"/>
        <env name="DB_CONNECTION" value="sqlite_testing"/>
        <env name="SESSION_DRIVER" value="array"/>
        <env name="QUEUE_DRIVER" value="sync"/>
    </php>

@marufalom have you tried running it just in memory, so remove this from your .env.testing file:

DB_DATABASE=database/test.sqlite

it will use the one from the phpunit.xml file.

Don't know why it is happening, below are some configurations you can try to see if it works.

1.

in your .env.testing file you specify the sqlite connection while in your database.php file you have a sqlite_testing connection. I'll assume you kept the regular sqlite connection so it might be using that.

If not please rename you connection either in the .env.testing or the database.php to the same name

2.

Try adding this line in your phpunit.xml file:

<server name="DATABASE_CONNECTION" value="sqlite"/>

close to other variables. It should not be necessary, but maybe due to some other configuration Laravel is not merging the .env.testing variables accordingly

3.

Remove any .php files from the bootstrap/cache directory

4.

Remove the vendor folder and run composer install again

I missed the phpunit.xml in your last post, sorry.

So try @nakov suggestion and remove the DB related variables from .env.testing to avoid conflicting with phpunit.xml

Thank you @nakov and @rodrigo.pedra , I tried all of those steps. My updated configs are:

phpunit.xml

    <php>
        <env name="APP_ENV" value="testing"/>
        <env name="DB_DATABASE" value=":memory:"/>
        <env name="CACHE_DRIVER" value="array"/>
        <env name="DB_CONNECTION" value="sqlite"/>
        <env name="SESSION_DRIVER" value="array"/>
        <env name="QUEUE_DRIVER" value="sync"/>
    </php>

env.testing

APP_ENV=testing
APP_KEY=someimportantrandomstrings

APP_DEBUG=true
APP_URL=http://localhost

DB_CONNECTION=sqlite

config/database.php

    'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],

Deleted database/database.sqlite file and runs the tests again. Still getting same error but this time there is no database.sqlite added on directory.

Sorry I am out of ideas of why this error is happening...

One last shot: can you paste all the error stack trace generated on your log file?

also tried removing <env name="DB_DATABASE" value=":memory:"/> from phpunit.xml, won't help

Eloquent generated Query is:

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 near "auto_increment": syntax error (SQL: create table `teachers` (`id` int unsigned not null auto_increment primary key, `name` varchar(100) not null))

Full error log:

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 near "auto_increment": syntax error (SQL: create table `teachers` (`id` int unsigned not null auto_increment primary key, `name` varchar(100) not null))
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:664
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:624
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:459
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Blueprint.php:97
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php:264
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php:165
 \vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php:237
 \database\migrations19_10_10_182138_create_processing_table.php:30
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:360
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:367
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:178
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:147
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:96
 \vendor\laravel\framework\src\Illuminate\Database\Console\Migrations\MigrateCommand.php:71
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:29
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:87
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:31
 \vendor\laravel\framework\src\Illuminate\Container\Container.php:572
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:183
 \vendor\symfony\console\Command\Command.php:255
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:170
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:198
 \vendor\laravel\framework\src\Illuminate\Database\Console\Migrations\FreshCommand.php:55
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:29
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:87
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:31
 \vendor\laravel\framework\src\Illuminate\Container\Container.php:572
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:183
 \vendor\symfony\console\Command\Command.php:255
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:170
 \vendor\symfony\console\Application.php:934
 \vendor\symfony\console\Application.php:273
 \vendor\symfony\console\Application.php:149
 \vendor\laravel\framework\src\Illuminate\Console\Application.php:89
 \vendor\laravel\framework\src\Illuminate\Console\Application.php:188
 \vendor\laravel\framework\src\Illuminate\Foundation\Console\Kernel.php:250
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\PendingCommand.php:136
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\PendingCommand.php:220
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\Concerns\InteractsWithConsole.php:55
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\RefreshDatabase.php:55
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\RefreshDatabase.php:18
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\TestCase.php:104
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\TestCase.php:71
 
 Caused by
 Doctrine\DBAL\Driver\PDOException: SQLSTATE[HY000]: General error: 1 near "auto_increment": syntax error
 
 \vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php:63
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:452
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:657
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:624
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:459
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Blueprint.php:97
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php:264
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php:165
 \vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php:237
 \database\migrations19_10_10_182138_create_processing_table.php:30
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:360
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:367
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:178
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:147
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:96
 \vendor\laravel\framework\src\Illuminate\Database\Console\Migrations\MigrateCommand.php:71
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:29
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:87
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:31
 \vendor\laravel\framework\src\Illuminate\Container\Container.php:572
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:183
 \vendor\symfony\console\Command\Command.php:255
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:170
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:198
 \vendor\laravel\framework\src\Illuminate\Database\Console\Migrations\FreshCommand.php:55
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:29
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:87
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:31
 \vendor\laravel\framework\src\Illuminate\Container\Container.php:572
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:183
 \vendor\symfony\console\Command\Command.php:255
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:170
 \vendor\symfony\console\Application.php:934
 \vendor\symfony\console\Application.php:273
 \vendor\symfony\console\Application.php:149
 \vendor\laravel\framework\src\Illuminate\Console\Application.php:89
 \vendor\laravel\framework\src\Illuminate\Console\Application.php:188
 \vendor\laravel\framework\src\Illuminate\Foundation\Console\Kernel.php:250
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\PendingCommand.php:136
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\PendingCommand.php:220
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\Concerns\InteractsWithConsole.php:55
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\RefreshDatabase.php:55
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\RefreshDatabase.php:18
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\TestCase.php:104
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\TestCase.php:71
 
 Caused by
 PDOException: SQLSTATE[HY000]: General error: 1 near "auto_increment": syntax error
 
 \vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php:61
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:452
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:657
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:624
 \vendor\laravel\framework\src\Illuminate\Database\Connection.php:459
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Blueprint.php:97
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php:264
 \vendor\laravel\framework\src\Illuminate\Database\Schema\Builder.php:165
 \vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php:237
 \database\migrations19_10_10_182138_create_processing_table.php:30
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:360
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:367
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:178
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:147
 \vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:96
 \vendor\laravel\framework\src\Illuminate\Database\Console\Migrations\MigrateCommand.php:71
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:29
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:87
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:31
 \vendor\laravel\framework\src\Illuminate\Container\Container.php:572
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:183
 \vendor\symfony\console\Command\Command.php:255
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:170
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:198
 \vendor\laravel\framework\src\Illuminate\Database\Console\Migrations\FreshCommand.php:55
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:29
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:87
 \vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:31
 \vendor\laravel\framework\src\Illuminate\Container\Container.php:572
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:183
 \vendor\symfony\console\Command\Command.php:255
 \vendor\laravel\framework\src\Illuminate\Console\Command.php:170
 \vendor\symfony\console\Application.php:934
 \vendor\symfony\console\Application.php:273
 \vendor\symfony\console\Application.php:149
 \vendor\laravel\framework\src\Illuminate\Console\Application.php:89
 \vendor\laravel\framework\src\Illuminate\Console\Application.php:188
 \vendor\laravel\framework\src\Illuminate\Foundation\Console\Kernel.php:250
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\PendingCommand.php:136
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\PendingCommand.php:220
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\Concerns\InteractsWithConsole.php:55
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\RefreshDatabase.php:55
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\RefreshDatabase.php:18
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\TestCase.php:104
 \vendor\laravel\framework\src\Illuminate\Foundation\Testing\TestCase.php:71

Just a thought. Did you manually install sqlite for php? If you did, which version did you install? Was it sqlite or sqlite3?

@sinnbeck In my php.ini I have pdo_sqlite enabled.

There is something suspicious...

1.

Remove the .env.testing file (move any needed env variable to the phpunit.xml file, APP_* are not needed to be moved there)

try to run again

(it seems when .env.testing is present Laravel does not merge with the regular .env file but uses only the .env.testing file which could be causing a conflict with some missing env variable, reference: https://github.com/laravel/framework/issues/18772)

2.

If step 1 does not work

Add this to the beginning of your test case:

dd(resolve(Illuminate\Database\Connection::class)->getSchemaGrammar())

and paste the results

Thank you @rodrigo.pedra . After removing .env.testing it works :D

Great! Glad we found it, was almost giving up.

As Laravel ignores the .env file when .env.testing is present you might have something missing on the .env.testing that was conflicting when running the tests.

But the good part is that it worked!

I had the same error and traced it to a place in my code where I run

        'ALTER TABLE data_table AUTO_INCREMENT = 1;'

I found that SQLite cannot do that, so I simply

        if (config('app.env') !== 'testing') {
            $this->runPdoQuery('ALTER TABLE data_table AUTO_INCREMENT = 1;');
        }