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
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
SET SESSION sql_log_bin= 0;
call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
SET SESSION sql_log_bin= 1;
include/sync_slave_sql_with_master.inc
CALL mtr.add_suppression(".*Column 1 of table .* cannot be converted from type.*");
CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
SET @@global.slave_rows_search_algorithms="TABLE_SCAN,INDEX_SCAN";
Warnings:
Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
[connection master]
FLUSH LOGS;
SET binlog_row_image = MINIMAL;
# Test non-nullable MV index
CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
UNIQUE INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DROP TABLE t1;
# Test nullable MV index
CREATE TABLE t1 (id INT, c INT, j JSON,
UNIQUE INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
INSERT INTO t1 VALUES (3, 3, NULL);
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DROP TABLE t1;
# Test non-unique MV index
CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DROP TABLE t1;
# Test composite MV index
CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY)), id));
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
include/sync_slave_sql_with_master.inc
[Connection Slave]
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
Variable_name Value
Replica_rows_last_search_algorithm_used TABLE_SCAN
[connection master]
DROP TABLE t1;
SET @@global.slave_rows_search_algorithms= "INTITAL_ROW_SEARCH_CONF";
Warnings:
Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
FLUSH LOGS;
include/sync_slave_sql_with_master.inc
SET @@global.slave_rows_search_algorithms= "INTITAL_ROW_SEARCH_CONF";
Warnings:
Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
include/rpl_end.inc
|
