/
alter_table_online.result
221 lines (221 loc) · 8.33 KB
/
alter_table_online.result
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
#
# alter ignore cannot be done online
#
create table t (a int);
alter ignore table t add primary key (a), algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: ALTER IGNORE TABLE. Try LOCK=SHARED
drop table t;
#
# MDEV-28771 Assertion `table->in_use&&tdc->flushed' failed after ALTER
#
create table t (a char(1));
insert into t values ('a'),('b');
select * from t join t as t2 join t as t3;
a a a
a a a
b a a
a b a
b b a
a a b
b a b
a b b
b b b
alter table t modify a int;
ERROR 22007: Truncated incorrect INTEGER value: 'a'
select * from t;
a
a
b
drop table t;
create table t (c double precision key,c2 char,c3 year);
insert into t values (7,3,1);
select a from t where a=all (select a from t where b=2 union select a from t where b=2);
ERROR 42S22: Unknown column 'a' in 'field list'
insert into t values (3,1,1);
alter table t change c c date,add key(c);
ERROR 22007: Incorrect date value: '7' for column `test`.`t`.`c` at row 1
select * from t;
c c2 c3
7 3 2001
3 1 2001
drop table t;
set sql_mode='';
create table t (c char unique,c2 int,stamp timestamp);
insert into t values (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);
Warnings:
Warning 1265 Data truncated for column 'stamp' at row 1
Warning 1265 Data truncated for column 'stamp' at row 2
Warning 1265 Data truncated for column 'stamp' at row 3
Warning 1265 Data truncated for column 'stamp' at row 4
Warning 1265 Data truncated for column 'stamp' at row 5
update t set c=(select * from t) where c in (select * from t);
ERROR 21000: Operand should contain 1 column(s)
alter table t modify c date;
ERROR 23000: Duplicate entry '0000-00-00' for key 'c'
select * from t;
c c2 stamp
1 1 0000-00-00 00:00:00
2 2 0000-00-00 00:00:00
3 3 0000-00-00 00:00:00
4 4 0000-00-00 00:00:00
5 5 0000-00-00 00:00:00
drop table t;
set sql_mode=default;
#
# MDEV-28944 XA assertions failing in binlog_rollback and binlog_commit
#
CREATE TABLE t (a INT) ENGINE=MyISAM;
INSERT INTO t VALUES (1);
connect con1,localhost,root,,test;
XA START 'xid';
SELECT * FROM t;
a
1
connection default;
ALTER TABLE t NOWAIT ADD KEY (a);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
connection con1;
UPDATE t SET a = 2;
XA END 'xid';
XA COMMIT 'xid' ONE PHASE;
DROP TABLE t;
disconnect con1;
connection default;
#
# MDEV-29068 Cascade foreign key updates do not apply in online alter
#
create table t1 (a int primary key) engine=InnoDB;
insert into t1 values (1),(2),(3);
create table t2 (b int, foreign key (b)
references t1 (a)
on update cascade) engine=InnoDB;
insert into t2 values (1),(2),(3);
alter table t2 add c int, algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: ON UPDATE CASCADE. Try LOCK=SHARED
alter table t2 add c int, algorithm=inplace, lock=none;
create or replace table t2 (b int, foreign key (b)
references t1 (a)
on delete set null) engine=InnoDB;
alter table t2 add c int, algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: ON DELETE SET NULL. Try LOCK=SHARED
alter table t2 add c int, algorithm=inplace, lock=none;
create or replace table t2 (b int, foreign key (b)
references t1 (a)
on delete no action) engine=InnoDB;
insert into t2 values (1),(2),(3);
alter table t2 add c int, algorithm=copy, lock=none;
create or replace table t2 (b int, foreign key (b)
references t1 (a)
on update restrict) engine=InnoDB;
insert into t2 values (1),(2),(3);
alter table t2 add c int, algorithm=copy, lock=none;
drop table t2, t1;
create table t1 (a int primary key, b int unique) engine=InnoDB;
insert into t1 values (1, 1),(2, 2),(3, 3);
create table t2 (a int references t1 (a),
b int references t1 (b) on update cascade) engine=InnoDB;
insert into t2 values (1, 1),(2, 2);
alter table t2 add c int, algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: ON UPDATE CASCADE. Try LOCK=SHARED
alter table t2 add c int, algorithm=copy;
alter table t2 add d int, algorithm=inplace;
drop table t2, t1;
#
# MDEV-30891 Assertion `!table->versioned(VERS_TRX_ID)' failed
# in Write_rows_log_event::binlog_row_logging_function
#
set system_versioning_alter_history= keep;
create table t1 (id int,
row_start bigint unsigned generated always as row start,
row_end bigint unsigned generated always as row end,
period for system_time (row_start, row_end))
engine=innodb with system versioning;
alter table t1 add c int, algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: BIGINT GENERATED ALWAYS AS ROW_START. Try LOCK=SHARED
alter table t1 add c int, algorithm=inplace;
alter table t1 add d int, lock=none;
set system_versioning_alter_history= default;
drop table t1;
#
# MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column autoinc
# and DML
#
create table t (a serial, b int) engine=innodb;
alter table t drop a, modify b serial, algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: CHANGE COLUMN ... AUTO_INCREMENT. Try LOCK=SHARED
set statement sql_mode= NO_AUTO_VALUE_ON_ZERO for
alter table t drop a, modify b serial, algorithm=copy, lock=none;
create or replace table t (a serial, b int) engine=innodb;
show create table t;
Table Create Table
t CREATE TABLE `t` (
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
# a is unique in the old table, but is shrunk in the new one.
# Only unsafe approach is fine because of possible collisions.
alter table t modify a int, modify b serial, algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: CHANGE COLUMN ... AUTO_INCREMENT. Try LOCK=SHARED
#
# Check that we treat autoinc columns correctly modify old autoinc is
# fine, adding new autoinc for existed column is unsafe.
#
create or replace table t (a serial) engine=innodb;
alter table t change a b serial, algorithm=copy, lock=none;
Warnings:
Note 1831 Duplicate index `b`. This is deprecated and will be disallowed in a future release
# Shrinking the autoinc field is considered safe.
# ER_WARN_DATA_OUT_OF_RANGE should be emitted otherwise.
alter table t change b b int auto_increment primary key,
algorithm=copy, lock=none;
alter table t add c int default(0), drop primary key, drop key a;
# key `b` is still there
show create table t;
Table Create Table
t CREATE TABLE `t` (
`b` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT 0,
UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
alter table t drop b, change c c serial, algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: CHANGE COLUMN ... AUTO_INCREMENT. Try LOCK=SHARED
# Check existed unique keys.
create or replace table t(a int, b int not null, c int not null, d int);
# No unique in the old table;
alter table t add unique(b, c), modify d int auto_increment, add key(d),
algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: CHANGE COLUMN ... AUTO_INCREMENT. Try LOCK=SHARED
alter table t add unique(a, b);
# Unique in the old table has nulls;
alter table t modify d int auto_increment, add key(d),
algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: CHANGE COLUMN ... AUTO_INCREMENT. Try LOCK=SHARED
alter table t add unique(b, c);
# Change unique's column;
alter table t change b x bigint, modify d int auto_increment, add key(d),
algorithm=copy, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: CHANGE COLUMN ... AUTO_INCREMENT. Try LOCK=SHARED
# Finally good. Simple renames with a type unchanged will not affect
# the result. Also NOT NULL -> NULL transform is fine.
alter table t modify d int auto_increment, add key(d),
change b x int null,
algorithm=copy, lock=none;
drop table t;
# MDEV-31172 Server crash or ASAN errors in online_alter_check_autoinc
create table t (a int, b int, c char(8), key(a,b,c));
alter table t modify c int auto_increment key, algorithm=copy;
drop table t;
# MDEV-31601 Some ALTER TABLE .. fail when they worked before, and with
# a wrong error message
create table t (a int) engine=aria;
insert into t values (1),(2);
alter table t algorithm=nocopy, order by a;
ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=COPY
alter table t engine=myisam, algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
drop table t;
create temporary table t (f int);
alter table t force, algorithm=instant;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY
drop table t;