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
|
#
# ==== Purpose ====
#
# The test checks if different operations on rows with generated fields as json,
# geometry, double and json using json function are completed without errors or
# memory leaks when slave_rows_search_algorithms uses hash scan.
#
# ==== References ====
#
# BUG#26280724 ASSERT FAILURE WHEN REPLICATING TABLE WITH VIRTUAL JSON COLUMN
# BUG#25873029 HEAP-USE-AFTER-FREE IN RPL.RPL_JSON, RPL.RPL_VIRTUAL_GCOL
#
--source include/have_binlog_format_row.inc
--source include/master-slave.inc
--source include/rpl_connection_slave.inc
set @saved_slave_rows_search_algorithms= @@global.slave_rows_search_algorithms;
SET GLOBAL slave_rows_search_algorithms= 'INDEX_SCAN,HASH_SCAN';
--source include/rpl_connection_master.inc
#
# Generated column as json
#
CREATE TABLE gtable (doc JSON);
INSERT INTO gtable (doc) VALUES
('{"id": 1, "name": "abcd efgh"}'),
('{"id": 2, "name": "abcd ijk"}');
ALTER TABLE gtable ADD COLUMN gtext JSON
GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.name')) VIRTUAL;
--source include/sync_slave_sql_with_master.inc
# Update one of the rows
--source include/rpl_connection_master.inc
UPDATE gtable SET doc = JSON_SET(doc, '$.name', 'abcd lmnopqrstuvxz')
WHERE JSON_EXTRACT(doc, '$.id') = 2;
--source include/sync_slave_sql_with_master.inc
# Insert a null row and modify it
--source include/rpl_connection_master.inc
INSERT INTO gtable(doc) VALUES (NULL);
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_master.inc
UPDATE gtable SET doc = '{ "id": 4, "name": "abc aba" }' where doc IS NULL;
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_master.inc
UPDATE gtable SET doc = NULL WHERE JSON_EXTRACT(doc, '$.id') = 4;
--source include/sync_slave_sql_with_master.inc
# Cleanup
--source include/rpl_connection_master.inc
DROP TABLE gtable;
--source include/sync_slave_sql_with_master.inc
#
# Generated column as a json (array)
#
--source include/rpl_connection_master.inc
CREATE TABLE gtable(id INT, doc JSON, gcol JSON AS (JSON_ARRAY(id, doc)));
INSERT INTO gtable(id, doc) VALUES(10, '{"a": 1}');
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_master.inc
UPDATE gtable SET doc = JSON_REPLACE(gcol, '$[0].a',"a");
--source include/sync_slave_sql_with_master.inc
# Cleanup
--source include/rpl_connection_master.inc
DROP TABLE gtable;
--source include/sync_slave_sql_with_master.inc
#
# Generated as geometry
#
--source include/rpl_connection_master.inc
CREATE TABLE gtable (doc TEXT);
INSERT INTO gtable (doc) VALUES ('point(1 1)'), ('point(2 2)');
ALTER TABLE gtable ADD COLUMN gtext GEOMETRY
GENERATED ALWAYS AS (ST_GEOMFROMTEXT(doc)) VIRTUAL;
UPDATE gtable SET doc = 'POINT(3 3)' WHERE ST_X(ST_GEOMFROMTEXT(doc)) = 2;
INSERT INTO gtable(doc) VALUES (NULL);
UPDATE gtable SET doc = 'POINT(4 4)' where doc IS NULL;
UPDATE gtable SET doc = NULL WHERE ST_X(ST_GEOMFROMTEXT(doc)) = 4;
--source include/sync_slave_sql_with_master.inc
# Cleanup
--source include/rpl_connection_master.inc
DROP TABLE gtable;
--source include/sync_slave_sql_with_master.inc
#
# Generated column as double
#
--source include/rpl_connection_master.inc
CREATE TABLE gtable(a DOUBLE,
b DOUBLE AS (a * a));
INSERT INTO gtable (a) VALUES (1);
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_master.inc
UPDATE gtable SET a= 2;
--source include/sync_slave_sql_with_master.inc
# Cleanup
--source include/rpl_connection_master.inc
DROP TABLE gtable;
--source include/sync_slave_sql_with_master.inc
#
# Generated column as geometry using a geometry function that reads from another
# geometry object
#
--source include/rpl_connection_master.inc
CREATE TABLE gtable(geo GEOMETRY, gcol GEOMETRY AS (GEOMETRYCOLLECTION(geo, geo)));
INSERT INTO gtable (geo) VALUES (POINT(1, 1));
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_master.inc
UPDATE gtable SET geo = LINESTRING(POINT(1, 1), POINT(2, 2));
--source include/sync_slave_sql_with_master.inc
# Cleanup
--source include/rpl_connection_master.inc
DROP TABLE gtable;
--source include/sync_slave_sql_with_master.inc
#
# Generated column as a JSON using JSON function that reads from another JSON
# object
#
# Save defaults and setup mts
SET @replica_parallel_type_saved= @@GLOBAL.REPLICA_PARALLEL_TYPE;
SET @replica_parallel_workers_saved= @@GLOBAL.REPLICA_PARALLEL_WORKERS;
SET @slave_preserver_commit_order_saved= @@GLOBAL.REPLICA_PRESERVE_COMMIT_ORDER;
--source include/stop_slave.inc
SET GLOBAL replica_parallel_type= 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers= 4;
SET GLOBAL replica_preserve_commit_order= ON;
--source include/start_slave.inc
CALL mtr.add_suppression("This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'");
--source include/rpl_connection_master.inc
CREATE TABLE gtable(id INT, jd JSON, td TEXT,
gcol JSON AS (JSON_OBJECT(id,jd,JSON_KEYS(jd),td)));
INSERT INTO gtable(id,jd,td) VALUES(100, '{"a":1}', 'characters');
--source include/sync_slave_sql_with_master.inc
# Warnings are disabled so that the warning regarding the lack of support for
# the sorting of non-scalar JSON values is not printed
--disable_warnings
--let diff_tables=master:gtable, slave:gtable
--source include/diff_tables.inc
--enable_warnings
--source include/rpl_connection_master.inc
UPDATE gtable SET jd = JSON_REPLACE(jd, '$[0].a',"a");
--source include/sync_slave_sql_with_master.inc
--disable_warnings
--let diff_tables=master:gtable, slave:gtable
--source include/diff_tables.inc
--enable_warnings
--source include/rpl_connection_master.inc
UPDATE gtable SET jd= JSON_SET(jd, '$.key', '["h","e","l","l","o"]');
--source include/sync_slave_sql_with_master.inc
--disable_warnings
--let diff_tables=master:gtable, slave:gtable
--source include/diff_tables.inc
--enable_warnings
# Cleanup
--source include/rpl_connection_master.inc
DROP TABLE gtable;
--source include/sync_slave_sql_with_master.inc
SET @@global.slave_rows_search_algorithms= @saved_slave_rows_search_algorithms;
--source include/stop_slave.inc
SET @@GLOBAL.REPLICA_PARALLEL_TYPE= @replica_parallel_type_saved;
--disable_warnings
SET @@GLOBAL.REPLICA_PARALLEL_WORKERS= @replica_parallel_workers_saved;
--enable_warnings
SET @@GLOBAL.REPLICA_PRESERVE_COMMIT_ORDER= @slave_preserver_commit_order_saved;
--source include/start_slave.inc
--source include/rpl_end.inc
|
