Using Literal for creating virtual columns throws error on MariaDB

[oskarkregar]

When using Literal class to create a virtual column in MariaDB like so:

$this->table['imaginary_table']->addColumn('imaginary_column', Literal::from("varchar(30) COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`json_column`, '$.json_value'))) VIRTUAL"));

it throws MySQL error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL

as NOT NULL is not allowed after creation of virtual column. NOT NULL is by default added on

phinx/src/Phinx/Db/Adapter/MysqlAdapter.php

Line 1365 in 343645c

$def .= $column->isNull() ? ' NULL' : ' NOT NULL';

Virtual column is set as NULL in MariaDB as default and cannot be changed so also if create statement is rewritten to:

$this->table['imaginary_table']->addColumn('imaginary_column', Literal::from("varchar(30) COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`json_column`, '$.json_value'))) VIRTUAL", [
    'null' => true
]));

note ['null' => true] as the option, it returns an error of

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL

I suggest rewriting:

phinx/src/Phinx/Db/Adapter/MysqlAdapter.php

Line 1365 in 343645c

$def .= $column->isNull() ? ' NULL' : ' NOT NULL';

to:

if (!($column->getType() instanceof Literal) || strpos($this->getConnection()->getAttribute(PDO::ATTR_SERVER_VERSION), "MariaDB") === false) {
    $def .= $column->isNull() ? ' NULL' : ' NOT NULL';
}

Tested on MariaDB and MySQL and works as expected.

Any thoughts?

Regards, Oskar

[dereuromark]

Feel free to make a PR here with suggested changes

[oskarkregar]

#2158 PR for the issue

[dereuromark]

PR open for discussion, please add the explanations there in the opening comment.