Debian Codegraph @

mysql-test/suite/rpl/r/rpl_functional_index.result

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