Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-498

orm:schema-tool --update fails (errno: 150 on MySQL 5.1.41) attempting to drop foreign keys when not necessary

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-ALPHA4
    • Fix Version/s: 2.0-BETA1
    • Component/s: Tools
    • Security Level: All
    • Labels:
      None
    • Environment:
      Windows Vista 32-bit, XAMPP (Apache, PHP 5.3.1, MySQL 5.1.41)

      Description

      Steps:
      1. Create tables using orm:schema-tool --re-create
      2. Add date_format and time_format to \Models\User
      3. Attempt to update schema using orm:schema-tool --update
      4. Receive error from MySQL: SQLSTATE[HY000]: General error: 1025 Error on rename of `.\blitzaroo#sql-a7c_e4` to `.\blitzaroo\resource` (errno: 150)

      If you take a look at the schema-tool --update --dump-sql command below you will see that it's trying to drop foreign keys when not needed.

      \Models\User

      User
      namespace Models;
      
      /** @Entity @Table(name="user") */
      class User extends \My\Model\AbstractModel implements \Zend_Acl_Role_Interface
      {
      	/** 
      	 * @Id @Column(type="integer")
      	 * @GeneratedValue(strategy="AUTO")
      	 */
      	protected $id;
      	
      	/** @Column(type="integer", unsigned=true) */
      	protected $role_id;
      	
      	/** @Column(type="string") */
      	protected $username;
      	
      	/** @Column(type="string") */
      	protected $password;
      	
      	/** @Column(type="string") */
      	protected $email;
      	
      	/** @Column(type="string") */
      	protected $date_format;
      	
      	/** @Column(type="string") */
      	protected $time_format;
      	
      	/** @Column(type="boolean") */
      	protected $calendar_start;
      	
      	/** @Column(type="datetime") */
      	protected $created;
      	
      	/** @Column(type="datetime") */
      	protected $updated;
      	
      	/**
      	 * @OneToOne(targetEntity="Role") 
      	 * @JoinColumn(name="role_id", referencedColumnName="id")
      	 */
      	protected $role;
      	
      	/**
      	 * Initialization
      	 */
      	public function init()
      	{
      		$this->created = $this->updated = new \DateTime('now');
      	}
      	
      	/**
      	 * @PreUpdate
      	 */
      	public function updated()
      	{
      		$this->updated = new \DateTime('now');
      	}
      	
      	/**
      	 * @return the $id
      	 */
      	public function getId()
      	{
      		return $this->id;
      	}
      	
      	/**
      	 * @return the $username
      	 */
      	public function getUsername()
      	{
      		return $this->username;
      	}
      	
      	/**
      	 * @return the $password
      	 */
      	public function getPassword()
      	{
      		return $this->password;
      	}
      	
      	/**
      	 * @return the $email
      	 */
      	public function getEmail()
      	{
      		return $this->email;
      	}
      	
      	/**
      	 * @return the $calendar_start
      	 */
      	public function getCalendarStart()
      	{
      		return $this->calendar_start;
      	}
      	
      	/**
      	 * @return $role_id
      	 */
      	public function getRoleId()
      	{
      		return $this->role->getName();
      	}
      	
      	/**
      	 * @return the $role
      	 */
      	public function getRole()
      	{
      		return $this->role;
      	}
      	
      	/**
      	 * @param $id the $id to set
      	 */
      	public function setId($id)
      	{
      		$this->id = $id;
      		return $this;
      	}
      	
      	/**
      	 * @param $username the $username to set
      	 */
      	public function setUsername($username)
      	{
      		$this->username = $username;
      		return $this;
      	}
      	
      	/**
      	 * @param $password the $password to set
      	 */
      	public function setPassword($password)
      	{
      		$this->password = $password;
      		return $this;
      	}
      	
      	/**
      	 * @param $email the $email to set
      	 */
      	public function setEmail($email)
      	{
      		$this->email = $email;
      		return $this;
      	}
      	
      	/**
      	 * @param $calendar_start the $calendar_start to set
      	 */
      	public function setCalendarStart($calendar_start)
      	{
      		$this->calendar_start = $calendar_start;
      		return $this;
      	}
      	
      	/**
      	 * @param $role the $role to set
      	 */
      	public function setRole($role)
      	{
      		$this->role = $role;
      		return $this;
      	}
      	
      	/**
      	 * Callback for determining if role is valid
      	 * 
      	 * @param mixed $value
      	 * @return true if $value is an instance of \Models\Role
      	 */
      	public function cbIsRole($value)
      	{
      		return $value instanceof Role;
      	}
      }
      

      orm:schema-tool --update --dump-sql

      schema_tool_update_dump_sql
      DROP INDEX parent_id ON resource
      DROP INDEX role_id ON resource
      DROP INDEX parent_id ON role
      ALTER TABLE user ADD date_format VARCHAR(255) NOT NULL, ADD time_format VARCHAR(255) NOT NULL
      DROP INDEX role_id on USER
      

      orm:schema-tool --re-create --dump-sql

      --re-create --dump-sql
      ALTER TABLE resource DROP FOREIGN KEY resource_ibfk_2
      ALTER TABLE resource DROP FOREIGN KEY resource_ibfk_1
      ALTER TABLE role DROP FOREIGN KEY role_ibfk_1
      ALTER TABLE user DROP FOREIGN KEY user_ibfk_1
      DROP TABLE resource
      DROP TABLE role
      DROP TABLE user
      CREATE TABLE resource (id INT AUTO_INCREMENT NOT NULL, parent_id INT DEFAULT NULL, role_id INT DEFAULT NULL, name VARCHAR(255) NOT NULL, actions VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDB
      CREATE TABLE role (id INT AUTO_INCREMENT NOT NULL, parent_id INT DEFAULT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDb
      CREATE TABLE user(id INT AUTO_INCREMENT NOT NULL, role_id INT NOT NULL, username VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, calendar_start TINYINT(1) NOT NULL, created DATETIME NOT NULL, updated DATETIME NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDb
      ALTER TABLE resource ADD FOREIGN KEY (parent_id) REFERENCES resource(id)
      ALTER TABLE resource ADD FOREIGN KEY (role_id) REFERENCES role(id)
      ALTER TABLE role ADD FOREIGN KEY (parent_id) REFERENCES role(id)
      ALTER TABLE user ADD FOREIGN KEY (role_id) REFERENCES role(id)
      

      Extra MySQL queries

      "MySQL"
      SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` FROM information_schema.key_column_usage k WHERE `REFERENCED_COLUMN_NAME` IS NOT NULL
      
      CONSTRAINT_NAME	COLUMN_NAME	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
      resource_ibfk_1	parent_id	resource	id
      resource_ibfk_2	role_id	role	id
      role_ibfk_1	parent_id	role	id
      user_ibfk_1	role_id	role	id
      
      SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ FROM information_schema.key_column_usage k /*!50116 INNER JOIN information_schema.referential_constraints c ON k.`CONSTRAINT_NAME` = c.constraint_name AND c.constraint_name = k.constraint_name AND c.table_name = 'user' */ WHERE `REFERENCED_COLUMN_NAME` IS NOT NULL AND k.table_name = 'user'
      
      CONSTRAINT_NAME	COLUMN_NAME	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME	update_rule	delete_rule
      user_ibfk_1	role_id	role	id	RESTRICT	RESTRICT
      

        Activity

        Hide
        Kyle Spraggs added a comment -

        If I comment lines 75 and 77 in Doctrine\DBAL\Schema\Comparator.php everything works properly and orm:schema-tool --update --dump-sql returns the proper SQL.

        ALTER TABLE user ADD date_format VARCHAR(255) NOT NULL, ADD time_format VARCHAR(255) NOT NULL

        Show
        Kyle Spraggs added a comment - If I comment lines 75 and 77 in Doctrine\DBAL\Schema\Comparator.php everything works properly and orm:schema-tool --update --dump-sql returns the proper SQL. ALTER TABLE user ADD date_format VARCHAR(255) NOT NULL, ADD time_format VARCHAR(255) NOT NULL
        Hide
        Benjamin Eberlei added a comment -

        Are you on alpha 4 or on trunk?

        Can you verify this also happens in trunk? I applied a patch to foreign key detection just 2 weeks ago or something.

        Show
        Benjamin Eberlei added a comment - Are you on alpha 4 or on trunk? Can you verify this also happens in trunk? I applied a patch to foreign key detection just 2 weeks ago or something.
        Hide
        Kyle Spraggs added a comment -

        Benjamin,

        I have tested the bug against TRUNK and all appears to work as intended. I'm going to mark this issue as fixed for 2.0-BETA1.

        Show
        Kyle Spraggs added a comment - Benjamin, I have tested the bug against TRUNK and all appears to work as intended. I'm going to mark this issue as fixed for 2.0-BETA1.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Kyle Spraggs
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: