| Bug #95218 | Virtual generated column altered unexpectedly when table definition changed | ||
|---|---|---|---|
| Submitted: | 2 May 2019 3:59 | Modified: | 2 May 2019 12:43 |
| Reporter: | Joseph Choi | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.7.25, 5.7.26 | OS: | Windows (7) |
| Assigned to: | CPU Architecture: | x86 (32-bit) | |
| Tags: | ALTER TABLE, ucs2, virtual generated column | ||
[2 May 2019 12:43]
MySQL Verification Team
Hello Joseph, Thank you for the report and test case. Verified as described with 5.7.26 build. regards, Umesh
[2 May 2019 12:44]
MySQL Verification Team
-
- 5.7.26 (lowest version checked 5.7.11 - same behavior too)
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database if not exists test;
use test;
CREATE TABLE `t1` (
Query OK, 1 row affected (0.00 sec)
mysql> use test;
`path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
`file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin
GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL,
`file_size` bigint,
PRIMARY KEY (`path`)
);Database changed
mysql> CREATE TABLE `t1` (
-> `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
-> `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin
-> GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL,
-> `file_size` bigint,
-> PRIMARY KEY (`path`)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
`file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\\',-(1))) VIRTUAL NOT NULL,
`file_size` bigint(20) DEFAULT NULL,
PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> INSERT INTO `t1` (`path`) VALUES ('\\path\\to\\file');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path | file_name |
+---------------+-----------+
| \path\to\file | file |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> ALTER TABLE `t1` MODIFY COLUMN `file_size` bigint DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
`file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\\',-(1))) VIRTUAL NOT NULL,
`file_size` bigint(20) DEFAULT '0',
PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+---------------+
| path | file_name |
+---------------+---------------+
| \path\to\file | \path\to\file |
+---------------+---------------+
1 row in set (0.00 sec)
mysql> CREATE INDEX `i1` ON `t1` (`file_name`);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
`file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\0\0\\',-(1))) VIRTUAL NOT NULL,
`file_size` bigint(20) DEFAULT '0',
PRIMARY KEY (`path`),
KEY `i1` (`file_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+---------------+
| path | file_name |
+---------------+---------------+
| \path\to\file | \path\to\file |
+---------------+---------------+
1 row in set (0.00 sec)
[2 May 2019 12:45]
MySQL Verification Team
- ALTER is DDL, changing category to DDL. Please don't change category!!
[2 May 2019 12:46]
MySQL Verification Team
- 8.0.16 - looks okay to me(at least gen col value is not messed)
bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test
Database changed
mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE `t1` (
-> `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
-> `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin
-> GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL,
-> `file_size` bigint,
-> PRIMARY KEY (`path`)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
`file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,_ucs2'\0\\',-(1))) VIRTUAL NOT NULL,
`file_size` bigint(20) DEFAULT NULL,
PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
mysql> INSERT INTO `t1` (`path`) VALUES ('\\path\\to\\file');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path | file_name |
+---------------+-----------+
| \path\to\file | file |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> ALTER TABLE `t1` MODIFY COLUMN `file_size` bigint DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
`file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,_ucs2'\0\\',-(1))) VIRTUAL NOT NULL,
`file_size` bigint(20) DEFAULT '0',
PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path | file_name |
+---------------+-----------+
| \path\to\file | file |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> CREATE INDEX `i1` ON `t1` (`file_name`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
`file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,_ucs2'\0\\',-(1))) VIRTUAL NOT NULL,
`file_size` bigint(20) DEFAULT '0',
PRIMARY KEY (`path`),
KEY `i1` (`file_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path | file_name |
+---------------+-----------+
| \path\to\file | file |
+---------------+-----------+
1 row in set (0.00 sec)
Description: The virtual generated column of ucs2 charset changed whenever the table definition is changed, either when another unrelated column is modified, or a new index is added to the table. The NULL character '\0' is prepend to the string literal within the virtual generated column defintion every time when the table definition is changed. This seems to affect ucs2 columns only, utf8 columns are not affected. It seems that when the engine rewrites the table definition, it parses the ucs2-encoded column definition with a wrong charset, and the re-encodes it back into ucs2. How to repeat: mysql> CREATE TABLE `t1` ( `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL, `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL, `file_size` bigint, PRIMARY KEY (`path`) ); mysql> INSERT INTO `t1` (`path`) VALUES ('\\path\\to\\file'); mysql> SELECT `path`, `file_name` FROM `t1`; +-------------+---------+ |path |file_name| +-------------+---------+ |\path\to\file|file | +-------------+---------+ mysql> ALTER TABLE `t1` MODIFY COLUMN `file_size` bigint DEFAULT 0; mysql> SELECT `path`, `file_name` FROM `t1`; +-------------+-------------+ |path |file_name | +-------------+-------------+ |\path\to\file|\path\to\file| +-------------+-------------+ mysql> SHOW CREATE TABLE `t1`\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL, `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\\',-(1))) VIRTUAL NOT NULL, `file_size` bigint(20) DEFAULT '0', PRIMARY KEY (`path`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci mysql> CREATE INDEX `i1` ON `t1` (`file_name`); mysql> SHOW CREATE TABLE `t1`\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL, `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\0\0\\',-(1))) VIRTUAL NOT NULL, `file_size` bigint(20) DEFAULT '0', PRIMARY KEY (`path`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci