Change String to Decimal Laravel Migration

Question

I'm trying to Alter my current table and can do it just fine with a SQL Query in my migration file like this:

<?php
    public function up()
    {
        Schema::table('xyz', function (Blueprint $table) {
        DB::statement('ALTER TABLE mydatabase.xyz CHANGE funding_needed funding_needed decimal(10, 2) NOT NULL DEFAULT 0.00 ');
        DB::statement('ALTER TABLE mydatabase.xyz CHANGE funding_received funding_received decimal(10, 2) NOT NULL DEFAULT 0.00 ');
        });
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('xyz', function (Blueprint $table) {
        DB::statement('ALTER TABLE mydatabase.xyz CHANGE funding_needed funding_needed varchar(191) NOT NULL ');
        DB::statement('ALTER TABLE mydatabase.xyz CHANGE funding_received funding_received varchar(191) NOT NULL DEFAULT 0');
        });
    }

But if I do it the this way I get an error:

<?php
    public function up()
    {
        Schema::table('xyz', function (Blueprint $table) {
             $table->decimal('funding_needed', 10, 2)->nullable(false)->default(0.00)->change();
             $table->decimal('funding_received', 10, 2)->nullable(false)->default(0.00)->change();
        });
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('xyz', function (Blueprint $table) {          
         $table->string('funding_needed', 191)->nullable(false)->default(false)->change();
         $table->string('funding_received', 191)->nullable(false)->default(0)->change();
        });
    }

ERROR:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8mb4 DEFAULT '0' NOT NULL COLLATE utf8mb4_unicode_ci, CHANGE ' at line 1 (SQL: ALTER TABLE xyz CHANGE funding_needed funding_needed NUMERIC(10, 2) CHARACTER SET utf8mb4 DEFAULT '0' NOT NULL COLLATE utf8mb4_unicode_ci, CHANGE funding_received funding_received NUMERIC(10, 2) CHARACTER SET utf8mb4 DEFAULT '0' NOT NULL COLLATE utf8mb4_unicode_ci)

I want to do it the 2nd way due to running this in production. I don't want to modify the production migration to match my production database.

Answer 1

As pointed on this Github thread for doctrine/dbal package, which is the package you install before you run this update migrations, there's a bug on changing column types.

Using ->charset(null) along with ->change() should serve as a workaround in the meantime, as pointed on the link.

Answer 2

guys, I had trouble to converting string to decimal and as @echo said in the comments

->charset(null)->change(); did the trick!

Thanks, guys for saving my time!