Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 610 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-index-online-fk.result b/mysql-test/suite/innodb/r/innodb-index-online-fk.result
new file mode 100644
index 00000000..84e8ea89
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-index-online-fk.result
@@ -0,0 +1,610 @@
+CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
+INSERT INTO parent VALUES(1,2),(2,3);
+CREATE INDEX tb ON parent(b);
+INSERT INTO parent VALUES(10,20),(20,30);
+CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON child(a2);
+INSERT INTO child VALUES(10,20);
+ALTER TABLE child ADD FOREIGN KEY(a2) REFERENCES parent(b),
+ALGORITHM = INPLACE;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
+SET foreign_key_checks = 0;
+ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
+REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
+REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Duplicate FOREIGN KEY constraint name 'test/fk_1'
+SET foreign_key_checks = 1;
+INSERT INTO child VALUES(1,2),(2,3);
+INSERT INTO child VALUES(4,4);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
+SELECT * FROM parent;
+a	b
+1	2
+2	3
+10	20
+20	30
+SET foreign_key_checks = 0;
+ALTER TABLE child ADD CONSTRAINT fk_20 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
+SHOW WARNINGS;
+Level	Code	Message
+Error	1822	Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
+SHOW ERRORS;
+Level	Code	Message
+Error	1822	Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
+CREATE INDEX idx1 on parent(a, b);
+ALTER TABLE child ADD CONSTRAINT fk_10 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ALTER TABLE child ADD CONSTRAINT fk_2 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX idx1(a1,a2),
+ALGORITHM = INPLACE;
+ALTER TABLE child ADD CONSTRAINT fk_3 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE;
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+test/fk_10	test/child	test/parent	2	5
+test/fk_2	test/child	test/parent	2	5
+test/fk_3	test/child	test/parent	2	5
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+test/fk_10	a1	a	0
+test/fk_10	a2	b	1
+test/fk_2	a1	a	0
+test/fk_2	a2	b	1
+test/fk_3	a1	a	0
+test/fk_3	a2	b	1
+SET foreign_key_checks = 1;
+INSERT INTO child VALUES(5,4);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1` int(11) NOT NULL,
+  `a2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a1`),
+  KEY `tb` (`a2`),
+  KEY `idx1` (`a1`,`a2`),
+  CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
+  CONSTRAINT `fk_10` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `fk_2` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `fk_3` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DELETE FROM parent where a = 1;
+SELECT * FROM child;
+a1	a2
+1	NULL
+2	3
+10	20
+SET foreign_key_checks = 0;
+SET @saved_debug_dbug = @@SESSION.debug_dbug;
+SET DEBUG_DBUG = '+d,innodb_test_open_ref_fail';
+ALTER TABLE child ADD CONSTRAINT fk_4 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+SET DEBUG_DBUG = @saved_debug_dbug;
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+test/fk_10	test/child	test/parent	2	5
+test/fk_2	test/child	test/parent	2	5
+test/fk_3	test/child	test/parent	2	5
+test/fk_4	test/child	test/parent	2	5
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+test/fk_10	a1	a	0
+test/fk_10	a2	b	1
+test/fk_2	a1	a	0
+test/fk_2	a2	b	1
+test/fk_3	a1	a	0
+test/fk_3	a2	b	1
+test/fk_4	a1	a	0
+test/fk_4	a2	b	1
+SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
+name	name
+test/child	a1
+test/child	a2
+SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
+NAME
+SYS_FOREIGN
+SYS_FOREIGN_COLS
+SYS_VIRTUAL
+mysql/innodb_index_stats
+mysql/innodb_table_stats
+mysql/transaction_registry
+test/child
+test/parent
+INSERT INTO child VALUES(5,4);
+SET foreign_key_checks = 1;
+INSERT INTO child VALUES(6,5);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
+SET foreign_key_checks = 0;
+CREATE TABLE `#parent` (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
+CREATE INDEX tb ON `#parent`(a, b);
+CREATE TABLE `#child` (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON `#child`(a1, a2);
+SET DEBUG_DBUG = '+d,innodb_test_no_foreign_idx';
+ALTER TABLE `#child` ADD CONSTRAINT fk_40 FOREIGN KEY (a1, a2)
+REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
+SET DEBUG_DBUG = @saved_debug_dbug;
+SHOW ERRORS;
+Level	Code	Message
+Error	1821	Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+test/fk_10	test/child	test/parent	2	5
+test/fk_2	test/child	test/parent	2	5
+test/fk_3	test/child	test/parent	2	5
+test/fk_4	test/child	test/parent	2	5
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+test/fk_10	a1	a	0
+test/fk_10	a2	b	1
+test/fk_2	a1	a	0
+test/fk_2	a2	b	1
+test/fk_3	a1	a	0
+test/fk_3	a2	b	1
+test/fk_4	a1	a	0
+test/fk_4	a2	b	1
+SET DEBUG_DBUG = '+d,innodb_test_no_reference_idx';
+ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
+SET DEBUG_DBUG = @saved_debug_dbug;
+SHOW ERRORS;
+Level	Code	Message
+Error	1822	Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
+SET DEBUG_DBUG = '+d,innodb_test_wrong_fk_option';
+ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_42'
+SET DEBUG_DBUG = @saved_debug_dbug;
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+test/fk_10	test/child	test/parent	2	5
+test/fk_2	test/child	test/parent	2	5
+test/fk_3	test/child	test/parent	2	5
+test/fk_4	test/child	test/parent	2	5
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+test/fk_10	a1	a	0
+test/fk_10	a2	b	1
+test/fk_2	a1	a	0
+test/fk_2	a2	b	1
+test/fk_3	a1	a	0
+test/fk_3	a2	b	1
+test/fk_4	a1	a	0
+test/fk_4	a2	b	1
+SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
+ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2)
+REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint 'test/fk_43' to system tables
+SET DEBUG_DBUG = @saved_debug_dbug;
+SHOW ERRORS;
+Level	Code	Message
+Error	1823	Failed to add the foreign key constraint 'test/fk_43' to system tables
+DROP TABLE `#child`;
+DROP TABLE `#parent`;
+SET foreign_key_checks = 0;
+ALTER TABLE child ADD CONSTRAINT fk_5 FOREIGN KEY (a2) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2)
+REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+test/fk_10	test/child	test/parent	2	5
+test/fk_2	test/child	test/parent	2	5
+test/fk_3	test/child	test/parent	2	5
+test/fk_4	test/child	test/parent	2	5
+test/fk_5	test/child	test/parent	1	6
+test/fk_6	test/child	test/parent	2	5
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+test/fk_10	a1	a	0
+test/fk_10	a2	b	1
+test/fk_2	a1	a	0
+test/fk_2	a2	b	1
+test/fk_3	a1	a	0
+test/fk_3	a2	b	1
+test/fk_4	a1	a	0
+test/fk_4	a2	b	1
+test/fk_5	a2	b	0
+test/fk_6	a1	a	0
+test/fk_6	a2	b	1
+DROP TABLE child;
+DROP TABLE parent;
+CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
+INSERT INTO parent VALUES(1,2),(2,3);
+CREATE INDEX tb ON parent(b);
+INSERT INTO parent VALUES(10,20),(20,30);
+CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON child(a2);
+INSERT INTO child VALUES(10,20);
+SET foreign_key_checks = 0;
+ALTER TABLE child DROP INDEX tb, ADD CONSTRAINT fk_4 FOREIGN KEY (a2)
+REFERENCES parent(b) ON DELETE CASCADE ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1` int(11) NOT NULL,
+  `a2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a1`),
+  KEY `fk_4` (`a2`),
+  CONSTRAINT `fk_4` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_4	test/child	test/parent	1	5
+SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_4	a2	b	0
+SET foreign_key_checks = 1;
+DROP TABLE child;
+DROP TABLE parent;
+CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
+INSERT INTO parent VALUES(1,2),(2,3);
+CREATE INDEX tb ON parent(b);
+INSERT INTO parent VALUES(10,20),(20,30);
+CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON child(a2);
+SET foreign_key_checks = 0;
+ALTER TABLE child CHANGE a2 a3 INT,
+ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR 42000: Key column 'a2' doesn't exist in table
+ALTER TABLE child CHANGE a2 a3 INT,
+ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+DROP TABLE child;
+DROP TABLE parent;
+CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
+INSERT INTO parent VALUES(1,2),(2,3);
+CREATE INDEX tb ON parent(b);
+INSERT INTO parent VALUES(10,20),(20,30);
+CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON child(a2);
+SET foreign_key_checks = 0;
+SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
+ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
+ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint 'test/fk_1' to system tables
+SET DEBUG_DBUG = @saved_debug_dbug;
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
+name	name
+test/child	a1
+test/child	a2
+SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
+NAME
+SYS_FOREIGN
+SYS_FOREIGN_COLS
+SYS_VIRTUAL
+mysql/innodb_index_stats
+mysql/innodb_table_stats
+mysql/transaction_registry
+test/child
+test/parent
+ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
+ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+Warnings:
+Warning	1280	Name 'idx' ignored for PRIMARY key.
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
+name	name
+test/child	a2
+test/child	a3
+SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
+NAME
+SYS_FOREIGN
+SYS_FOREIGN_COLS
+SYS_VIRTUAL
+mysql/innodb_index_stats
+mysql/innodb_table_stats
+mysql/transaction_registry
+test/child
+test/parent
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a3` int(11) NOT NULL,
+  `a2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a3`),
+  KEY `tb` (`a2`),
+  CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE child;
+CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
+ALTER TABLE child ADD PRIMARY KEY idx (a1),
+ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+Warnings:
+Warning	1280	Name 'idx' ignored for PRIMARY key.
+SELECT * from information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a2	b	0
+SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
+name	name
+test/child	a1
+test/child	a2
+SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
+NAME
+SYS_FOREIGN
+SYS_FOREIGN_COLS
+SYS_VIRTUAL
+mysql/innodb_index_stats
+mysql/innodb_table_stats
+mysql/transaction_registry
+test/child
+test/parent
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1` int(11) NOT NULL,
+  `a2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a1`),
+  KEY `fk_1` (`a2`),
+  CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE child;
+CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
+ALTER TABLE child CHANGE a1 a3 INT,
+ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+SELECT * from information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_1	test/child	test/parent	1	6
+SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_1	a3	b	0
+SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
+name	name
+test/child	a2
+test/child	a3
+SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
+NAME
+SYS_FOREIGN
+SYS_FOREIGN_COLS
+SYS_VIRTUAL
+mysql/innodb_index_stats
+mysql/innodb_table_stats
+mysql/transaction_registry
+test/child
+test/parent
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a3` int(11) DEFAULT NULL,
+  `a2` int(11) DEFAULT NULL,
+  KEY `fk_1` (`a3`),
+  CONSTRAINT `fk_1` FOREIGN KEY (`a3`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE child;
+CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
+ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
+ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_1'
+DROP TABLE parent;
+DROP TABLE child;
+CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL, c INT) ENGINE = InnoDB;
+INSERT INTO parent VALUES(1,2,3),(2,3,4);
+CREATE INDEX tb ON parent(b);
+CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON child(a2);
+ALTER TABLE child
+ADD CONSTRAINT fk_a FOREIGN KEY (a2) REFERENCES parent(b)
+ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+ALTER TABLE child
+ADD CONSTRAINT fk_b FOREIGN KEY (a1) REFERENCES parent(a),
+ALGORITHM = INPLACE;
+ALTER TABLE child CHANGE a2 a2_new INT, CHANGE a1 a1_new INT;
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1_new` int(11) DEFAULT NULL,
+  `a2_new` int(11) DEFAULT NULL,
+  `a3` int(11) DEFAULT NULL,
+  KEY `tb` (`a2_new`),
+  KEY `fk_b` (`a1_new`),
+  CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
+  CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * from information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_a	test/child	test/parent	1	6
+test/fk_b	test/child	test/parent	1	0
+SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_a	a2_new	b	0
+test/fk_b	a1_new	a	0
+ALTER TABLE child
+ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
+ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
+ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1_new` int(11) DEFAULT NULL,
+  `a2_new` int(11) DEFAULT NULL,
+  `a3` int(11) DEFAULT NULL,
+  KEY `tb` (`a2_new`),
+  KEY `fk_b` (`a1_new`),
+  CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
+  CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * from information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_a	test/child	test/parent	1	6
+test/fk_b	test/child	test/parent	1	0
+SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_a	a2_new	b	0
+test/fk_b	a1_new	a	0
+ALTER TABLE child
+ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
+ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
+ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
+ALGORITHM = INPLACE;
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1_new` int(11) DEFAULT NULL,
+  `a2_new` int(11) DEFAULT NULL,
+  `a3` int(11) DEFAULT NULL,
+  KEY `tb` (`a2_new`),
+  KEY `fk_new_1` (`a1_new`),
+  KEY `fk_new_3` (`a3`),
+  CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
+  CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`),
+  CONSTRAINT `fk_new_1` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`b`),
+  CONSTRAINT `fk_new_2` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`a`),
+  CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * from information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_a	test/child	test/parent	1	6
+test/fk_b	test/child	test/parent	1	0
+test/fk_new_1	test/child	test/parent	1	0
+test/fk_new_2	test/child	test/parent	1	0
+test/fk_new_3	test/child	test/parent	1	0
+SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_a	a2_new	b	0
+test/fk_b	a1_new	a	0
+test/fk_new_1	a1_new	b	0
+test/fk_new_2	a2_new	a	0
+test/fk_new_3	a3	a	0
+DROP TABLE child;
+CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON child(a2);
+ALTER TABLE child ADD PRIMARY KEY idx (a1),
+ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
+ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
+ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
+ALGORITHM = INPLACE;
+ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1` int(11) NOT NULL,
+  `a2` int(11) DEFAULT NULL,
+  `a3` int(11) DEFAULT NULL,
+  KEY `tb` (`a2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * from information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+ALTER TABLE child ADD PRIMARY KEY idx (a1),
+ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
+ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
+ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
+ALGORITHM = INPLACE;
+Warnings:
+Warning	1280	Name 'idx' ignored for PRIMARY key.
+SHOW CREATE TABLE child;
+Table	Create Table
+child	CREATE TABLE `child` (
+  `a1` int(11) NOT NULL,
+  `a2` int(11) DEFAULT NULL,
+  `a3` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a1`),
+  KEY `tb` (`a2`),
+  KEY `fk_new_3` (`a3`),
+  CONSTRAINT `fk_new_1` FOREIGN KEY (`a1`) REFERENCES `parent` (`b`),
+  CONSTRAINT `fk_new_2` FOREIGN KEY (`a2`) REFERENCES `parent` (`a`),
+  CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * from information_schema.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/fk_new_1	test/child	test/parent	1	0
+test/fk_new_2	test/child	test/parent	1	0
+test/fk_new_3	test/child	test/parent	1	0
+SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/fk_new_1	a1	b	0
+test/fk_new_2	a2	a	0
+test/fk_new_3	a3	a	0
+SET foreign_key_checks = 1;
+DROP TABLE child;
+DROP TABLE parent;
+CREATE TABLE Parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
+INSERT INTO Parent VALUES(1,2),(2,3);
+CREATE INDEX tb ON Parent(b);
+INSERT INTO Parent VALUES(10,20),(20,30);
+CREATE TABLE Child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
+CREATE INDEX tb ON Child(a2);
+INSERT INTO Child VALUES(10,20);
+SET foreign_key_checks = 0;
+ALTER TABLE Child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
+REFERENCES Parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
+ALGORITHM = INPLACE;
+DROP TABLE Child;
+DROP TABLE Parent;
+CREATE TABLE `t2`(a int,c int,d int) ENGINE=INNODB;
+CREATE TABLE `t3`(a int,c int,d int) ENGINE=INNODB;
+CREATE INDEX idx ON t3(a);
+ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a);
+ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a);
+ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL;
+ERROR HY000: Failed to add the foreign key constraint 'test/e' to system tables
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
+ID	FOR_NAME	REF_NAME	N_COLS	TYPE
+test/e	test/t2	test/t3	1	0
+test/fw	test/t2	test/t3	1	0
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
+ID	FOR_COL_NAME	REF_COL_NAME	POS
+test/e	d	a	0
+test/fw	c	a	0
+DROP TABLE t2;
+DROP TABLE t3;