Debian Codegraph @

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

raw
1 --source include/have_debug.inc
2 --source include/master-slave.inc
3 --source include/have_grep.inc
4
5 --echo # Create a table with functional index
6 CREATE TABLE t1 (col1 INT, INDEX ((ABS(col1))));
7 INSERT INTO t1 VALUES (-12);
8
9 --echo # Show the hidden columns so we can see the computed value.
10 SET SESSION debug="+d,show_hidden_columns";
11 SELECT * FROM t1;
12 SET SESSION debug="-d,show_hidden_columns";
13
14 --echo # Sync slave with master, and verify that the table exists on the slave.
15 --source include/sync_slave_sql_with_master.inc
16
17 --echo # Show the hidden columns so we can see that the computed value is
18 --echo # reflected on the slave.
19 SET SESSION debug="+d,show_hidden_columns";
20 SELECT * FROM t1;
21 SET SESSION debug="-d,show_hidden_columns";
22
23 EXPLAIN SELECT col1 FROM t1 WHERE ABS(col1) = 12;
24
25 SET SESSION debug="+d,show_hidden_columns";
26 SHOW CREATE TABLE t1;
27 SET SESSION debug="-d,show_hidden_columns";
28
29 --source include/rpl_connection_master.inc
30 DROP TABLE t1;
31
32 SET @start_row_image_value= @@session.binlog_row_image;
33
34 --echo # Try with various row_image values
35 SET @@session.binlog_row_image= MINIMAL;
36 CREATE TABLE t1 (col1 INT, INDEX ((ABS(col1))));
37 INSERT INTO t1 VALUES (-12);
38 --source include/sync_slave_sql_with_master.inc
39
40 --echo # Show the hidden columns so we can see that the computed value is
41 --echo # reflected on the slave.
42 SET SESSION debug="+d,show_hidden_columns";
43 SELECT * FROM t1;
44 EXPLAIN SELECT col1 FROM t1 WHERE ABS(col1) = 12;
45 SHOW CREATE TABLE t1;
46 SET SESSION debug="-d,show_hidden_columns";
47
48 --source include/rpl_connection_master.inc
49 DROP TABLE t1;
50
51
52 SET @@session.binlog_row_image= noblob;
53 CREATE TABLE t1 (col1 INT, INDEX ((ABS(col1))));
54 INSERT INTO t1 VALUES (-12);
55 --source include/sync_slave_sql_with_master.inc
56
57 --echo # Show the hidden columns so we can see that the computed value is
58 --echo # reflected on the slave.
59 SET SESSION debug="+d,show_hidden_columns";
60 SELECT * FROM t1;
61 EXPLAIN SELECT col1 FROM t1 WHERE ABS(col1) = 12;
62 SHOW CREATE TABLE t1;
63 SET SESSION debug="-d,show_hidden_columns";
64
65 --source include/rpl_connection_master.inc
66 DROP TABLE t1;
67
68 SET @@session.binlog_row_image= @start_row_image_value;
69
70 --echo #
71 --echo # Bug#28207118 UNIQUE FUNCTIONAL INDEX IS NOT CORRECTLY BEING CONSIDERED
72 --echo # ON SLAVE.
73 --echo #
74 CREATE TABLE t1 (col1 INT, UNIQUE INDEX idx ((col1 + col1)));
75
76 --source include/sync_slave_sql_with_master.inc
77 INSERT INTO t1 (col1) VALUES (1);
78
79 --source include/rpl_connection_master.inc
80 call mtr.add_suppression("Replica SQL for channel '': .*Could not execute Write_rows event on table test.t1");
81 call mtr.add_suppression("Replica SQL for channel '': .*Error 'Duplicate entry '2'");
82 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
83 INSERT INTO t1 (col1) VALUES (1), (2);
84
85 --source include/rpl_connection_slave.inc
86 --let $slave_sql_errno = convert_error(ER_DUP_ENTRY)
87 --let $slave_timeout = 5
88 --source include/wait_for_slave_sql_error.inc
89 DELETE FROM t1;
90 START SLAVE;
91
92 --source include/rpl_connection_master.inc
93 INSERT INTO t1 (col1) VALUES (3), (4);
94 SELECT col1 FROM t1;
95
96 --source include/sync_slave_sql_with_master.inc
97 SELECT col1 FROM t1;
98
99 --source include/rpl_connection_master.inc
100 DROP TABLE t1;
101
102 --echo #
103 --echo # Bug#28218073 SLAVE CRASHES WHEN AN UPDATE ON SLAVE REFERS TO COLUMN
104 --echo # USED IN FUNCTIONAL INDEX.
105 --echo #
106 CREATE TABLE t(a INT, b INT, UNIQUE INDEX c ((a + b)));
107 INSERT INTO t VALUES (1, 2);
108 INSERT INTO t VALUES (2, 3);
109 INSERT INTO t VALUES (3, 4);
110
111 --source include/sync_slave_sql_with_master.inc
112 SELECT a, b FROM t;
113 --error ER_DUP_ENTRY
114 UPDATE t SET b = 5 WHERE b - a = 1;
115 SELECT a, b FROM t;
116
117 --source include/rpl_connection_master.inc
118 DROP TABLE t;
119
120 --echo #
121 --echo # Bug#28218481 UPDATE TABLE ON SLAVE DOESN'T UPDATE WHEN FUNCTIONAL INDEX
122 --echo # MATCH EXPR IN WHERE.
123 --echo #
124 CREATE TABLE t (a INT, b INT, INDEX c ((a + b)));
125 INSERT INTO t VALUES (3, 5);
126 INSERT INTO t VALUES (1, 2);
127 INSERT INTO t VALUES (2, 7);
128 SELECT a, b FROM t;
129 UPDATE t SET a = 4 WHERE a + b = 3;
130 SELECT a, b FROM t;
131
132 --source include/sync_slave_sql_with_master.inc
133 SELECT a, b FROM t;
134 UPDATE t SET a = 9 WHERE a + b = 8;
135 SELECT a, b FROM t;
136
137 --source include/rpl_connection_master.inc
138 DROP TABLE t;
139
140 --echo #
141 --echo # Bug#28243453 ASSERTION`MAP->BITMAP&&MAP2->BITMAP&&MAP->N_BITS==MAP2->
142 --echo # N_BITS' FAILED ON SLAVE
143 --echo #
144 CREATE TABLE t(a INT, b INT);
145
146 --source include/sync_slave_sql_with_master.inc
147 ALTER TABLE t ADD UNIQUE INDEX i ((a + b));
148
149 --source include/rpl_connection_master.inc
150 INSERT INTO t VALUES (1, 2);
151
152 --source include/sync_slave_sql_with_master.inc
153 SELECT a, b FROM t;
154
155 --source include/rpl_connection_master.inc
156 DROP TABLE t;
157
158 --echo #
159 --echo # Bug#29317684 REPLICATION IS SENSITIVE TO ORDER OF HIDDEN COLUMNS FOR
160 --echo # FUNCTIONAL INDEXES
161 --echo #
162 --echo # What we want to see here, is that the order of the columns (both
163 --echo # hidden and visible) are the same on master and slave, even though
164 --echo # the columns are created in different order.
165 --echo #
166 --echo # Note that this bug is only visible with row-based replication.
167 SET @@session.sql_log_bin = 0;
168 CREATE TABLE t (a INT, INDEX ((a+1)));
169 ALTER TABLE t ADD COLUMN b INT;
170 SET @@session.sql_log_bin = 1;
171 --source include/rpl_connection_slave.inc
172 SET @@session.sql_log_bin = 0;
173 CREATE TABLE t (a INT, b INT, INDEX ((a+1)));
174 SET @@session.sql_log_bin = 1;
175 --source include/rpl_connection_master.inc
176 INSERT INTO t VALUES (1, 2);
177
178 --source include/sync_slave_sql_with_master.inc
179
180 --let diff_tables= master:test.t,slave:test.t
181 --source include/diff_tables.inc
182
183 --source include/rpl_connection_master.inc
184 DROP TABLE t;
185
186 --echo #
187 --echo # Bug#107366 REPLICATION ERROR WITH HASH_SCAN ROW SEARCH ALGORITHM FOR
188 --echo # FUNCTIONAL INDEXES
189
190 --source include/rpl_connection_master.inc
191
192 CREATE TABLE t1 (
193 id INT,
194 c1 JSON NOT NULL,
195 c2 INT,
196 KEY comp ((CAST(c1 AS UNSIGNED ARRAY)))
197 ) ENGINE=InnoDB;
198
199 INSERT INTO t1 VALUES(1,'[1046, 2995]',1);
200
201 UPDATE t1 SET c2=2;
202
203 --source include/sync_slave_sql_with_master.inc
204
205 --let $row_count= `SELECT COUNT(*) FROM t1 WHERE c1->"$[0]"=1046 AND c1->"$[1]"=2995 AND c2=2;`
206 --let $assert_text= Table t1 was updated
207 --let $assert_cond= $row_count = 1
208 --source include/assert.inc
209
210 --source include/rpl_connection_master.inc
211
212 DROP TABLE t1;
213
214 --source include/rpl_end.inc