A bug with renaming existing column in Laravel migrations

Question

I added a new migration to rename an old column. Everything seems correct in this code, for me:

public function up()
{
   Schema::table('reports', function (Blueprint $table) {
        $table->renameColumn('reporter_id', 'created_by');
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('reports', function (Blueprint $table) {
        $table->renameColumn('created_by', 'reporter_id');
    });
}

But then I faced an error:

In Connection.php line 664: SQLSTATE[0A000]: Feature not supported: 1846 ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. (SQL: ALTER TABLE reports CHANGE reporter_id created_b    y INT NOT NULL)                                                                                                                                                                                                          
In PDOStatement.php line 140: SQLSTATE[0A000]: Feature not supported: 1846 ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
In PDOStatement.php line 138: SQLSTATE[0A000]: Feature not supported: 1846 ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. `

Could you help me to fix this?

That's not a bug or even related to Laravel. That's the database server telling you that you can't rename a column when other tables have that column as a foreign key. You need to remove the existing foreign keys from the other tables (for that column), rename the column and then create the foreign keys in the other tables again to target the renamed column. — M. Eriksson
– M. Eriksson, Commented Apr 5, 2018 at 8:00
You are facing this because you are updating key used by other tables...Check this first. — Muhammad Rizwan
– Muhammad Rizwan, Commented Apr 5, 2018 at 8:01

Sohel0415 · Accepted Answer · 2018-04-05 08:24:35Z

1

First drop koreign key on up method.

public function up()
{
  Schema::table('reports', function (Blueprint $table) {
     $table->dropForeign('reports_reporter_id_foreign');
     $table->renameColumn('reporter_id', 'created_by');
  });
}

Then add foreign key again on down method.

public function down()
{
    Schema::table('reports', function (Blueprint $table) {
        $table->renameColumn('created_by', 'reporter_id');
        $table->foreign('reporter_id')->references('id')->on('your_related_table')->onDelete('cascade');
    });
}

answered Apr 5, 2018 at 8:24

Sohel0415

9,89124 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stuart Steedman · Accepted Answer · 2018-04-06 16:10:48Z

1

I've encountered this too - it makes no sense, because when I use my standard SQL client to rename the same field ... it works. But it just doesn't work as a migration script. Thus, I ended up running the RENAME inside a DB::statement and that worked for me:

     /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {

        DB::statement("ALTER TABLE `mydb`.`mytable`   
          CHANGE `currentfieldname` `newfieldname` 
          INT(10) UNSIGNED NOT NULL;");

    }

edited Apr 6, 2018 at 16:10

answered Apr 6, 2018 at 15:55

Stuart Steedman

1461 silver badge6 bronze badges

Collectives™ on Stack Overflow

A bug with renaming existing column in Laravel migrations

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related