Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-167

Schema comparator doesn't work properly with columnDefinition's

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.0.8, 2.1, 2.1.1, 2.1.2
    • Fix Version/s: None
    • Security Level: All
    • Labels:
      None

      Description

      Schema comparator will mostly always return changed properties on columns for entities defined with columnDefinition even they are identical in the DB. This is due to weak low-lever compatibility of SchemaTool#getCreateSchemaSql() and SchemaTool#getSchemaFromMetadata() – the first one doesn't reconstruct columnDefinition, and the other one never supports 'fixed', 'default', cannot determine, whether it is boolean or integer (ex. TINYINT in the DB), etc...

      All this results in extremely annoying unnecessary alter-table-change-columns surrounded by dropping and after that re-enabling constrains dependent on those columns.

      I mean stuff like this:

      symfony2#app/console doctrine:schema:update --dump-sql
      ...
      ALTER TABLE es_hotels DROP FOREIGN KEY FK_527F88EE584598A3F92F3E70;
      ALTER TABLE es_hotels DROP FOREIGN KEY FK_527F88EE584598A37A3ABE5D;
      ALTER TABLE es_hotels DROP FOREIGN KEY FK_527F88EE584598A3EE551564;
      ALTER TABLE es_hotels CHANGE is_active is_active TINYINT(1) NOT NULL DEFAULT '1', CHANGE checksum checksum CHAR(32) DEFAULT NULL;
      ALTER TABLE es_hotels ADD CONSTRAINT FK_527F88EE584598A3F92F3E70 FOREIGN KEY (operator_id, country_id) REFERENCES es_countries(operator_id, id) ON DELETE CASCADE;
      ALTER TABLE es_hotels ADD CONSTRAINT FK_527F88EE584598A37A3ABE5D FOREIGN KEY (operator_id, resort_id) REFERENCES es_resorts(operator_id, id) ON DELETE CASCADE;
      ALTER TABLE es_hotels ADD CONSTRAINT FK_527F88EE584598A3EE551564 FOREIGN KEY (operator_id, subresort_id) REFERENCES es_subresorts(operator_id, id) ON DELETE CASCADE;
      ...
      

      The simple solution would be to fix schema comparator not to signal any changes on columns with columnDefinition properties.
      But would be much and much better to add some code to all *SchemaManager#getPortableTableColumnDefinition so they would reconstuct columnDefinition and they would be matched in the schema comparator.

      I can do this

        Activity

        Hide
        Roderick Schaefer | We handle IT added a comment -

        I'm having the same issue on my production webserver, but not on the development webserver. I find that odd. It tries to drop all foreign keys and create them again, although without the CHANGE statement you are referring to, Dmitry.

        Show
        Roderick Schaefer | We handle IT added a comment - I'm having the same issue on my production webserver, but not on the development webserver. I find that odd. It tries to drop all foreign keys and create them again, although without the CHANGE statement you are referring to, Dmitry.
        Hide
        Benjamin Eberlei added a comment -

        This maybe fixable by making a hash out of the column definition and saving it into a database comment.

        The Foreign Key problem maybe because of an old MySQL version 5.0.x

        Show
        Benjamin Eberlei added a comment - This maybe fixable by making a hash out of the column definition and saving it into a database comment. The Foreign Key problem maybe because of an old MySQL version 5.0.x
        Hide
        Joe Cai added a comment -

        @beberlei, sounds good to me. any plan of implementing this?

        Show
        Joe Cai added a comment - @beberlei, sounds good to me. any plan of implementing this?

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Dmitry Strygin
          • Votes:
            3 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: