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
106
107
108
109
110
111
112
|
################################################################################
# This is an auxillary file used by rpl_rename_index.test and
# gr_rename_index.test to test index rename with GR and rpl.
#
# Steps:
# 1. Create table on master with index (FULLTEXT/SPATIAL/SIMPLE INDEX).
# 2. Rename the index using ALTER TABLE..RENAME INDEX command.
# 3. Check that index is renamed sucessfully on both the servers.
# 4. Clean-up
#
# Usage:
# --let $master= server1
# --let $slave = server2
# --source extra/rpl_tests/rpl_rename_index.inc
#
# $master - master server.
# $slave - slave server.
################################################################################
--let $rename_index= 0
while ($rename_index < 3)
{
--echo
--echo #1. Create table on master with index
--let $rpl_connection_name= $master
--source include/rpl_connection.inc
if ($rename_index == 0)
{
CREATE TABLE t1 (a int PRIMARY KEY, b char(10), FULLTEXT KEY (b));
INSERT INTO t1 VALUES (1,'abc'), (2, 'def'), (3,'ghi');
--let $update_query= UPDATE t1 SET b='mysql' WHERE b='abc'
}
if ($rename_index == 1)
{
CREATE TABLE t1 (a int PRIMARY KEY, b GEOMETRY NOT NULL SRID 0);
CREATE SPATIAL INDEX b ON t1 (b);
INSERT INTO t1 VALUES (1, ST_GEOMFROMText('POINT(1 1)')),
(2, ST_GEOMFROMTEXT('POLYGON((1 2,5 4,9 9,1 9,1 2))')),
(3, ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'));
--let $update_query= UPDATE t1 SET b=ST_GEOMFROMText('POINT(5 5)') WHERE b=ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)')
}
if ($rename_index == 2)
{
CREATE TABLE t1 ( a INT PRIMARY KEY, b int, KEY (b));
INSERT INTO t1 VALUES (1,10),(2,20),(3,30);
--let $update_query= UPDATE t1 SET b=11 WHERE b=10
}
# Check that t1 has a index named b on both the servers.
--let $assert_text= There should be a index named b on table t1.
--let $assert_cond= [SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = "test" AND TABLE_NAME = "t1" AND INDEX_NAME= "b"]=1;
--source include/assert.inc
--source include/rpl_sync.inc
--let $rpl_connection_name= $slave
--source include/rpl_connection.inc
--let $assert_text= There should be a index named b on table t1.
--let $assert_cond= [SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = "test" AND TABLE_NAME = "t1" AND INDEX_NAME= "b"]=1;
--source include/assert.inc
--echo
--echo #2. Rename the index using ALTER TABLE..RENAME INDEX command.
--let $rpl_connection_name= $master
--source include/rpl_connection.inc
ALTER TABLE t1 RENAME index b to idx;
--echo
--echo #3. Check that index is renamed sucessfully on both the servers.
--let $assert_text= There should not be a index named b on table t1.
--let $assert_cond= [SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = "test" AND TABLE_NAME = "t1" AND INDEX_NAME= "b"]=0;
--source include/assert.inc
--let $assert_text= There should be a index named idx on table t1.
--let $assert_cond= [SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = "test" AND TABLE_NAME = "t1" AND INDEX_NAME= "idx" ]=1;
--source include/assert.inc
--source include/rpl_sync.inc
--let $rpl_connection_name= $slave
--source include/rpl_connection.inc
--let $assert_text= There should not be a index named b on table t1.
--let $assert_cond= [SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = "test" AND TABLE_NAME = "t1" AND INDEX_NAME= "b" ]=0;
--source include/assert.inc
--let $assert_text= There should be a index named idx on table t1.
--let $assert_cond= [SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = "test" AND TABLE_NAME = "t1" AND INDEX_NAME= "idx" ]=1;
--source include/assert.inc
# Perform another dml operation on t1.
--let $rpl_connection_name= $master
--source include/rpl_connection.inc
--eval $update_query
# Ensure that data is consistent on both the servers
--source include/rpl_sync.inc
--let $diff_tables=$master:t1, $slave:t1
--source include/diff_tables.inc
--echo
--echo #4. Clean-up
DROP TABLE t1;
--source include/rpl_sync.inc
--inc $rename_index
}
|
