Debian Codegraph @

mysql-test/suite/parts/r/partition-dml-1-6-innodb.result

raw
1 SET @auto= @@global.autocommit;
2 SET @@global.autocommit= off;
3 SET @iso= @@global.transaction_isolation;
4 SET @@global.transaction_isolation= 'read-uncommitted';
5 SET @completion= @@global.completion_type;
6 SET @@global.completion_type= 'no_chain';
7 SET @lock_timeout= @@global.lock_wait_timeout;
8 SET @@global.lock_wait_timeout= 1;
9 SELECT @@global.lock_wait_timeout;
10 @@global.lock_wait_timeout
11 1
12 SET @innodb_lock_timeout= @@global.innodb_lock_wait_timeout;
13 SET @@global.innodb_lock_wait_timeout= 1;
14 SELECT @@global.innodb_lock_wait_timeout;
15 @@global.innodb_lock_wait_timeout
16 1
17 # Additional tests for WL#5217 by QA, testplan 1.1
18 CREATE TABLE t1
19 (a INT NOT NULL,
20 b varchar (64),
21 INDEX ind_t1 (b,a),
22 PRIMARY KEY (a))
23 ENGINE = InnoDB
24 PARTITION BY RANGE (a)
25 SUBPARTITION BY HASH (a) SUBPARTITIONS 3
26 (PARTITION pNeg VALUES LESS THAN (0)
27 (SUBPARTITION subp0 ,
28 SUBPARTITION subp1 ,
29 SUBPARTITION subp2 ),
30 PARTITION `p0-29` VALUES LESS THAN (30)
31 (SUBPARTITION subp3 ,
32 SUBPARTITION subp4 ,
33 SUBPARTITION subp5 ),
34 PARTITION `p30-299` VALUES LESS THAN (300)
35 (SUBPARTITION subp6 ,
36 SUBPARTITION subp7 ,
37 SUBPARTITION subp8 ),
38 PARTITION `p300-2999` VALUES LESS THAN (3000)
39 (SUBPARTITION subp9 ,
40 SUBPARTITION subp10 ,
41 SUBPARTITION subp11 ),
42 PARTITION `p3000-299999` VALUES LESS THAN (300000)
43 (SUBPARTITION subp12 ,
44 SUBPARTITION subp13 ,
45 SUBPARTITION subp14 ));
46 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0');
47 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0');
48 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0');
49 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0');
50 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3');
51 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3');
52 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3');
53 INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3');
54 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5');
55 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5');
56 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5');
57 INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5');
58 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6');
59 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6');
60 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6');
61 INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6');
62 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8');
63 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8');
64 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8');
65 INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8');
66 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8');
67 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8');
68 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8');
69 INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8');
70 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12');
71 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12');
72 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12');
73 INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12');
74 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14');
75 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14');
76 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14');
77 INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14');
78 CREATE USER test_user_1 IDENTIFIED BY 'testpw';
79 CREATE USER test_user_2 IDENTIFIED BY 'testpw';
80 GRANT ALL PRIVILEGES ON test.* TO test_user_1;
81 GRANT ALL PRIVILEGES ON test.* TO test_user_2;
82 connect session1, localhost, test_user_1,'testpw',test;
83 UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9;
84 SELECT * FROM t1 PARTITION (`p0-29`,`p30-299`);
85 a b
86 1 p0-29-upd-1
87 2 p0-29-upd-1
88 21 (p0-29-)subp5
89 22 (p0-29-)subp5
90 23 (p0-29-)subp5
91 231 (p30-299-)subp8
92 232 (p30-299-)subp8
93 233 (p30-299-)subp8
94 234 (p30-299-)subp8
95 24 (p0-29-)subp5
96 3 p0-29-upd-1
97 31 (p30-299-)subp6
98 32 (p30-299-)subp6
99 33 (p30-299-)subp6
100 34 (p30-299-)subp6
101 4 p0-29-upd-1
102 connect session2, localhost, test_user_1,'testpw',test;
103 ####### expect not to see the updated values
104 SELECT * FROM t1 PARTITION (`p0-29`,`p30-299`);
105 a b
106 1 p0-29-upd-1
107 2 p0-29-upd-1
108 21 (p0-29-)subp5
109 22 (p0-29-)subp5
110 23 (p0-29-)subp5
111 231 (p30-299-)subp8
112 232 (p30-299-)subp8
113 233 (p30-299-)subp8
114 234 (p30-299-)subp8
115 24 (p0-29-)subp5
116 3 p0-29-upd-1
117 31 (p30-299-)subp6
118 32 (p30-299-)subp6
119 33 (p30-299-)subp6
120 34 (p30-299-)subp6
121 4 p0-29-upd-1
122 connection session1;
123 COMMIT WORK;
124 connection session2;
125 ####### expect to see the updated values.
126 SELECT * FROM t1 PARTITION (`p0-29`,`p30-299`);
127 a b
128 1 p0-29-upd-1
129 2 p0-29-upd-1
130 21 (p0-29-)subp5
131 22 (p0-29-)subp5
132 23 (p0-29-)subp5
133 231 (p30-299-)subp8
134 232 (p30-299-)subp8
135 233 (p30-299-)subp8
136 234 (p30-299-)subp8
137 24 (p0-29-)subp5
138 3 p0-29-upd-1
139 31 (p30-299-)subp6
140 32 (p30-299-)subp6
141 33 (p30-299-)subp6
142 34 (p30-299-)subp6
143 4 p0-29-upd-1
144 disconnect session1;
145 disconnect session2;
146 connection default;
147 DROP TABLE t1;
148 DROP USER test_user_1;
149 DROP USER test_user_2;
150 SET @@global.autocommit= @auto;
151 SET @@global.transaction_isolation= @iso;
152 SET @@global.completion_type= @completion;
153 SET @@global.lock_wait_timeout= @lock_timeout;
154 SET @@global.innodb_lock_wait_timeout= @innodb_lock_timeout;