1
#############################################################
4
# Purpose: online alter under load test
5
##############################################################
6
# Change Author: Jonathan
8
# Purpose: Add testing for online alter w/partitions
9
# and some original test cleanup and additions
10
##############################################################
11
--source include/ndb_have_online_alter.inc
12
-- source include/have_multi_ndb.inc
13
# test uses mysqlslap, hence not in embedded
14
-- source include/not_embedded.inc
15
# mysqlslap seems to be not_windows. remove this when removed from mysqlslap.test
16
-- source include/not_windows.inc
17
# -- source include/have_log_bin.inc
20
DROP TABLE IF EXISTS t1;
21
CREATE DATABASE IF NOT EXISTS mysqlslap;
24
# Create utiltity table used to hold the output from ndb_show_table
25
CREATE TEMPORARY TABLE IF NOT EXISTS ndb_show_tables_results (
30
_database VARCHAR(255),
37
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
38
--echo ~ basic online alter test during load
39
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
42
create table t1 (pk int key, a int) engine ndb;
43
insert into t1 values (1,0);
45
--source ndb_show_tables_result.inc
46
set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%');
49
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
50
--echo ~ Starting mysqlslap
51
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
54
let $end_mysqlslap= 5000;
55
--exec $MYSQL_SLAP --query="update test.t1 set a=a+1 where pk=1" -i $end_mysqlslap >> $NDB_TOOLS_OUTPUT &
57
# wait for 100 updates
65
select @val:=a from t1 where pk=1;
66
let $val= `select @end > @val `;
72
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
73
--echo ~ Alter table t1 add column b
74
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
78
ALTER ONLINE TABLE t1 ADD b INT;
81
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
82
--echo ~ Check table t1 ID has not changed
83
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
86
--source ndb_show_tables_result.inc
87
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
90
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
91
--echo ~ Starting mysqlslap using column b
92
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
96
--exec $MYSQL_SLAP --query="update test.t1 set b=b+1 where pk=1" -i $end_mysqlslap >> $NDB_TOOLS_OUTPUT &
98
# wait for 100 updates
106
select @val:=b from t1 where pk=1;
107
let $val= `select @end > @val`;
113
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
114
--echo ~ Alter table t1 add column c
115
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
118
# add a column online
119
ALTER ONLINE TABLE t1 ADD c INT;
122
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
123
--echo ~ Check table t1 ID has not changed
124
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
127
--source ndb_show_tables_result.inc
128
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
131
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
132
--echo ~ Starting mysqlslap using column c
133
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
137
--exec $MYSQL_SLAP --query="update test.t1 set c=c+1 where pk=1" -i $end_mysqlslap >> $NDB_TOOLS_OUTPUT &
139
# wait for mysqlslap to end
142
--eval select @end:=$end_mysqlslap
144
# 10 minutes = 600s sleep 0.1 => 6000
149
select @val1:=a,@val2:=b,@val3:=c from t1 where pk=1;
150
let $val= `select @end > @val1 || @end > @val2 || @end > @val3`;
164
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
165
--echo ~ Alter table t1 and try to add partitions
166
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
169
--error ER_NOT_SUPPORTED_YET
170
ALTER ONLINE TABLE t1 PARTITION BY HASH(pk);
173
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
174
--echo ~ Check table t1 ID has not changed
175
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
178
--source ndb_show_tables_result.inc
179
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
182
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
183
--echo ~ cleanup section
184
--echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
188
drop table t1, ndb_show_tables_results;
189
drop database mysqlslap;