[#DBAL-237] schema:update fails when changing from ManyToOne to OneToOne - Doctrine Project

Details

Type: Bug
Status: Resolved
Priority: Major
Resolution: Fixed
Affects Version/s: 2.1.6
Fix Version/s: 2.2.2
Component/s: None
Security Level: All
Labels:
None
Environment:
Symfony 2.0.9

Description

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 IDX_9AFB9A3755EB82D0 ON tapi_phone;
DROP INDEX IDX_9AFB9A37A6A12EC1 ON tapi_phone;
ALTER TABLE tapi_phone ADD location_id INT DEFAULT NULL, CHANGE extension extension VARCHAR(255) DEFAULT NULL, CHANGE protocol protocol VARCHAR(255) DEFAULT NULL;
ALTER TABLE tapi_phone ADD CONSTRAINT FK_9AFB9A3764D218E FOREIGN KEY (location_id) REFERENCES tapi_location(id);
CREATE UNIQUE INDEX UNIQ_9AFB9A37F85E0677 ON tapi_phone (username);
CREATE UNIQUE INDEX UNIQ_9AFB9A37B728E969 ON tapi_phone (mac_address);
CREATE UNIQUE INDEX UNIQ_9AFB9A3755EB82D0 ON tapi_phone (default_user_id);
CREATE INDEX IDX_9AFB9A3764D218E ON tapi_phone (location_id);
CREATE UNIQUE INDEX UNIQ_9AFB9A37A6A12EC1 ON tapi_phone (registered_user_id)

Expected output from --dump-sql

ALTER TABLE tapi_phone DROP FOREIGN KEY FK_9AFB9A3755EB82D0;
DROP INDEX IDX_9AFB9A3755EB82D0 ON tapi_phone;
ALTER TABLE tapi_phone DROP FOREIGN KEY FK_9AFB9A37A6A12EC1;
DROP INDEX IDX_9AFB9A37A6A12EC1 ON tapi_phone;
ALTER TABLE tapi_phone ADD location_id INT DEFAULT NULL, CHANGE extension extension VARCHAR(255) DEFAULT NULL, CHANGE protocol protocol VARCHAR(255) DEFAULT NULL;
ALTER TABLE tapi_phone ADD CONSTRAINT FK_9AFB9A3764D218E FOREIGN KEY (location_id) REFERENCES tapi_location(id);
ALTER TABLE tapi_phone ADD CONSTRAINT FK_9AFB9A3755EB82D0 FOREIGN KEY (default_user_id) REFERENCES tapi_user (id);
ALTER TABLE tapi_phone ADD CONSTRAINT FK_9AFB9A37A6A12EC1 FOREIGN KEY (registered_user_id) REFERENCES tapi_user (id);
CREATE UNIQUE INDEX UNIQ_9AFB9A37F85E0677 ON tapi_phone (username);
CREATE UNIQUE INDEX UNIQ_9AFB9A37B728E969 ON tapi_phone (mac_address);
CREATE UNIQUE INDEX UNIQ_9AFB9A3755EB82D0 ON tapi_phone (default_user_id);
CREATE INDEX IDX_9AFB9A3764D218E ON tapi_phone (location_id);
CREATE UNIQUE INDEX UNIQ_9AFB9A37A6A12EC1 ON tapi_phone (registered_user_id);

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Aigars Gedroics added a comment - 17/Feb/12 2:21 PM

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;
}

Show

Aigars Gedroics added a comment - 17/Feb/12 2:21 PM 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; }

Hide

Permalink

Benjamin Eberlei added a comment - 14/Mar/12 8:24 PM

Moved to dBAL

Show

Benjamin Eberlei added a comment - 14/Mar/12 8:24 PM Moved to dBAL

Hide

Permalink

Benjamin Eberlei added a comment - 14/Mar/12 8:28 PM

Fixed

Show

Benjamin Eberlei added a comment - 14/Mar/12 8:28 PM Fixed

schema:update fails when changing from ManyToOne to OneToOne

Details

Description

Activity

People

Dates