I am new to Database Migrations and I am trying to figure out exactly how they work. I am working in Yii2. So if I go into my local database and create a new column called test and then run console/yii migration/update job

First Migration File:

<?php

use yii\db\Migration;

<?php

use yii\db\Migration;

class m211012_201641_01_create_table_job extends Migration
{
    public function up()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB';
        }

        $this->createTable(
            '{{%job}}',
            [
                'job_id' => $this->primaryKey(),
                'client_id' => $this->integer()->notNull(),
                'title' => $this->string(),
                'instruction' => $this->string(),
                'recurring_job' => $this->string(),
                'start_date' => $this->date(),
                'end_date' => $this->date(),
                'start_time' => $this->time(),
                'end_time' => $this->time(),
                'repeat' => $this->string(),
                'duration' => $this->string()->notNull(),
                'first_visit' => $this->date(),
                'last_visit' => $this->date(),
                'total_vist' => $this->string()->notNull(),
                'test' => $this->integer(),
            ],
            $tableOptions
        );

        $this->addForeignKey(
            'job_fk0',
            '{{%job}}',
            ['client_id'],
            '{{%client}}',
            ['client_id'],
            'RESTRICT',
            'RESTRICT'
        );
    }

    public function down()
    {
        $this->dropTable('{{%job}}');
    }
}

Updated Migration File

<?php

use yii\db\Migration;

class m211012_201641_01_create_table_job extends Migration
{
    public function up()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB';
        }

        $this->createTable(
            '{{%job}}',
            [
                'job_id' => $this->primaryKey(),
                'client_id' => $this->integer()->notNull(),
                'title' => $this->string(),
                'instruction' => $this->string(),
                'recurring_job' => $this->string(),
                'start_date' => $this->date(),
                'end_date' => $this->date(),
                'start_time' => $this->time(),
                'end_time' => $this->time(),
                'repeat' => $this->string(),
                'duration' => $this->string()->notNull(),
                'first_visit' => $this->date(),
                'last_visit' => $this->date(),
                'total_vist' => $this->string()->notNull(),
                'test' => $this->integer(),
            ],
            $tableOptions
        );

        $this->addForeignKey(
            'job_fk0',
            '{{%job}}',
            ['client_id'],
            '{{%client}}',
            ['client_id'],
            'RESTRICT',
            'RESTRICT'
        );
    }

    public function down()
    {
        $this->dropTable('{{%job}}');
    }
}

The issue that I am running into is that If i have my coworker run this Migration.

It will throw an error stating:

# console/yii migrate
Yii Migration Tool (based on Yii v2.0.43)

Total 1 new migration to be applied:
    m211012_201641_01_create_table_job

Apply the above migration? (yes|no) [no]:yes
*** applying m211012_201641_01_create_table_job
    > create table {{%job}} ...Exception 'yii\db\Exception' with message 'SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'job' already exists
The SQL being executed was: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `client_id` int(11) NOT NULL,
    `title` varchar(255),
    `instruction` varchar(255),
    `recurring_job` varchar(255),
    `start_date` date,
    `end_date` date,
    `start_time` time,
    `end_time` time,
    `repeat` varchar(255),
    `duration` varchar(255) NOT NULL,
    `first_visit` date,
    `last_visit` date,
    `total_vist` varchar(255) NOT NULL,
    `test` int(11)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB'

in /var/www/html/online/vendor/yiisoft/yii2/db/Schema.php:678

Error Info:
Array
(
    [0] => 42S01
    [1] => 1050
    [2] => Table 'job' already exists
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'job' already exists'

What exactly am I doing wrong? Am I not understanding Database Migrations correctly?

CodePudding user response：

For adding a column to the table you should create new migration.

this is the example provided in yii2 guide

class m150811_220037_add_position_column_to_post_table extends Migration
 {
    public function up()
    {
        $this->addColumn('post', 'position', $this->integer());
    }

    public function down()
   {
       $this->dropColumn('post', 'position');
   }
}

CodePudding user response：

It looks that you are using my package in here - you must state it in your question because it is not obvious to everybody and because of that - confusing.

Looks like you have that table already created in the database but there is no migration of it applied and registered so when you are trying to update it with new column it generates "create" migration instead of "update" one.

You need to apply "create" migration first either by generating it with fh option (fixHistory) or by adding it some other way to migration_history table.