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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
CREATE USER test2@localhost;
CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM;
CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10) ,
PARTITION p1 VALUES LESS THAN (100) ,
PARTITION p2 VALUES LESS THAN (1000) );
CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM
PARTITION BY RANGE (a)
SUBPARTITION BY HASH(a)
(PARTITION p0 VALUES LESS THAN (10)
(SUBPARTITION sp00,
SUBPARTITION sp01,
SUBPARTITION sp02,
SUBPARTITION sp03,
SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100)
(SUBPARTITION sp10 ,
SUBPARTITION sp11 ,
SUBPARTITION sp12 ,
SUBPARTITION sp13 ,
SUBPARTITION sp14 ),
PARTITION p2 VALUES LESS THAN (1000)
(SUBPARTITION sp20,
SUBPARTITION sp21,
SUBPARTITION sp22,
SUBPARTITION sp23,
SUBPARTITION sp24));
INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine");
INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen");
INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine");
INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen");
INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine");
INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine");
INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine");
INSERT INTO t_null VALUES (1, "NULL");
INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight");
INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen");
INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen");
INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight");
INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight");
INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight");
CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1;
CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2;
CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3;
CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4;
CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0;
GRANT USAGE ON *.* TO test2@localhost;
GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost;
connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
SHOW GRANTS FOR CURRENT_USER;
Grants for test2@localhost
GRANT USAGE ON *.* TO `test2`@`localhost`
GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO `test2`@`localhost`
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
SELECT * FROM t_10;
a b
2 Two
4 Four
6 Six
8 Eight
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
a b
1 One
3 Three
5 Five
9 Nine
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
SELECT * FROM t_10;
a b
1 One
3 Three
5 Five
9 Nine
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
a b
2 Two
4 Four
6 Six
8 Eight
ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
SELECT * FROM tsp_00;
a b
SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
a b
2 Two
4 Four
5 Five
6 Six
8 Eight
ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
SELECT * FROM tsp_00;
a b
5 Five
SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
a b
2 Two
4 Four
6 Six
8 Eight
disconnect test2;
connection default;
REVOKE INSERT ON test.* FROM test2@localhost;
connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
USE test;
SHOW GRANTS FOR CURRENT_USER;
Grants for test2@localhost
GRANT USAGE ON *.* TO `test2`@`localhost`
GRANT SELECT, UPDATE, CREATE, DROP, ALTER ON `test`.* TO `test2`@`localhost`
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
ERROR 42000: INSERT command denied to user 'test2'@'localhost' for table 'tp'
disconnect test2;
connection default;
GRANT INSERT ON test.* TO test2@localhost;
REVOKE CREATE ON test.* FROM test2@localhost;
connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
USE test;
SHOW GRANTS FOR CURRENT_USER;
Grants for test2@localhost
GRANT USAGE ON *.* TO `test2`@`localhost`
GRANT SELECT, INSERT, UPDATE, DROP, ALTER ON `test`.* TO `test2`@`localhost`
ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
ERROR 42000: CREATE command denied to user 'test2'@'localhost' for table 'tsp'
disconnect test2;
connection default;
GRANT CREATE ON test.* TO test2@localhost;
REVOKE DROP ON test.* FROM test2@localhost;
connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
SHOW GRANTS FOR CURRENT_USER;
Grants for test2@localhost
GRANT USAGE ON *.* TO `test2`@`localhost`
GRANT SELECT, INSERT, UPDATE, CREATE, ALTER ON `test`.* TO `test2`@`localhost`
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
ERROR 42000: DROP command denied to user 'test2'@'localhost' for table 'tp'
disconnect test2;
connection default;
DROP TABLE IF EXISTS t_10;
DROP TABLE IF EXISTS t_100;
DROP TABLE IF EXISTS t_1000;
DROP TABLE IF EXISTS tp;
DROP TABLE IF EXISTS tsp;
DROP TABLE IF EXISTS tsp_00;
DROP TABLE IF EXISTS tsp_01;
DROP TABLE IF EXISTS tsp_02;
DROP TABLE IF EXISTS tsp_03;
DROP TABLE IF EXISTS tsp_04;
DROP TABLE IF EXISTS t_empty;
DROP TABLE IF EXISTS t_null;
DROP USER test2@localhost;
|
