Debian Codegraph @

mysql-test/suite/rpl/r/rpl_invisible_indexes.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 an index
7 CREATE TABLE t1 ( i INT , KEY (i));
8 INSERT INTO t1 VALUES (2),(3),(5),(7),(11),(13);
9 ANALYZE TABLE t1;
10 Table Op Msg_type Msg_text
11 test.t1 analyze status OK
12 include/sync_slave_sql_with_master.inc
13 [Connection Slave]
14 # Check that the index is on the slave
15 SHOW INDEXES FROM t1;
16 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
17 t1 1 i 1 i A 6 NULL NULL YES BTREE YES NULL
18 [Connection Master]
19 # Make the index invisible
20 ALTER TABLE t1 ALTER INDEX i INVISIBLE;
21 ANALYZE TABLE t1;
22 Table Op Msg_type Msg_text
23 test.t1 analyze status OK
24 include/sync_slave_sql_with_master.inc
25 [Connection Slave]
26 # Verify that the index is invisible on the slave
27 SHOW INDEXES FROM t1;
28 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
29 t1 1 i 1 i A 6 NULL NULL YES BTREE NO NULL
30 EXPLAIN SELECT * FROM t1 WHERE i=3;
31 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
32 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
33 Warnings:
34 Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where (`test`.`t1`.`i` = 3)
35 [Connection Master]
36 ALTER TABLE t1 ALTER INDEX i VISIBLE;
37 ANALYZE TABLE t1;
38 Table Op Msg_type Msg_text
39 test.t1 analyze status OK
40 include/sync_slave_sql_with_master.inc
41 [Connection Slave]
42 # Verify that the index is visible on the slave
43 SHOW INDEXES FROM t1;
44 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
45 t1 1 i 1 i A 6 NULL NULL YES BTREE YES NULL
46 EXPLAIN SELECT * FROM t1 WHERE i=3;
47 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
48 1 SIMPLE t1 NULL ref i i 5 const 1 100.00 Using index
49 Warnings:
50 Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where (`test`.`t1`.`i` = 3)
51 [Connection Master]
52 # Create an invisible index on the table
53 CREATE UNIQUE INDEX idx ON t1(i) INVISIBLE;
54 include/sync_slave_sql_with_master.inc
55 [Connection Slave]
56 # Verify that the invisible is created on the slave
57 SHOW INDEXES FROM t1;
58 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
59 t1 0 idx 1 i A 6 NULL NULL YES BTREE NO NULL
60 t1 1 i 1 i A 6 NULL NULL YES BTREE YES NULL
61 [Connection Master]
62 ALTER TABLE t1 DROP INDEX i, ALTER INDEX idx VISIBLE, ALTER INDEX idx INVISIBLE, ALTER INDEX idx VISIBLE;
63 ANALYZE TABLE t1;
64 Table Op Msg_type Msg_text
65 test.t1 analyze status OK
66 include/sync_slave_sql_with_master.inc
67 [Connection Slave]
68 SHOW INDEXES FROM t1;
69 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
70 t1 0 idx 1 i A 6 NULL NULL YES BTREE YES NULL
71 [Connection Master]
72 # Alter the engine
73 ALTER TABLE t1 ALTER INDEX idx INVISIBLE, ALTER INDEX idx VISIBLE;
74 ANALYZE TABLE t1;
75 Table Op Msg_type Msg_text
76 test.t1 analyze status OK
77 include/sync_slave_sql_with_master.inc
78 [Connection Slave]
79 SHOW INDEXES FROM t1;
80 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
81 t1 0 idx 1 i A 6 NULL NULL YES BTREE YES NULL
82 [Connection Master]
83 # Clean up
84 DROP TABLE t1;
85 include/sync_slave_sql_with_master.inc
86 [Connection Slave]
87 SELECT * FROM t1;
88 ERROR 42S02: Table 'test.t1' doesn't exist
89 include/rpl_end.inc