Alter table auto_increment, syntax error

I think the ; at the end of the statement is not expected

DB::statement("ALTER TABLE configurations AUTO_INCREMENT = 14000");

Or check if table configurations exist, and if you don't have any value above 14000 in the column.

Getting same error after removing ; Deleted the database.sqlite file before running the migration, so the database was fresh. Still same error.

public function up()
{
    Schema::create('configurations', function (Blueprint $table) {
        $table->increments('id');
        $table->string('short_description');
        $table->text('long_description')->nullable();
        $table->text('internal_notes')->nullable();
        $table->string('tags');
        $table->boolean('active')->default(true);
        $table->timestamps();
    });
    
    DB::statement("ALTER TABLE configurations AUTO_INCREMENT = 14000");
}

Error:

SQLSTATE[HY000]: General error: 1 near "AUTO_INCREMENT": syntax error (SQL: ALTER TABLE configurations AUTO_INCREMENT = 14000) {"exception":"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: General error: 1 near \"AUTO_INCREMENT\": syntax error (SQL: ALTER TABLE configurations AUTO_INCREMENT = 14000) at /home/ec2-user/environment/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1 near \"AUTO_INCREMENT\": syntax error at /home/ec2-user/environment/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452)

The database is created, but the auto increment is not adjusted to 14000. I can add records to the table, but starting at 1

@KGP43 - If it's sqlite looks like you have to set the increment differently:

https://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite/692871#692871

@REALRANDYALLEN - I tried that and it helped, some. No error but the ID still start at 1.

public function up()
{
    Schema::create('configurations', function (Blueprint $table) {
        $table->increments('id');
        $table->string('short_description');
        $table->text('long_description')->nullable();
        $table->text('internal_notes')->nullable();
        $table->string('tags');
        $table->boolean('active')->default(true);
        $table->timestamps();
    });
    
    # For sqlite
    DB::statement("UPDATE SQLITE_SEQUENCE SET seq = 14000 WHERE name = 'configurations'");
}

Think the syntax looks okay, at least according to that topic. I also found this one, same solution: https://stackoverflow.com/a/27159763/825793

Maybe a case issue :

DB::statement("UPDATE sqlite_sequence SET seq = 14000 WHERE name = 'configurations'");

https://www.sqlite.org/fileformat2.html#seqtab

@VILFAGO - Getting same result. No errors, but ID still start at 1.

Recreating database.sqlite upon each test, so it's an empty table when I create the first record. Not sure if that is causing the problems, but dont think so.

If no data, maybe the table exist, but there is no row.

Try:

DB::insert('insert into sqlite_sequence (name, seq) values (?, ?)', ['configurations', 14000]);

If the sqlite_sequence row for an AUTOINCREMENT table does not exist when the AUTOINCREMENT table is updated, then a new sqlite_sequence row is created. If the sqlite_sequence.seq value for an AUTOINCREMENT table is manually set to something other than an integer and there is a subsequent attempt to insert the or update the AUTOINCREMENT table, then the behavior is undefined.

@VILFAGO - That worked :D Thanks a lot !

public function up()
{
    Schema::create('configurations', function (Blueprint $table) {
        $table->increments('id');
        $table->string('short_description');
        $table->text('long_description')->nullable();
        $table->text('internal_notes')->nullable();
        $table->string('tags');
        $table->boolean('active')->default(true);
        $table->timestamps();
    });
    
    # For mysql
    #DB::statement("ALTER TABLE configurations AUTO_INCREMENT = 14000");
    
    # For sqlite
    DB::insert('insert into sqlite_sequence (name, seq) values (?, ?)', ['configurations', 14000]);
}