Debian Codegraph @

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

raw
1 --source include/have_binlog_format_row.inc
2 --source include/master-slave.inc
3 --source include/have_debug.inc
4 SET SESSION sql_log_bin= 0;
5 call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
6 --let $initial_master_rows_search = `SELECT @@global.slave_rows_search_algorithms`
7 SET SESSION sql_log_bin= 1;
8 --source include/sync_slave_sql_with_master.inc
9 CALL mtr.add_suppression(".*Column 1 of table .* cannot be converted from type.*");
10 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
11 --let $initial_slave_rows_search = `SELECT @@global.slave_rows_search_algorithms`
12 SET @@global.slave_rows_search_algorithms="TABLE_SCAN,INDEX_SCAN";
13 --source include/rpl_connection_master.inc
14
15 FLUSH LOGS;
16
17 SET binlog_row_image = MINIMAL;
18
19 --echo # Test non-nullable MV index
20 CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
21 UNIQUE INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
22 INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
23 UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
24 --source include/sync_slave_sql_with_master.inc
25 --echo [Connection Slave]
26 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
27 --source include/rpl_connection_master.inc
28 DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
29 --source include/sync_slave_sql_with_master.inc
30 --echo [Connection Slave]
31 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
32 --source include/rpl_connection_master.inc
33 DROP TABLE t1;
34
35 --echo # Test nullable MV index
36 CREATE TABLE t1 (id INT, c INT, j JSON,
37 UNIQUE INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
38 INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
39 INSERT INTO t1 VALUES (3, 3, NULL);
40 UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
41 --source include/sync_slave_sql_with_master.inc
42 --echo [Connection Slave]
43 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
44 --source include/rpl_connection_master.inc
45 DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
46 --source include/sync_slave_sql_with_master.inc
47 --echo [Connection Slave]
48 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
49 --source include/rpl_connection_master.inc
50 DROP TABLE t1;
51
52 --echo # Test non-unique MV index
53 CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
54 INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
55 INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
56 INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
57 UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
58 --source include/sync_slave_sql_with_master.inc
59 --echo [Connection Slave]
60 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
61 --source include/rpl_connection_master.inc
62 DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
63 --source include/sync_slave_sql_with_master.inc
64 --echo [Connection Slave]
65 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
66 --source include/rpl_connection_master.inc
67 DROP TABLE t1;
68
69 --echo # Test composite MV index
70 CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
71 INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY)), id));
72 INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
73 INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
74 UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
75 --source include/sync_slave_sql_with_master.inc
76 --echo [Connection Slave]
77 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
78 --source include/rpl_connection_master.inc
79 DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
80 --source include/sync_slave_sql_with_master.inc
81 --echo [Connection Slave]
82 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
83 --source include/rpl_connection_master.inc
84 DROP TABLE t1;
85
86 --replace_result $initial_master_rows_search INTITAL_ROW_SEARCH_CONF
87 --eval SET @@global.slave_rows_search_algorithms= "$initial_master_rows_search"
88 FLUSH LOGS;
89 --source include/sync_slave_sql_with_master.inc
90 --replace_result $initial_slave_rows_search INTITAL_ROW_SEARCH_CONF
91 --eval SET @@global.slave_rows_search_algorithms= "$initial_slave_rows_search"
92
93 --source include/rpl_end.inc
94