[DDC-2551] schema-tool does not match join columns with identifier options Created: 13/Jul/13  Updated: 16/Jul/13  Resolved: 15/Jul/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Atans Chiu Assignee: Marco Pivetta
Resolution: Can't Fix Votes: 0
Labels: schematool
Environment:

Zend Framework 2
Doctrine ORM 2.3.4



 Description   
/**
 * Role
 *
 * @ORM\Entity
 * @ORM\Table(name="rbac_role", options={"collate"="utf8_general_ci"})
 * @package User\Entity
 */
class Role
{
    /**
     * @ORM\Id
     * @ORm\Column(name="role_id", type="integer", options={"unsigned"=true})
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    protected $id;

    /**
     * @ORM\Column(name="role_name", name="string", length=32, nullable=true)
     * @var string
     */
    protected $name = null;


    /**
     * @ORM\ManyToOne(targetEntity="Role")
     * @ORM\JoinColumn(name="parent_role_id", referencedColumnName="id", nullable=true)
     * @var Role
     */
    protected $parentRole = null;
$ php vendor/doctrine/orm/bin/doctrine.php orm:schema-tool:update --force

Updating database schema...



  [Doctrine\DBAL\DBALException]
  An exception occurred while executing 'ALTER TABLE rbac_role ADD CONSTRAINT
   FK_C55D6FF2A44B56EA FOREIGN KEY (parent_role_id) REFERENCES rbac_role (id)
  ':

  SQLSTATE[HY000]: General error: 1005 Can't create table 'rbac.#sql-98_24b'
  (errno: 150)






  [PDOException]
  SQLSTATE[HY000]: General error: 1005 Can't create table 'rbac.#sql-98_24b'
  (errno: 150)

Problem here: ====================================================

ALTER TABLE rbac_role ADD CONSTRAINT
   FK_C55D6FF2A44B56EA FOREIGN KEY (parent_role_id) REFERENCES rbac_role (id)

 ==> `rbac_role (id)` should be `rbac_role (role_id)


 Comments   
Comment by Marco Pivetta [ 15/Jul/13 ]

You can make your join column unsigned by using the `columnDefinition` property.

Comment by Marco Pivetta [ 15/Jul/13 ]

The schema tool can't use the column options to match join columns with identifier columns.

Column options are one step further and cannot really be applied transitively to join columns, since they may contain vendor specific settings.

Comment by Atans Chiu [ 15/Jul/13 ]

Thanks.


@ORM\JoinColumn(
 name="parent_role_id",
  referencedColumnName="id",
  nullable=true,
  columnDefinition="ALTER TABLE rbac_role ADD CONSTRAINT FK_C55D6FF2A44B56EA FOREIGN KEY (parent_role_id) REFERENCES rbac_role (role_id)"
)

Is this right ?

Comment by Marco Pivetta [ 15/Jul/13 ]

No. Your problem is that the join coulm is signed, whereas the referenced identifier column is unsigned.

Comment by Atans Chiu [ 15/Jul/13 ]

I do not understand, how can I do ?

Comment by Marco Pivetta [ 15/Jul/13 ]

Simple solution: just drop the

options={"unsigned"=true}

from your annotations

Comment by Atans Chiu [ 16/Jul/13 ]

Thanks Marco Pivetta.

Comment by Atans Chiu [ 16/Jul/13 ]

Marco Pivetta, It is the same problem (2.4.0-RC2).

/**
 * Role
 *
 * @ORM\Entity
 * @ORM\Table(name="rbac_role", options={"collate"="utf8_general_ci"})
 * @package User\Entity
 */
class Role
{
    /**
     * @ORM\Id
     * @ORm\Column(name="role_id", type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    protected $id;

    /**
     * @ORM\Column(name="role_name", name="string", length=32, nullable=true)
     * @var string
     */
    protected $name = null;


    /**
     * @ORM\ManyToOne(targetEntity="Role")
     * @ORM\JoinColumn(name="parent_role_id", referencedColumnName="id", nullable=true)
     * @var Role
     */
    protected $parentRole = null;

    /**
     * @ORM\ManyToMany(targetEntity="Permission")
     * @ORM\JoinTable(
     *  name="rbac_role_permission",
     *  joinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id")},
     *  inverseJoinColumns={@ORM\JoinColumn(name="perm_id", referencedColumnName="id")}
     * )
     * @var Permission[]
     */
    protected $permissions;
$ php vendor/doctrine/orm/bin/doctrine.php orm:schema-tool:update --dump-sql
ALTER TABLE rbac_role ADD CONSTRAINT FK_C55D6FF2A44B56EA FOREIGN KEY (parent
e_id) REFERENCES rbac_role (id);
ALTER TABLE rbac_role_permission CHANGE role_id role_id INT NOT NULL;
ALTER TABLE rbac_role_permission ADD CONSTRAINT FK_C31A0CF0D60322AC FOREIGN
(role_id) REFERENCES rbac_role (id);
ALTER TABLE rbac_role_permission ADD CONSTRAINT FK_C31A0CF0FA6311EF FOREIGN
(perm_id) REFERENCES rbac_permission (id);

rbac_role (id) does not change to rbac_role (role_id)

Comment by Marco Pivetta [ 16/Jul/13 ]

That's normal. I already marked the issue as "won't fix". Please don't use the issue tracker as a helpdesk.

Comment by Atans Chiu [ 16/Jul/13 ]

Oh, sorry

Generated at Thu Dec 18 23:12:55 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.