Unclear of the cause of migration/update causing: [23000]: Integrity constraint violation

[optmsp]

I am trying to use migration/update to create new migrations to alter existing tables and hitting a stumbling block. This is what I'm getting:

$ php yii migration/update rtm_category --onlyShow
 > 1 table excluded by the config.
 > Comparing current table 'rtm_category' with its migrations ...[trace]            Begin transaction
[trace]            Begin transaction
ERROR!
 > SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Every Day' for key 'rtm_schedule_option.name'
The SQL being executed was: INSERT INTO `rtm_schedule_option` (`name`, `sort_order`, `schedule_type_id`, `recurring`, `created_at`, `updated_at`, `created_by`, `updated_by`, `uuid`) VALUES ('Every Day', 41, 3, 'Every Day', NOW(), NOW(), 1, 1, '3efd813229f711ec9f221204fb92bd6d')

I'm unclear what is causing this. The database is running fine.

I am also unclear why the table rtm_schedule_optionis generating this error when I'm specifying rtm_category. I have trace logging enabled but am unable to see any debug output that would indicate what is happening here.

What would my next steps be to debug this?

For clarity: this is being run on a dev system with an existing migration/up and a fully functioning version of the application. I am assuming I do not need to run this on a pre-migration system, but if that is wrong then by all means let me know.

[bizley]

I'm guessing that one of your existing migrations contains the instruction to insert data into the database, and you are not using the insert or execute action to do that - is it the case?

If so, the only thing I can suggest is to put that migration on the skipMigrations list so it can be skipped on the extraction step. Or you can update the instruction to use insert or execute - my package will ignore it then.

If this is not the case let me know, you might stumble upon some error that I need to fix.

[bizley]

Closing since there is no response. Please comment here if you want to continue with that issue.

[optmsp]

Hi there, sorry for delay. You are correct, I am not using insert or execute in the migration generating the error. I went through and excluded all of my data init migrations using --skip-migrations and it began working.

[optmsp]

Okay, I now have a new problem and haven't quite figured it out. So I've noticed that most of my tables generator this error:

 > Comparing current table 'rtm_auth_assignment' with its migrations ...DONE!
 > Comparing current table 'rtm_auth_item' with its migrations ...TABLE IS UP-TO-DATE.
 > Comparing current table 'rtm_auth_item_child' with its migrations ...DONE!
 > Comparing current table 'rtm_auth_rule' with its migrations ...TABLE IS UP-TO-DATE.
 > Comparing current table 'rtm_category' with its migrations ...ERROR!
 > Column data must be provided as an instance of yii\db\ColumnSchemaBuilder. Do you have column configuration provided as a string while not using experimental mode (--ex)?

I'm unclear of what I am doing wrong in my original migration? I am simply extending yii\db\Migration. I am relatively new to yii (I'm using yii2), so it's possible I simply did something the non-yii way? Here is the migration:

<?php

use yii\db\Migration;

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

        $this->createTable(
            '{{%category}}',
            [
                'id' => $this->primaryKey(),
                'uuid' => $this->string(36),
                'oplock' => $this->integer()->defaultValue('0')->notNull(),
                'user_id' => $this->integer()->notNull(),
                'created_at' => $this->dateTime()->defaultExpression('CURRENT_TIMESTAMP'),
                'updated_at' => $this->dateTime(),
                'deleted_at' => $this->dateTime(),
                'deleted_by' => $this->integer()->defaultValue('0'),
                'created_by' => $this->integer(),
                'updated_by' => $this->integer(),
                'name' => $this->string()->notNull(),
                'global_id' => $this->integer()->notNull()->defaultValue('0'),
                'global_enabled' => $this->boolean()->notNull()->defaultValue('0'),
                'is_active' => $this->boolean()->notNull()->defaultValue('1'),
            ],
            $tableOptions
        );

        $this->createIndex('user_id', '{{%category}}', ['user_id']);

        $this->addForeignKey(
            'rtm_category_ibfk_1',
            '{{%category}}',
            ['user_id'],
            '{{%user}}',
            ['id'],
            'RESTRICT',
            'NO ACTION'
        );
    }

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

[bizley]

Next time please create a new issue for a new problem. I've run this migration locally and I cannot see any problem. Please provide all relevant migrations and the exact command you are using to run them.

[optmsp]

Okay, I thought about creating a new issue but sometimes people want a thread to play out. It's a 50/50 thing. I can create a new issue now and start from there to keep this thread clean?

[bizley]

Yes, please. I'm closing this one.