Virtuality is all messed up when chancing the column afterwards.

[claskfosmic]

I've hit some issues with NULL-values in columns which were used for in a unique key and found the explanation on https://stackoverflow.com/questions/4081783/unique-key-with-nulls. We've tried the answer from Alexander YancharukAlexander Yancharuk, which worked nice.

For the checksum, we used a virtual generated column. When creating this database and adding this column, phpMyAdmin is working fine. Howerver, when changing the virtual generated column afterwards, for example when we wan't to change the name, the virtuality is all messed up.

To Reproduce

Steps to reproduce the behavior:

1. Login into phpMyAdmin, select a database.
2. Create the table from the example in the StackOverflow post, use query below:

CREATE TABLE `employee` (
  `id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date_of_birth` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Add a new column using phpMyAdmin, like in the screenshot below. Name it "checksun" (the typo is intentional). Configure it as a virtual column and use MD5(CONCAT(name, IFNULL(date_of_birth, ''))) for the virtually and click button [ save ].

The executed query will be something like this: ALTER TABLE `employee` ADD `checksun` CHAR(32) AS (MD5(CONCAT(name, IFNULL(date_of_birth, '')))) VIRTUAL NOT NULL;

4. After adding the column, from the structure tab, choose button [ change ] for the column "checksun", because we want to edit the typo.

5. Fix the typo in the field name, change it to "checksum" and click button [ save ].

6. See error

The executed query was: ALTER TABLE `employee` CHANGE `checksun` `checksum` CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS (md5(concat(`name`,ifnull(`date_of_birth`,_utf8mb4\'\')))) VIRTUAL NOT NULL; For some reason, the empty space in the concat is not prefixed with _utf8m4b, which causes the query to fail.

Expected behavior

I expected the name of the column to change. Instead, I got an error on the virtuallity for the column. It looks like a incorrect value is placed inside the edit form. Which causes a invalid value to be sent back, causing the query to fail.

Server configuration

• Operating system: Ubuntu 20.04.1 LTS
• Kernal: Version 5.4.0-100-generic
• Web server: Apache/2.4.41(Ubuntu)
• Database version: Percona Server Linux Ver 8.0.29-21
• PHP version: 8.0.22
• phpMyAdmin version: 5.2.0

[williamdes]

Hi @claskfosmic
Thank you for this nice report !