Fresh Neos installation with missing migrations

[darmstaedter]

Description

A fresh Neos installation has missing migrations. But when you run them they result in a SQL error.

Steps to Reproduce

1. Get a fresh Neos Installation
2. Install it completely via the web installer
3. run ./flow doctrine:migrationgenerate

Expected behavior

There shouldn't be any migrations classes to be generated.

Actual behavior

There is a migrations class created with the following content:

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

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

/**
 * Auto-generated Migration: Please modify to your needs! This block will be used as the migration description if getDescription() is not used.
 */
class Version20190502151118 extends AbstractMigration
{

    /**
     * @return string
     */
    public function getDescription()
    {
        return '';
    }

    /**
     * @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".');

        $this->addSql('DROP TABLE neos_demo_domain_model_registration');
        $this->addSql('ALTER TABLE neos_contentrepository_domain_model_nodedata DROP INDEX IDX_CE6515692D45FE4D, ADD UNIQUE INDEX UNIQ_CE6515692D45FE4D (movedto)');
        $this->addSql('DROP INDEX parentpath ON neos_contentrepository_domain_model_nodedata');
        $this->addSql('CREATE INDEX parentpath ON neos_contentrepository_domain_model_nodedata (parentpath)');
        $this->addSql('DROP INDEX idx_35dc14f03332102a ON neos_flow_resourcemanagement_persistentresource');
        $this->addSql('CREATE INDEX IDX_6954B1F63332102A ON neos_flow_resourcemanagement_persistentresource (sha1)');
        $this->addSql('DROP INDEX sourceuripathhash ON neos_redirecthandler_databasestorage_domain_model_redirect');
    }

    /**
     * @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".');

        $this->addSql('CREATE TABLE neos_demo_domain_model_registration (persistence_object_identifier VARCHAR(40) NOT NULL COLLATE utf8mb4_unicode_ci, username VARCHAR(255) NOT NULL COLLATE utf8mb4_unicode_ci, password VARCHAR(255) NOT NULL COLLATE utf8mb4_unicode_ci, firstname VARCHAR(255) NOT NULL COLLATE utf8mb4_unicode_ci, lastname VARCHAR(255) NOT NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY(persistence_object_identifier)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB COMMENT = \'\' ');
        $this->addSql('ALTER TABLE neos_contentrepository_domain_model_nodedata DROP INDEX UNIQ_CE6515692D45FE4D, ADD INDEX IDX_CE6515692D45FE4D (movedto)');
        $this->addSql('DROP INDEX parentpath ON neos_contentrepository_domain_model_nodedata');
        $this->addSql('CREATE INDEX parentpath ON neos_contentrepository_domain_model_nodedata (parentpath(255))');
        $this->addSql('DROP INDEX idx_6954b1f63332102a ON neos_flow_resourcemanagement_persistentresource');
        $this->addSql('CREATE INDEX IDX_35DC14F03332102A ON neos_flow_resourcemanagement_persistentresource (sha1)');
        $this->addSql('CREATE INDEX sourceuripathhash ON neos_redirecthandler_databasestorage_domain_model_redirect (sourceuripathhash, host)');
    }
}

When you run ./flow doctrine:migrate after the creation the following error occurs:

An exception occurred while executing 'CREATE INDEX parentpath ON neos_contentrepository_domain_model_nodedata (parentpath)':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

Affected Versions

Neos: 4.3.0

Flow: 5.3.0

PHP: 7.3.4

MariDB: 10.3.14

[kdambekalns]

Thanks for the issue. It's a known issue in a way. I saw this already, and figured we need to update things in our side.

The reason for this might be many: subtle changes to Doctrine or DB defaults, forgotten migrations in the past development, …

I'll check that carefully…

[kdambekalns]

One reason for differences detected in older setups (Neos 3.x): doctrine/orm#6565 - caused by a change in MariadDB 10.2 and fixed in DBAL 2.7. That causes changes that are no changes, like:

ALTER TABLE neos_media_domain_model_asset CHANGE resource resource VARCHAR(40) DEFAULT NULL

even though the table structure matches exactly.

[kdambekalns]

The parentpath index on NodeData is supposed to be limited to 255 characters, see this comment from the PHP class:

 * The parentpath index above is actually limited to a size of 255 characters in the corresponding MySQL migration,
 * something that cannot be expressed through the annotation.

This can be properly done using annotations as of DBAL 2.9.0 (used in Neos 4.3 / Flow 5.3), see doctrine/dbal#2412

Even if doing that, generated migrations will pick up a DROP/CREATE cycle for that index. Doh.

[kdambekalns]

Some other changes are actually "forgotten" adjustments that we need to fix.

[kdambekalns]

Still open:

$this->addSql('DROP TABLE neos_demo_domain_model_registration');
$this->addSql('DROP INDEX sourceuripathhash ON neos_redirecthandler_databasestorage_domain_model_redirect');

as these are not part of the development distribution…

[kdambekalns]

Note there will still be some differences to be detected, due to mismatches between what the DB reports and what Doctrine thinks it should report. That is true for MySQL/MariaDB as well as PostgreSQL.

[kdambekalns]

The DROP TABLE neos_demo_domain_model_registration is the result of the demo site being removed. A package that no longer exists doesn't need any tables… :)

[kdambekalns]

The DROP INDEX sourceuripathhash … confuses me, as that index is clearly requested in https://github.com/neos/redirecthandler-databasestorage/blob/89afaf857bb79286127544e659fa00d5124e5499/Classes/Domain/Model/Redirect.php#L27