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

        Atans Chiu created issue -
        Atans Chiu made changes -
        Field Original Value New Value
        Description ```php
        /**
         * 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)

        ```




        Doctrine 2.4.x-dev

        ALTER TABLE... (parent_role_id) REFERENCES `rbac_role (id)` should be `rbac_role (role_id)`
        ```php
        /**
         * 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)`
        Atans Chiu made changes -
        Environment Zend Framework 2
        Doctrine 2.4.x
        Zend Framework 2
        Doctrine 2.3.4
        Atans Chiu made changes -
        Environment Zend Framework 2
        Doctrine 2.3.4
        Zend Framework 2
        Doctrine ORM 2.3.4
        Marco Pivetta made changes -
        Description ```php
        /**
         * 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)`
        {code}
        /**
         * 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;
        {code}

        {code}
        $ 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)

        {code}




        Problem here: ====================================================
        {code}
        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)
        {code}
        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.
        Marco Pivetta made changes -
        Summary doctrine schema-tool problem schema-tool does not match join columns with identifier options
        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.
        Marco Pivetta made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Assignee Benjamin Eberlei [ beberlei ] Marco Pivetta [ ocramius ]
        Fix Version/s 2.4 [ 10321 ]
        Resolution Can't Fix [ 7 ]
        Atans Chiu made changes -
        Fix Version/s 2.3.4 [ 10420 ]
        Marco Pivetta made changes -
        Affects Version/s 2.3.4 [ 10420 ]
        Marco Pivetta made changes -
        Fix Version/s 2.3.4 [ 10420 ]
        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

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-2551, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated:
              Resolved: