Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Using Literal for creating virtual columns throws error on MariaDB #2157

Closed
oskarkregar opened this issue Dec 20, 2022 · 3 comments · May be fixed by #2158
Closed

Using Literal for creating virtual columns throws error on MariaDB #2157

oskarkregar opened this issue Dec 20, 2022 · 3 comments · May be fixed by #2158

Comments

@oskarkregar
Copy link

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
Copy link
Member

Feel free to make a PR here with suggested changes

@oskarkregar oskarkregar mentioned this issue Dec 20, 2022
Open
@oskarkregar
Copy link
Author

#2158 PR for the issue

@dereuromark
Copy link
Member

dereuromark commented Dec 20, 2022 •
edited

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

add fix for mariadb virtual column creation oskarkregar/phinx
2 participants
@dereuromark @oskarkregar