New Flow environment generates doctrine migrations

[mrimann]

(re-opening here as a new issue as I wasn't able to move the issue I opened in the wrong project, see neos/flow-development-distribution#37)

A Flow application where I add a new domain model or change an existing one and after that ran a doctrine:migrationgenerate command to generate the according migration always showed "additional" changes to the schema that were not related to my changes to the domain models. At first I thought $someoneElseOrMyself left over some dirty unfinished stuff - but we couldn't figure it out.
The second time I've seen the issue was a fresh project - where it seemed unexpected that there is anything in a strange condition. The command always showed differences between the migrations and the actual DB schema.
Then I started playing with ddev to see if that fits my needs for a local dev environment - and again I saw this issue, with a fresh, empty database. At this point, also my thought of "well, maybe our MySQL config is not optimal for Flow/Neos" also vanished.

I've now stumbled over this annoyance the n-th time and finally took the time to dig deeper into it:

Steps to reproduce:

• create project from scratch, e.g. by checking out https://github.com/neos/flow-base-distribution
• configure database to use an empty database (in my case in a fresh, empty ddev environment that did not exist prior to this)
• just to be sure, check that the database is really empty
• execute the doctrine migrations that already exist: ./flow doctrine:migrate
• let's check that no differences exist now: ./flow doctrine:migrationgenerate

Expected behaviour:

• no changes to the schema detected, resulting in no new migration file (e.g. "no changes needed")

Current behaviour:

• the doctrine:migrationgenerate command detects changes
• a new migration file is generated, contaning the following change in the up() method:

$this->addSql('DROP INDEX idx_35dc14f03332102a ON neos_flow_resourcemanagement_persistentresource');
$this->addSql('CREATE INDEX IDX_6954B1F63332102A ON neos_flow_resourcemanagement_persistentresource (sha1)');

[mrimann]

Just for the records, here's the full output of the doctrine:migrate command:

uid_1000@e92cdb75ad5d:/var/www/html$ ./flow doctrine:migrate
Migrating up to 20180827132710 from 0

  ++ migrating 20110613223837

     -> CREATE TABLE flow3_resource_resourcepointer (hash VARCHAR(255) NOT NULL, PRIMARY KEY(hash)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> CREATE TABLE flow3_resource_resource (flow3_persistence_identifier VARCHAR(40) NOT NULL, flow3_resource_resourcepointer VARCHAR(255) DEFAULT NULL, filename VARCHAR(255) DEFAULT NULL, fileextension VARCHAR(255) DEFAULT NULL, INDEX IDX_11FFD19FD0275681 (flow3_resource_resourcepointer), PRIMARY KEY(flow3_persistence_identifier)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> CREATE TABLE flow3_security_account (flow3_persistence_identifier VARCHAR(40) NOT NULL, party_abstractparty VARCHAR(40) DEFAULT NULL, accountidentifier VARCHAR(255) DEFAULT NULL, authenticationprovidername VARCHAR(255) DEFAULT NULL, credentialssource VARCHAR(255) DEFAULT NULL, creationdate DATETIME DEFAULT NULL, expirationdate DATETIME DEFAULT NULL, roles LONGTEXT DEFAULT NULL COMMENT '(DC2Type:array)', INDEX IDX_44D0753B38110E12 (party_abstractparty), PRIMARY KEY(flow3_persistence_identifier)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> CREATE TABLE flow3_resource_securitypublishingconfiguration (flow3_persistence_identifier VARCHAR(40) NOT NULL, allowedroles LONGTEXT DEFAULT NULL COMMENT '(DC2Type:array)', PRIMARY KEY(flow3_persistence_identifier)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> CREATE TABLE flow3_policy_role (identifier VARCHAR(255) NOT NULL, PRIMARY KEY(identifier)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> ALTER TABLE flow3_resource_resource ADD CONSTRAINT flow3_resource_resource_ibfk_1 FOREIGN KEY (flow3_resource_resourcepointer) REFERENCES flow3_resource_resourcepointer(hash)

  ++ migrated (0.73s)

  ++ migrating 20110824124835

     -> RENAME TABLE flow3_policy_role TO typo3_flow3_security_policy_role
     -> RENAME TABLE flow3_resource_resource TO typo3_flow3_resource_resource
     -> RENAME TABLE flow3_resource_resourcepointer TO typo3_flow3_resource_resourcepointer
     -> RENAME TABLE flow3_resource_securitypublishingconfiguration TO typo3_flow3_security_authorization_resource_securitypublis_6180a
     -> RENAME TABLE flow3_security_account TO typo3_flow3_security_account

  ++ migrated (0.02s)

  ++ migrating 20110920125736

     -> CREATE TABLE typo3_flow3_mvc_web_routing_objectpathmapping (objecttype VARCHAR(255) NOT NULL, uripattern VARCHAR(255) NOT NULL, pathsegment VARCHAR(255) NOT NULL, identifier VARCHAR(255) DEFAULT NULL, PRIMARY KEY(objecttype, uripattern, pathsegment)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB

  ++ migrated (0.01s)

  ++ migrating 20110923125535

     -> ALTER TABLE typo3_flow3_resource_resource DROP FOREIGN KEY typo3_flow3_resource_resource_ibfk_1
     -> DROP INDEX IDX_11FFD19FD0275681 ON typo3_flow3_resource_resource
     -> ALTER TABLE typo3_flow3_resource_resource CHANGE flow3_resource_resourcepointer resourcepointer VARCHAR(255) DEFAULT NULL
     -> ALTER TABLE typo3_flow3_resource_resource ADD  CONSTRAINT typo3_flow3_resource_resource_ibfk_1 FOREIGN KEY (resourcepointer) REFERENCES typo3_flow3_resource_resourcepointer(hash)
     -> CREATE INDEX IDX_B4D45B323CB65D1 ON typo3_flow3_resource_resource (resourcepointer)
     -> ALTER TABLE typo3_flow3_security_account CHANGE party_abstractparty party VARCHAR(40) DEFAULT NULL

  ++ migrated (0.03s)

  ++ migrating 20120328152041

     -> RENAME TABLE typo3_flow3_mvc_web_routing_objectpathmapping TO typo3_flow3_mvc_routing_objectpathmapping

  ++ migrated (0s)

  ++ migrating 20120329220340

     -> ALTER TABLE typo3_flow3_mvc_routing_objectpathmapping CHANGE identifier identifier VARCHAR(255) NOT NULL
     -> ALTER TABLE typo3_flow3_resource_resource CHANGE filename filename VARCHAR(255) NOT NULL, CHANGE fileextension fileextension VARCHAR(255) NOT NULL
     -> ALTER TABLE typo3_flow3_security_authorization_resource_securitypublis_6180a CHANGE allowedroles allowedroles LONGTEXT NOT NULL COMMENT '(DC2Type:array)'
     -> ALTER TABLE typo3_flow3_security_account CHANGE accountidentifier accountidentifier VARCHAR(255) NOT NULL, CHANGE authenticationprovidername authenticationprovidername VARCHAR(255) NOT NULL, CHANGE credentialssource credentialssource VARCHAR(255) NOT NULL, CHANGE creationdate creationdate DATETIME NOT NULL, CHANGE roles roles LONGTEXT NOT NULL COMMENT '(DC2Type:array)'

  ++ migrated (0.03s)

  ++ migrating 20120412093748

     -> RENAME TABLE typo3_flow3_security_authorization_resource_securitypublis_6180a TO typo3_flow3_security_authorization_resource_securitypubli_6180a

  ++ migrated (0s)

  ++ migrating 20120429213445

     -> CREATE UNIQUE INDEX flow3_identity_typo3_flow3_security_account ON typo3_flow3_security_account (accountidentifier, authenticationprovidername)

  ++ migrated (0s)

  ++ migrating 20120520211354

     -> ALTER TABLE typo3_flow3_security_account CHANGE credentialssource credentialssource VARCHAR(255) DEFAULT NULL

  ++ migrated (0.01s)

  ++ migrating 20120625211647

     -> CREATE TABLE typo3_flow3_resource_publishing_abstractpublishingconfiguration (flow3_persistence_identifier VARCHAR(40) NOT NULL, dtype VARCHAR(255) NOT NULL, PRIMARY KEY(flow3_persistence_identifier)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> ALTER TABLE typo3_flow3_resource_resource ADD publishingconfiguration VARCHAR(40) DEFAULT NULL
     -> ALTER TABLE typo3_flow3_resource_resource ADD CONSTRAINT FK_B4D45B32A4A851AF FOREIGN KEY (publishingconfiguration) REFERENCES typo3_flow3_resource_publishing_abstractpublishingconfiguration (flow3_persistence_identifier)
     -> CREATE INDEX IDX_B4D45B32A4A851AF ON typo3_flow3_resource_resource (publishingconfiguration)
     -> ALTER TABLE typo3_flow3_security_authorization_resource_securitypubli_6180a ADD CONSTRAINT FK_234846D521E3D446 FOREIGN KEY (flow3_persistence_identifier) REFERENCES typo3_flow3_resource_publishing_abstractpublishingconfiguration (flow3_persistence_identifier) ON DELETE CASCADE

  ++ migrated (0.04s)

  ++ migrating 20120930203452

     -> ALTER TABLE typo3_flow3_resource_resource DROP FOREIGN KEY FK_B4D45B32A4A851AF
     -> ALTER TABLE typo3_flow3_security_authorization_resource_securitypubli_6180a DROP FOREIGN KEY FK_234846D521E3D446
     -> ALTER TABLE typo3_flow3_resource_publishing_abstractpublishingconfiguration DROP PRIMARY KEY
     -> ALTER TABLE typo3_flow3_resource_publishing_abstractpublishingconfiguration CHANGE flow3_persistence_identifier persistence_object_identifier VARCHAR(40) NOT NULL
     -> ALTER TABLE typo3_flow3_resource_publishing_abstractpublishingconfiguration ADD PRIMARY KEY (persistence_object_identifier)
     -> ALTER TABLE typo3_flow3_resource_resource DROP PRIMARY KEY
     -> ALTER TABLE typo3_flow3_resource_resource CHANGE flow3_persistence_identifier persistence_object_identifier VARCHAR(40) NOT NULL
     -> ALTER TABLE typo3_flow3_resource_resource ADD PRIMARY KEY (persistence_object_identifier)
     -> ALTER TABLE typo3_flow3_security_account DROP PRIMARY KEY
     -> ALTER TABLE typo3_flow3_security_account CHANGE flow3_persistence_identifier persistence_object_identifier VARCHAR(40) NOT NULL
     -> ALTER TABLE typo3_flow3_security_account ADD PRIMARY KEY (persistence_object_identifier)
     -> ALTER TABLE typo3_flow3_security_authorization_resource_securitypubli_6180a DROP PRIMARY KEY
     -> ALTER TABLE typo3_flow3_security_authorization_resource_securitypubli_6180a CHANGE flow3_persistence_identifier persistence_object_identifier VARCHAR(40) NOT NULL
     -> ALTER TABLE typo3_flow3_security_authorization_resource_securitypubli_6180a ADD PRIMARY KEY (persistence_object_identifier)
     -> ALTER TABLE typo3_flow3_resource_resource ADD CONSTRAINT FK_B4D45B32A4A851AF FOREIGN KEY (publishingconfiguration) REFERENCES typo3_flow3_resource_publishing_abstractpublishingconfiguration (persistence_object_identifier)
     -> ALTER TABLE typo3_flow3_security_authorization_resource_securitypubli_6180a ADD CONSTRAINT FK_234846D521E3D446 FOREIGN KEY (persistence_object_identifier) REFERENCES typo3_flow3_resource_publishing_abstractpublishingconfiguration (persistence_object_identifier) ON DELETE CASCADE
     -> RENAME TABLE typo3_flow3_mvc_routing_objectpathmapping TO typo3_flow_mvc_routing_objectpathmapping
     -> RENAME TABLE typo3_flow3_resource_publishing_abstractpublishingconfiguration TO typo3_flow_resource_publishing_abstractpublishingconfiguration
     -> RENAME TABLE typo3_flow3_resource_resource TO typo3_flow_resource_resource
     -> RENAME TABLE typo3_flow3_resource_resourcepointer TO typo3_flow_resource_resourcepointer
     -> RENAME TABLE typo3_flow3_security_account TO typo3_flow_security_account
     -> RENAME TABLE typo3_flow3_security_authorization_resource_securitypubli_6180a TO typo3_flow_security_authorization_resource_securitypublis_861cb
     -> RENAME TABLE typo3_flow3_security_policy_role TO typo3_flow_security_policy_role

  ++ migrated (0.24s)

  ++ migrating 20130319131400

     -> CREATE TABLE typo3_flow_security_account_roles_join (flow_security_account VARCHAR(40) NOT NULL, flow_policy_role VARCHAR(255) NOT NULL, INDEX IDX_ADF11BBC58842EFC (flow_security_account), INDEX IDX_ADF11BBC23A1047C (flow_policy_role), PRIMARY KEY(flow_security_account, flow_policy_role)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> CREATE TABLE typo3_flow_security_policy_role_parentroles_join (flow_policy_role VARCHAR(255) NOT NULL, parent_role VARCHAR(255) NOT NULL, INDEX IDX_D459C58E23A1047C (flow_policy_role), INDEX IDX_D459C58E6A8ABCDE (parent_role), PRIMARY KEY (flow_policy_role, parent_role)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
     -> ALTER TABLE typo3_flow_security_account_roles_join ADD CONSTRAINT FK_ADF11BBC58842EFC FOREIGN KEY (flow_security_account) REFERENCES typo3_flow_security_account (persistence_object_identifier)
     -> ALTER TABLE typo3_flow_security_account_roles_join ADD CONSTRAINT FK_ADF11BBC23A1047C FOREIGN KEY (flow_policy_role) REFERENCES typo3_flow_security_policy_role (identifier)
     -> ALTER TABLE typo3_flow_security_policy_role_parentroles_join ADD CONSTRAINT FK_D459C58E23A1047C FOREIGN KEY (flow_policy_role) REFERENCES typo3_flow_security_policy_role (identifier)
     -> ALTER TABLE typo3_flow_security_policy_role_parentroles_join ADD CONSTRAINT FK_D459C58E6A8ABCDE FOREIGN KEY (parent_role) REFERENCES typo3_flow_security_policy_role (identifier)
     -> ALTER TABLE typo3_flow_security_policy_role ADD sourcehint VARCHAR(6) NOT NULL
     -> ALTER TABLE typo3_flow_security_account DROP roles

  ++ migrated (8.89s)

  ++ migrating 20141015125841

     -> ALTER TABLE typo3_flow_resource_resource DROP FOREIGN KEY FK_B4D45B32A4A851AF
     -> ALTER TABLE typo3_flow_resource_resource DROP FOREIGN KEY typo3_flow_resource_resource_ibfk_1
     -> DROP INDEX IDX_B4D45B323CB65D1 ON typo3_flow_resource_resource
     -> ALTER TABLE typo3_flow_resource_resource CHANGE resourcepointer sha1 VARCHAR(40) NOT NULL, ADD md5 VARCHAR(32) DEFAULT NULL, ADD collectionname VARCHAR(255) DEFAULT NULL, DROP publishingconfiguration, DROP fileextension, ADD mediatype VARCHAR(100) DEFAULT NULL, ADD relativepublicationpath VARCHAR(255) NOT NULL, ADD filesize NUMERIC(20, 0) DEFAULT NULL
     -> DROP TABLE typo3_flow_resource_resourcepointer

  ++ migrated (0.03s)

  ++ migrating 20141113173712

     -> ALTER TABLE typo3_flow_security_account ADD roleidentifiers LONGTEXT DEFAULT NULL COMMENT '(DC2Type:simple_array)'
     -> ALTER TABLE typo3_flow_security_account_roles_join DROP FOREIGN KEY FK_ADF11BBC23A1047C
     -> ALTER TABLE typo3_flow_security_policy_role_parentroles_join DROP FOREIGN KEY FK_D459C58E6A8ABCDE
     -> ALTER TABLE typo3_flow_security_policy_role_parentroles_join DROP FOREIGN KEY FK_D459C58E23A1047C
     -> DROP TABLE typo3_flow_security_account_roles_join
     -> DROP TABLE typo3_flow_security_authorization_resource_securitypublis_861cb
     -> DROP TABLE typo3_flow_security_policy_role
     -> DROP TABLE typo3_flow_security_policy_role_parentroles_join

  ++ migrated (0.03s)

  ++ migrating 20150129225152

     -> DROP TABLE typo3_flow_resource_publishing_abstractpublishingconfiguration

  ++ migrated (0s)

  ++ migrating 20150206114820

     -> ALTER TABLE typo3_flow_security_account DROP party

  ++ migrated (0.01s)

  ++ migrating 20150309181635

     -> DROP INDEX flow3_identity_typo3_flow3_security_account ON typo3_flow_security_account
     -> CREATE UNIQUE INDEX flow_identity_typo3_flow_security_account ON typo3_flow_security_account (accountidentifier, authenticationprovidername)

  ++ migrated (0.01s)

  ++ migrating 20150611154419

Migration 20150611154419 was executed but did not result in any SQL statements.

  ++ migrated (0s)

  ++ migrating 20151110113650

     -> ALTER TABLE typo3_flow_security_account ADD lastsuccessfulauthenticationdate DATETIME DEFAULT NULL, ADD failedauthenticationcount INT DEFAULT 0

  ++ migrated (0.01s)

  ++ migrating 20160601164332

     -> ALTER TABLE typo3_flow_security_account CHANGE failedauthenticationcount failedauthenticationcount INT DEFAULT NULL

  ++ migrated (0s)

  ++ migrating 20161115142022

     -> RENAME TABLE typo3_flow_resource_resource TO typo3_flow_resourcemanagement_persistentresource

  ++ migrated (0.01s)

  ++ migrating 20161124185047

     -> RENAME TABLE typo3_flow_mvc_routing_objectpathmapping TO neos_flow_mvc_routing_objectpathmapping
     -> RENAME TABLE typo3_flow_resourcemanagement_persistentresource TO neos_flow_resourcemanagement_persistentresource
     -> RENAME TABLE typo3_flow_security_account TO neos_flow_security_account

  ++ migrated (0.01s)

  ++ migrating 20161125121218

     -> UPDATE neos_flow_security_account SET roleidentifiers=REPLACE(roleidentifiers, 'TYPO3.Flow:', 'Neos.Flow:')
     -> UPDATE neos_flow_security_account SET roleidentifiers=REPLACE(roleidentifiers, 'TYPO3.Neos:', 'Neos.Neos:')
     -> UPDATE neos_flow_security_account SET roleidentifiers=REPLACE(roleidentifiers, 'TYPO3.TYPO3CR:', 'Neos.ContentRepository:')
     -> UPDATE neos_flow_security_account SET roleidentifiers=REPLACE(roleidentifiers, 'TYPO3.Setup:', 'Neos.Setup:')

  ++ migrated (0s)

  ++ migrating 20170110130149

     -> DROP INDEX flow_identity_typo3_flow_security_account ON neos_flow_security_account
     -> CREATE UNIQUE INDEX flow_identity_neos_flow_security_account ON neos_flow_security_account (accountidentifier, authenticationprovidername)

  ++ migrated (0.01s)

  ++ migrating 20180622074421

     -> CREATE INDEX IDX_35DC14F03332102A ON neos_flow_resourcemanagement_persistentresource (sha1)

  ++ migrated (0.01s)

  ++ migrating 20180827132710

     -> CREATE INDEX IDX_535A651E772E836ADCCB5599802C8F9D ON neos_flow_mvc_routing_objectpathmapping (identifier, uripattern, pathsegment)

  ++ migrated (0s)

  ------------------------

  ++ finished in 10.13s
  ++ 26 migrations executed
  ++ 93 sql queries

The only migration that was not "green" in the output was 20150611154419 because of:

Migration 20150611154419 was executed but did not result in any SQL statements.

Looking into that migration file, this seems obvious and unrelated to my above mentioned issue (the migration updates database entries of file-resources in case they need to be moved + updated - which obviously is not the case in an empty database).

[albe]

Thanks for taking the time and effort to dig into this annoyance!
Looks as if a migration for the persistent resource is missing in the flow distribution. After some digging, I found 637cbdb#diff-2f029ffcce17a4f0cca86de5ccefda16 which should contain the index migration, but for some reason isn't applied it seems. Could this be due to case-sensitivity in the index name with the array_key_exists check? @kdambekalns

[kdambekalns]

See neos/neos-development-collection#2475

[rkrahnen]

I'm struggling with the same problem since I executed Migration 20180622074421, because it's not correct. The index IDX_35DC14F03332102A is used for both tables, typo3_flow_resource_resource and neos_flow_resourcemanagement_persistentresource, which is wrong because Doctrine uses the table name for hashing. The correct index for neos_flow_resourcemanagement_persistentresource would be IDX_6954B1F63332102A.

In my opinion the following migration could be added to solve the issue:

<?php
namespace Neos\Flow\Persistence\Doctrine\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

class Version20190516092450 extends AbstractMigration
{

    /**
     * @return string
     */
    public function getDescription()
    {
        return 'Fixed index in "sha1" column of the "resource" table';
    }

    /**
     * @param Schema $schema
     * @return void
     */
    public function up(Schema $schema)
    {
        // this up() migration is autogenerated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on "mysql".');
        
        if (in_array('neos_flow_resourcemanagement_persistentresource', $this->sm->listTableNames())
            && array_key_exists(strtolower('IDX_35DC14F03332102A'), $this->sm->listTableIndexes('neos_flow_resourcemanagement_persistentresource'))) {
            $this->addSql('DROP INDEX IDX_35DC14F03332102A ON neos_flow_resourcemanagement_persistentresource');
            $this->addSql('CREATE INDEX IDX_6954B1F63332102A ON neos_flow_resourcemanagement_persistentresource (sha1)');
        }
    }

    /**
     * @param Schema $schema
     * @return void
     */
    public function down(Schema $schema)
    {
        // this down() migration is autogenerated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on "mysql".');
        
        if (in_array('neos_flow_resourcemanagement_persistentresource', $this->sm->listTableNames())
            && array_key_exists(strtolower('IDX_6954B1F63332102A'), $this->sm->listTableIndexes('neos_flow_resourcemanagement_persistentresource'))) {
            $this->addSql('DROP INDEX IDX_6954B1F63332102A ON neos_flow_resourcemanagement_persistentresource');
            $this->addSql('CREATE INDEX IDX_35DC14F03332102A ON neos_flow_resourcemanagement_persistentresource (sha1)');
        }
    }
}

Because of the checks for table and index existence it would not affect projects where the owner has changed the index manually, but would help all others.