diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-wl5980-alter.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-wl5980-alter.result | 1419 |
1 files changed, 1419 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result new file mode 100644 index 00000000000..2a1b61b44ee --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result @@ -0,0 +1,1419 @@ +# +# This is a copy of innodb-alter.test except using remote tablespaces +# and showing those files. +# +SET default_storage_engine=InnoDB; +SET GLOBAL innodb_file_per_table=ON; +SET NAMES utf8; +CREATE TABLE t1 ( +c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, +INDEX(c2)) +ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; +INSERT INTO t1 SET c1=1; +CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE NAME LIKE 'test/t%'; +CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +CREATE TABLE t1p LIKE t1; +CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3), +CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2), +CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2)) +ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; +CREATE TABLE sys_foreign SELECT i.* +FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i +WHERE FOR_NAME LIKE 'test/t%'; +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c2 0 +test/t1c3 c3 c2 0 +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c2 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c2 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c2 0 +test/t1c3 c3 c2 0 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT '1', + `ct` text, + PRIMARY KEY (`c1`), + KEY `c2` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +ALTER TABLE t1 ALTER c2 DROP DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL, + `c2` int(11), + `ct` text, + PRIMARY KEY (`c1`), + KEY `c2` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c2 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c2 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c2 0 +test/t1c3 c3 c2 0 +ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1; +ALTER TABLE t1 CHANGE c1 c1 INT FIRST; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c2 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c2 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c2 0 +test/t1c3 c3 c2 0 +ALTER TABLE t1 CHANGE C2 c3 INT; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c3 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c3 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c3 0 +test/t1c3 c3 c2 0 +ALTER TABLE t1 CHANGE c3 C INT; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +C 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 C +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 C 0 +test/t1c3 c3 c2 0 +ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 Cöŀumň_TWO 0 +test/t1c3 c3 c2 0 +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +Cöŀumň_TWO 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 Cöŀumň_TWO +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 Cöŀumň_TWO 0 +test/t1c3 c3 c2 0 +ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT; +ERROR 42S22: Unknown column 'cöĿǖmň_two' in 't1' +ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +t3.frm +t3.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t3.ibd +SELECT st.NAME, i.NAME +FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES i +ON i.TABLE_ID=st.TABLE_ID; +NAME NAME +test/t1 test/t3 +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `c1` int(11) NOT NULL, + `c3` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`c1`), + KEY `c2` (`c3`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +SHOW CREATE TABLE t1c; +Table Create Table +t1c CREATE TABLE `t1c` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `c2` (`c2`), + KEY `c3` (`c3`), + CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t3` (`c3`), + CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +ALTER TABLE t3 CHANGE c3 +`12345678901234567890123456789012345678901234567890123456789012345` INT; +ERROR 42000: Identifier name '12345678901234567890123456789012345678901234567890123456789012345' is too long +ALTER TABLE t3 CHANGE c3 +`1234567890123456789012345678901234567890123456789012345678901234` INT; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +t3.frm +t3.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t3.ibd +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `c1` int(11) NOT NULL, + `1234567890123456789012345678901234567890123456789012345678901234` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`c1`), + KEY `c2` (`1234567890123456789012345678901234567890123456789012345678901234`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +ALTER TABLE t3 CHANGE +`1234567890123456789012345678901234567890123456789012345678901234` +`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿偀` INT; +ERROR 42000: Identifier name '倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠' is too long +ALTER TABLE t3 CHANGE +`1234567890123456789012345678901234567890123456789012345678901234` +`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿ä` INT; +ERROR 42000: Identifier name '倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠' is too long +ALTER TABLE t3 CHANGE +`1234567890123456789012345678901234567890123456789012345678901234` +`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +t3.frm +t3.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t3.ibd +ALTER TABLE t3 CHANGE +`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä` +c3 INT; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +t3.frm +t3.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t3.ibd +ALTER TABLE t3 CHANGE c3 𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜 INT; +ERROR HY000: Invalid utf8 character string: '\xF0\x90\x8C\x80\xF0\x90\x8C\x81\xF0\x90\x8C\x82\xF0\x90\x8C\x83' +ALTER TABLE t3 CHANGE c3 😲 INT; +ERROR HY000: Invalid utf8 character string: '\xF0\x9F\x98\xB2' +ALTER TABLE t3 RENAME TO t2; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +t2.frm +t2.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t2.ibd +SELECT st.NAME, i.NAME +FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES i +ON i.TABLE_ID=st.TABLE_ID; +NAME NAME +test/t1 test/t2 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` int(11) NOT NULL, + `c3` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`c1`), + KEY `c2` (`c3`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +RENAME TABLE t2 TO t1; +SELECT st.NAME, i.NAME +FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES i +ON i.TABLE_ID=st.TABLE_ID; +NAME NAME +test/t1 test/t1 +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.ibd +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c3 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c3 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c3 0 +test/t1c3 c3 c2 0 +ALTER TABLE t1 DROP INDEX c2; +ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint +ALTER TABLE t1 DROP INDEX c4; +ERROR 42000: Can't DROP 'c4'; check that column/key exists +ALTER TABLE t1c DROP FOREIGN KEY c2; +ERROR 42000: Can't DROP 'c2'; check that column/key exists +ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2; +ERROR 42000: Can't DROP 'c2'; check that column/key exists +ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2, DROP INDEX c2; +ERROR 42000: Can't DROP 'c2'; check that column/key exists +ALTER TABLE t1c DROP INDEX c2; +ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint +ALTER TABLE t1c DROP FOREIGN KEY ẗ1C2; +ERROR 42000: Can't DROP 'ẗ1C2'; check that column/key exists +SHOW CREATE TABLE t1c; +Table Create Table +t1c CREATE TABLE `t1c` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `c2` (`c2`), + KEY `c3` (`c3`), + CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`), + CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +SET foreign_key_checks=0; +DROP TABLE t1p; +SET foreign_key_checks=1; +SHOW CREATE TABLE t1c; +Table Create Table +t1c CREATE TABLE `t1c` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `c2` (`c2`), + KEY `c3` (`c3`), + CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`), + CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c3 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c3 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c3 0 +test/t1c3 c3 c2 0 +CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) +ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; +ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3; +ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint +ALTER TABLE t1c DROP INDEX C3; +ERROR HY000: Cannot drop index 'c3': needed in a foreign key constraint +SET foreign_key_checks=0; +ALTER TABLE t1c DROP INDEX C3; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +t1p.ibd +SET foreign_key_checks=1; +SHOW CREATE TABLE t1c; +Table Create Table +t1c CREATE TABLE `t1c` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `c2` (`c2`), + CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`), + CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c3 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c3 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c3 0 +test/t1c3 c3 c2 0 +ALTER TABLE t1c DROP FOREIGN KEY t1C3; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +t1p.ibd +SHOW CREATE TABLE t1c; +Table Create Table +t1c CREATE TABLE `t1c` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `c2` (`c2`), + CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c3 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c3 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +test/t1c2 c2 c3 0 +ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +t1p.ibd +SHOW CREATE TABLE t1c; +Table Create Table +t1c CREATE TABLE `t1c` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c3 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +c2 0 c3 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT; +### files in MYSQL_DATA_DIR/test +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1.frm +t1.isl +t1c.frm +t1c.isl +t1p.frm +t1p.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1.ibd +t1c.ibd +t1p.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +c1 0 6 1283 4 +c2 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 c1 +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +CREATE TABLE t1o LIKE t1; +ALTER TABLE t1 ADD FULLTEXT INDEX (ct), +CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt, +ALGORITHM=INPLACE, LOCK=NONE; +ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED. +ALTER TABLE t1 ADD FULLTEXT INDEX (ct), +CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt, +ALGORITHM=INPLACE, LOCK=SHARED; +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +SHOW CREATE TABLE tt; +Table Create Table +tt CREATE TABLE `tt` ( + `pk` int(11) NOT NULL, + `c2` int(11) DEFAULT '42', + `ct` text, + PRIMARY KEY (`pk`), + FULLTEXT KEY `ct` (`ct`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' +ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY; +ERROR 42000: Incorrect column name 'dB_row_Id' +ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=INPLACE; +ERROR 42000: Incorrect column name 'DB_ROW_ID' +ALTER TABLE t1o CHANGE c1 DB_TRX_ID INT; +ERROR 42000: Incorrect column name 'DB_TRX_ID' +ALTER TABLE t1o CHANGE c1 db_roll_ptr INT; +ERROR 42000: Incorrect column name 'DB_ROLL_PTR' +ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT, +ALGORITHM=COPY; +ERROR 42000: Incorrect column name 'FTS_DOC_ID' +ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT, +ALGORITHM=INPLACE; +ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index +ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_Doc_ID INT, +ALGORITHM=INPLACE; +ERROR 42000: Incorrect column name 'FTS_Doc_ID' +ALTER TABLE t1o ADD FULLTEXT INDEX(ct), +CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, +ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. +CREATE TABLE t1n LIKE t1o; +ALTER TABLE t1n ADD FULLTEXT INDEX(ct); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=INPLACE; +ERROR 42000: Incorrect column name 'FTS_DOC_ID' +ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=COPY; +ERROR 42000: Incorrect column name 'Fts_DOC_ID' +ALTER TABLE t1n CHANGE FTS_DOC_ID c11 INT, ALGORITHM=INPLACE; +ERROR 42S22: Unknown column 'FTS_DOC_ID' in 't1n' +ALTER TABLE t1n CHANGE c1 FTS_DOC_ïD INT, ALGORITHM=INPLACE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +ALTER TABLE t1n CHANGE FTS_DOC_ÏD c1 INT, ALGORITHM=INPLACE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +ALTER TABLE t1n CHANGE c1 c2 INT, CHANGE c2 ct INT, CHANGE ct c1 TEXT, +ALGORITHM=INPLACE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +SHOW CREATE TABLE t1n; +Table Create Table +t1n CREATE TABLE `t1n` ( + `c2` int(11) NOT NULL, + `ct` int(11) DEFAULT NULL, + `c1` text, + PRIMARY KEY (`c2`), + FULLTEXT KEY `ct` (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1n CHANGE c2 c1 INT, CHANGE ct c2 INT, CHANGE c1 ct TEXT, +ALGORITHM=COPY; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +SHOW CREATE TABLE t1n; +Table Create Table +t1n CREATE TABLE `t1n` ( + `c1` int(11) NOT NULL, + `c2` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`c1`), + FULLTEXT KEY `ct` (`ct`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE; +ERROR 42000: Key column 'c2' doesn't exist in table +ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY; +ERROR 42000: Key column 'c2' doesn't exist in table +ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. +ALTER TABLE t1n CHANGE c2 c4 INT, LOCK=NONE; +ALTER TABLE t1n ADD INDEX(c4), LOCK=NONE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +SHOW CREATE TABLE t1n; +Table Create Table +t1n CREATE TABLE `t1n` ( + `c1` int(11) NOT NULL, + `c4` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`c1`), + KEY `c4` (`c4`), + FULLTEXT KEY `ct` (`ct`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1n DROP INDEX c4; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE; +ERROR 42S21: Duplicate column name 'c1' +ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. +ALTER TABLE t1n CHANGE c4 c11 INT, LOCK=NONE; +ALTER TABLE t1n ADD INDEX(c11), LOCK=NONE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1n.frm +t1n.ibd +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +SHOW CREATE TABLE t1n; +Table Create Table +t1n CREATE TABLE `t1n` ( + `c1` int(11) NOT NULL, + `c11` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`c1`), + KEY `c11` (`c11`), + FULLTEXT KEY `ct` (`ct`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1n; +ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +call mtr.add_suppression("Error: no matching column for .FTS_DOC_ID. in index .ct.--temporary-- of table .test...t1o"); +ALTER TABLE t1o ADD FULLTEXT INDEX(ct), +CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, +ALGORITHM=INPLACE; +ERROR HY000: Incorrect key file for table 't1o'; try to repair it +ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, LOCK=NONE; +ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +SELECT sc.pos FROM information_schema.innodb_sys_columns sc +INNER JOIN information_schema.innodb_sys_tables st +ON sc.TABLE_ID=st.TABLE_ID +WHERE st.NAME='test/t1o' AND sc.NAME='FTS_DOC_ID'; +pos +0 +SHOW CREATE TABLE t1o; +Table Create Table +t1o CREATE TABLE `t1o` ( + `FTS_DOC_ID` bigint(20) unsigned NOT NULL, + `c2` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`FTS_DOC_ID`), + FULLTEXT KEY `ct` (`ct`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, +DROP INDEX ct, LOCK=NONE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1c.frm +t1c.isl +t1o.frm +t1o.ibd +t1p.frm +t1p.isl +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +t1c.ibd +t1p.ibd +tt.ibd +SHOW CREATE TABLE t1o; +Table Create Table +t1o CREATE TABLE `t1o` ( + `foo_id` bigint(20) unsigned NOT NULL, + `c2` int(11) DEFAULT NULL, + `ct` text, + PRIMARY KEY (`foo_id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign; +CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES +WHERE NAME='test/t1o'; +CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +CREATE TABLE sys_foreign SELECT i.* +FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i WHERE FOR_NAME='test/t1o'; +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +foo_id 0 6 1800 8 +c2 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 foo_id +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id); +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1o.frm +t1o.ibd +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +tt.ibd +ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, +ADD FULLTEXT INDEX(ct); +ERROR HY000: Incorrect key file for table 't1o'; try to repair it +ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL, LOCK=NONE; +ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE; +### files in MYSQL_DATA_DIR/test +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +FTS_AUX_INDEX_1.ibd +FTS_AUX_INDEX_2.ibd +FTS_AUX_INDEX_3.ibd +FTS_AUX_INDEX_4.ibd +FTS_AUX_INDEX_5.ibd +FTS_AUX_INDEX_6.ibd +FTS_AUX_BEING_DELETED.ibd +FTS_AUX_BEING_DELETED_CACHE.ibd +FTS_AUX_CONFIG.ibd +FTS_AUX_DELETED.ibd +FTS_AUX_DELETED_CACHE.ibd +sys_foreign.frm +sys_foreign.ibd +sys_indexes.frm +sys_indexes.ibd +sys_tables.frm +sys_tables.ibd +t1o.frm +t1o.ibd +tt.frm +tt.isl +### files in MYSQL_TMP_DIR/alt_dir/test +tt.ibd +ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL; +ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index +DROP TABLE sys_indexes; +CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; +NAME POS MTYPE PRTYPE LEN +FTS_DOC_ID 0 6 1800 8 +c2 1 6 1027 4 +ct 2 5 524540 10 +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; +NAME POS NAME +PRIMARY 0 FTS_DOC_ID +FTS_DOC_ID_INDEX 0 FTS_DOC_ID +ct 0 ct +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; +ID FOR_COL_NAME REF_COL_NAME POS +# +# Cleanup +# +DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign; +### files in MYSQL_DATA_DIR/test +### files in MYSQL_TMP_DIR/alt_dir/test |