Framework: Laravel 5.5/5.6 Changing Columns Not Working

[tprj29]

• Laravel Version: 5.4.x/5.5.x
• PHP Version: 7.1
• Database Driver & Version: Postgres

Description:

I have multiple Laravel Schemas on which i want to migrate some migrations. One of my migrations is as following:

    <?php

    use Illuminate\Support\Facades\Schema;
    use Illuminate\Database\Schema\Blueprint;
    use Illuminate\Database\Migrations\Migration;

    class EditColumnPaymentMethodLengthOnRecurringPaymentsTable extends Migration
    {
        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
            Schema::table('recurring_payments', function (Blueprint $table) {
                $table->string('payment_method', 150)->nullable()->change();
                $table->text('test')->nullable();
            });
        }

        /**
         * Reverse the migrations.
         *
         * @return void
         */
        public function down()
        {
            Schema::table('recurring_payments', function (Blueprint $table) {
                $table->string('payment_method', 18)->nullable()->change();
                $table->dropColumn('test');
            });
        }
    }

What happens is that the new column is added to the database (and dropped if rolledBack) but the column 'payment_method' always has the length of 18 characters. Except for the first Schema this one works fine.

This is the command i migrate with:

    <?php

    namespace App\Console\Commands;

    use App\Account;
    use App\Support\Schema;
    use Illuminate\Console\Command;

    class MigrateTenantsCommand extends Command
    {
        /**
         * The name and signature of the console command.
         *
         * @var string
         */
        protected $signature = 'tenant:migrate {--force : Force the operation to run when in production.}
                    {--pretend : Dump the SQL queries that would be run.}
                    {--seed : Indicates if the seed task should be re-run.}
                    {--step : Force the migrations to be run so they can be rolled back individually.}';

        /**
         * The console command description.
         *
         * @var string
         */
        protected $description = 'Migrate all tenants';

        /**
         * Create a new command instance.
         *
         * @return void
         */
        public function __construct()
        {
            parent::__construct();
        }

        /**
         * Execute the console command.
         *
         * @return mixed
         */
        public function handle()
        {
            $tenants = Account::all();
            foreach($tenants as $tenant)
            {
                $schema = new Schema;

                // Migrate into the new schema
                $schema->migrate(
                    $tenant->schema,
                    $this->getOptionsFromArgs()
                );
            }
        }

        private function getOptionsFromArgs()
        {
            $options = [
                '--path' => 'database/migrations/tenants/schema'
            ];

            if($this->option('force'))
            {
                $options['--force'] = true;
            }
            if($this->option('pretend'))
            {
                $options['--pretend'] = true;
            }
            if($this->option('seed'))
            {
                $options['--seed'] = true;
            }
            if($this->option('step'))
            {
                $options['--step'] = true;
            }
            return $options;
        }
    }

someone pointed out to run the command with --pretend. When i did this i got an exception: [Doctrine\DBAL\Schema\SchemaException] There is no column with name 'payment_method' on table 'recurring_payments'.

It is not a problem with the table being there or not. It is probably a problem with Doctrine not finding the right Schema Config and therefor using the public Schema config. In the public schema the table is not present but i don't want to use the public schema. What i'm doing wrong here, how can I tell Doctrine to use the right Schema config? How is it that Doctrine is finding the right config the first time at least that is wat it looks like.

The Schema/Schema Class of Doctrine:

    public function __construct(
            array $tables = array(),
            array $sequences = array(),
            SchemaConfig $schemaConfig = null,
            array $namespaces = array()
        ) {
            if ($schemaConfig == null) {
                $schemaConfig = new SchemaConfig();
            }
            $this->_schemaConfig = $schemaConfig;

            $this->_setName($schemaConfig->getName() ?: 'public');

            foreach ($namespaces as $namespace) {
                $this->createNamespace($namespace);
            }

            foreach ($tables as $table) {
                $this->_addTable($table);
            }

            foreach ($sequences as $sequence) {
                $this->_addSequence($sequence);
            }
        }

Steps To Reproduce:

1. add change() to one of your migrations
2. switch between multiple schema's

[decadence]

5.4 is not supported.

[tprj29]

I could test this issue on 5.5 (I think it is still in there).

[decadence]

So you should check it on 5.5

[sisve]

Just to be sure (since we get issue reports of varying levels of debugging); do you use a tool like DataGrip that shows the database schema, and does it perhaps have a cache you need to refresh to show the changes the migration did?

[tprj29]

I do use DataGrip (love JetBrains), but no it isn't an issue with the cache of Datagrip. I can literally see the column test appear in the table but the column payment_method is not changed at all.
My migrate up method:

$table->string('payment_method', 150)->nullable()->change();
$table->text('test')->nullable();

BTW this is how i switch between schema's:

    public function switch($schema = 'public')
    {
        config(['database.connections.shared.schema' => $schema]);

        DB::reconnect();        
        if (!is_array($schema)) {
            $schema = [$schema];
        }

        $query = 'SET search_path TO ' . implode(',', $schema);


        $result = DB::statement($query);
    }

[tprj29]

I tested this case on the latest Laravel 5.5.x and the issue is still present. So the issue is still present in Laravel 5.5

[tprj29]

Anyone thoughts on how to solve this problem?

[stryaponoff]

I'm using 5.6.24. This problem still exists in this version.

[vinnyvimto]

I came to this issue with the same problem, using Laravel 5.6, where a column I'd changed to nullable was showing as not null in DataGrip.

However after some playing around I realised even though it shows as not null in DataGrip and I can't change the column to <null> in DataGrip, if I added a new row it would in fact show as nullable.

If you right-click on the column in the sidebar and hit Synchronize it may solve the problem for you.

[tprj29]

I'm not sure if the issue is still there. It was definitely not an issue with cache of Data Grip for me but thanks for your input. I ll do some retesting soon!

[36864]

Out of curiosity, did you ever attempt to verify if the column was changed by any means other than DataGrip?

[tprj29]

I only used DataGrip but I created a new tenant schema with my migrations to ensure there was no caching in place for this specific schema.table.column. Besides that my logic would fail on a PostgreSQL error when inserting in this column considering the newly changes.
So I think it is safe to say there was no caching issue with DataGrip that caused this issue.

[tprj29]

Still an issue.

[driesvints]

Can you try to put the statements in different schema calls to see if that solves your problem? For both the up and down methods I mean.

        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
            Schema::table('recurring_payments', function (Blueprint $table) {
                $table->string('payment_method', 150)->nullable()->change();
            });
            Schema::table('recurring_payments', function (Blueprint $table) {
                $table->text('test')->nullable();
            });
        }

[tprj29]

This doesn't seem to fix the issue.

[staudenmeir]

What does the recurring_payments table look like before you alter it with this migration?

[basbassbasss]

The after() function also does not seem to be functioning as expected on Postgres. I think this is related to this issue, although I'm not entirely sure about that. I have created a demo repo that demonstrates what goes wrong.

As can be seen in the demo repo, I first create a posts table. Next, I added another migration that adds the uuid column. I expect the column to appear after the id column, but it does not:

When switching to a mysql connection, it does work, with the exact same migrations:

This is on Postgres version 11.4, PHP 7.3.7 and Laravel 5.8.30.

[staudenmeir]

@BasMulders Your issue is not related to this one.

PostgreSQL doesn't allow inserting columns at a specific position. You can only insert the new column(s) at the end or recreate the whole table.

[tprj29]

What does the recurring_payments table look like before you alter it with this migration?

@staudenmeir i'm not sure if I understand your question. I'm trying to add one column and update another column. If I do this in one migrations the column i'm trying to updated is failing (I'm trying to make the column nullable). As a workaround I just write raw statements for now.
The problem seems to be on the update of a column because I'm not able to update any column in the traditional way. Even not when i moved the update statement to a separated migrations.

I'm guessing the change method doesn't respect my current search path (current database schema). But I have no idea how to set the current schema other than:
$result = DB::statement('SET search_path TO '. $schema);

[staudenmeir]

What does the migration that creates the table look like?

[Phactory]

i have the same issue on newer pgsql version (older works fine)

[tprj29]

@staudenmeir just a regular up migrations for this table:

    public function up()
    {
        Schema::create('recurring_payments', function (Blueprint $table) {
            $table->uuid('id', 18);
            $table->string('sfdc_id', 18);
            $table->boolean('active')->nullable();
            $table->decimal('amount', 18, 0)->nullable();
            $table->string('payment_method', 18)->nullable();
            $table->timestamps();

            $table->primary('id');
            $table->unique('sfdc_id');
        });
    }

[staudenmeir]

What does the App\Support\Schema class look like?

[tprj29]

<?php

namespace App\Support;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Artisan;

/**
 * Class Schema
 *
 */
class Schema
{
    /**
     * List all the tables for a schema
     *
     * @param string $schemaName
     *
     * @return mixed
     */
    protected function listTables($schema)
    {
        $tables = DB::table('information_schema.tables')
            ->select('table_name')
            ->where('table_schema', '=', $schema)
            ->get();

        return $tables;
    }

    public static function listColumns($schema, $table)
    {
        $columns = DB::table('information_schema.columns')
            ->select('column_name')
            ->where('table_schema', '=', $schema)
            ->where('table_name', '=', $table)
            ->get();
        $columns = $columns->keyBy('column_name')->keys();
        return $columns;
    }

    /**
     * Check to see if a table exists within a schema
     *
     * @param string $schemaName
     * @param string $tableName
     *
     * @return bool
     */
    protected function tableExists($schema, $tableName)
    {
        $tables = $this->listTables($schema);
        foreach ($tables as $table) {
            if ($table->table_name === $tableName) {
                return true;
            }
        }

        return false;
    }

    /**
     * Check to see if a schema exists
     *
     * @param string $schemaName
     *
     * @return bool
     */
    public function schemaExists($schema)
    {
        $schema = DB::table('information_schema.schemata')
            ->select('schema_name')
            ->where('schema_name', '=', $schema)
            ->count();

        return ($schema > 0);
    }

    /**
     * Create a new schema
     *
     * @param string $schemaName
     */
    public function create($schema)
    {
        $query = DB::statement('CREATE SCHEMA ' . $schema);
    }

    /**
     * Set the search_path to the schema name
     *
     * @param string|array $schemaName
     */
    public function switch($schema = 'public')
    {
        config(['database.connections.shared.schema' => $schema]);

        DB::reconnect();
        if (!is_array($schema)) {
            $schema = [$schema];
        }

        $query = 'SET search_path TO ' . implode(',', $schema);


        $result = DB::statement($query);
    }

    /**
     * Drop an existing schema
     *
     * @param string $schemaName
     */
    public function drop($schema)
    {
        $query = DB::statement('DROP SCHEMA '.$schema . ' CASCADE');
    }

    /**
     * Run migrations on a schema
     *
     * @param string $schemaName
     * @param array  $args
     */
    public function migrate($schema, $args = [])
    {
        $this->switch($schema);
        if (!$this->tableExists($schema, 'migrations')) {
            Artisan::call('migrate:install');
        }

        Artisan::call('migrate', $args);
    }

    /**
     * Re-run all the migrations on a schema
     *
     * @param string $schemaName
     * @param array  $args
     */
    public function migrateRefresh($schema, $args = [])
    {
        $this->switch($schema);

        Artisan::call('migrate:refresh', $args);
    }

    /**
     * Reverse all migrations on a schema
     *
     * @param string $schemaName
     * @param array  $args
     */
    public function migrateReset($schema, $args = [])
    {
        $this->switch($schema);

        Artisan::call('migrate:reset', $args);
    }

    /**
     * Rollback the latest migration on a schema
     *
     * @param string $schemaName
     * @param array  $args
     */
    public function migrateRollback($schema, $args = [])
    {
        $this->switch($schema);

        Artisan::call('migrate:rollback', $args);
    }
}

[staudenmeir]

Sorry, but that's too much custom code. Please create a fresh Laravel installation, add the code necessary to reproduce the issue and upload it to GitHub.

[driesvints]

Closing this issue because it's inactive, already solved, old or not relevant anymore. Feel free to reply if you're still experiencing this issue and we'll re-open this issue.

[muhdaimanrs]

Still had this issue in latest laravel version.