Debian Codegraph @

mysql-test/suite/rpl/t/rpl_replica_stored_generated_columns.test

raw
1 # ==== Purpose ====
2 #
3 # The purpose of this test is to ensure that stored generated columns are
4 # correctly updated when a row is applied on the replica side.
5 #
6 # ==== Requirements ====
7 #
8 # R1. Stored generated columns that exist only on the replica must be
9 # updated after a `Write_event` is successfully applied.
10 #
11 # R1. Stored generated columns that exist only on the replica must be
12 # updated after an `Update_event` is successfully applied.
13 #
14 # R3. Stored generated columns that also exist on the source must be
15 # updated after a `Write_event` is successfully applied.
16 #
17 # R4. Stored generated columns that also exist on the source must be
18 # updated after an `Update_event` is successfully applied.
19 #
20 # ==== Implementation ====
21 #
22 # Setup
23 # -----
24 # 1. Create a table on the source.
25 # 2. Add stored and virtual generated columns to the given table, on the
26 # replica.
27 #
28 # TC1. Stored generated columns are updated after a `Write_rows_event`
29 # --------------------------------------------------------------------
30 # 1. Insert 10 rows on the source.
31 # 2. Ensure that the count of rows where the stored generated columns is
32 # not null is 10, on the replica.
33 #
34 # TC2. Stored generated columns are updated after an `Update_rows_event`
35 # ----------------------------------------------------------------------
36 # 1. Update the 10 rows on the source.
37 # 2. Ensure that the count of rows where the stored generated columns is
38 # not null is 10, on the replica.
39 #
40 # TC3. Stored generated columns are removed after a `Delete_rows_event`
41 # ---------------------------------------------------------------------
42 # 1. Delete the 10 rows on the source.
43 # 2. Ensure that the count of rows is 0.
44 #
45 # ==== References ====
46 #
47 # BUG#32292413 GENERATED COLUMNS (VIRTUAL & STORED) DO NOT UPDATE ON
48 # REPLICA
49 #
50
51 --source include/have_binlog_format_row.inc
52 --source include/master-slave.inc
53
54 # Setup
55 # -----
56 # 1. Create a table on the source.
57 CREATE TABLE `log_data` (
58 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
59 `series` INT UNSIGNED NOT NULL,
60 `tm_ts` VARCHAR(14) DEFAULT NULL,
61 `data` BLOB,
62 `v1_gcol` INT GENERATED ALWAYS AS (`series` + 1) VIRTUAL NOT NULL,
63 `s1_gcol` INT GENERATED ALWAYS AS (`series` + 1) STORED NOT NULL,
64 `v2_gcol` INT GENERATED ALWAYS AS (`series` + 2) VIRTUAL NOT NULL,
65 `s2_gcol` INT GENERATED ALWAYS AS (`series` + 2) STORED NOT NULL,
66 PRIMARY KEY (`id`),
67 KEY `v1_idx` (`v1_gcol`),
68 KEY `s1_idx` (`s1_gcol`)
69 ) ENGINE = InnoDB;
70
71 # 2. Add stored and virtual generated columns to the given table, on the
72 # replica.
73 --source include/sync_slave_sql_with_master.inc
74 ALTER TABLE
75 `log_data`
76 MODIFY
77 `v1_gcol` INT GENERATED ALWAYS AS (`series` - 1) VIRTUAL NOT NULL,
78 MODIFY
79 `s1_gcol` INT GENERATED ALWAYS AS (`series` - 1) STORED NOT NULL,
80 MODIFY
81 `v2_gcol` INT GENERATED ALWAYS AS (`series` + 3) VIRTUAL NOT NULL,
82 MODIFY
83 `s2_gcol` INT GENERATED ALWAYS AS (`series` + 3) STORED NOT NULL,
84 ADD
85 COLUMN `s_len` INT GENERATED ALWAYS AS (
86 LENGTH(`data`)
87 ) STORED,
88 ADD
89 COLUMN `s_date` DATE GENERATED ALWAYS AS (
90 CAST(`tm_ts` AS DATE)
91 ) STORED,
92 ADD
93 COLUMN `v_date` DATE GENERATED ALWAYS AS (
94 CAST(`tm_ts` AS DATE)
95 ) VIRTUAL,
96 ADD
97 COLUMN `s_hash` char(40) GENERATED ALWAYS AS (
98 SHA(
99 HEX(`data`)
100 )
101 ) STORED,
102 ADD
103 COLUMN `v_hash` char(40) GENERATED ALWAYS AS (
104 SHA(
105 HEX(`data`)
106 )
107 ) VIRTUAL,
108 ADD
109 KEY `k_s_len` (s_len),
110 ADD
111 KEY `k_s_date` (s_date),
112 ADD
113 KEY `k_v_date`(v_date),
114 ADD
115 KEY `k_s_hash`(s_hash),
116 ADD
117 KEY `k_v_hash`(v_hash);
118
119 # TC1. Stored generated columns are updated after a `Write_rows_event`
120 # --------------------------------------------------------------------
121 --source include/rpl_connection_master.inc
122 # 1. Insert 10 rows on the source.
123 INSERT INTO `log_data` (`series`, `tm_ts`, `data`)
124 SELECT *
125 FROM
126 (
127 WITH RECURSIVE sequence AS (
128 SELECT
129 1 AS level
130 UNION ALL
131 SELECT
132 level + 1
133 FROM
134 sequence
135 WHERE
136 level < 10
137 )
138 SELECT
139 1,
140 CURDATE()+ 0,
141 REPEAT(
142 rand(),
143 3
144 )
145 FROM
146 sequence
147 ) AS F;
148
149 --source include/sync_slave_sql_with_master.inc
150 # 2. Ensure that the count of rows where the stored generated columns is
151 # not null is 10, on the replica.
152 --let $updated_count = `SELECT COUNT(1) FROM log_data WHERE s_date IS NOT NULL AND s_hash IS NOT NULL`
153 --let $assert_text = Stored columns not NULL count is 10
154 --let $assert_cond = "$updated_count" = "10"
155 --source include/assert.inc
156 --let $updated_count = `SELECT COUNT(1) FROM log_data WHERE v2_gcol = 4 AND s2_gcol = 4`
157 --let $assert_text = Stored columns with series equal to 4 count is 10
158 --let $assert_cond = "$updated_count" = "10"
159 --source include/assert.inc
160 --let $updated_count = `SELECT COUNT(1) FROM log_data WHERE v1_gcol = 0 AND s1_gcol = 0`
161 --let $assert_text = Stored columns with series equal to 0 count is 10
162 --let $assert_cond = "$updated_count" = "10"
163 --source include/assert.inc
164
165 # TC2. Stored generated columns are updated after an `Update_rows_event`
166 # ----------------------------------------------------------------------
167 --source include/rpl_connection_master.inc
168 # 1. Update the 10 rows on the source.
169 UPDATE `log_data` SET `tm_ts` = "20210101", `series` = 2;
170
171 --source include/sync_slave_sql_with_master.inc
172 # 2. Ensure that the count of rows where the stored generated columns is
173 # not null is 10, on the replica.
174 --let $updated_count = `SELECT COUNT(1) FROM log_data WHERE s_date = "2021-01-01"`
175 --let $assert_text = Stored columns with s_date equal to 2021-01-01 count is 10
176 --let $assert_cond = "$updated_count" = "10"
177 --source include/assert.inc
178 --let $updated_count = `SELECT COUNT(1) FROM log_data WHERE v2_gcol = 5 AND s2_gcol = 5`
179 --let $assert_text = Stored columns with series equal to 5 count is 10
180 --let $assert_cond = "$updated_count" = "10"
181 --source include/assert.inc
182 --let $updated_count = `SELECT COUNT(1) FROM log_data WHERE v1_gcol = 1 AND s1_gcol = 1`
183 --let $assert_text = Stored columns with series equal to 1 count is 10
184 --let $assert_cond = "$updated_count" = "10"
185 --source include/assert.inc
186
187 # TC3. Stored generated columns are removed after a `Delete_rows_event`
188 # ---------------------------------------------------------------------
189 --source include/rpl_connection_master.inc
190 # 1. Delete the 10 rows on the source.
191 DELETE FROM `log_data` WHERE `id` < 11;
192
193 --source include/sync_slave_sql_with_master.inc
194 # 2. Ensure that the count of rows is 0.
195 --let $updated_count = `SELECT COUNT(1) FROM log_data`
196 --let $assert_text = Count is 0
197 --let $assert_cond = "$updated_count" = "0"
198 --source include/assert.inc
199
200 --source include/rpl_connection_master.inc
201 DROP TABLE `log_data`;
202 --source include/rpl_end.inc