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
|
drop table if exists t1,t2,t3;
#
# Test ALTER TABLE to and from s3
#
create table t1 (a int, b int) engine=aria;
insert into t1 select seq,seq+10 from seq_1_to_1000;
alter table t1 engine=s3;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
alter table t1 comment="hello";
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 COMMENT='hello'
alter table t1 engine=aria;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 COMMENT='hello'
alter table t1 engine=s3;
alter table t1 engine=innodb;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 COMMENT='hello'
select count(*), sum(a), sum(b) from t1;
count(*) sum(a) sum(b)
1000 500500 510500
drop table t1;
#
# Test ALTER TABLE to and from s3 with rename
#
create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_10;
alter table t1 rename to t2, engine=s3;
select count(*), sum(a), sum(b) from t2;
count(*) sum(a) sum(b)
10 55 155
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
alter table t2 rename to t3, engine=aria;
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
select count(*), sum(a), sum(b) from t3;
count(*) sum(a) sum(b)
10 55 155
drop table t3;
#
# Test changing options for a s3 table
#
create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_1000;
alter table t1 engine=s3;
alter table t1 engine=s3, compression_algorithm="zlib";
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 `compression_algorithm`='zlib'
select count(*), sum(a), sum(b) from t1;
count(*) sum(a) sum(b)
1000 500500 510500
drop table t1;
#
# Test ALTER TABLE for S3
#
create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_10;
alter table t1 add column c int, engine=s3;
alter table t1 add column d int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL
) ENGINE=S3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
select count(*), sum(a), sum(b), sum(c), sum(d) from t1;
count(*) sum(a) sum(b) sum(c) sum(d)
10 55 155 NULL NULL
drop table t1;
#
# Test ALTER TABLE with locked table for S3
#
create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_10;
lock table t1 write;
alter table t1 add column c int, engine=s3;
ERROR HY000: Table 't1' is read only
unlock tables;
select count(*), sum(a), sum(b), sum(c) from t1;
count(*) sum(a) sum(b) sum(c)
10 55 155 NULL
lock table t1 write;
ERROR HY000: Table 't1' is read only
lock table t1 read;
select count(*), sum(a), sum(b), sum(c) from t1;
count(*) sum(a) sum(b) sum(c)
10 55 155 NULL
unlock tables;
drop table t1;
#
# Test RENAME TABLE
#
create table t1 (a int, b int) engine=aria select seq as a, seq+10 as b from seq_1_to_10;
alter table t1 engine=s3;
rename table t1 to t3;
alter table t3 rename t2;
select count(*), sum(a), sum(b) from t2;
count(*) sum(a) sum(b)
10 55 155
select count(*), sum(a), sum(b) from t1;
ERROR 42S02: Table 'database.t1' doesn't exist
drop table t2;
|
