| 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 |