renameColumn migration fails for existing column with columns does not exist error

Question

I get the error Doctrine\DBAL\Schema\SchemaException : There is no column with name 'price' on table 'class_rooms'. while running migration to rename the price column in class_rooms table of my app. The table is created via a migration that runs before this renaming migration through a migration file named 2019_03_13_054619_create_class_rooms_table with following code

class CreateClassRoomsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('class_rooms', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->uuid('yoga_style_id');
            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            $table->time('duration_from')->nullable();
            $table->time('duration_to')->nullable();
            $table->string('name');
            $table->bigInteger('price');
            $table->longText('description')->nullable();
            $table->string('image')->nullable();
            $table->string('meta_title')->nullable();
            $table->text('meta_description')->nullable();
            $table->text('meta_keywords')->nullable();
            $table->boolean('active')->default(true);
            $table->softDeletes();
            $table->timestamps();
            $table->foreign('yoga_style_id')->references('id')->on('yoga_styles')
                ->onDelete('cascade');
            }
        );
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('class_rooms');
    }
}

as you can see the price column is defined here. I have doctrine/dbal installed and Laravel version I'm using is 5.7. The rename migration is as follows

class RenamePriceClassRoomsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('class_rooms', function (Blueprint $table) {
            $table->renameColumn('price', 'cost_subscribed');
        });
    }

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

The error I get is

Migrating: 2019_04_10_132923_rename_price_class_rooms_table

   Doctrine\DBAL\Schema\SchemaException  : There is no column with name 'price' on table 'class_rooms'.

  at /Users/anadi/Code/new_web_app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/SchemaException.php:82
    78|      * @return \Doctrine\DBAL\Schema\SchemaException
    79|      */
    80|     public static function columnDoesNotExist($columnName, $table)
    81|     {
  > 82|         return new self(
    83|             sprintf("There is no column with name '%s' on table '%s'.", $columnName, $table),
    84|             self::COLUMN_DOESNT_EXIST
    85|         );
    86|     }

Answer 1

There are some known issues with innoDB and biginteger column types. Looking at bug reports, it's not clear if they have fixed this. For example https://bugs.mysql.com/bug.php?id=15383

In your case, the column name is price, it stands to reason that changing the column type to integer won't corrupt the existing values. In case, the column isn't part of a foreign key neither. This is a possible solution.

public function up()
    {
        Schema::table('class_rooms', function (Blueprint $table) {
            $table->integer('price')->change();
            $table->renameColumn('price', 'cost_subscribed');
            //  $table->bigInteger('price')->change(); //->optional
        });
    }

Answer 2

It seems the column is already renamed, you can check your table, but better way to do it is check the column exists or not before renaming it, if the column does not exists, you can just create a column with your desire name and type

class RenamePriceClassRoomsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if (Schema::hasColumn('class_rooms', 'price')) {
            Schema::table('class_rooms', function (Blueprint $table) {
                $table->renameColumn('price', 'cost_subscribed');
            });
        } else {
           Schema::table('class_rooms', function($table) {
               $table->integer('price')->after('<some_column>');
           });
        }
    }

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

or even choose to just add the column you want to add and delete the existing column if exists, but only if there is no data or you can use DB::raw() to copy the data from one column to another once the column is created and after it is done remove the column.

Schema::table('class_rooms', function($table) {
       $table->integer('cost_subscribed')->after('<some_column>');
   });
   if (Schema::hasColumn('class_rooms', 'price')) {
        Schema::table('class_rooms', function (Blueprint $table) {
            $table->dropColumn('price');
        });
    }

There is a closed issue in mysql, that may be the cause of this issue, the issue describes as follows.

Updating a row on an updatable ResultSet with a BigInteger object on an UNSIGNED INT fails. A MysqlDataTruncation is throwned when updateRow() is executed.

and suggested an workaround: insert the value as a String, not only string using integer will also work. But as per current update with new commit have already fixed it, so it should not occur in new versions of mysql.

Answer 3

If you have tried migrating this before, but failed in the middle or if you have rolled this back before but not the create table migration, it is possible that you have already renamed the table since the function will not rename itself back to 'price'. However, it is not for sure as I don't have the information of what migrations you ran before this.

Answer 4

First check in your class_rooms has price column. if you have that column then as per documentation you have to install doctrine/dbal package

composer require doctrine/dbal

Then try

class RenamePriceClassRoomsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('class_rooms', function (Blueprint $table) {
            $table->renameColumn('price', 'cost_subscribed');
        });
    }

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