List:Commits« Previous MessageNext Message »
From:marko.makela Date:June 1 2012 8:04am
Subject:bzr push into mysql-trunk-wl6255 branch (marko.makela:3921 to 3923)
View as plain text  
 3923 Marko Mäkelä	2012-06-01
      Enable ALTER TABLE info in a test.
      
      Add a test case that demonstrates an inconsistency in WL#6251.

    modified:
      mysql-test/suite/innodb/r/innodb-index.result
      mysql-test/suite/innodb/t/innodb-index.test
 3922 Marko Mäkel (MODIFY...NULL)

    added:
      mysql-test/suite/innodb/r/innodb-alter-nullable.result
      mysql-test/suite/innodb/t/innodb-alter-nullable.test
 3921 Marko Mäkelä	2012-05-31
      WL#6255 On-line ALTER TABLE...MODIFY...NULL
      
      Support Alter_inplace_info::ALTER_COLUMN_NULLABLE.
      
      row_log_table_apply_convert_mrec(): Convert the DATA_NOT_NULL flag.
      Add a TODO comment for MODIFY...NOT NULL.
      
      row_merge_read_clustered_index(): Add an nullable[] array in addition
      to nonnull[]. Adjust the DATA_NOT_NULL flag as needed.

    modified:
      mysql-test/r/alter_table.result
      mysql-test/suite/innodb/r/innodb-table-online.result
      mysql-test/suite/innodb/t/innodb-table-online.test
      storage/innobase/handler/handler0alter.cc
      storage/innobase/row/row0log.cc
      storage/innobase/row/row0merge.cc
=== added file 'mysql-test/suite/innodb/r/innodb-alter-nullable.result'
--- a/mysql-test/suite/innodb/r/innodb-alter-nullable.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb-alter-nullable.result	revid:marko.makela@stripped679u
@@ -0,0 +1,43 @@
+CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT) ENGINE=InnoDB;
+INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9);
+ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE'
+ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 't'
+ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 't'
+ALTER TABLE t MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
+ERROR 42S22: Unknown column 'c2' in 't'
+ALTER TABLE t MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
+ERROR 42S22: Unknown column 'c2' in 't'
+ALTER TABLE t CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE t MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
+SET SQL_MODE='STRICT_ALL_TABLES';
+UPDATE t SET c2=NULL;
+ERROR 23000: Column 'c2' cannot be null
+SELECT * FROM t;
+c1	c2	c3
+1	2	3
+4	5	6
+7	8	9
+ALTER TABLE t MODIFY c2 INT, ALGORITHM=INPLACE;
+BEGIN;
+UPDATE t SET c2=NULL;
+SELECT * FROM t;
+c1	c2	c3
+1	NULL	3
+4	NULL	6
+7	NULL	9
+ROLLBACK;
+SELECT * FROM t;
+c1	c2	c3
+1	2	3
+4	5	6
+7	8	9
+ALTER TABLE t MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
+WHERE NAME='test/t';
+TABLE_ID	NAME	FLAG	N_COLS	SPACE
+19	test/t	1	6	0
+DROP TABLE t;

=== modified file 'mysql-test/suite/innodb/r/innodb-index.result'
--- a/mysql-test/suite/innodb/r/innodb-index.result	revid:marko.makela@stripped
+++ b/mysql-test/suite/innodb/r/innodb-index.result	revid:marko.makela@stripped
@@ -1,8 +1,11 @@
 set global innodb_file_per_table=on;
 set global innodb_file_format='Barracuda';
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
 create table t1(a varchar(2) primary key) engine=innodb;
 insert into t1 values('');
 create index t1a1 on t1(a(1));
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 drop table t1;
 create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
 insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
@@ -12,6 +15,8 @@ ERROR 42000: Duplicate key name 'b'
 alter table t1 add index (b,b);
 ERROR 42S21: Duplicate column name 'b'
 alter table t1 add index d2 (d);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -42,6 +47,8 @@ t1	CREATE TABLE `t1` (
   KEY `d2` (`d`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 alter table t1 add index (b);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -53,6 +60,8 @@ t1	CREATE TABLE `t1` (
   KEY `b` (`b`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 alter table t1 add unique index (c), add index (d);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -69,6 +78,8 @@ explain select * from t1 force index(c)
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	c	10	NULL	4	NULL
 alter table t1 add primary key (a), drop index c;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -81,11 +92,14 @@ t1	CREATE TABLE `t1` (
   KEY `b` (`b`),
   KEY `d` (`d`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 alter table t1 add primary key (c);
 ERROR 42000: Multiple primary key defined
 alter table t1 drop primary key, add primary key (b);
 ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
 create unique index c on t1 (c);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -109,6 +123,8 @@ a	b	c	d
 5	5	oo	oo
 4	4	tr	tr
 alter table t1 drop index b, add index (b);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -178,6 +194,8 @@ create table t1(a int not null, b int, c
 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
 commit;
 alter table t1 add index (c(2));
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -188,7 +206,10 @@ t1	CREATE TABLE `t1` (
   PRIMARY KEY (`a`),
   KEY `c` (`c`(2))
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 alter table t1 add unique index (d(10));
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -200,6 +221,7 @@ t1	CREATE TABLE `t1` (
   UNIQUE KEY `d` (`d`(10)),
   KEY `c` (`c`(2))
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 insert into t1 values(5,1,'ggg','ggg');
 select * from t1;
 a	b	c	d
@@ -243,6 +265,8 @@ t1	CREATE TABLE `t1` (
   KEY `c` (`c`(2))
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 alter table t1 drop index d;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values(8,9,'fff','fff');
 select * from t1;
 a	b	c	d
@@ -284,6 +308,8 @@ create table t1(a int not null, b int, c
 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
 commit;
 alter table t1 add unique index (b,c);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values(8,9,'fff','fff');
 select * from t1;
 a	b	c	d
@@ -313,6 +339,8 @@ t1	CREATE TABLE `t1` (
   UNIQUE KEY `b` (`b`,`c`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 alter table t1 add index (b,c);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values(11,11,'kkk','kkk');
 select * from t1;
 a	b	c	d
@@ -345,6 +373,8 @@ t1	CREATE TABLE `t1` (
   KEY `b_2` (`b`,`c`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 alter table t1 add unique index (c,d);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values(13,13,'yyy','aaa');
 select * from t1;
 a	b	c	d
@@ -410,14 +440,22 @@ ERROR HY000: Cannot drop index 'b': need
 alter table t2 drop index b, drop index c, drop index d;
 ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
 create unique index dc on t2 (d,c);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 create index dc on t1 (b,c);
-alter table t2 add primary key (a);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+alter table t2 add primary key (alpha), change a alpha int;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values (1,1,1);
 insert into t3 values (1,1,1);
 insert into t4 values (1,1,1);
 insert into t2 values (1,1,1,1,1);
 commit;
 alter table t4 add constraint dc foreign key (a) references t1(a);
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 0
 show create table t4;
 Table	Create Table
 t4	CREATE TABLE `t4` (
@@ -430,6 +468,10 @@ t4	CREATE TABLE `t4` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 alter table t3 add constraint dc foreign key (a) references t1(a);
 ERROR HY000: Can't create table '#sql-temporary' (errno: 121)
+SET FOREIGN_KEY_CHECKS=0;
+alter table t3 add constraint dc foreign key (a) references t1(a);
+ERROR HY000: Failed to add the foreign key constraint 'test/dc' to system tables
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
 show create table t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
@@ -437,18 +479,21 @@ t3	CREATE TABLE `t3` (
   `c` int(11) NOT NULL,
   `d` int(11) DEFAULT NULL,
   PRIMARY KEY (`a`),
-  KEY `c` (`c`)
+  KEY `c` (`c`),
+  CONSTRAINT `dc` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 alter table t2 drop index b, add index (b);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `a` int(11) NOT NULL,
+  `alpha` int(11) NOT NULL DEFAULT '0',
   `b` int(11) NOT NULL,
   `c` int(11) NOT NULL,
   `d` int(11) NOT NULL,
   `e` int(11) DEFAULT NULL,
-  PRIMARY KEY (`a`),
+  PRIMARY KEY (`alpha`),
   UNIQUE KEY `dc` (`d`,`c`),
   KEY `c` (`c`),
   KEY `b` (`b`),
@@ -461,14 +506,17 @@ ERROR 23000: Cannot delete or update a p
 drop index dc on t4;
 ERROR 42000: Can't DROP 'dc'; check that column/key exists
 alter table t3 drop foreign key dc;
-ERROR 42000: Can't DROP 'dc'; check that column/key exists
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 alter table t4 drop foreign key dc;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 select * from t2;
-a	b	c	d	e
+alpha	b	c	d	e
 1	1	1	1	1
 delete from t1;
 select * from t2;
-a	b	c	d	e
+alpha	b	c	d	e
 drop table t2,t4,t3,t1;
 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=utf8;
 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
@@ -642,6 +690,8 @@ drop table t1;
 create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
 insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
 alter table t1 add unique index (b);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
 show create table t1;
 Table	Create Table
@@ -702,6 +752,8 @@ drop table t1;
 create table t1(a int not null, b int not null) engine=innodb;
 insert into t1 values (1,1);
 alter table t1 add primary key(b);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values (2,2);
 show create table t1;
 Table	Create Table
@@ -733,6 +785,8 @@ drop table t1;
 create table t1(a int not null) engine=innodb;
 insert into t1 values (1);
 alter table t1 add primary key(a);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 insert into t1 values (2);
 show create table t1;
 Table	Create Table
@@ -759,7 +813,7 @@ set global innodb_file_per_table=0;
 set global innodb_file_format=Antelope;
 set global innodb_file_format_max=Antelope;
 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
-SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+SET FOREIGN_KEY_CHECKS=0;
 CREATE TABLE t1(
 c1	BIGINT(12) NOT NULL,
 PRIMARY KEY (c1)
@@ -808,8 +862,10 @@ c2	BIGINT(12) NOT NULL,
 c3	BIGINT(12) NOT NULL,
 PRIMARY KEY (c1,c2,c3)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+SET FOREIGN_KEY_CHECKS=0;
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3) REFERENCES t1(c1);
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -850,21 +906,44 @@ c2	BIGINT(12) NOT NULL,
 c3	BIGINT(12) NOT NULL,
 PRIMARY KEY (c1)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
+ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
+ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
 ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
-ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
+ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t2 DROP FOREIGN KEY fk_t2_ca;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;
 ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
 ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
-FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
 ERROR HY000: Can't create table '#sql-temporary' (errno: 150)
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_t2_ca' in the referenced table 't1'
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+affected rows: 0
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -872,6 +951,7 @@ t1	CREATE TABLE `t1` (
   `c2` bigint(12) NOT NULL,
   PRIMARY KEY (`c2`,`c1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -882,7 +962,10 @@ t2	CREATE TABLE `t2` (
   KEY `fk_t2_ca` (`c3`,`c2`),
   CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -894,7 +977,10 @@ t2	CREATE TABLE `t2` (
   KEY `i_t2_c2_c1` (`c2`,`c1`),
   CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -907,7 +993,10 @@ t2	CREATE TABLE `t2` (
   KEY `i_t2_c3_c1_c2` (`c3`,`c1`,`c2`),
   CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -920,6 +1009,7 @@ t2	CREATE TABLE `t2` (
   KEY `i_t2_c3_c2` (`c3`,`c2`),
   CONSTRAINT `fk_t2_ca` FOREIGN KEY (`c3`, `c2`) REFERENCES `t1` (`c2`, `c1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+affected rows: 1
 DROP TABLE t2;
 DROP TABLE t1;
 CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB;
@@ -944,8 +1034,14 @@ ERROR HY000: Lock wait timeout exceeded;
 ALTER TABLE t2 ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
 ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE t2 ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE'
 CREATE INDEX t2a ON t2(a);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
+affected rows: 5
+info: Records: 5  Duplicates: 0  Warnings: 0
 SELECT * FROM t2i;
 ERROR HY000: Table definition has changed, please retry transaction
 SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;

=== added file 'mysql-test/suite/innodb/t/innodb-alter-nullable.test'
--- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test	revid:marko.makela@oracle.com-20120601080346-y813g63r1tas679u
@@ -0,0 +1,66 @@
+--source include/have_innodb.inc
+
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT) ENGINE=InnoDB;
+INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9);
+
+# This one will be a no-op.
+# MySQL should perhaps issue an error, because it refuses to modify
+# the PRIMARY KEY column c1 from NOT NULL to NULL.
+ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+
+# Request some conflicting changes for a single column.
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
+
+# No-ops.
+ALTER TABLE t CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE t MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
+
+connect (con1,localhost,root,,);
+connection con1;
+
+SET SQL_MODE='STRICT_ALL_TABLES';
+
+--error ER_BAD_NULL_ERROR
+UPDATE t SET c2=NULL;
+
+SELECT * FROM t;
+
+connection default;
+
+# This should change the column to NULL.
+ALTER TABLE t MODIFY c2 INT, ALGORITHM=INPLACE;
+
+connection con1;
+BEGIN;
+UPDATE t SET c2=NULL;
+SELECT * FROM t;
+ROLLBACK;
+SELECT * FROM t;
+
+disconnect con1;
+connection default;
+
+# This should be no-op.
+ALTER TABLE t MODIFY c2 INT NULL, ALGORITHM=INPLACE;
+
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
+WHERE NAME='test/t';
+
+DROP TABLE t;
+
+# Check that all connections opened by test cases in this file are really
+# gone so execution of other tests won't be affected by their presence.
+--source include/wait_until_count_sessions.inc

=== modified file 'mysql-test/suite/innodb/t/innodb-index.test'
--- a/mysql-test/suite/innodb/t/innodb-index.test	revid:marko.makela@stripped31192236-hjso1xpadewdu19a
+++ b/mysql-test/suite/innodb/t/innodb-index.test	revid:marko.makela@stripped080346-y813g63r1tas679u
@@ -7,10 +7,14 @@ let $format=`select @@innodb_file_format
 set global innodb_file_per_table=on;
 set global innodb_file_format='Barracuda';
 
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
+
 # Bug#13654923 BOGUS DEBUG ASSERTION IN INDEX CREATION FOR ZERO-LENGTH RECORD
 create table t1(a varchar(2) primary key) engine=innodb;
 insert into t1 values('');
+--enable_info
 create index t1a1 on t1(a(1));
+--disable_info
 drop table t1;
 
 create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
@@ -20,19 +24,26 @@ commit;
 alter table t1 add index b (b), add index b (b);
 --error ER_DUP_FIELDNAME
 alter table t1 add index (b,b);
+--enable_info
 alter table t1 add index d2 (d);
+--disable_info
 show create table t1;
 explain select * from t1 force index(d2) order by d;
 select * from t1 force index (d2) order by d;
 --error ER_DUP_ENTRY
 alter table t1 add unique index (b);
 show create table t1;
+--enable_info
 alter table t1 add index (b);
+--disable_info
 show create table t1;
 
+--enable_info
 alter table t1 add unique index (c), add index (d);
+--disable_info
 show create table t1;
 explain select * from t1 force index(c) order by c;
+--enable_info
 alter table t1 add primary key (a), drop index c;
 show create table t1;
 --error ER_MULTIPLE_PRI_KEY
@@ -40,10 +51,13 @@ alter table t1 add primary key (c);
 --error ER_DUP_ENTRY
 alter table t1 drop primary key, add primary key (b);
 create unique index c on t1 (c);
+--disable_info
 show create table t1;
 explain select * from t1 force index(c) order by c;
 select * from t1 force index(c) order by c;
+--enable_info
 alter table t1 drop index b, add index (b);
+--disable_info
 show create table t1;
 insert into t1 values(6,1,'ggg','ggg');
 select * from t1;
@@ -59,10 +73,12 @@ drop table t1;
 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
 commit;
+--enable_info
 alter table t1 add index (c(2));
 show create table t1;
 alter table t1 add unique index (d(10));
 show create table t1;
+--disable_info
 insert into t1 values(5,1,'ggg','ggg');
 select * from t1;
 select * from t1 force index(c) order by c;
@@ -71,7 +87,9 @@ explain select * from t1 order by b;
 explain select * from t1 force index(c) order by c;
 explain select * from t1 force index(d) order by d;
 show create table t1;
+--enable_info
 alter table t1 drop index d;
+--disable_info
 insert into t1 values(8,9,'fff','fff');
 select * from t1;
 select * from t1 force index(c) order by c;
@@ -84,19 +102,25 @@ drop table t1;
 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
 commit;
+--enable_info
 alter table t1 add unique index (b,c);
+--disable_info
 insert into t1 values(8,9,'fff','fff');
 select * from t1;
 select * from t1 force index(b) order by b;
 explain select * from t1 force index(b) order by b;
 show create table t1;
+--enable_info
 alter table t1 add index (b,c);
+--disable_info
 insert into t1 values(11,11,'kkk','kkk');
 select * from t1;
 select * from t1 force index(b) order by b;
 explain select * from t1 force index(b) order by b;
 show create table t1;
+--enable_info
 alter table t1 add unique index (c,d);
+--disable_info
 insert into t1 values(13,13,'yyy','aaa');
 select * from t1;
 select * from t1 force index(b) order by b;
@@ -123,32 +147,44 @@ alter table t4 drop index d;
 alter table t2 drop index b;
 --error ER_DROP_INDEX_FK
 alter table t2 drop index b, drop index c, drop index d;
+--enable_info
 # Apparently, the following makes mysql_alter_table() drop index d.
 create unique index dc on t2 (d,c);
 create index dc on t1 (b,c);
 # This should preserve the foreign key constraints.
-alter table t2 add primary key (a);
+alter table t2 add primary key (alpha), change a alpha int;
+--disable_info
 insert into t1 values (1,1,1);
 insert into t3 values (1,1,1);
 insert into t4 values (1,1,1);
 insert into t2 values (1,1,1,1,1);
 commit;
+--enable_info
 alter table t4 add constraint dc foreign key (a) references t1(a);
+--disable_info
 show create table t4;
 --replace_regex /'test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
 # a foreign key 'test/dc' already exists
 --error ER_CANT_CREATE_TABLE
 alter table t3 add constraint dc foreign key (a) references t1(a);
+SET FOREIGN_KEY_CHECKS=0;
+--error ER_FK_FAIL_ADD_SYSTEM
+alter table t3 add constraint dc foreign key (a) references t1(a);
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
 show create table t3;
+--enable_info
 alter table t2 drop index b, add index (b);
+--disable_info
 show create table t2;
 --error ER_ROW_IS_REFERENCED_2
 delete from t1;
 --error ER_CANT_DROP_FIELD_OR_KEY
 drop index dc on t4;
 --error ER_CANT_DROP_FIELD_OR_KEY
+--enable_info
 alter table t3 drop foreign key dc;
 alter table t4 drop foreign key dc;
+--disable_info
 select * from t2;
 delete from t1;
 select * from t2;
@@ -190,7 +226,9 @@ drop table t1;
 
 create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
 insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
+--enable_info
 alter table t1 add unique index (b);
+--disable_info
 insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
 show create table t1;
 check table t1;
@@ -204,7 +242,9 @@ drop table t1;
 
 create table t1(a int not null, b int not null) engine=innodb;
 insert into t1 values (1,1);
+--enable_info
 alter table t1 add primary key(b);
+--disable_info
 insert into t1 values (2,2);
 show create table t1;
 check table t1;
@@ -217,7 +257,9 @@ drop table t1;
 
 create table t1(a int not null) engine=innodb;
 insert into t1 values (1);
+--enable_info
 alter table t1 add primary key(a);
+--disable_info
 insert into t1 values (2);
 show create table t1;
 check table t1;
@@ -236,7 +278,7 @@ eval set global innodb_file_format_max=$
 # constraint modifications (Issue #70, Bug #38786)
 #
 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
-SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+SET FOREIGN_KEY_CHECKS=0;
 
 CREATE TABLE t1(
   c1	BIGINT(12) NOT NULL,
@@ -279,8 +321,10 @@ CREATE TABLE t2(
   PRIMARY KEY (c1,c2,c3)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
+SET FOREIGN_KEY_CHECKS=0;
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
  FOREIGN KEY (c3) REFERENCES t1(c1);
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
 
 SHOW CREATE TABLE t2;
 
@@ -310,26 +354,48 @@ CREATE TABLE t2(
   PRIMARY KEY (c1)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
+SET FOREIGN_KEY_CHECKS=0;
+--enable_info
+
 --replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
 --error ER_CANT_CREATE_TABLE
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
+--error ER_FK_NO_INDEX_PARENT
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
  FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
 --replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
 --error ER_CANT_CREATE_TABLE
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
+--error ER_FK_NO_INDEX_PARENT
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
  FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
+
+# FIXME (WL#6251 problem): this should fail, like the ALGORITHM=COPY below
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+ALTER TABLE t2 DROP FOREIGN KEY fk_t2_ca;
+
 --replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
 --error ER_CANT_CREATE_TABLE
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
- FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+ FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;
+
 ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
 --replace_regex /'test\.#sql-[0-9_a-f-]*'/'#sql-temporary'/
 --error ER_CANT_CREATE_TABLE
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
+ FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
+--error ER_FK_NO_INDEX_PARENT
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
  FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
 
 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
  FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
+
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+
 SHOW CREATE TABLE t1;
 SHOW CREATE TABLE t2;
 CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
@@ -338,6 +404,7 @@ CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1,
 SHOW CREATE TABLE t2;
 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
 SHOW CREATE TABLE t2;
+--disable_info
 
 DROP TABLE t2;
 DROP TABLE t1;
@@ -365,9 +432,11 @@ CREATE INDEX t1a ON t1(a);
 # InnoDB cannot change a column to NOT NULL yet
 --error ER_NOT_SUPPORTED_YET
 ALTER TABLE t2 ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
+--enable_info
 CREATE INDEX t2a ON t2(a);
 ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
 ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
+--disable_info
 connection b;
 # t2i and t2c are too new for this transaction, because they were rebuilt
 --error ER_TABLE_DEF_CHANGED
@@ -402,4 +471,6 @@ show create table t2;
 show create table t2i;
 show create table t2c;
 
+--disable_info
+
 DROP TABLE t1,t2,t2c,t2i;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk-wl6255 branch (marko.makela:3921 to 3923) marko.makela1 Jun