Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-237

schema:update fails when changing from ManyToOne to OneToOne

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major 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

        Hide
        Aigars Gedroics added a comment -

        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 - 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
        Benjamin Eberlei added a comment -

        Moved to dBAL

        Show
        Benjamin Eberlei added a comment - Moved to dBAL
        Hide
        Benjamin Eberlei added a comment -

        Fixed

        Show
        Benjamin Eberlei added a comment - Fixed

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Matt Lehner
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: