Can't change Laravel table column type

1
votes

I cannot execute migrate:fresh.

Command fails at changing table column from:

$table->string('language_id')->default('')->length(255);

to:

$table->integer('language_id')->unsigned()->default(1)->change();


Error that I'm getting:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHARACTER SET utf8mb4 DEFAULT 1 NOT N ULL COLLATE utf8mb4_unicode_ci' at line 1 (SQL: ALTER TABLE users CHANGE language_id language_id INT UNSIGNED CHA RACTER SET utf8mb4 DEFAULT 1 NOT NULL COLLATE utf8mb4_unicode_ci)

Any help/hints would be appreciated.

3
mysqllaravelxamppmariadb
Try to first set it to integer then set default value or vice versa - ege
@ege same error appeared after changing to $table->integer('language_id')->default(1)->unsigned()->change(); - crescast
I meant you are changing from string to integer. Maybe you need to remove some attributes of that field before changing it. Like CHARACTER SET utf8mb4 as it says in your error - ege
Otherwise create new column. Migrate all your reformatted data there, then drop old column, and rename the current - ege
okay. How about this: try to change it using phpmyadmin or similar. copy the sql-queries it uses and use DB::raw($query) to add it to a migration file. It's laborious, but it might reveal what the issue is. - ege

3 Answers

3
votes

If the standard solution is not working. You can try this:

public function up()
{
    DB::statement('ALTER TABLE mytable MODIFY mycolumn  INTEGER;');
}

public function down()
{
    DB::statement('ALTER TABLE mytable MODIFY mycolumn STRING;');
}
0
votes

Depending on the Laravel Documentation:

Note: Before changing a column, be sure to add the doctrine/dbal dependency to your composer.json file.

Try this

Schema::table('your_table_name', function (Blueprint $table) {
        $table->unsignedInteger('language_id')->default(1);
});
0
votes

Need doctrine/dbal

composer require doctrine/dbal

write a new migration & write this

 public function up()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->integer('language_id')->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('your_table_name', function (Blueprint $table) {
            $table->dropColumn('language_id');
        });
    }