MySQL Forums
Forum List  »  Replication

Re: Mysql 8.0.22 - Indexes on virtual columns do not update
Posted by: Vasilis Kioustelidis
Date: December 11, 2020 10:35AM

I've managed to replicate the behaviour by creating a small lab using docker. I've created two docker instances named olap_replica_v8.0.22_a and olap_replica_v8.0.22_b. The instance olap_replica_v8.0.22_a holds the 'original' data and olap_replica_v8.0.22_b is the replica that contains the same table+generated columns+indexes.

The original data table on olap_replica_v8.0.22_a is as follows:
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| ID | int unsigned | NO | PRI | NULL | auto_increment |
| TM_TS | varchar(14) | YES | | NULL | |
| DATA | blob | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+


Next I created the replica table on olap_replica_v8.0.22_b as follows:
+--------+--------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------------------+
| ID | int unsigned | NO | PRI | NULL | auto_increment |
| TM_TS | varchar(14) | YES | | NULL | |
| DATA | blob | YES | | NULL | |
| s_date | date | YES | MUL | NULL | STORED GENERATED |
| v_date | date | YES | MUL | NULL | VIRTUAL GENERATED |
| s_hash | char(40) | YES | MUL | NULL | STORED GENERATED |
| v_hash | char(40) | YES | MUL | NULL | VIRTUAL GENERATED |
+--------+--------------+------+-----+---------+-------------------+

And the indexes on the computed columns:
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| log_data | 0 | PRIMARY | 1 | ID | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |
| log_data | 1 | k_s_date | 1 | s_date | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| log_data | 1 | k_s_hash | 1 | s_hash | A | 10 | NULL | NULL | YES | BTREE | | | YES | NULL |
| log_data | 1 | k_v_date | 1 | v_date | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| log_data | 1 | k_v_hash | 1 | v_hash | A | 10 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Finally I populated the original data using random values:
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+
| ID | TM_TS | DATA |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 20201211 | 0x302E36313537343138363136323632363534302E36313537343138363136323632363534302E36313537343138363136323632363534 |
| 2 | 20201211 | 0x302E3331313939303536343937393630343033302E3331313939303536343937393630343033302E3331313939303536343937393630343033 |
| 3 | 20201211 | 0x302E37313237323732373037353238363839302E37313237323732373037353238363839302E37313237323732373037353238363839 |
| 4 | 20201211 | 0x302E36323736363436383935353931373732302E36323736363436383935353931373732302E36323736363436383935353931373732 |
| 5 | 20201211 | 0x302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238 |
| 6 | 20201211 | 0x302E3131373731343239333431303733353438302E3131373731343239333431303733353438302E3131373731343239333431303733353438 |
| 7 | 20201211 | 0x302E35383831343634393839373435343931302E35383831343634393839373435343931302E35383831343634393839373435343931 |
| 8 | 20201211 | 0x302E35383735383936343533373431383431302E35383735383936343533373431383431302E35383735383936343533373431383431 |
| 9 | 20201211 | 0x302E3137333530383736303638303931383336302E3137333530383736303638303931383336302E3137333530383736303638303931383336 |
| 10 | 20201211 | 0x302E3130343737343839383031353638343335302E3130343737343839383031353638343335302E3130343737343839383031353638343335 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+


The same rows on the replica however are different (stored columns come up as NULL) and the indexes do not work:
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+--------+------------+--------+------------------------------------------+
| ID | TM_TS | DATA | s_date | v_date | s_hash | v_hash |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+--------+------------+--------+------------------------------------------+
| 1 | 20201211 | 0x302E36313537343138363136323632363534302E36313537343138363136323632363534302E36313537343138363136323632363534 | NULL | 2020-12-11 | NULL | 768b42b27c912d774b4112c94c000235384a86da |
| 2 | 20201211 | 0x302E3331313939303536343937393630343033302E3331313939303536343937393630343033302E3331313939303536343937393630343033 | NULL | 2020-12-11 | NULL | 89fb345354fa330cfa83ceee888f6da10f0aaf3e |
| 3 | 20201211 | 0x302E37313237323732373037353238363839302E37313237323732373037353238363839302E37313237323732373037353238363839 | NULL | 2020-12-11 | NULL | 50656f30f4265bf1e330b8e54eff3c7e7e941cb7 |
| 4 | 20201211 | 0x302E36323736363436383935353931373732302E36323736363436383935353931373732302E36323736363436383935353931373732 | NULL | 2020-12-11 | NULL | 3db8647974731b9185310c779517eed590a2643e |
| 5 | 20201211 | 0x302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238 | NULL | 2020-12-11 | NULL | bfbc30a6d13f8e21ad9263f8f6c70c2de5f26e8d |
| 6 | 20201211 | 0x302E3131373731343239333431303733353438302E3131373731343239333431303733353438302E3131373731343239333431303733353438 | NULL | 2020-12-11 | NULL | aa1194aaa2f95c6bf1f050c5bcac8899249b8d9e |
| 7 | 20201211 | 0x302E35383831343634393839373435343931302E35383831343634393839373435343931302E35383831343634393839373435343931 | NULL | 2020-12-11 | NULL | 9a2d77ed383dc5731cd740ca585b67b77e760306 |
| 8 | 20201211 | 0x302E35383735383936343533373431383431302E35383735383936343533373431383431302E35383735383936343533373431383431 | NULL | 2020-12-11 | NULL | caed9858d0278b5a18c6a580bb6cca7716da6acb |
| 9 | 20201211 | 0x302E3137333530383736303638303931383336302E3137333530383736303638303931383336302E3137333530383736303638303931383336 | NULL | 2020-12-11 | NULL | a757ea18725fa483f7d5ffd257db8eff6583b685 |
| 10 | 20201211 | 0x302E3130343737343839383031353638343335302E3130343737343839383031353638343335302E3130343737343839383031353638343335 | NULL | 2020-12-11 | NULL | f8e3f8936f3918f3409375df01921f567ae2a875 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+--------+------------+--------+------------------------------------------+

mysql> select * from virtual_test.log_data where v_date=CURDATE();
Empty set (0.00 sec)

mysql> select * from virtual_test.log_data where s_date=CURDATE();
Empty set (0.01 sec)

mysql> select * from virtual_test.log_data where v_hash='f8e3f8936f3918f3409375df01921f567ae2a875';
Empty set (0.01 sec)

mysql> select * from virtual_test.log_data where v_hash=s_hash;
Empty set (0.00 sec)

mysql> select v_hash,s_hash from virtual_test.log_data where v_hash=s_hash;
Empty set (0.00 sec)

Finally, I recreated all generated columns and indexes and the values were computed correctly:

ALTER TABLE virtual_test.log_data DROP COLUMN `s_date`,DROP COLUMN `v_date`,DROP COLUMN `s_hash`,DROP COLUMN `v_hash`, DROP KEY `k_s_date`,DROP KEY `k_v_date`,DROP KEY `k_s_hash`,DROP KEY `k_v_hash`, ADD COLUMN `s_date` date GENERATED ALWAYS AS (cast(`tm_ts` as date)) STORED, ADD COLUMN `v_date` date GENERATED ALWAYS AS (cast(`tm_ts` as date)) VIRTUAL, ADD COLUMN `s_hash` char(40) GENERATED ALWAYS AS (sha(hex(`DATA`))) STORED, ADD COLUMN `v_hash` char(40) GENERATED ALWAYS AS (sha(hex(`DATA`))) VIRTUAL, ADD KEY `k_s_date`(s_date), ADD KEY `k_v_date`(v_date), ADD KEY `k_s_hash`(s_hash), ADD KEY `k_v_hash`(v_hash) ;

mysql> select * from virtual_test.log_data where v_date=CURDATE();
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------+------------------------------------------+
| ID | TM_TS | DATA | s_date | v_date | s_hash | v_hash |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------+------------------------------------------+
| 1 | 20201211 | 0x302E36313537343138363136323632363534302E36313537343138363136323632363534302E36313537343138363136323632363534 | 2020-12-11 | 2020-12-11 | 768b42b27c912d774b4112c94c000235384a86da | 768b42b27c912d774b4112c94c000235384a86da |
| 2 | 20201211 | 0x302E3331313939303536343937393630343033302E3331313939303536343937393630343033302E3331313939303536343937393630343033 | 2020-12-11 | 2020-12-11 | 89fb345354fa330cfa83ceee888f6da10f0aaf3e | 89fb345354fa330cfa83ceee888f6da10f0aaf3e |
| 3 | 20201211 | 0x302E37313237323732373037353238363839302E37313237323732373037353238363839302E37313237323732373037353238363839 | 2020-12-11 | 2020-12-11 | 50656f30f4265bf1e330b8e54eff3c7e7e941cb7 | 50656f30f4265bf1e330b8e54eff3c7e7e941cb7 |
| 4 | 20201211 | 0x302E36323736363436383935353931373732302E36323736363436383935353931373732302E36323736363436383935353931373732 | 2020-12-11 | 2020-12-11 | 3db8647974731b9185310c779517eed590a2643e | 3db8647974731b9185310c779517eed590a2643e |
| 5 | 20201211 | 0x302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238 | 2020-12-11 | 2020-12-11 | bfbc30a6d13f8e21ad9263f8f6c70c2de5f26e8d | bfbc30a6d13f8e21ad9263f8f6c70c2de5f26e8d |
| 6 | 20201211 | 0x302E3131373731343239333431303733353438302E3131373731343239333431303733353438302E3131373731343239333431303733353438 | 2020-12-11 | 2020-12-11 | aa1194aaa2f95c6bf1f050c5bcac8899249b8d9e | aa1194aaa2f95c6bf1f050c5bcac8899249b8d9e |
| 7 | 20201211 | 0x302E35383831343634393839373435343931302E35383831343634393839373435343931302E35383831343634393839373435343931 | 2020-12-11 | 2020-12-11 | 9a2d77ed383dc5731cd740ca585b67b77e760306 | 9a2d77ed383dc5731cd740ca585b67b77e760306 |
| 8 | 20201211 | 0x302E35383735383936343533373431383431302E35383735383936343533373431383431302E35383735383936343533373431383431 | 2020-12-11 | 2020-12-11 | caed9858d0278b5a18c6a580bb6cca7716da6acb | caed9858d0278b5a18c6a580bb6cca7716da6acb |
| 9 | 20201211 | 0x302E3137333530383736303638303931383336302E3137333530383736303638303931383336302E3137333530383736303638303931383336 | 2020-12-11 | 2020-12-11 | a757ea18725fa483f7d5ffd257db8eff6583b685 | a757ea18725fa483f7d5ffd257db8eff6583b685 |
| 10 | 20201211 | 0x302E3130343737343839383031353638343335302E3130343737343839383031353638343335302E3130343737343839383031353638343335 | 2020-12-11 | 2020-12-11 | f8e3f8936f3918f3409375df01921f567ae2a875 | f8e3f8936f3918f3409375df01921f567ae2a875 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------+------------------------------------------+

Vasilis Kioustelidis

Technical Director

EDPS S.A.
+30-210-8993660, Mob: +30-6944709598,Fax:+30-210-8993662

Email:kioustelidis@edps.gr URL:http://www.edps.gr

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Mysql 8.0.22 - Indexes on virtual columns do not update
396
December 11, 2020 10:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.