Debian Codegraph @

mysql-test/suite/ndb/t/ndb_alter_table_online2.test

raw
1 #############################################################
2 # Author: Tomas
3 # Date: 2007-09
4 # Purpose: online alter under load test
5 ##############################################################
6 # Change Author: Jonathan
7 # Date 2007-10
8 # Purpose: Add testing for online alter w/partitions
9 # and some original test cleanup and additions
10 ##############################################################
11 -- source include/have_multi_ndb.inc
12 # mysqlslap seems to be not_windows. remove this when removed from mysqlslap.test
13 -- source include/not_windows.inc
14 # -- source include/have_log_bin.inc
15
16 connection default;
17
18 # This test is really a walking race-condition...
19 # and valgrind in PB2..."normally" gets timeout...
20 # so ignore this test in valgrind
21 # In CluB it actually more frequently fails in gcc-madness, not sure why
22 # (same timeout)
23 -- source include/not_valgrind.inc
24
25 CREATE DATABASE mysqlslap;
26
27 # Create view which displays all user tables.
28 # NOTE! Uses a format which is backward compatible with the output
29 # from "ndb_show_tables" previously used by this test - for example names
30 # have surrounding quotes.
31 --disable_query_log ONCE
32 CREATE VIEW ndb_show_tables_results AS
33 SELECT
34 id,
35 /* Hardcoded since only user tables are selected */
36 'UserTable' AS type,
37 /* Table name is last part of fq_name */
38 CONCAT("'", CONCAT(SUBSTRING_INDEX(fq_name, "/", -1), "'")) AS name
39 FROM ndbinfo.dict_obj_info WHERE type = 2 /* User table */;
40
41 --echo
42 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
43 --echo ~ basic online alter test during load
44 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
45 --echo
46
47 create table t1 (pk int key, a int) engine ndb;
48 insert into t1 values (1,0);
49
50 set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%');
51
52 --echo
53 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
54 --echo ~ Starting mysqlslap
55 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
56 --echo
57
58 let $end_mysqlslap= 2000;
59 --exec $MYSQL_SLAP --query="update test.t1 set a=a+1 where pk=1; select sleep(0.01);" -i $end_mysqlslap >> $NDB_TOOLS_OUTPUT &
60
61 # wait for 100 updates
62 --disable_result_log
63 --disable_query_log
64 select @end:=100;
65 let $val= 1;
66 while ($val)
67 {
68 --sleep 0.1
69 select @val:=a from t1 where pk=1;
70 let $val= `select @end > @val `;
71 }
72 --enable_result_log
73 --enable_query_log
74
75 --echo
76 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
77 --echo ~ Alter table t1 add column b
78 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
79 --echo
80
81 # add a column online
82 ALTER TABLE t1 algorithm=inplace, ADD b INT;
83
84 --echo
85 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
86 --echo ~ Check table t1 ID has not changed
87 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
88 --echo
89
90 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
91
92 --echo
93 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
94 --echo ~ Starting mysqlslap using column b
95 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
96 --echo
97
98 update t1 set b = 0 where pk = 1;
99 --exec $MYSQL_SLAP --query="update test.t1 set b=b+1 where pk=1; select sleep(0.01);" -i $end_mysqlslap >> $NDB_TOOLS_OUTPUT &
100
101 # wait for 100 updates
102 --disable_result_log
103 --disable_query_log
104 select @end:=100;
105 let $val= 1;
106 while ($val)
107 {
108 --sleep 0.1
109 select @val:=b from t1 where pk=1;
110 let $val= `select @end > @val`;
111 }
112 --enable_result_log
113 --enable_query_log
114
115 --echo
116 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
117 --echo ~ Alter table t1 add column c
118 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
119 --echo
120
121 # add a column online
122 ALTER TABLE t1 algorithm=inplace, ADD c INT;
123
124 --echo
125 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
126 --echo ~ Check table t1 ID has not changed
127 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
128 --echo
129
130 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
131
132 --echo
133 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
134 --echo ~ Starting mysqlslap using column c
135 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
136 --echo
137
138 update t1 set c = 0 where pk = 1;
139 --exec $MYSQL_SLAP --query="update test.t1 set c=c+1 where pk=1; select sleep(0.01);" -i $end_mysqlslap >> $NDB_TOOLS_OUTPUT &
140
141 # wait for mysqlslap to end
142 --disable_result_log
143 --disable_query_log
144 --eval select @end:=$end_mysqlslap
145 let $val= 1;
146 # 10 minutes = 600s sleep 0.1 => 6000
147 let $maxwait = 6000;
148 while ($val)
149 {
150 --sleep 0.1
151 select @val1:=a,@val2:=b,@val3:=c from t1 where pk=1;
152 let $val= `select @end > @val1 || @end > @val2 || @end > @val3`;
153 dec $maxwait;
154
155 if (!$maxwait)
156 {
157 let $val = 0;
158 }
159 }
160 --enable_result_log
161 --enable_query_log
162
163 select * from t1;
164
165
166 --echo
167 --echo BUG#17400320 algorithm= is not supported for ALTER TABLE with <partition_options>
168 --echo - thus disabling parts of this test
169 if (0)
170 {
171 --echo
172 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
173 --echo ~ Alter table t1 and try to add partitions
174 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
175 --echo
176
177 --error ER_ALTER_OPERATION_NOT_SUPPORTED
178 ALTER TABLE t1 algorithm=inplace, PARTITION BY HASH(pk);
179 }
180
181 --echo
182 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
183 --echo ~ Check table t1 ID has not changed
184 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
185 --echo
186
187 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
188
189
190 --echo
191 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
192 --echo ~ test that aborted online alter rollback DDL transaction
193 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
194 --echo
195
196 connect (con1,localhost,root,,test);
197
198 --echo # Connection default
199 connection default;
200 START TRANSACTION;
201 SELECT * FROM t1;
202
203 --echo # Connection con1
204 --connection con1
205 SET lock_wait_timeout=1;
206 --error ER_LOCK_WAIT_TIMEOUT
207 ALTER TABLE t1 algorithm=inplace, ADD d INT;
208 --error ER_LOCK_WAIT_TIMEOUT
209 ALTER TABLE t1 algorithm=inplace, ADD d INT;
210 SELECT * FROM t1;
211
212 connection default;
213 COMMIT;
214
215 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
216
217 --echo
218 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
219 --echo ~ cleanup section
220 --echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
221 --echo
222
223 # drop the table
224 drop table t1;
225 drop database mysqlslap;
226 --remove_file $NDB_TOOLS_OUTPUT
227
228 --disable_query_log ONCE
229 DROP VIEW ndb_show_tables_results;