Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2551

schema-tool does not match join columns with identifier options

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Can't Fix
    • Affects Version/s: 2.3.4
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
    • 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)
      

        Activity

        Hide
        Marco Pivetta added a comment -

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

        Show
        Marco Pivetta added a comment - You can make your join column unsigned by using the `columnDefinition` property.
        Hide
        Marco Pivetta added a comment -

        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.

        Show
        Marco Pivetta added a comment - 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.
        Hide
        Atans Chiu added a comment - - edited

        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 ?

        Show
        Atans Chiu added a comment - - edited 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 ?
        Hide
        Marco Pivetta added a comment -

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

        Show
        Marco Pivetta added a comment - No. Your problem is that the join coulm is signed, whereas the referenced identifier column is unsigned.
        Hide
        Atans Chiu added a comment -

        I do not understand, how can I do ?

        Show
        Atans Chiu added a comment - I do not understand, how can I do ?
        Hide
        Marco Pivetta added a comment -

        Simple solution: just drop the

        options={"unsigned"=true}

        from your annotations

        Show
        Marco Pivetta added a comment - Simple solution: just drop the options={ "unsigned" = true } from your annotations
        Hide
        Atans Chiu added a comment -

        Thanks Marco Pivetta.

        Show
        Atans Chiu added a comment - Thanks Marco Pivetta.
        Hide
        Atans Chiu added a comment - - edited

        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)

        Show
        Atans Chiu added a comment - - edited 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)
        Hide
        Marco Pivetta added a comment -

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

        Show
        Marco Pivetta added a comment - That's normal. I already marked the issue as "won't fix". Please don't use the issue tracker as a helpdesk.
        Hide
        Atans Chiu added a comment -

        Oh, sorry

        Show
        Atans Chiu added a comment - Oh, sorry

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Atans Chiu
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: