alter_table_online_debug.result

set default_storage_engine= innodb;
connect  con2, localhost, root,,;
connection default;
#
# Test insert
#
# Insert and add column
create or replace table t1 (a int);
insert t1 values (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NULL, algorithm= copy, lock= none;
connection con2;
insert into t1 values (123), (456), (789);
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
5	NULL
123	NULL
456	NULL
789	NULL
# Insert, error
create or replace table t1 (a int);
insert t1 values (5), (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set session lock_wait_timeout=1;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add unique (a), algorithm= copy, lock= none;
connection con2;
start transaction;
insert into t1 values (123), (456), (789);
set debug_sync= 'now SIGNAL end';
connection default;
ERROR 23000: Duplicate entry '5' for key 'a'
connection con2;
commit;
connection default;
select variable_value into @otd from information_schema.session_status where variable_name='Opened_table_definitions';
select * from t1;
a
5
5
123
456
789
select variable_value-@otd from information_schema.session_status where variable_name='Opened_table_definitions';
variable_value-@otd
1
set session lock_wait_timeout=default;
# long transaction and add column
create or replace table t1 (a int);
insert t1 values (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
set session lock_wait_timeout=1;
alter table t1 add b int NULL, algorithm= copy, lock= none;
connection con2;
start transaction;
insert into t1 values (123), (456), (789);
set debug_sync= 'now SIGNAL end';
connection default;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
select * from t1;
a
5
set session lock_wait_timeout=default;
connection con2;
rollback;
connection default;
# Insert and add NOT NULL column without default value
create or replace table t1 (a int);
insert t1 values (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NOT NULL, algorithm= copy, lock= none;
connection con2;
insert into t1 values (123), (456), (789);
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
5	0
123	0
456	0
789	0
# Insert and add a column with a default value
create or replace table t1 (a int);
insert t1 values (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NOT NULL default (222), algorithm= copy, lock= none;
connection con2;
insert into t1 values (123), (456), (789);
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
5	222
123	222
456	222
789	222
#
# Test update
#
# Update and add a column
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 22);
insert t1 values (3, 44);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add c int default(1),
algorithm= copy, lock= none;
connection con2;
update t1 set b= 55 where a = 1;
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b	c
1	55	1
3	44	1
# Update and add a column in the middle
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 22);
insert t1 values (3, 44);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add c int default(1) after a,
algorithm= copy, lock= none;
connection con2;
update t1 set b= 55 where a = 1;
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	c	b
1	1	55
3	1	44
#
# Test primary key change
#
# Drop key, add key
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 22);
insert t1 values (3, 44);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 drop primary key, add primary key(b),
algorithm= copy, lock= none;
connection con2;
update t1 set b= 55 where a = 1;
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
3	44
1	55
# Drop key, add key. Two updates
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 11);
insert t1 values (2, 22);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 drop primary key, add primary key(b),
algorithm= copy, lock= none;
connection con2;
update t1 set b= 33 where a = 1;
update t1 set b= 44 where a = 2;
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
1	33
2	44
#
# Various tests, see below
#
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 11);
insert t1 values (2, 22);
insert t1 values (3, 33);
insert t1 values (4, 44);
insert t1 values (5, 55);
insert t1 values (6, 66);
insert t1 values (7, 77);
insert t1 values (8, 88);
insert t1 values (9, 99);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 drop primary key, add primary key(b),
algorithm= copy, lock= none;
connection con2;
# Two updates
update t1 set b= 1001 where a = 1;
update t1 set b= 2002 where a = 2;
# Two updates in transaction
set autocommit = 0;
start transaction;
update t1 set b= 3003 where a = 3;
update t1 set b= 4004 where a = 4;
commit;
set autocommit = 1;
# Second update is rolled back
update t1 set b= 5005 where a = 5;
set autocommit = 0;
start transaction;
update t1 set b= 6006 where a = 6;
rollback;
set autocommit = 1;
# Second execution in transaction fails
set autocommit = 0;
start transaction;
update t1 set b= 7007 where a = 7;
update t1 set a= 8, b= 8008 where a = 8 or a = 9 order by a;
ERROR 23000: Duplicate entry '8' for key 'PRIMARY'
commit;
set autocommit = 1;
select * from t1;
a	b
1	1001
2	2002
3	3003
4	4004
5	5005
6	66
7	7007
8	88
9	99
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
1	1001
2	2002
3	3003
4	4004
5	5005
6	66
7	7007
8	88
9	99
#
# MYISAM. Only Inserts can be tested.
# (everything else is a table lock disallowing concurrent reads)
#
create or replace table t1 (a int) engine=myisam;
insert t1 values (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NULL, algorithm= copy, lock= none;
connection con2;
insert into t1 values (123), (456), (789);
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
5	NULL
123	NULL
456	NULL
789	NULL
# MYISAM + error
create or replace table t1 (a int primary key) engine=myisam;
insert t1 values (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NULL, algorithm= copy, lock= none;
connection con2;
insert into t1 values (1),(2),(3),(4),(5),(6);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
select * from t1;
a
1
2
3
4
5
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
5	NULL
1	NULL
2	NULL
3	NULL
4	NULL
# Aria + error
set @@binlog_format=row;
create or replace table t1 (a int primary key) engine=aria;
insert t1 values (5);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NULL, algorithm= copy, lock= none;
connection con2;
insert into t1 values (1),(2),(3),(4),(5),(6);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
select * from t1;
a
1
2
3
4
5
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
5	NULL
1	NULL
2	NULL
3	NULL
4	NULL
set @@binlog_format=default;
# Test incompatible changes
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 22);
insert t1 values (3, 44);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 drop primary key, add primary key(b),
algorithm= copy, lock= none;
connection con2;
update t1 set b= 44 where a = 1;
set debug_sync= 'now SIGNAL end';
connection default;
ERROR 23000: Duplicate entry '44' for key 'PRIMARY'
select * from t1;
a	b
1	44
3	44
# Test log read after EXCLUSIVE lock
# Transaction is started before ALTER, and UPDATE is made.
# Then more UPDATEs.
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 11);
insert t1 values (2, 22);
insert t1 values (3, 33);
insert t1 values (4, 44);
insert t1 values (5, 55);
set debug_sync= 'alter_table_online_before_lock SIGNAL locking WAIT_FOR end';
set debug_sync= 'alter_table_online_downgraded SIGNAL downgraded';
alter table t1 drop primary key, add primary key(b),
algorithm= copy, lock= none;
connection con2;
begin;
set debug_sync= 'now WAIT_FOR downgraded';
update t1 set b= 111 where a = 1;
set debug_sync= 'now WAIT_FOR locking';
set debug_sync= 'now SIGNAL end';
update t1 set b= 222 where a = 2;
update t1 set b= 333 where a = 3;
update t1 set b= 444 where a = 4;
commit;
update t1 set b= 555 where a = 5;
connection default;
select * from t1;
a	b
1	111
2	222
3	333
4	444
5	555
#
# Test progress report.
#
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 11);
insert t1 values (2, 22);
insert t1 values (3, 33);
insert t1 values (4, 44);
set debug_sync= 'alter_table_online_before_lock SIGNAL locking WAIT_FOR end';
set debug_sync= 'alter_table_online_downgraded SIGNAL downgraded'
                                             ' WAIT_FOR start_replication';
set debug_sync= 'alter_table_online_progress SIGNAL applied WAIT_FOR proceed'
                                           ' EXECUTE 9';
alter table t1 drop primary key, add primary key(b),
algorithm= copy, lock= none;
connection con2;
set debug_sync= 'now WAIT_FOR downgraded';
update t1 set b= 111 where a = 1;
insert t1 values (5, 55);
update t1 set b= 555 where a = 5;
insert t1 values (6, 66);
update t1 set b= 666 where a = 6;
set debug_sync= 'now SIGNAL start_replication';
# First signal is for log description event.
set debug_sync= 'now WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
3	51.220
set debug_sync= 'now SIGNAL proceed WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
3	61.789
set debug_sync= 'now SIGNAL proceed WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
3	70.325
set debug_sync= 'now SIGNAL proceed WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
3	80.894
set debug_sync= 'now SIGNAL proceed WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
3	89.431
set debug_sync= 'now SIGNAL proceed WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
3	100.000
set debug_sync= 'now SIGNAL proceed WAIT_FOR locking';
begin;
update t1 set b= 222 where a = 2;
update t1 set b= 333 where a = 3;
update t1 set b= 444 where a = 4;
commit;
set debug_sync= 'now SIGNAL end WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
4	33.333
set debug_sync= 'now SIGNAL proceed WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
4	66.667
set debug_sync= 'now SIGNAL proceed WAIT_FOR applied';
select stage, progress from INFORMATION_SCHEMA.PROCESSLIST where id = @con;
stage	progress
4	100.000
set debug_sync= 'now SIGNAL proceed';
connection default;
select * from t1;
a	b
1	111
2	222
3	333
4	444
5	555
6	666
#
# Test system versioning
#
create or replace table t1 (a int primary key, b int);
insert t1 values (1, 22);
insert t1 values (3, 44);
connection con2;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
set timestamp = 1;
alter table t1 add system versioning,
algorithm= copy, lock= none;
connection con2;
set timestamp = 2;
update t1 set b= 55 where a = 1;
set timestamp = 3;
insert into t1 values (6, 77);
set debug_sync= 'now SIGNAL end';
connection default;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
select *, UNIX_TIMESTAMP(row_start), UNIX_TIMESTAMP(row_end) from t1 for system_time all;
a	b	UNIX_TIMESTAMP(row_start)	UNIX_TIMESTAMP(row_end)
1	55	1.000000	2147483647.999999
3	44	1.000000	2147483647.999999
6	77	1.000000	2147483647.999999
alter table t1 drop system versioning, algorithm= copy, lock= none;
ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
#
# Test ROLLBACK TO SAVEPOINT
#
create or replace table t1 (a int);
insert t1 values (1), (2);
create or replace table t2 (a int);
insert t2 values (1), (2);
connection con2;
begin;
update t2 set a= 222 where a = 2;
savepoint savie;
update t2 set a= 111 where a = 1;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NULL, algorithm= copy, lock= none;
connection con2;
update t1 set a= 123 where a = 1;
savepoint whoopsie;
rollback to savepoint savie;
commit;
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
1	NULL
2	NULL
select * from t2;
a
1
222
create or replace table t1 (a int);
insert t1 values (1), (2);
create or replace table t2 (a int);
insert t2 values (1), (2);
create or replace table t3 (a int) engine=myisam;
insert t3 values (1);
connection con2;
begin;
update t2 set a= 222 where a = 2;
savepoint savie;
update t2 set a= 111 where a = 1;
set debug_sync= 'now WAIT_FOR ended';
connection default;
set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
alter table t1 add b int NULL, algorithm= copy, lock= none;
connection con2;
update t1 set a= 222 where a = 2;
savepoint whoopsie;
update t1 set a= 123 where a = 1;
insert t3 values (2);
select * from t1;
a
123
222
rollback to savepoint whoopsie;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select * from t1;
a
1
222
select * from t3;
a
1
2
commit;
set debug_sync= 'now SIGNAL end';
connection default;
select * from t1;
a	b
1	NULL
222	NULL
select * from t2;
a
111
222
select * from t3;
a
1
2
# Cleanup
set debug_sync= 'reset';
drop table t1;
drop table t2;
drop table t3;