Debian Codegraph @

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

raw
1 --source include/have_binlog_format_row.inc
2 --source include/have_binlog_transaction_dependency_tracking_commit_order.inc
3 --source include/master-slave.inc
4
5
6 # Procedure to test metadata replication success against several types of data
7 #
8 --write_file $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc PROCEDURE
9 if ($cast_type == '') {
10 --die !!!ERROR IN TEST: you must set $cast_type
11 }
12 if ($insert_value == '') {
13 --die !!!ERROR IN TEST: you must set $insert_value
14 }
15
16 --source include/rpl_connection_master.inc
17 DROP TABLE t1;
18 eval CREATE TABLE t1 (
19 id INT KEY AUTO_INCREMENT,
20 j4 JSON ,
21 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS $cast_type ARRAY)))));
22 --eval INSERT INTO t1(j4) VALUES($insert_value)
23 --source include/sync_slave_sql_with_master.inc
24 --let $diff_tables= master:t1, slave:t1
25 --source include/diff_tables.inc
26 # END OF
27 PROCEDURE
28
29 SET SESSION sql_log_bin= 0;
30 call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
31 --let $initial_master_rows_search = `SELECT @@global.slave_rows_search_algorithms`
32 SET SESSION sql_log_bin= 1;
33 --source include/sync_slave_sql_with_master.inc
34 CALL mtr.add_suppression(".*Column 1 of table .* cannot be converted from type.*");
35 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
36 --let $initial_slave_rows_search = `SELECT @@global.slave_rows_search_algorithms`
37 SET @@global.slave_rows_search_algorithms="TABLE_SCAN,INDEX_SCAN";
38 FLUSH LOGS;
39 --source include/rpl_connection_master.inc
40
41 --echo #
42 --echo # WL#8955: Add multi-valied index support
43 --echo #
44 CREATE TABLE t1(pl INT AUTO_INCREMENT KEY, f1 JSON, KEY i1((CAST(f1->'$[*]' AS UNSIGNED ARRAY))));
45 insert into t1(f1) values
46 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
47 (cast('[5,7]' as json)),
48 (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
49 (cast('[9,2,7]' as json)), (cast('[1,3]' as json)),
50 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)), (cast('[8,4,3,5]' as json)),
51 (cast('[9,2,7]' as json)), (cast('[9,2,7]' as json)),
52 (cast('[1,3]' as json)),
53 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)),
54 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[9,2,7]' as json)),
55 ('[98,99]');
56 --source include/sync_slave_sql_with_master.inc
57 --echo [Connection Slave]
58 --let $diff_tables= master:t1, slave:t1
59 --source include/diff_tables.inc
60
61 analyze table t1;
62 select * from t1 where 5 member of (f1->"$[*]");
63 explain select * from t1 where 5 member of (f1->"$[*]");
64
65 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
66 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
67 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
68 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
69 --source include/rpl_connection_master.inc
70 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
71 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
72 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
73 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
74 --source include/sync_slave_sql_with_master.inc
75 --echo [Connection Slave]
76 --let $diff_tables= master:t1, slave:t1
77 --source include/diff_tables.inc
78 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
79 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
80 --source include/rpl_connection_master.inc
81 DROP TABLE t1;
82
83 SET @start_row_image_value = @@session.binlog_row_image;
84 SET @@session.binlog_row_image = MINIMAL;
85
86 SET @save_btdt= @@global.binlog_transaction_dependency_tracking;
87 SET @@global.binlog_transaction_dependency_tracking = 'WRITESET';
88
89 # Test transactions dependency is properly calculated
90 FLUSH LOGS;
91 --source include/save_binlog_position.inc
92 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON NOT NULL,
93 KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
94 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
95 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
96 INSERT INTO t1(data) VALUES ('[33,44]');
97 INSERT INTO t1(data) VALUES ('[0,99]');
98 --let $logical_timestamps = 0 1;1 2;2 3;1 4;1 5
99 --source include/assert_logical_timestamps.inc
100
101 --source include/sync_slave_sql_with_master.inc
102 --echo [Connection Slave]
103 --let $diff_tables= master:t1, slave:t1
104 --source include/diff_tables.inc
105 --echo [Connection Master]
106 --connection master
107 DROP TABLE t1;
108
109 FLUSH LOGS;
110 --source include/save_binlog_position.inc
111 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, id INT DEFAULT 0,
112 data JSON NOT NULL,
113 UNIQUE KEY zips(id, (CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
114 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
115 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
116 INSERT INTO t1(data) VALUES ('[33,44]');
117 INSERT INTO t1(data) VALUES ('[0,99]');
118 --let $logical_timestamps = 0 1;1 2;2 3;1 4;3 5
119 --source include/assert_logical_timestamps.inc
120
121 --source include/sync_slave_sql_with_master.inc
122 --echo [Connection Slave]
123 --let $diff_tables= master:t1, slave:t1
124 --source include/diff_tables.inc
125 --echo [Connection Master]
126 --connection master
127 DROP TABLE t1;
128
129 FLUSH LOGS;
130 --source include/save_binlog_position.inc
131 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, id INT DEFAULT 0,
132 data JSON NOT NULL,
133 UNIQUE KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY)), id));
134 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
135 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
136 INSERT INTO t1(data) VALUES ('[33,44]');
137 INSERT INTO t1(data) VALUES ('[0,99]');
138 --let $logical_timestamps = 0 1;1 2;2 3;1 4;3 5
139 --source include/assert_logical_timestamps.inc
140
141 --source include/sync_slave_sql_with_master.inc
142 --echo [Connection Slave]
143 --let $diff_tables= master:t1, slave:t1
144 --source include/diff_tables.inc
145 --echo [Connection Master]
146 --connection master
147 DROP TABLE t1;
148
149 FLUSH LOGS;
150 --source include/save_binlog_position.inc
151 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON NOT NULL,
152 UNIQUE KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
153 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
154 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
155 INSERT INTO t1(data) VALUES ('[]');
156 INSERT INTO t1(data) VALUES ('[0,99]');
157 --let $logical_timestamps = 0 1;1 2;2 3;1 4;3 5
158 --source include/assert_logical_timestamps.inc
159
160 --source include/sync_slave_sql_with_master.inc
161 --echo [Connection Slave]
162 --let $diff_tables= master:t1, slave:t1
163 --source include/diff_tables.inc
164 --echo [Connection Master]
165 --connection master
166 DROP TABLE t1;
167
168 FLUSH LOGS;
169 # Test that non-unique index doesn''t generate collisions
170 --source include/save_binlog_position.inc
171 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON,
172 KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
173 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
174 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
175 INSERT INTO t1(data) VALUES ('[33,44]');
176 INSERT INTO t1(data) VALUES (NULL);
177 INSERT INTO t1(data) VALUES ('[0,99]');
178 --let $logical_timestamps = 0 1;1 2;2 3;1 4;1 5;1 6
179 --source include/assert_logical_timestamps.inc
180
181 --source include/sync_slave_sql_with_master.inc
182 --echo [Connection Slave]
183 --let $diff_tables= master:t1, slave:t1
184 --source include/diff_tables.inc
185
186 --let $cast_type = CHAR(10)
187 --let $insert_value = '["foobar"]'
188 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
189
190 --let $cast_type = BINARY(10)
191 --let $insert_value = '["foobar"]'
192 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
193
194 --let $cast_type = DECIMAL
195 --let $insert_value = '[123]'
196 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
197
198 --let $cast_type = DECIMAL(4,2)
199 --let $insert_value = '[12.34]'
200 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
201
202 --let $cast_type = UNSIGNED
203 --let $insert_value = '[12]'
204 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
205
206 --let $cast_type = SIGNED
207 --let $insert_value = '[-1234]'
208 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
209
210 --let $cast_type = DATE
211 --let $insert_value = '["01-01-01"]'
212 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
213
214 --let $cast_type = TIME(6)
215 --let $insert_value = '["01:01:01.123456"]'
216 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
217
218 --let $cast_type = DATETIME(6)
219 --let $insert_value = '["01-01-01 01:01:01.123456"]'
220 --source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
221
222 --source include/rpl_connection_master.inc
223 DROP TABLE t1;
224
225 SET @@global.binlog_transaction_dependency_tracking = @save_btdt;
226 --replace_result $initial_master_rows_search INTITAL_ROW_SEARCH_CONF
227 --eval SET @@global.slave_rows_search_algorithms= "$initial_master_rows_search"
228 FLUSH LOGS;
229 --source include/sync_slave_sql_with_master.inc
230 --replace_result $initial_slave_rows_search INTITAL_ROW_SEARCH_CONF
231 --eval SET @@global.slave_rows_search_algorithms= "$initial_slave_rows_search"
232
233 --remove_file $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
234 --source include/rpl_end.inc
235 # End: