DBAL-167: Schema comparator doesn't work properly with columnDefinition's

[doctrinebot]

Jira issue originally created by user strygin:

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:

...
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 ;-)

[doctrinebot]

• is duplicated by DBAL-1096: schema-tool:update does not understand columnDefinition correctly

[doctrinebot]

Comment created by roderick:

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.

[doctrinebot]

Comment created by @beberlei:

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

[doctrinebot]

Comment created by joec4i:

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

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.