add myisam test, add rresultnm/onlilne_alter_pre_rebase

2 files changed, 231 insertions, 17 deletions

diff --git a/mysql-test/suite/binlog/r/online_alter.result b/mysql-test/suite/binlog/r/online_alter.result
new file mode 100644
index 00000000000..cff2da7017b
--- /dev/null
+++ b/mysql-test/suite/binlog/r/online_alter.result
@@ -0,0 +1,204 @@
+connect  con2, localhost, root,,;
+connection default;
+create or replace table t1 (a int) engine=innodb;
+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
+create or replace table t1 (a int) engine=innodb;
+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;
+Warnings:
+Error	1364	Field 'b' doesn't have a default value
+Error	1364	Field 'b' doesn't have a default value
+Error	1364	Field 'b' doesn't have a default value
+select * from t1;
+a	b
+5	0
+123	0
+456	0
+789	0
+create or replace table t1 (a int) engine=innodb;
+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
+create or replace table t1 (a int primary key, b int) engine=innodb;
+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
+# test primary key change
+create or replace table t1 (a int primary key, b int) engine=innodb;
+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
+# test primary key change
+create or replace table t1 (a int primary key, b int) engine=innodb;
+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) engine=innodb;
+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;
+# Double execution
+update t1 set b= 1001 where a = 1;
+update t1 set b= 2002 where a = 2;
+# Double execution 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 execution 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.
+#
+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
+# Cleanup
+set debug_sync= 'reset';
+drop table t1;
diff --git a/mysql-test/suite/binlog/t/online_alter.test b/mysql-test/suite/binlog/t/online_alter.test
index 7551397b904..3d3421ebea7 100644
--- a/mysql-test/suite/binlog/t/online_alter.test
+++ b/mysql-test/suite/binlog/t/online_alter.test
@@ -4,22 +4,6 @@
 --connect (con2, localhost, root,,)
 --connection default
 
-# reset master;
-
-create table t1 (a int) engine=innodb;
-create table t2 (a int) engine=innodb;
-
-# FLUSH LOGS;
-begin;
-insert t1 values (5);
-commit;
-begin;
-insert t2 values (5);
-commit;
-
-# SHOW BINLOG EVENTS;
-# SHOW BINLOG EVENTS in 'master-bin.000002';
-
 create or replace table t1 (a int) engine=innodb;
 insert t1 values (5);
 
@@ -233,7 +217,33 @@ set debug_sync= 'now SIGNAL end';
 --sorted_result
 select * from t1;
 
+--echo #
+--echo # MYISAM. Only Inserts can be tested.
+--echo #
+
+create or replace table t1 (a int) engine=myisam;
+insert t1 values (5);
+
+--connection con2
+--send
+set debug_sync= 'now WAIT_FOR ended';
+
+--connection default
+set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end';
+
+--send
+alter table t1 add b int NULL, algorithm= copy, lock= none;
+
+--connection con2
+--reap
+insert into t1 values (123), (456), (789);
+set debug_sync= 'now SIGNAL end';
+
+--connection default
+--reap
+select * from t1;
+
 
 --echo # Cleanup
 set debug_sync= 'reset';
-drop table t1, t2;
+drop table t1;