mirror of
https://github.com/MariaDB/server.git
synced 2025-02-10 07:34:11 +01:00
132 lines
4 KiB
Text
132 lines
4 KiB
Text
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;
|
|
Warnings:
|
|
Warning 1036 Table 't1' is read only
|
|
Warning 1213 Deadlock found when trying to get lock; try restarting transaction
|
|
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;
|