Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.6, 10.2.7, 10.2.8, 10.2.9, 10.2.10, 10.2.11
-
None
-
CentOS7
Description
In versions up to 10.2.11, the following procedure does not update the "GENERATED COLUMN" value of some records.
CREATE TABLE `t1` (
|
`col1` bigint(20) NOT NULL AUTO_INCREMENT, |
`col2` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, |
`col3` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, |
`col4` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, |
`col5` varchar(100) GENERATED ALWAYS AS (concat_ws('\n',`col3`,`col4`)) STORED, |
PRIMARY KEY (`col1`),
|
KEY i1 (col2)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
#!/bin/bash
|
for i in {1..500}; do |
mysql -u root test -e "INSERT INTO t1 (col1, col2, col3, col4) VALUES (${i}, 'AAA', '', ''); UPDATE t1 SET col3 = 'BBB', col4 = 'CCC' WHERE col1 = ${i} AND col2 = 'AAA';" |
done
|
$ mysql -u root test -e "select count(*) from t1 where col5 not like 'BBB%'" |
+----------+
|
| count(*) |
|
+----------+
|
| 31 | |
+----------+
|
$ mysql -u root test -e "select * from t1 where col1 = 499\G" |
*************************** 1. row *************************** |
col1: 499 |
col2: AAA
|
col3: BBB
|
col4: CCC
|
col5:
|
 |
$
|
If you delete the index of "i1" or delete the condition of col2 from the "UPDATE Query" where condition, it will not be reproduced, so I think this is the effect of the secondary index.