Debian Codegraph @

mysql-test/suite/s3/alter.result

raw
1 drop table if exists t1,t2,t3;
2 #
3 # Test ALTER TABLE to and from s3
4 #
5 create table t1 (a int, b int) engine=aria;
6 insert into t1 select seq,seq+10 from seq_1_to_1000;
7 alter table t1 engine=s3;
8 show create table t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `a` int(11) DEFAULT NULL,
12 `b` int(11) DEFAULT NULL
13 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
14 alter table t1 comment="hello";
15 show create table t1;
16 Table Create Table
17 t1 CREATE TABLE `t1` (
18 `a` int(11) DEFAULT NULL,
19 `b` int(11) DEFAULT NULL
20 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1 COMMENT='hello'
21 alter table t1 engine=aria;
22 show create table t1;
23 Table Create Table
24 t1 CREATE TABLE `t1` (
25 `a` int(11) DEFAULT NULL,
26 `b` int(11) DEFAULT NULL
27 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1 COMMENT='hello'
28 alter table t1 engine=s3;
29 alter table t1 engine=innodb;
30 show create table t1;
31 Table Create Table
32 t1 CREATE TABLE `t1` (
33 `a` int(11) DEFAULT NULL,
34 `b` int(11) DEFAULT NULL
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1 COMMENT='hello'
36 select count(*), sum(a), sum(b) from t1;
37 count(*) sum(a) sum(b)
38 1000 500500 510500
39 drop table t1;
40 #
41 # Test ALTER TABLE to and from s3 with rename
42 #
43 create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_10;
44 alter table t1 rename to t2, engine=s3;
45 select count(*), sum(a), sum(b) from t2;
46 count(*) sum(a) sum(b)
47 10 55 155
48 show create table t2;
49 Table Create Table
50 t2 CREATE TABLE `t2` (
51 `a` int(11) DEFAULT NULL,
52 `b` int(11) DEFAULT NULL
53 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
54 alter table t2 rename to t3, engine=aria;
55 show create table t3;
56 Table Create Table
57 t3 CREATE TABLE `t3` (
58 `a` int(11) DEFAULT NULL,
59 `b` int(11) DEFAULT NULL
60 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
61 select count(*), sum(a), sum(b) from t3;
62 count(*) sum(a) sum(b)
63 10 55 155
64 drop table t3;
65 #
66 # Test changing options for a s3 table
67 #
68 create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_1000;
69 alter table t1 engine=s3;
70 alter table t1 engine=s3, compression_algorithm="zlib";
71 show create table t1;
72 Table Create Table
73 t1 CREATE TABLE `t1` (
74 `a` int(11) DEFAULT NULL,
75 `b` int(11) DEFAULT NULL
76 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1 `compression_algorithm`='zlib'
77 select count(*), sum(a), sum(b) from t1;
78 count(*) sum(a) sum(b)
79 1000 500500 510500
80 drop table t1;
81 #
82 # Test ALTER TABLE for S3
83 #
84 create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_10;
85 alter table t1 add column c int, engine=s3;
86 alter table t1 add column d int;
87 show create table t1;
88 Table Create Table
89 t1 CREATE TABLE `t1` (
90 `a` int(11) DEFAULT NULL,
91 `b` int(11) DEFAULT NULL,
92 `c` int(11) DEFAULT NULL,
93 `d` int(11) DEFAULT NULL
94 ) ENGINE=S3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
95 select count(*), sum(a), sum(b), sum(c), sum(d) from t1;
96 count(*) sum(a) sum(b) sum(c) sum(d)
97 10 55 155 NULL NULL
98 drop table t1;
99 #
100 # Test ALTER TABLE with locked table for S3
101 #
102 create table t1 (a int, b int) engine=aria select seq as a,seq+10 as b from seq_1_to_10;
103 lock table t1 write;
104 alter table t1 add column c int, engine=s3;
105 Warnings:
106 Warning 1036 Table 't1' is read only
107 Warning 1213 Deadlock found when trying to get lock; try restarting transaction
108 unlock tables;
109 select count(*), sum(a), sum(b), sum(c) from t1;
110 count(*) sum(a) sum(b) sum(c)
111 10 55 155 NULL
112 lock table t1 write;
113 ERROR HY000: Table 't1' is read only
114 lock table t1 read;
115 select count(*), sum(a), sum(b), sum(c) from t1;
116 count(*) sum(a) sum(b) sum(c)
117 10 55 155 NULL
118 unlock tables;
119 drop table t1;
120 #
121 # Test RENAME TABLE
122 #
123 create table t1 (a int, b int) engine=aria select seq as a, seq+10 as b from seq_1_to_10;
124 alter table t1 engine=s3;
125 rename table t1 to t3;
126 alter table t3 rename t2;
127 select count(*), sum(a), sum(b) from t2;
128 count(*) sum(a) sum(b)
129 10 55 155
130 select count(*), sum(a), sum(b) from t1;
131 ERROR 42S02: Table 'database.t1' doesn't exist
132 drop table t2;
133 # MDEV-31781 ALTER TABLE ENGINE=s3 fails
134 create table t (a int) engine=Aria;
135 alter table t engine=S3, algorithm=copy, lock=none;
136 ERROR 0A000: LOCK=NONE is not supported. Reason: S3. Try LOCK=SHARED