False positives in migrations (diff task)

[mriegert]

Propel-gen diff generates a migration file which would ALTER many of our tables to the state in that they already are. The workaround would be to check and delete all unnecessary ALTER commands as long as there are no relevant changes in these tables because the getDownSQL() task will destroy some information.

• When you have a column description (DDL in a MySQL database) like CREATE TABLE.... length_in smallint(5) unsigned DEFAULT NULL COMMENT 'in m to the right', ...
• and a corresponding schema.xml generated for example from ORM Designer with a column-tag column description="in m to the right" name="length_in" type="SMALLINT" sqlType="smallint(5) unsigned"
• there will be a getUpSQL() task in the generated migration file that contains the following ALTER command: ALTER TABLE customer_station_tracks CHANGE length_out length_out smallint(5) unsigned COMMENT 'in m to the left';
• the getDownSQL() does'nt contain the description information and looks like this: ALTER TABLE customer_station_tracks CHANGE length_out length_out smallint(5) unsigned;
• the generated schema.sql (with propel-gen) contains the comments and generates a DDL like this Create Table... length_in smallint(5) unsigned COMMENT 'in m to the right',...

We have a similar issue with some MEDIUMINT type tables (know that Propel has less types than MySql):

• DDL: ... departure_lateness_diff mediumint(9) DEFAULT NULL, ...
• schema.xml: ... column name="departure_lateness_diff" type="INTEGER" sqlType="MEDIUMINT(9)" ...
• migration_file_up: ... ALTER TABLE disposition_changes CHANGE departure_lateness_diff departure_lateness_diff MEDIUMINT(9); ...
• migration_file_down: ... ALTER TABLE disposition_changes CHANGE departure_lateness_diff departure_lateness_diff SMALLINT(9); ...
• schema.sql: ... departure_lateness_diff MEDIUMINT(9), ...

Checked out and tested with several (also the actual) versions of the propel generator. Looking into the code, found a query in the MysqlSchemaParser "SHOW COLUMNS FROM " . $table->getName()"" to parse the DDL. With this query some information like the COMMENT will be discarded.

[analytik]

+1

[stanleyli60]

+1 too. I am experiencing this bug too and it is annoy to see a bunch of comment migrations in the migration files.

Do you guys have a play around with this?

[Enchufadoo]

Hi guys I'm experiencing this behavior, I just starting commenting my schema and I found for example that if I add a description to a VARCHAR field it will keep trying to run the same migration over and over for that field:

Sorry for the spanish naming, but I wanted to post the exact case I'm having

<table name="log_sistema" idMethod="native" phpName="LogSistema" description="Registro de eventos del sistema">
        <column name="id" type="INTEGER" primaryKey="true" autoIncrement="true" required="true"
                description="Identificador unico"/>
        <column name="channel" type="VARCHAR" size="255" required="true"
                description="Tipo de evento en monolog, parte de que registro es"/>
        <column name="level" type="INTEGER" defaultValue="0" required="true"
                description="Que tan grave es el registro, mas alto mas grave, algo de monolog"/>
        <column name="mensaje" size="1000" type="VARCHAR" required="true"
                description="Especifica que evento es"/>
        <column name="trace" type="VARCHAR" size="2000" required="true"
                description="En caso de ser una excepcion aca va el trace"/>
        <column name="fecha" defaultExpr="CURRENT_TIMESTAMP" type="TIMESTAMP" required="true"
                description="Fecha del evento"/>
        <column name="id_usuario" type="INTEGER" required="false"
                description="Usuario del sistema"/>
        <column name="extra" type="LONGVARCHAR" required="false"
                description="Datos json extra del evento"/>
        <column name="id_tipo" type="INTEGER" required="false"
                description="Tipo de registro en el log"/>
        <index name="id_usuario">
            <index-column name="id_usuario"/>
        </index>
        <foreign-key skipSql="true" foreignTable="users" name="id_usuario1">
            <reference skipSql="true" local="id_usuario" foreign="user_id" />
        </foreign-key>
        <index name="id_tipo">
            <index-column name="id_tipo"/>
        </index>
        <vendor type="mysql">
            <parameter name="Engine" value="InnoDB"/>
        </vendor>
    </table>

So whenever I ran the migration command:

call ..\vendor\bin\propel.bat migration:diff --skip-removed-table --config-dir=..\config --schema-dir=..\config
pause
call ..\vendor\bin\propel.bat migrate --config-dir=..\config

I will keep getting this migration script

<?php
class PropelMigration_1469061913
{
    public $comment = '';

    public function preUp($manager)
    {
        // add the pre-migration code here
    }

    public function postUp($manager)
    {
        // add the post-migration code here
    }

    public function preDown($manager)
    {
        // add the pre-migration code here
    }

    public function postDown($manager)
    {
        // add the post-migration code here
    }

    /**
     * Get the SQL statements for the Up migration
     *
     * @return array list of the SQL strings to execute for the Up migration
     *               the keys being the datasources
     */
    public function getUpSQL()
    {
        return array (
  'cartilla' => '
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `log_sistema`

  CHANGE `channel` `channel` VARCHAR(255) NOT NULL COMMENT \'Tipo de evento en monolog, parte de que registro es\',

  CHANGE `mensaje` `mensaje` VARCHAR(1000) NOT NULL COMMENT \'Especifica que evento es\',

  CHANGE `trace` `trace` VARCHAR(2000) NOT NULL COMMENT \'En caso de ser una excepcion aca va el trace\';

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
',
);
    }

    /**
     * Get the SQL statements for the Down migration
     *
     * @return array list of the SQL strings to execute for the Down migration
     *               the keys being the datasources
     */
    public function getDownSQL()
    {
        return array (
  'cartilla' => '
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `log_sistema`

  CHANGE `channel` `channel` VARCHAR(255) NOT NULL,

  CHANGE `mensaje` `mensaje` VARCHAR(1000) NOT NULL,

  CHANGE `trace` `trace` VARCHAR(2000) NOT NULL;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
',
);
    }

}

And further more some comments will not be generated on the first migration, the id and fecha fields are ignored.

[djkoza]

+1 (Removing the size property fixed the issue)