Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3040

doctrine:schema:update datetimetz field type not null

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Can't Fix
    • Affects Version/s: 2.4.2
    • Fix Version/s: 2.4.2
    • Component/s: ORM
    • Security Level: All

      Description

      I have some fields like

          /**
           * Adding date in format ISO-8601 YYYY-MM-DDThh:mm:ss±hhmm
           *
           * @var \DateTime
           *
           * @ORM\Column(name="added_at", type="datetimetz", nullable=false)
           */
          private $addedAt;
      
          /**
           * Expire date in format ISO-8601 YYYY-MM-DDThh:mm:ss±hhmm
           *
           * @var \DateTime
           *
           * @ORM\Column(name="expired_at", type="datetimetz", nullable=false)
           */
          private $expiredAt;
      

      @ORM\Column -> nullable=false

      In database this field already not null
      But when i execute in console:

      ./app/console doctrine:schema:update --dump-sql --env=dev

      answer:

      ALTER TABLE test CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;

      If I change datetimetz to datetime type fot $expiredAt
      and execute:

      ./app/console doctrine:schema:update --dump-sql --env=dev

      answer:

      ALTER TABLE test CHANGE added_at added_at DATETIME NOT NULL;

        Activity

        Hide
        Coroliov Oleg added a comment -

        have some news about this problem ?

        Show
        Coroliov Oleg added a comment - have some news about this problem ?
        Hide
        Marco Pivetta added a comment -

        Coroliov Oleg does the DDL update statement persist in the diffs even after running it?

        Show
        Marco Pivetta added a comment - Coroliov Oleg does the DDL update statement persist in the diffs even after running it?
        Hide
        Coroliov Oleg added a comment -
        $ ./app/console doctrine:schema:update --dump-sql --env=dev
        ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
        ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
        ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
        ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
        ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
        ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
        ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        
        $ ./app/console doctrine:schema:update --force
        Updating database schema...
        Database schema updated successfully! "10" queries were executed
        
        $ ./app/console doctrine:schema:update --dump-sql --env=dev
        ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
        ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
        ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
        ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
        ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
        ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
        ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        

        or if i use additional bundle

        $ ./app/console doctrine:schema:update --dump-sql --env=dev
        ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
        ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
        ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
        ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
        ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
        ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
        ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        
        $ ./app/console doctrine:migrations:diff
        Generated new migration class to "/Users/ruscon/projects/xxx/app/DoctrineMigrations/Version20140407004542.php" from schema differences.
        
        $ ./app/console doctrine:migrations:migrate
        
                            Application Migrations
        
        
        WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y
        Migrating up to 20140407004542 from 20140405144932
        
          ++ migrating 20140407004542
        
             -> ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL
             -> ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL
             -> ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL
             -> ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL
             -> ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL
             -> ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL
             -> ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL
             -> ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL
             -> ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL
             -> ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL
        
          ++ migrated (3.46s)
        
          ------------------------
        
          ++ finished in 3.46
          ++ 1 migrations executed
          ++ 10 sql queries
        
        $ ./app/console doctrine:schema:update --dump-sql --env=dev
        ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
        ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
        ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
        ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
        ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
        ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
        ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
        ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        
        Show
        Coroliov Oleg added a comment - $ ./app/console doctrine:schema:update --dump-sql --env=dev ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL; ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL; ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL; ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL; ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL; ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL; ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL; ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL; $ ./app/console doctrine:schema:update --force Updating database schema... Database schema updated successfully! "10" queries were executed $ ./app/console doctrine:schema:update --dump-sql --env=dev ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL; ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL; ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL; ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL; ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL; ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL; ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL; ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL; or if i use additional bundle $ ./app/console doctrine:schema:update --dump-sql --env=dev ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL; ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL; ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL; ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL; ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL; ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL; ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL; ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL; $ ./app/console doctrine:migrations:diff Generated new migration class to "/Users/ruscon/projects/xxx/app/DoctrineMigrations/Version20140407004542.php" from schema differences. $ ./app/console doctrine:migrations:migrate Application Migrations WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue ? (y/n)y Migrating up to 20140407004542 from 20140405144932 ++ migrating 20140407004542 -> ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL -> ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL -> ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL -> ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL -> ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL -> ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL -> ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL -> ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL -> ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL -> ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL ++ migrated (3.46s) ------------------------ ++ finished in 3.46 ++ 1 migrations executed ++ 10 sql queries $ ./app/console doctrine:schema:update --dump-sql --env=dev ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL; ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL; ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL; ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL; ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL; ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL; ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL; ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL; ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
        Hide
        Steve Müller added a comment - - edited

        I assume from the syntax that you are using MySQL. MySQL does not have a native type for DateTimeTz, therefore the mapping always falls back to the native DATETIME type. See the DBAL documentation (footnote 15): http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#mapping-matrix

        The problem here is not the nullable flag but the type you use. Therefore you get the irrgeular update statements from the schema tool. Use Doctrine's "datetime" type instead and you'll be fine. You won't be able to store time zone information for a date time in MySQL anyways as it does not have a type for this.

        Show
        Steve Müller added a comment - - edited I assume from the syntax that you are using MySQL. MySQL does not have a native type for DateTimeTz, therefore the mapping always falls back to the native DATETIME type. See the DBAL documentation (footnote 15): http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#mapping-matrix The problem here is not the nullable flag but the type you use. Therefore you get the irrgeular update statements from the schema tool. Use Doctrine's "datetime" type instead and you'll be fine. You won't be able to store time zone information for a date time in MySQL anyways as it does not have a type for this.
        Hide
        Coroliov Oleg added a comment -

        Steve Müller, you right. Thanks.

        Show
        Coroliov Oleg added a comment - Steve Müller, you right. Thanks.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: