php - 学说迁移问题

Question

我正在使用 doctrine 和 mysql 运行一个 symfony 4 项目。

我遇到了一个问题，即 doctrine:migrations:diff 不断创建 migrations，无论映射数据自上次 diff/migration 以来是否发生了变化

例如

如果我删除我的数据库，删除我所有的迁移文件，创建一个新的空白数据库，运行 ./bin/console doctrine:migrations:diff 然后 ./bin/console doctrine:migrations:migrate 它创建了我的数据库结构。然后，在不修改代码的情况下，我再次运行 ./bin/console doctrine:migrations:diff，我得到了一个新的迁移文件，它改变了许多表。

这是初始差异:

public function up(Schema $schema) : void
{
    // this up() migration is auto-generated, please modify it to your needs
    $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

    $this->addSql('CREATE TABLE oauth_access_token (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_F7FA86A45F37A13B (token), INDEX IDX_F7FA86A419EB6921 (client_id), INDEX IDX_F7FA86A4A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE oauth_client (id INT AUTO_INCREMENT NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris LONGTEXT NOT NULL COMMENT \'(DC2Type:array)\', secret VARCHAR(255) NOT NULL, allowed_grant_types LONGTEXT NOT NULL COMMENT \'(DC2Type:array)\', PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE fos_user (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) DEFAULT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, confirmation_token VARCHAR(180) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT \'(DC2Type:array)\', name VARCHAR(255) DEFAULT NULL, phone_number VARCHAR(255) DEFAULT NULL, notes LONGTEXT DEFAULT NULL, UNIQUE INDEX UNIQ_957A647992FC23A8 (username_canonical), UNIQUE INDEX UNIQ_957A6479A0D96FBF (email_canonical), UNIQUE INDEX UNIQ_957A6479C05FB297 (confirmation_token), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE oauth_refresh_token (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_55DCF7555F37A13B (token), INDEX IDX_55DCF75519EB6921 (client_id), INDEX IDX_55DCF755A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event (id INT AUTO_INCREMENT NOT NULL, program_id INT DEFAULT NULL, scheduled_employee_id INT DEFAULT NULL, location_id INT DEFAULT NULL, date DATE DEFAULT NULL, start_time TIME DEFAULT NULL, end_time TIME DEFAULT NULL, isi_report VARCHAR(255) DEFAULT NULL, INDEX IDX_3BAE0AA73EB8070A (program_id), INDEX IDX_3BAE0AA73E2088FC (scheduled_employee_id), INDEX IDX_3BAE0AA764D218E (location_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event_user_request (event_id INT NOT NULL, user_id INT NOT NULL, INDEX IDX_8E899B6671F7E88B (event_id), INDEX IDX_8E899B66A76ED395 (user_id), PRIMARY KEY(event_id, user_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event_supplier_varietal (event_id INT NOT NULL, supplier_varietal_id INT NOT NULL, INDEX IDX_8FE63B1171F7E88B (event_id), INDEX IDX_8FE63B118EEC9B31 (supplier_varietal_id), PRIMARY KEY(event_id, supplier_varietal_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE retail_account (id INT AUTO_INCREMENT NOT NULL, account_name VARCHAR(255) NOT NULL, contact_name VARCHAR(255) NOT NULL, contact_email VARCHAR(255) NOT NULL, contact_phone VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE oauth_auth_code (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri LONGTEXT NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_4D12F0E05F37A13B (token), INDEX IDX_4D12F0E019EB6921 (client_id), INDEX IDX_4D12F0E0A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE retail_account_location (id INT AUTO_INCREMENT NOT NULL, retail_account_id INT DEFAULT NULL, store_number VARCHAR(255) DEFAULT NULL, store_name VARCHAR(255) DEFAULT NULL, store_address VARCHAR(255) DEFAULT NULL, store_city VARCHAR(255) DEFAULT NULL, store_state VARCHAR(255) DEFAULT NULL, store_zip VARCHAR(12) DEFAULT NULL, manager_name VARCHAR(255) DEFAULT NULL, manager_phone VARCHAR(255) DEFAULT NULL, assistant_manager_name VARCHAR(255) DEFAULT NULL, assistant_manager_phone VARCHAR(255) DEFAULT NULL, INDEX IDX_E8E7CE679014C119 (retail_account_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event_gallery_image (id INT AUTO_INCREMENT NOT NULL, event_id INT DEFAULT NULL, path VARCHAR(255) NOT NULL, INDEX IDX_5F964D071F7E88B (event_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE supplier (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, contact_name VARCHAR(255) NOT NULL, contact_email VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE supplier_varietal (id INT AUTO_INCREMENT NOT NULL, supplier_id INT DEFAULT NULL, program_id INT DEFAULT NULL, name VARCHAR(255) NOT NULL, INDEX IDX_1A7F61702ADD6D8C (supplier_id), INDEX IDX_1A7F61703EB8070A (program_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE recap_question (id INT AUTO_INCREMENT NOT NULL, program_id INT DEFAULT NULL, parent_id INT DEFAULT NULL, question VARCHAR(255) NOT NULL, is_default TINYINT(1) DEFAULT NULL, INDEX IDX_8F870D7A3EB8070A (program_id), INDEX IDX_8F870D7A727ACA70 (parent_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE program (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, dates LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', sell_sheet VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE program_retail_account (program_id INT NOT NULL, retail_account_id INT NOT NULL, INDEX IDX_7AF8F3CD3EB8070A (program_id), INDEX IDX_7AF8F3CD9014C119 (retail_account_id), PRIMARY KEY(program_id, retail_account_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE program_supplier (program_id INT NOT NULL, supplier_id INT NOT NULL, INDEX IDX_1A1F904B3EB8070A (program_id), INDEX IDX_1A1F904B2ADD6D8C (supplier_id), PRIMARY KEY(program_id, supplier_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE recap_answer (id INT AUTO_INCREMENT NOT NULL, event_id INT DEFAULT NULL, question_id INT DEFAULT NULL, varietal_id INT DEFAULT NULL, answer LONGTEXT NOT NULL, INDEX IDX_F13D2AA271F7E88B (event_id), INDEX IDX_F13D2AA21E27F6BF (question_id), INDEX IDX_F13D2AA2575B3020 (varietal_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('ALTER TABLE oauth_access_token ADD CONSTRAINT FK_F7FA86A419EB6921 FOREIGN KEY (client_id) REFERENCES oauth_client (id)');
    $this->addSql('ALTER TABLE oauth_access_token ADD CONSTRAINT FK_F7FA86A4A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE oauth_refresh_token ADD CONSTRAINT FK_55DCF75519EB6921 FOREIGN KEY (client_id) REFERENCES oauth_client (id)');
    $this->addSql('ALTER TABLE oauth_refresh_token ADD CONSTRAINT FK_55DCF755A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event ADD CONSTRAINT FK_3BAE0AA73EB8070A FOREIGN KEY (program_id) REFERENCES program (id)');
    $this->addSql('ALTER TABLE event ADD CONSTRAINT FK_3BAE0AA73E2088FC FOREIGN KEY (scheduled_employee_id) REFERENCES fos_user (id)');
    $this->addSql('ALTER TABLE event ADD CONSTRAINT FK_3BAE0AA764D218E FOREIGN KEY (location_id) REFERENCES retail_account_location (id)');
    $this->addSql('ALTER TABLE event_user_request ADD CONSTRAINT FK_8E899B6671F7E88B FOREIGN KEY (event_id) REFERENCES event (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event_user_request ADD CONSTRAINT FK_8E899B66A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event_supplier_varietal ADD CONSTRAINT FK_8FE63B1171F7E88B FOREIGN KEY (event_id) REFERENCES event (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event_supplier_varietal ADD CONSTRAINT FK_8FE63B118EEC9B31 FOREIGN KEY (supplier_varietal_id) REFERENCES supplier_varietal (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE oauth_auth_code ADD CONSTRAINT FK_4D12F0E019EB6921 FOREIGN KEY (client_id) REFERENCES oauth_client (id)');
    $this->addSql('ALTER TABLE oauth_auth_code ADD CONSTRAINT FK_4D12F0E0A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE retail_account_location ADD CONSTRAINT FK_E8E7CE679014C119 FOREIGN KEY (retail_account_id) REFERENCES retail_account (id)');
    $this->addSql('ALTER TABLE event_gallery_image ADD CONSTRAINT FK_5F964D071F7E88B FOREIGN KEY (event_id) REFERENCES event (id)');
    $this->addSql('ALTER TABLE supplier_varietal ADD CONSTRAINT FK_1A7F61702ADD6D8C FOREIGN KEY (supplier_id) REFERENCES supplier (id)');
    $this->addSql('ALTER TABLE supplier_varietal ADD CONSTRAINT FK_1A7F61703EB8070A FOREIGN KEY (program_id) REFERENCES program (id)');
    $this->addSql('ALTER TABLE recap_question ADD CONSTRAINT FK_8F870D7A3EB8070A FOREIGN KEY (program_id) REFERENCES program (id)');
    $this->addSql('ALTER TABLE recap_question ADD CONSTRAINT FK_8F870D7A727ACA70 FOREIGN KEY (parent_id) REFERENCES recap_question (id)');
    $this->addSql('ALTER TABLE program_retail_account ADD CONSTRAINT FK_7AF8F3CD3EB8070A FOREIGN KEY (program_id) REFERENCES program (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE program_retail_account ADD CONSTRAINT FK_7AF8F3CD9014C119 FOREIGN KEY (retail_account_id) REFERENCES retail_account (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE program_supplier ADD CONSTRAINT FK_1A1F904B3EB8070A FOREIGN KEY (program_id) REFERENCES program (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE program_supplier ADD CONSTRAINT FK_1A1F904B2ADD6D8C FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE recap_answer ADD CONSTRAINT FK_F13D2AA271F7E88B FOREIGN KEY (event_id) REFERENCES event (id)');
    $this->addSql('ALTER TABLE recap_answer ADD CONSTRAINT FK_F13D2AA21E27F6BF FOREIGN KEY (question_id) REFERENCES recap_question (id)');
    $this->addSql('ALTER TABLE recap_answer ADD CONSTRAINT FK_F13D2AA2575B3020 FOREIGN KEY (varietal_id) REFERENCES supplier_varietal (id)');
}

和第二个差异:

public function up(Schema $schema) : void
{
    // this up() migration is auto-generated, please modify it to your needs
    $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

    $this->addSql('ALTER TABLE oauth_access_token CHANGE user_id user_id INT DEFAULT NULL, CHANGE expires_at expires_at INT DEFAULT NULL, CHANGE scope scope VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE fos_user CHANGE salt salt VARCHAR(255) DEFAULT NULL, CHANGE last_login last_login DATETIME DEFAULT NULL, CHANGE confirmation_token confirmation_token VARCHAR(180) DEFAULT NULL, CHANGE password_requested_at password_requested_at DATETIME DEFAULT NULL, CHANGE name name VARCHAR(255) DEFAULT NULL, CHANGE phone_number phone_number VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE oauth_refresh_token CHANGE user_id user_id INT DEFAULT NULL, CHANGE expires_at expires_at INT DEFAULT NULL, CHANGE scope scope VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE event CHANGE program_id program_id INT DEFAULT NULL, CHANGE scheduled_employee_id scheduled_employee_id INT DEFAULT NULL, CHANGE location_id location_id INT DEFAULT NULL, CHANGE date date DATE DEFAULT NULL, CHANGE start_time start_time TIME DEFAULT NULL, CHANGE end_time end_time TIME DEFAULT NULL, CHANGE isi_report isi_report VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE oauth_auth_code CHANGE user_id user_id INT DEFAULT NULL, CHANGE expires_at expires_at INT DEFAULT NULL, CHANGE scope scope VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE retail_account_location CHANGE retail_account_id retail_account_id INT DEFAULT NULL, CHANGE store_number store_number VARCHAR(255) DEFAULT NULL, CHANGE store_name store_name VARCHAR(255) DEFAULT NULL, CHANGE store_address store_address VARCHAR(255) DEFAULT NULL, CHANGE store_city store_city VARCHAR(255) DEFAULT NULL, CHANGE store_state store_state VARCHAR(255) DEFAULT NULL, CHANGE store_zip store_zip VARCHAR(12) DEFAULT NULL, CHANGE manager_name manager_name VARCHAR(255) DEFAULT NULL, CHANGE manager_phone manager_phone VARCHAR(255) DEFAULT NULL, CHANGE assistant_manager_name assistant_manager_name VARCHAR(255) DEFAULT NULL, CHANGE assistant_manager_phone assistant_manager_phone VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE event_gallery_image CHANGE event_id event_id INT DEFAULT NULL');
    $this->addSql('ALTER TABLE supplier_varietal CHANGE supplier_id supplier_id INT DEFAULT NULL, CHANGE program_id program_id INT DEFAULT NULL');
    $this->addSql('ALTER TABLE recap_question CHANGE program_id program_id INT DEFAULT NULL, CHANGE parent_id parent_id INT DEFAULT NULL, CHANGE is_default is_default TINYINT(1) DEFAULT NULL');
    $this->addSql('ALTER TABLE program CHANGE dates dates LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', CHANGE sell_sheet sell_sheet VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE recap_answer CHANGE event_id event_id INT DEFAULT NULL, CHANGE question_id question_id INT DEFAULT NULL, CHANGE varietal_id varietal_id INT DEFAULT NULL');
}

如您所见，第一个差异创建了表 oauth_access_token 并设置了列 user_id INT DEFAULT NULL

然后第二个差异尝试修改表 ALTER TABLE oauth_access_token CHANGE user_id user_id INT DEFAULT NULL

此外，如果我运行 ./bin/console doctrine:migrations:diff ./bin/console doctrine:migrations:migrate 然后 ./bin/console doctrine:schema:validate 我得到:

映射

[OK] 映射文件正确。

数据库

[错误] 数据库模式与当前映射文件不同步。

Answer 1

这似乎是 MariaDB 版本的问题。我运行的是 10.3，需要降级以实现兼容性

https://github.com/doctrine/dbal/issues/3202