Skip to content

Loading…

DBAL-237: schema:update fails when changing from ManyToOne to OneToOne #1412

Closed
doctrinebot opened this Issue · 4 comments

2 participants

@doctrinebot

Jira issue originally created by user mlehner:

If a relation is changed from ManyToOne to OneToOne a Mysql exception 1025 is thrown because schema:update attempts to drop the indexes without first dropping the foreign keys. This seems to happen since the foreign keys are not deleted from the entity.. but the indexes change type (from index to unique).

Incorrect output from --dump-sql

DROP INDEX IDX9AFB9A3755EB82D0 ON tapiphone;
DROP INDEX IDX9AFB9A37A6A12EC1 ON tapiphone;
ALTER TABLE tapiphone ADD locationid INT DEFAULT NULL, CHANGE extension extension VARCHAR(255) DEFAULT NULL, CHANGE protocol protocol VARCHAR(255) DEFAULT NULL;
ALTER TABLE tapiphone ADD CONSTRAINT FK_9AFB9A3764D218E FOREIGN KEY (location_id) REFERENCES tapilocation(id);
CREATE UNIQUE INDEX UNIQ9AFB9A37F85E0677 ON tapiphone (username);
CREATE UNIQUE INDEX UNIQ9AFB9A37B728E969 ON tapi_phone (macaddress);
CREATE UNIQUE INDEX UNIQ9AFB9A3755EB82D0 ON tapi_phone (default_userid);
CREATE INDEX IDX9AFB9A3764D218E ON tapi_phone (locationid);
CREATE UNIQUE INDEX UNIQ9AFB9A37A6A12EC1 ON tapi_phone (registered_userid)

Expected output from --dump-sql

ALTER TABLE tapiphone DROP FOREIGN KEY FK9AFB9A3755EB82D0;
DROP INDEX IDX9AFB9A3755EB82D0 ON tapiphone;
ALTER TABLE tapiphone DROP FOREIGN KEY FK9AFB9A37A6A12EC1;
DROP INDEX IDX9AFB9A37A6A12EC1 ON tapiphone;
ALTER TABLE tapiphone ADD locationid INT DEFAULT NULL, CHANGE extension extension VARCHAR(255) DEFAULT NULL, CHANGE protocol protocol VARCHAR(255) DEFAULT NULL;
ALTER TABLE tapiphone ADD CONSTRAINT FK_9AFB9A3764D218E FOREIGN KEY (location_id) REFERENCES tapilocation(id);
ALTER TABLE tapiphone ADD CONSTRAINT FK_9AFB9A3755EB82D0 FOREIGN KEY (default_user_id) REFERENCES tapiuser (id);
ALTER TABLE tapiphone ADD CONSTRAINT FK_9AFB9A37A6A12EC1 FOREIGN KEY (registered_user_id) REFERENCES tapiuser (id);
CREATE UNIQUE INDEX UNIQ9AFB9A37F85E0677 ON tapiphone (username);
CREATE UNIQUE INDEX UNIQ9AFB9A37B728E969 ON tapi_phone (macaddress);
CREATE UNIQUE INDEX UNIQ9AFB9A3755EB82D0 ON tapi_phone (default_userid);
CREATE INDEX IDX9AFB9A3764D218E ON tapi_phone (locationid);
CREATE UNIQUE INDEX UNIQ9AFB9A37A6A12EC1 ON tapi_phone (registered_userid);

@doctrinebot

Comment created by gedrox:

I have solved it by extending the method inside MySqlPlatform class (based in DBAL 2.1.5-DEV):

 * Fix for DROP/CREATE index after foreign key change from OneToOne to ManyToOne
 * @author Aigars Gedroics
 * @param TableDiff $diff
 * @return array
 */
protected function getPreAlterTableIndexForeignKeySQL(TableDiff $diff)
{
    $sql = array();
    $table = $diff->name;

    foreach ($diff->removedIndexes AS $remKey => $remIndex) {

        foreach ($diff->addedIndexes as $addKey => $addIndex) {
            if ($remIndex->getColumns() == $addIndex->getColumns()) {

                $columns = $addIndex->getColumns();
                $type = '';
                if ($addIndex->isUnique()) {
                    $type = 'UNIQUE ';
                }

                $query = 'ALTER TABLE ' . $table . ' DROP INDEX ' . $remIndex->getName() . ', ';
                $query .= 'ADD ' . $type . 'INDEX ' . $addIndex->getName();
                $query .= ' (' . $this->getIndexFieldDeclarationListSQL($columns) . ')';

                $sql[] = $query;

                unset($diff->removedIndexes[$remKey]);
                unset($diff->addedIndexes[$addKey]);

                break;
            }
        }
    }

    $sql = array_merge($sql, parent::getPreAlterTableIndexForeignKeySQL($diff));

    return $sql;
}
@doctrinebot

Comment created by @beberlei:

Moved to dBAL

@doctrinebot

Comment created by @beberlei:

Fixed

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label
@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.2.2 milestone
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.