Mysql adapter: Changing the STORED status

[killua-eu]

I encountered an error when using the literal type to change columns.

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Util\Literal;

class ContactsGenerated2 extends Phinx\Migration\AbstractMigration
{
    public function change()
    {
        $this->execute('SET unique_checks=0; SET foreign_key_checks=0;');
        $this->table('t_contacts', [
                'id' => false,
                'primary_key' => ['c_uid'],
                'engine' => 'InnoDB',
                'encoding' => 'utf8mb4',
                'collation' => 'utf8mb4_0900_ai_ci',
                'row_format' => 'DYNAMIC',
            ])
            ->changeColumn('c_fn', Literal::from("varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (`c_json`->>'$.fn') VIRTUAL"), [
                'null' => true,
            ])
            ->update();
        $this->execute('SET unique_checks=1; SET foreign_key_checks=1;');
    }
}

As long as I change only the json path c_json->>'$.to.something.else', all works well, but changing VIRTUAL to STORED or vice-versa fails with

PDOException: SQLSTATE[HY000]: General error: 3106 'Changing the STORED status' is not supported for generated columns. in /srv/varwwwhtml/glued-skeleton/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:194...

I think that when the STORED status is changed, the changed column needs to get dropped and recreated.

[garas]

Phinx doesn't handle generated columns.

You should removeColumn(), then addColumn().

[killua-eu]

Well actually Phinx handles generated columns well enough, just not consistently. There's support through the literal type that kind of works. Sometimes changeColumn() is just fine, on other occasions removeColumn() and addColumn() is required. From what I've seen so far here a list of what works with changeColumn() and where it does not:

• [x] change a GENERATED VIRTUAL column into a differently GENERATED VIRTUAL column
• [x] change a GENERATED STORED column into a differently GENERATED STORED column
• [x] change a classical (STORED) column into a GENERATED STORED column
• [x] change a GENERATED STORED column into classical STORED column
• [ ] change a classical (STORED) column into a GENERATED VIRTUAL column
• [ ] change a GENERATED STORED column into a GENERATED VIRTUAL column

Since a majority of the use cases work already, I'd really welcome if phinx would eventually support the latter two cases where things fail. Maybe this could be a nice addition for v0.13?

[garas]

It is limitations of MySQL:

• Virtual generated columns cannot be altered to stored generated columns, or vice versa. To work around this, drop the column, then add it with the new definition.
• Nongenerated columns can be altered to stored but not virtual generated columns.
• Stored but not virtual generated columns can be altered to nongenerated columns. The stored generated values become the values of the nongenerated column.

Phinx doesn't handle generated columns, but support comes through column definition with Literal::from().

[killua-eu]

You're rephrasing what I wrote, so its hard not to agree :) Does the fact that mysql has limitations prevent phinx from picking up a workaround to achieve consistent behaviour? I believe that being able to rely on consistency even though the underlying database is lacking is one of the best reasons for using phinx, don't you?

[garas]

To add workarounds, Phinx needs generated columns support first.

[zdenekgebauer]

I found another problem with generated columns and NULL on MariaDB :

->addColumn(
'cs', 
Literal::from("varchar(50) GENERATED ALWAYS AS (JSON_VALUE(`translations`,'$.name.cs')) VIRTUAL UNIQUE")

generates:

 ADD `cs` varchar(50) GENERATED ALWAYS AS (JSON_VALUE(`translations`,'$.name.cs')) VIRTUAL UNIQUE NULL,

which causes syntax error, because SQL must not contains "NUL" nor "NOT NUL" . Is there any way how ommit NULL/NOT NULL ?