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