diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/alter_table_online.test | 546 |
1 files changed, 248 insertions, 298 deletions
diff --git a/mysql-test/main/alter_table_online.test b/mysql-test/main/alter_table_online.test index 6ef9661c..4d8f8311 100644 --- a/mysql-test/main/alter_table_online.test +++ b/mysql-test/main/alter_table_online.test @@ -1,335 +1,285 @@ -# -# Test of ALTER ONLINE TABLE syntax -# - +--source include/binlog_combinations.inc --source include/have_innodb.inc ---source include/have_partition.inc -# -# Test of things that can be done online -# - -create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')) engine=myisam; -insert into t1 (a) values (1),(2),(3); - -alter online table t1 modify b int default 5, alter c set default 'X'; -alter online table t1 change b new_name int; -alter online table t1 modify e enum('a','b','c'); -alter online table t1 comment "new comment"; -alter table t1 add constraint q check (a > 0); -alter online table t1 drop constraint q; - -# No OPs - -alter online table t1 algorithm=INPLACE, lock=NONE; -alter online table t1; -alter table t1 algorithm=INPLACE; -alter table t1 lock=NONE; -show create table t1; -drop table t1; +--source include/not_embedded.inc -# -# everything with temporary tables is "online", i.e. without locks -# -create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); -insert into t1 (a) values (1),(2),(3); - -alter online table t1 modify b int default 5, alter c set default 'X'; -alter online table t1 change b new_name int; -alter online table t1 modify e enum('a','b','c'); -alter online table t1 comment "new comment"; -alter online table t1 rename to t2; -show create table t2; -drop table t2; +--echo # +--echo # alter ignore cannot be done online +--echo # +create table t (a int); +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter ignore table t add primary key (a), algorithm=copy, lock=none; +drop table t; -# -# Test also with Aria -# - -create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')) engine=aria; -insert into t1 (a) values (1),(2),(3); -alter online table t1 modify b int default 5; -alter online table t1 change b new_name int; -alter online table t1 modify e enum('a','b','c'); -alter online table t1 comment "new comment"; -show create table t1; -alter online table t1 page_checksum=1; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 page_checksum=0; -drop table t1; +--echo # +--echo # MDEV-28771 Assertion `table->in_use&&tdc->flushed' failed after ALTER +--echo # -# -# Test of things that is not possible to do online -# +create table t (a char(1)); +insert into t values ('a'),('b'); +select * from t join t as t2 join t as t3; +--error ER_TRUNCATED_WRONG_VALUE +alter table t modify a int; +select * from t; +drop table t; + +create table t (c double precision key,c2 char,c3 year); +insert into t values (7,3,1); +--error ER_BAD_FIELD_ERROR +select a from t where a=all (select a from t where b=2 union select a from t where b=2); +insert into t values (3,1,1); +--error ER_TRUNCATED_WRONG_VALUE +alter table t change c c date,add key(c); +select * from t; +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); +--error ER_OPERAND_COLUMNS +update t set c=(select * from t) where c in (select * from t); +--error ER_DUP_ENTRY +alter table t modify c date; +select * from t; +drop table t; +set sql_mode=default; -create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); -insert into t1 (a) values (1),(2),(3); +--echo # +--echo # MDEV-28944 XA assertions failing in binlog_rollback and binlog_commit +--echo # +--disable_view_protocol +CREATE TABLE t (a INT) ENGINE=MyISAM; +INSERT INTO t VALUES (1); + +--connect (con1,localhost,root,,test) +XA START 'xid'; +SELECT * FROM t; + +--connection default +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t NOWAIT ADD KEY (a); + +--connection con1 +UPDATE t SET a = 2; +XA END 'xid'; +XA COMMIT 'xid' ONE PHASE; + +DROP TABLE t; +--disconnect con1 +--connection default +--enable_view_protocol + +--echo # +--echo # MDEV-29068 Cascade foreign key updates do not apply in online alter +--echo # +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); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 drop column b, add b int; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify b bigint; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify e enum('c','a','b'); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify c varchar(50); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify c varchar(100); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 add f int; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -alter online table t1 engine=memory; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 rename to t2; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 checksum=1; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 add constraint check (b > 0); - -alter table t1 engine=innodb; -alter table t1 add index (b); -alter online table t1 add index c (c); -alter online table t1 drop index b; -alter online table t1 comment "new comment"; -show create table t1; -drop table t1; +alter table t2 add c int, algorithm=copy, lock=none; +alter table t2 add c int, algorithm=inplace, lock=none; -create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); -insert into t1 (a) values (1),(2),(3); - -# -# everything with temporary tables is "online", i.e. without locks -# -alter online table t1 drop column b, add b int; -alter online table t1 modify b bigint; -alter online table t1 modify e enum('c','a','b'); -alter online table t1 modify c varchar(50); -alter online table t1 modify c varchar(100); -alter online table t1 add f int; -alter online table t1 engine=memory; - -alter table t1 engine=innodb; -alter table t1 add index (b); -alter online table t1 add index c (c); -alter online table t1 drop index b; -drop table t1; +create or replace table t2 (b int, foreign key (b) + references t1 (a) + on delete set null) engine=InnoDB; -# -# Test merge tables -# -create table t1 (a int not null primary key, b int, c varchar(80)); -create table t2 (a int not null primary key, b int, c varchar(80)); -create table t3 (a int not null primary key, b int, c varchar(80)) engine=merge UNION=(t1); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t3 union=(t1,t2); -drop table t1,t2,t3; - -# -# MDEV-9868 Altering a partitioned table comment does a full copy -# -create table t1 (i int) partition by hash(i) partitions 2; -alter online table t1 comment 'test'; -drop table t1; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t2 add c int, algorithm=copy, lock=none; +alter table t2 add c int, algorithm=inplace, lock=none; -# -# MDEV-9168 altering a column comment does a full copy -# -create table t1 (a int); -alter online table t1 modify a int comment 'test'; -drop table t1; +create or replace table t2 (b int, foreign key (b) + references t1 (a) + on delete no action) engine=InnoDB; -create table t1 (a int) engine=innodb; -alter online table t1 modify a int comment 'test'; -drop table t1; +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); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t2 add c int, algorithm=copy, lock=none; +alter table t2 add c int, algorithm=copy; +alter table t2 add d int, algorithm=inplace; +# Cleanup +drop table t2, t1; + +--echo # +--echo # MDEV-30891 Assertion `!table->versioned(VERS_TRX_ID)' failed +--echo # in Write_rows_log_event::binlog_row_logging_function +--echo # +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; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t1 add c int, algorithm=copy, lock=none; +alter table t1 add c int, algorithm=inplace; +alter table t1 add d int, lock=none; -create table t1 (a int) partition by hash(a) partitions 2; -alter online table t1 modify a int comment 'test'; +set system_versioning_alter_history= default; drop table t1; --echo # ---echo # MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB +--echo # MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column autoinc +--echo # and DML --echo # -# -# ALTER to the same [VAR]BINARY type -# -CREATE TABLE t1 (a BINARY(10)); -ALTER TABLE t1 MODIFY a BINARY(10), ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a VARBINARY(10)); -ALTER TABLE t1 MODIFY a VARBINARY(10), ALGORITHM=INPLACE; -DROP TABLE t1; - -# -# ALTER to the same BLOB variant -# -CREATE TABLE t1 (a TINYBLOB); -ALTER TABLE t1 MODIFY a TINYBLOB, ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a MEDIUMBLOB); -ALTER TABLE t1 MODIFY a MEDIUMBLOB, ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a BLOB); -ALTER TABLE t1 MODIFY a BLOB, ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a LONGBLOB); -ALTER TABLE t1 MODIFY a LONGBLOB, ALGORITHM=INPLACE; -DROP TABLE t1; - -# -# ALTER to the same [VAR]CHAR type -# -CREATE TABLE t1 (a CHAR(10)); -ALTER TABLE t1 MODIFY a CHAR(10), ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a VARCHAR(10)); -ALTER TABLE t1 MODIFY a VARCHAR(10), ALGORITHM=INPLACE; -DROP TABLE t1; - - -# -# ALTER to the same TEXT variant -# -CREATE TABLE t1 (a TINYTEXT); -ALTER TABLE t1 MODIFY a TINYTEXT, ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a MEDIUMTEXT); -ALTER TABLE t1 MODIFY a MEDIUMTEXT, ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a TEXT); -ALTER TABLE t1 MODIFY a TEXT, ALGORITHM=INPLACE; -DROP TABLE t1; - -CREATE TABLE t1 (a LONGTEXT); -ALTER TABLE t1 MODIFY a LONGTEXT, ALGORITHM=INPLACE; -DROP TABLE t1; - -# -# ALTER from a non-binary to a binary collation -# -CREATE TABLE t1 (a CHAR(10)); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; -DROP TABLE t1; +create table t (a serial, b int) engine=innodb; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t drop a, modify b serial, algorithm=copy, lock=none; -CREATE TABLE t1 (a VARCHAR(10)); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_bin, ALGORITHM=INPLACE; -DROP TABLE t1; +set statement sql_mode= NO_AUTO_VALUE_ON_ZERO for +alter table t drop a, modify b serial, algorithm=copy, lock=none; -CREATE TABLE t1 (a TINYTEXT); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; -DROP TABLE t1; +create or replace table t (a serial, b int) engine=innodb; +show create table t; +--echo # a is unique in the old table, but is shrunk in the new one. +--echo # Only unsafe approach is fine because of possible collisions. +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify a int, modify b serial, algorithm=copy, lock=none; -CREATE TABLE t1 (a MEDIUMTEXT); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; -DROP TABLE t1; +--echo # +--echo # Check that we treat autoinc columns correctly modify old autoinc is +--echo # fine, adding new autoinc for existed column is unsafe. +--echo # +create or replace table t (a serial) engine=innodb; -CREATE TABLE t1 (a TEXT); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_bin, ALGORITHM=INPLACE; -DROP TABLE t1; +alter table t change a b serial, algorithm=copy, lock=none; -CREATE TABLE t1 (a LONGTEXT); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_bin, ALGORITHM=INPLACE; -DROP TABLE t1; +--echo # Shrinking the autoinc field is considered safe. +--echo # 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 from a binary to a non-binary collation -# -CREATE TABLE t1 (a CHAR(10) COLLATE latin1_bin); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +alter table t add c int default(0), drop primary key, drop key a; +--echo # key `b` is still there +show create table t; -CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t drop b, change c c serial, algorithm=copy, lock=none; -CREATE TABLE t1 (a TINYTEXT COLLATE latin1_bin); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +--echo # Check existed unique keys. +create or replace table t(a int, b int not null, c int not null, d int); -CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_bin); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +--echo # No unique in the old table; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t add unique(b, c), modify d int auto_increment, add key(d), + algorithm=copy, lock=none; -CREATE TABLE t1 (a TEXT COLLATE latin1_bin); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +alter table t add unique(a, b); +--echo # Unique in the old table has nulls; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify d int auto_increment, add key(d), + algorithm=copy, lock=none; -CREATE TABLE t1 (a LONGTEXT COLLATE latin1_bin); +alter table t add unique(b, c); +--echo # Change unique's column; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t change b x bigint, modify d int auto_increment, add key(d), + algorithm=copy, lock=none; + +--echo # Finally good. Simple renames with a type unchanged will not affect +--echo # 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; + +--echo # 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; + +--echo # MDEV-31601 Some ALTER TABLE .. fail when they worked before, and with +--echo # a wrong error message +create table t (a int) engine=aria; +insert into t values (1),(2); --error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; - -# -# ALTER from a non-binary collation to another non-binary collation -# -CREATE TABLE t1 (a CHAR(10) COLLATE latin1_general_ci); +alter table t algorithm=nocopy, order by a; --error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a CHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +alter table t engine=myisam, algorithm=inplace; +drop table t; -CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_general_ci); +create temporary table t (f int); --error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +alter table t force, algorithm=instant; +drop table t; -CREATE TABLE t1 (a TINYTEXT COLLATE latin1_general_ci); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a TINYTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +create sequence s engine=MyISAM; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table s engine=Aria, lock=none; +alter table s engine=Aria; +drop sequence s; -CREATE TABLE t1 (a MEDIUMTEXT COLLATE latin1_general_ci); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a MEDIUMTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; -CREATE TABLE t1 (a TEXT COLLATE latin1_general_ci); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a TEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; +--echo # MDEV-31631 Adding auto-increment column to a table with history online +--echo # behaves differently from non-online +create sequence s; +create table t1(a int, x int NULL default(nextval(s))); +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t1 add b int default (nextval(s)), lock=none; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t1 add b int primary key auto_increment, lock=none; + +create table t2(a int, b int NULL default(nextval(s))); +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t2 modify b int not null default (nextval(s)), lock=none; + +drop table t2; +drop table t1; +drop sequence s; + +--echo # +--echo # MDEV-33348 ALTER TABLE lock waiting stages are indistinguishable +--echo # +--disable_view_protocol +--connect con2, localhost, root + +create table t1 (a int); +insert t1 values (5); + +start transaction; +select * from t1; + +--connection default +--let $con= `select connection_id()` +send alter table t1 add b int NULL, algorithm= copy, lock= none; + +--connection con2 +evalp set @con= $con; + +let $wait_condition= select stage = 4 and progress = 100 + and state= "Waiting for table metadata lock" + from information_schema.processlist where id = @con; +--source include/wait_condition.inc + +query_vertical select stage, state, info from information_schema.processlist where id = @con; + +rollback; + +--connection default +reap; -CREATE TABLE t1 (a LONGTEXT COLLATE latin1_general_ci); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; -DROP TABLE t1; - -# End of 10.0 tests - -# -# MDEV-11335 Changing delay_key_write option for MyISAM table should not copy rows -# -select @@global.delay_key_write; -create table t1 (a int, b int, key(b)); -flush tables; -flush status; -show status like 'Feature_delay_key_write'; -insert t1 values (1,2),(2,3),(3,4); -show status like 'Feature_delay_key_write'; -alter online table t1 delay_key_write=1; -show status like 'Feature_delay_key_write'; -flush tables; -insert t1 values (1,2),(2,3),(3,4); -show status like 'Feature_delay_key_write'; -alter online table t1 delay_key_write=0; -show status like 'Feature_delay_key_write'; -flush tables; -insert t1 values (1,2),(2,3),(3,4); -show status like 'Feature_delay_key_write'; drop table t1; +--disconnect con2 +--enable_view_protocol -# End of 10.1 tests +--echo # End of 11.2 tests |