Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-289

Wrong diff between Oracle 'Date' type and Metadata 'Date' type

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: None
    • Component/s: Platforms
    • Security Level: All
    • Labels:
      None
    • Environment:
      Oracle Express 11g and doctrine-dbal 2.2.2

      Description

      Hi,
      I want to update my schema but I am getting the following error message:

      [Doctrine\DBAL\Driver\OCI8\OCI8Exception]
      ORA-01442: column to be modified to NOT NULL is already NOT NULL

      --dump-sql says:

      ALTER TABLE CONSIGNMENTS MODIFY (SDGDATE  DATE DEFAULT NULL);

      The current table structure looks like that:

      Column Name Data Type Nullable Default Primary Key
      SDGDATE DATE Yes NULL

      Mapping definition:

      /**
       * @ORM\Column(name="SDGDATE",type="date",nullable=true)
       */
      private $sdgdate;
      

      I think there is a problem with the comparison between Oracles 'Date' type and the Metadata 'Date' type. I am used to use MySQL as DBMS but our customer uses Oracle. MySQL's 'datetime' and Oracle's 'date' type are store the same data.

      I am not sure what should be done here. Because this could lead to confusion for all Oracle user.

      Maybe there could be a request to the platform in the Schema Comparator here:

      public function diffColumn(Column $column1, Column $column2)
      {
          $changedProperties = array();
          if ( $column1->getType() != $column2->getType() ) {
              $changedProperties[] = 'type';
          }
          // ...
      }
      

        Activity

        Christian Stoller created issue -
        Hide
        Christian Stoller added a comment -

        When I put a breakpoint on the second line of the diffColumn method in my IDE and stop there during debugging I have those variable values:

        $this				Doctrine\DBAL\Schema\Comparator		
        $changedProperties		array[0]		
        $column1			Doctrine\DBAL\Schema\Column		
          _type				Doctrine\DBAL\Types\DateTimeType	<-- datetime
          _precision			integer		10	
          _scale			integer		0	
          _unsigned			boolean		0	
          _fixed			boolean		0	
          _notnull			boolean		0	
          _autoincrement		boolean		0	
          _platformOptions		array[0]		
          _comment			string		""	
          _customSchemaOptions		array[0]		
          _name				string		"SDGDATE"	
          _quoted			boolean		0	
        $column2			Doctrine\DBAL\Schema\Column		
          _type				Doctrine\DBAL\Types\DateType		<-- date
          _precision			integer		0	
          _scale			integer		0	
          _unsigned			boolean		0	
          _fixed			boolean		0	
          _notnull			boolean		0	
          _autoincrement		boolean		0	
          _platformOptions		array[1]		
          _customSchemaOptions		array[0]		
          _name				string		"SDGDATE"	
          _quoted			boolean		0	
        

        You see that there are different types. But it would be nice if the comparison would say: "Theay are equal - no diff"

        Show
        Christian Stoller added a comment - When I put a breakpoint on the second line of the diffColumn method in my IDE and stop there during debugging I have those variable values: $this Doctrine\DBAL\Schema\Comparator $changedProperties array[0] $column1 Doctrine\DBAL\Schema\Column _type Doctrine\DBAL\Types\DateTimeType <-- datetime _precision integer 10 _scale integer 0 _unsigned boolean 0 _fixed boolean 0 _notnull boolean 0 _autoincrement boolean 0 _platformOptions array[0] _comment string "" _customSchemaOptions array[0] _name string "SDGDATE" _quoted boolean 0 $column2 Doctrine\DBAL\Schema\Column _type Doctrine\DBAL\Types\DateType <-- date _precision integer 0 _scale integer 0 _unsigned boolean 0 _fixed boolean 0 _notnull boolean 0 _autoincrement boolean 0 _platformOptions array[1] _customSchemaOptions array[0] _name string "SDGDATE" _quoted boolean 0 You see that there are different types. But it would be nice if the comparison would say: "Theay are equal - no diff"
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Workflow jira [ 13741 ] jira-feedback2 [ 17611 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17611 ] jira-feedback3 [ 19964 ]
        Hide
        Benjamin Eberlei added a comment -

        This issue can't be fixed. Doctrine has this type abstraction here which prevents a special case fix here. The solution in this case is obvious, set the type to "datetime" on Oracle.

        Show
        Benjamin Eberlei added a comment - This issue can't be fixed. Doctrine has this type abstraction here which prevents a special case fix here. The solution in this case is obvious, set the type to "datetime" on Oracle.
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Can't Fix [ 7 ]
        Hide
        Christian Stoller added a comment -

        Sorry, this issue commes up again.

        Shipment:
          type: entity
          fields:
            id:
              type: integer
              id: true
              generator:
                strategy: AUTO
            pickupDate:
              type: date
              column: pickup_date
            pickupTimeFrom:
              type: time
              column: pickup_time_from
          lifecycleCallbacks: {  }
        

        When I update my schema with the above YAML mapping, I always get the following error although I haven't changed anything at the mapping.


        C:\projects\xyz\trunk>php app\console doctrine:schema:update --force
        Updating database schema...
        Der Befehl "stty" ist entweder falsch geschrieben oder
        konnte nicht gefunden werden.

        [Doctrine\DBAL\Driver\OCI8\OCI8Exception]
        ORA-01442: column to be modified to NOT NULL is already NOT NULL

        C:\projects\osl\trunk>php app\console doctrine:schema:update --dump-sql
        ALTER TABLE SHIPMENT MODIFY (pickup_date DATE NOT NULL, pickup_time_from DATE NOT NULL)

        Okay, I could change the types to 'datetime', but what if I have to move to MySQL? I just want to store 'date' and 'time', not 'datetime'.

        Show
        Christian Stoller added a comment - Sorry, this issue commes up again. Shipment: type: entity fields: id: type: integer id: true generator: strategy: AUTO pickupDate: type: date column: pickup_date pickupTimeFrom: type: time column: pickup_time_from lifecycleCallbacks: { } When I update my schema with the above YAML mapping, I always get the following error although I haven't changed anything at the mapping. C:\projects\xyz\trunk>php app\console doctrine:schema:update --force Updating database schema... Der Befehl "stty" ist entweder falsch geschrieben oder konnte nicht gefunden werden. [Doctrine\DBAL\Driver\OCI8\OCI8Exception] ORA-01442: column to be modified to NOT NULL is already NOT NULL C:\projects\osl\trunk>php app\console doctrine:schema:update --dump-sql ALTER TABLE SHIPMENT MODIFY (pickup_date DATE NOT NULL, pickup_time_from DATE NOT NULL) Okay, I could change the types to 'datetime', but what if I have to move to MySQL? I just want to store 'date' and 'time', not 'datetime'.
        Christian Stoller made changes -
        Resolution Can't Fix [ 7 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Hide
        Steve Müller added a comment -

        I think the problem is not the types used but the ALTER TABLE statement generated for Oracle. I think this has been fixed in the current master.

        See: https://github.com/doctrine/dbal/commit/eee502c9ef34322c12607dafb4e1ef1ee8ea8daa

        Can you please have a look if this still occurrs with current master?

        Show
        Steve Müller added a comment - I think the problem is not the types used but the ALTER TABLE statement generated for Oracle. I think this has been fixed in the current master. See: https://github.com/doctrine/dbal/commit/eee502c9ef34322c12607dafb4e1ef1ee8ea8daa Can you please have a look if this still occurrs with current master?
        Steve Müller made changes -
        Status Reopened [ 4 ] Awaiting Feedback [ 10000 ]
        Hide
        Christian Stoller added a comment -

        Sorry, I can't test it anymore, because the project is not set up anymore. But the change seems to be the solution
        I think the issue could be closed.

        Show
        Christian Stoller added a comment - Sorry, I can't test it anymore, because the project is not set up anymore. But the change seems to be the solution I think the issue could be closed.
        Hide
        Steve Müller added a comment -

        Christian Stoller This ticket is somehow in status "Awaiting Feedback". I cannot resolve this. Can you please check if you can do anything about the status. Otherwise this ticket will still show up as unresolved.

        Show
        Steve Müller added a comment - Christian Stoller This ticket is somehow in status "Awaiting Feedback". I cannot resolve this. Can you please check if you can do anything about the status. Otherwise this ticket will still show up as unresolved.
        Hide
        Christian Stoller added a comment -

        Sorry, I have no permissions to close the bug. I'll contact Benjamin

        Show
        Christian Stoller added a comment - Sorry, I have no permissions to close the bug. I'll contact Benjamin
        Benjamin Eberlei made changes -
        Status Awaiting Feedback [ 10000 ] Open [ 1 ]
        Hide
        Benjamin Eberlei added a comment -

        Was fixed in another ticket

        Show
        Benjamin Eberlei added a comment - Was fixed in another ticket
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]

        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=DBAL-289, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Christian Stoller
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: