Column options are ignored for GENERATED / VIRTUAL / STORED columns

[mvorisek]

Steps to reproduce

1. create or update any GENERATED / VIRTUAL / STORED column with "NOT NULL", specifict charset or type options like "UNSINGED"
2. save

Example

Create table with one STORED column with type VARCHAR(10), collation ascii_general_ci and NOT NULL. This is currently not possible via GUI. The GUI currently also ignores these options on column def. change, resulting that all options are removed.

Expected behaviour

Configured column options should be saved as normal (not generated) columns. Columns options are fully supported by MySQL and honored / validated as for normal columns.

Actual behaviour

Column options are ignored in columns create or update via GUI.

Server configuration

any

[williamdes]

Can you try with version 4.8.1 ?
I am unable to reproduce this issue.

[mvorisek]

Issue evaluated in 4.8.1:

Create table:

CREATE TABLE ttt (
  test int(11) NOT NULL,
  testvirt varchar(11) COLLATE utf8_bin GENERATED ALWAYS AS (test) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Go to the "Structure" tab and try to change the collation of the generated column to "utf8_general_ci".

Currently the PhpMyAdmin generates:

ALTER TABLE ttt CHANGE testvirt testvirt VARCHAR(11) AS (test) VIRTUAL; 

But it should generate something like this:

ALTER TABLE ttt CHANGE testvirt testvirt VARCHAR(11) CHARACTER SET utf8
    COLLATE utf8_general_ci AS (test) VIRTUAL NULL;

[williamdes]

MariaDB does not support COLLATE utf8_bin in CREATE statement.
And the query the PMA should generate does not work in MariaDB.

Maybe this explains why this issue.

[mvorisek]

Yes, tested on MySQL. Maria added the virtual columns support sooner and the syntax and features differs.

Here is my proposal for MySQL:
d254e8a

Currently is GUI at least for me is unsuable as I need to edit the virtual column like the normal columns. The PhpMyAdmin can generate the full ALTER parameters (collation, nullable, etc.) at least for MySQL. I have no experience with Maria DB as I use MySQL only.

[williamdes]

@mvorisek Does this issue still exist (on 4.8.6-dev) ?

[mvorisek]

The issue is still presented and once virtual/generated column is changed in the edit, all the column details like collation are discarded,

[williamdes]

https://mariadb.atlassian.net/browse/MDEV-7655

CREATE TABLE `vcol_test` (
       `v_col1` varchar(255) COLLATE latin1_general_ci AS (col1) VIRTUAL,
       `col1` varchar(50) COLLATE latin1_general_ci DEFAULT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

works on MySQL 8.0 and not on MariaDB
Seems to have an issue with COLLATE and CHARSET

So this issue can no be solved in a good way

[williamdes]

Does not work:

ALTER TABLE `contact` CHANGE `zdzd1` `zdzd1` VARCHAR(22) COLLATE utf8_slovenian_ci  AS ( 1 + 1 ) VIRTUAL

ALTER TABLE `contact` CHANGE `zdzd1` `zdzd1` VARCHAR(22)  CHARACTER SET utf8 COLLATE utf8_slovenian_ci  AS ( 1 + 1 ) VIRTUAL

Works:

ALTER TABLE `contact` CHANGE `zdzd1` `zdzd1` VARCHAR(22) CHARACTER SET utf8 AS ( 1 + 1 ) VIRTUAL

[mvorisek]

MySQL handles the virtual/generated columns differently and the the PhpMyAdmin needs to do the same.

I will update the patch with Util::getServerType() === 'MySQL' condition tomorrow.

[mvorisek]

Generator fixed in PR #15275

Mysql: fixed
MariadDB: not affected

Can the PR be merged and included in the next release?

[williamdes]

@mvorisek Yes, I think so
Is the PR finished?

[mvorisek]

Yes. PR is complete. Extra attributes for MySQL are fixed, non-virtual columns or columns in other databases are untouched.

[williamdes]

Hi @mvorisek I would like to merge the PR, can you implement a check to disable collation change since it does not change for MariaDB.
Actually changing the collation creates a modal (who cares) in javascript but creates an error since we try to change the collation (and we did say in the preview SQL that it would not change)

[mvorisek]

@williamdes Please describe how to replicate.

• I tried to change collation for non-virtual string column with Maria DB - as normal, it works.
• I tried to change collation for virtual string column with Maria DB - it seems Maria DB does not accept any collation nor nullable info and the PR handles it correctly - it does not include these details in the generated SQL field.

[williamdes]

@mvidner To reproduce go to my demo server 4.8.6 (public/public)

• Run query on "MariaDB 3 (cookie)" server

CREATE TABLE `vcol_test11` (
       `v_col1` varchar(255)  AS (col1) VIRTUAL,
       `col1` varchar(50) COLLATE latin1_general_ci DEFAULT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

• Go to structure and try to change the collation of v_col1
• Click preview > NO collation (as expected)
• Close preview and click "Save"
• Accept modal
• See error
  

[mvorisek]

The same for MySQL actually. As you have written, the preview SQL it fine (and can be sucessfully commited for both MariaDB and Mysql).

The "save" generates ALTER TABLE `vcol_test11` CHANGE `v_col1` `v_col1` BLOB;. Notice the BLOB at the end even if submitted in the GUI as VARCHAR. It seems to be some issue with PhpMyAdmin and not directly related with this patch. Any idea why the SQL differs with the preview?

[williamdes]

@mvorisek Yes, there is an additional query that is added if the charset changed between the initial value of the form and the real value that is submitted

The preview is always not accurate since this query is always missing.

[mvorisek]

@williamdes Great work!

[williamdes]

@mvorisek Thanks to you !