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

Closed
doctrinebot opened this Issue Apr 6, 2010 · 4 comments

2 participants

@doctrinebot

Jira issue originally created by user spiffyjr:

Steps:
1. Create tables using orm:schema-tool --re-create
2. Add dateformat and timeformat 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

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

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

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

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
@doctrinebot

Comment created by spiffyjr:

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 dateformat VARCHAR(255) NOT NULL, ADD timeformat VARCHAR(255) NOT NULL

@doctrinebot

Comment created by @beberlei:

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.

@doctrinebot

Comment created by spiffyjr:

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.

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.0-BETA1 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment