[BUG] Columns are scrambled when using TEXT data type

[wurst-hans]

Seems to be a critical bug. Stumbled upon this, because a live project crashed after trying to apply database changes.

How to reproduce
Assume there is a table:

mysql> DESCRIBE migration;
+----------+------+------+-----+---------+----------------+
| Field    | Type | Null | Key | Default | Extra          |
+----------+------+------+-----+---------+----------------+
| id       | int  | NO   | PRI | NULL    | auto_increment |
| column_1 | text | NO   |     | NULL    |                |
| column_3 | text | NO   |     | NULL    |                |
| column_4 | text | NO   |     | NULL    |                |
+----------+------+------+-----+---------+----------------+
4 rows in set (0,00 sec)

Run generate command. The migration file looks like:

public function morph(): void
{
    $this->morphTable('migration', [
        'columns' => [
            new Column(
                'id',
                [
                    'type' => Column::TYPE_INTEGER,
                    'notNull' => true,
                    'autoIncrement' => true,
                    'size' => 1,
                    'first' => true
                ]
            ),
            new Column(
                'column_1',
                [
                    'type' => Column::TYPE_TEXT,
                    'notNull' => true,
                    'after' => 'id'
                ]
            ),
            new Column(
                'column_3',
                [
                    'type' => Column::TYPE_TEXT,
                    'notNull' => true,
                    'after' => 'column_1'
                ]
            ),
            new Column(
                'column_4',
                [
                    'type' => Column::TYPE_TEXT,
                    'notNull' => true,
                    'after' => 'column_3'
                ]
            ),
        ],
        'indexes' => [
            new Index('PRIMARY', ['id'], 'PRIMARY'),
        ],
        'options' => [
            'TABLE_TYPE' => 'BASE TABLE',
            'AUTO_INCREMENT' => '',
            'ENGINE' => 'InnoDB',
            'TABLE_COLLATION' => 'utf8mb4_0900_ai_ci',
        ],
    ]);
}

Add a new column right after column_1

mysql> ALTER TABLE migration ADD column_2 TEXT NOT NULL AFTER column_1;
Query OK, 0 rows affected (0,04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE migration;
+----------+------+------+-----+---------+----------------+
| Field    | Type | Null | Key | Default | Extra          |
+----------+------+------+-----+---------+----------------+
| id       | int  | NO   | PRI | NULL    | auto_increment |
| column_1 | text | NO   |     | NULL    |                |
| column_2 | text | NO   |     | NULL    |                |
| column_3 | text | NO   |     | NULL    |                |
| column_4 | text | NO   |     | NULL    |                |
+----------+------+------+-----+---------+----------------+
5 rows in set (0,00 sec)

Use generate command again to track differences (even if it creates a full dump again, which is not ideal IMO), which would create a migration file like this:

public function morph(): void
{
    $this->morphTable('migration', [
        'columns' => [
            new Column(
                'id',
                [
                    'type' => Column::TYPE_INTEGER,
                    'notNull' => true,
                    'autoIncrement' => true,
                    'size' => 1,
                    'first' => true
                ]
            ),
            new Column(
                'column_1',
                [
                    'type' => Column::TYPE_TEXT,
                    'notNull' => true,
                    'after' => 'id'
                ]
            ),
            new Column(
                'column_2',
                [
                    'type' => Column::TYPE_TEXT,
                    'notNull' => true,
                    'after' => 'column_1'
                ]
            ),
            new Column(
                'column_3',
                [
                    'type' => Column::TYPE_TEXT,
                    'notNull' => true,
                    'after' => 'column_2'
                ]
            ),
            new Column(
                'column_4',
                [
                    'type' => Column::TYPE_TEXT,
                    'notNull' => true,
                    'after' => 'column_3'
                ]
            ),
        ],
        'indexes' => [
            new Index('PRIMARY', ['id'], 'PRIMARY'),
        ],
        'options' => [
            'TABLE_TYPE' => 'BASE TABLE',
            'AUTO_INCREMENT' => '',
            'ENGINE' => 'InnoDB',
            'TABLE_COLLATION' => 'utf8mb4_0900_ai_ci',
        ],
    ]);
}

As you can see, this file looks good, new column is added in correct place. But when I run this migration on production database now, the column column_3 is replaced with column_2.

First notice: column_3 is not dropped, it is renamed to new column (because in my test case column_2 contains exactly the same content that has been stored in column_3 before).
Second notice: In my test, that happens on TEXT columns only, but not when using INT columns.

BTW: I would appreciate if there is a quick fix, because this is a common use case for us.

[Jeckerson]

@wurst-hans Hello, thanks for report! Will try to see ASAP.

[Jeckerson]

Released in https://github.com/phalcon/migrations/releases/tag/v2.2.4