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
|
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 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 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 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 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 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 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 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;
|
