ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
SET NAMES utf8mb4;
CREATE TABLE t1 (
c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT,
INDEX(c2))
ENGINE=InnoDB;
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;
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 DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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 DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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;
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;
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;
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;
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;
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 DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
ALTER TABLE t3 CHANGE c3
`12345678901234567890123456789012345678901234567890123456789012345` INT;
ERROR 42000: Identifier name '12345678901234567890123456789012345678901234567890123456789012345' is too long
ALTER TABLE t3 CHANGE c3
`1234567890123456789012345678901234567890123456789012345678901234` INT;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `c1` int(11) NOT NULL,
  `1234567890123456789012345678901234567890123456789012345678901234` int(11) DEFAULT NULL,
  `ct` text DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`1234567890123456789012345678901234567890123456789012345678901234`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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;
ALTER TABLE t3 CHANGE
`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä`
c3 INT;
ALTER TABLE t3 CHANGE c3 𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜 INT;
ERROR HY000: Invalid utf8mb4 character string: '\xF0\x90\x8C\x80\xF0\x90\x8C\x81\xF0\x90\x8C\x82\xF0\x90\x8C\...'
ALTER TABLE t3 CHANGE c3 😲 INT;
ERROR HY000: Invalid utf8mb4 character string: '\xF0\x9F\x98\xB2'
ALTER TABLE t3 RENAME TO t2;
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 DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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
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 INDEX `c4`; check that it exists
ALTER TABLE t1c DROP FOREIGN KEY c2;
ERROR 42000: Can't DROP FOREIGN KEY `c2`; check that it exists
ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2;
ERROR 42000: Can't DROP FOREIGN KEY `c2`; check that it exists
ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2, DROP INDEX c2;
ERROR 42000: Can't DROP FOREIGN KEY `c2`; check that it 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 FOREIGN KEY `ẗ1C2`; check that it 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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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;
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;
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 COLLATE=latin1_swedish_ci
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;
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 COLLATE=latin1_swedish_ci
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;
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 COLLATE=latin1_swedish_ci
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;
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;
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 DEFAULT NULL,
  PRIMARY KEY (`pk`),
  FULLTEXT KEY `ct` (`ct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
CREATE TABLE tu (
pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT,
FULLTEXT INDEX(t)
) ENGINE=InnoDB;
ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
DROP TABLE tu;
CREATE TABLE tv (
pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT,
UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID),
FULLTEXT INDEX(t)
) ENGINE=InnoDB;
ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
DROP TABLE tv;
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 COLUMN DB_TRX_ID INT;
ERROR 42000: Incorrect column name 'DB_TRX_ID'
ALTER TABLE t1o ADD COLUMN db_roll_ptr INT;
ERROR 42000: Incorrect column name 'db_roll_ptr'
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT;
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID INT;
ERROR 42S21: Duplicate column name 'FTS_DOC_ID'
ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY
ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, DROP INDEX ct, ALGORITHM=INPLACE;
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN cu TEXT;
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT,
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 t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT;
ERROR 42000: Incorrect column name 'FTS_DOC_ID'
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED,
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 t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
ERROR 42000: Incorrect column name 'FTS_DOC_ID'
ALTER TABLE t1o ADD FULLTEXT INDEX(cu),
ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL, 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 t1o ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE;
ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY
ALTER TABLE t1o DROP COLUMN FTS_DOC_ID;
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 42000: Incorrect column name 'FTS_DOC_ID'
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. Try ALGORITHM=COPY
CREATE TABLE t1n LIKE t1o;
ALTER TABLE t1n ADD FULLTEXT INDEX(ct);
Warnings:
Note	1831	Duplicate index `ct_2`. This is deprecated and will be disallowed in a future release
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;
ALTER TABLE t1n CHANGE FTS_DOC_ÏD c1 INT, ALGORITHM=INPLACE;
ALTER TABLE t1n CHANGE c1 c2 INT, CHANGE c2 ct INT, CHANGE ct c1 TEXT,
ALGORITHM=INPLACE;
SHOW CREATE TABLE t1n;
Table	Create Table
t1n	CREATE TABLE `t1n` (
  `c2` int(11) NOT NULL,
  `ct` int(11) DEFAULT NULL,
  `c1` text DEFAULT NULL,
  `cu` text DEFAULT NULL,
  PRIMARY KEY (`c2`),
  FULLTEXT KEY `ct` (`c1`),
  FULLTEXT KEY `ct_2` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t1n CHANGE c2 c1 INT, CHANGE ct c2 INT, CHANGE c1 ct TEXT,
ALGORITHM=COPY;
SHOW CREATE TABLE t1n;
Table	Create Table
t1n	CREATE TABLE `t1n` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  `ct` text DEFAULT NULL,
  `cu` text DEFAULT NULL,
  PRIMARY KEY (`c1`),
  FULLTEXT KEY `ct` (`ct`),
  FULLTEXT KEY `ct_2` (`ct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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;
SHOW CREATE TABLE t1n;
Table	Create Table
t1n	CREATE TABLE `t1n` (
  `c1` int(11) NOT NULL,
  `c4` int(11) DEFAULT NULL,
  `ct` text DEFAULT NULL,
  `cu` text DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c4` (`c4`),
  FULLTEXT KEY `ct` (`ct`),
  FULLTEXT KEY `ct_2` (`ct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t1n DROP INDEX c4;
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;
SHOW CREATE TABLE t1n;
Table	Create Table
t1n	CREATE TABLE `t1n` (
  `c1` int(11) NOT NULL,
  `c11` int(11) DEFAULT NULL,
  `ct` text DEFAULT NULL,
  `cu` text DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c11` (`c11`),
  FULLTEXT KEY `ct` (`ct`),
  FULLTEXT KEY `ct_2` (`ct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t1n;
ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct,
ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct;
ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE;
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE;
ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY
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 DEFAULT NULL,
  `cu` text DEFAULT NULL,
  PRIMARY KEY (`FTS_DOC_ID`),
  FULLTEXT KEY `ct` (`ct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
DROP INDEX ct, ALGORITHM=INPLACE;
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 DEFAULT NULL,
  `cu` text DEFAULT NULL,
  PRIMARY KEY (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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
cu	3	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(FTS_DOC_ID),
ADD FULLTEXT INDEX(ct),
CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
ALTER TABLE t1o DROP INDEX ct, DROP INDEX FTS_DOC_ID_INDEX,
CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id);
ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
ADD FULLTEXT INDEX(ct);
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
cu	3	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
DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign;
CREATE TABLE t (t TEXT, FULLTEXT(t)) ENGINE=InnoDB;
DROP INDEX t ON t;
SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
prefix	name
FTS_0	test/FTS_AUX_BEING_DELETED
FTS_0	test/FTS_AUX_BEING_DELETED_CACHE
FTS_0	test/FTS_AUX_CONFIG
FTS_0	test/FTS_AUX_DELETED
FTS_0	test/FTS_AUX_DELETED_CACHE
SELECT sc.pos, sc.NAME 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/t';
pos	NAME
0	t
1	FTS_DOC_ID
ALTER TABLE t ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
prefix	name
ALTER TABLE t ADD FULLTEXT INDEX(t);
SELECT sc.pos, sc.NAME 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/t';
pos	NAME
0	t
1	FTS_DOC_ID
DROP TABLE t;
#
# Bug #19465984 INNODB DATA DICTIONARY IS NOT UPDATED WHILE
#		RENAMING THE COLUMN
#
CREATE TABLE t1(c1 INT NOT NULL, PRIMARY KEY(c1))ENGINE=INNODB;
CREATE TABLE t2(c2 INT NOT NULL, FOREIGN KEY(c2) REFERENCES t1(c1))ENGINE=INNODB;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c2` int(11) NOT NULL,
  KEY `c2` (`c2`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t1 CHANGE COLUMN c1 C1 INT;
ALTER TABLE t2 CHANGE COLUMN c2 C2 INT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `C1` int(11) NOT NULL,
  PRIMARY KEY (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `C2` int(11) DEFAULT NULL,
  KEY `c2` (`C2`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`C2`) REFERENCES `t1` (`C1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
ALTER TABLE t2 CHANGE COLUMN C2 c6 INT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c5` int(11) NOT NULL,
  PRIMARY KEY (`c5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c6` int(11) DEFAULT NULL,
  KEY `c2` (`c6`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c6`) REFERENCES `t1` (`c5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN
INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID
WHERE T.NAME='test/t1';
NAME
c5
SELECT F.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS F INNER JOIN
INFORMATION_SCHEMA.INNODB_SYS_INDEXES I ON F.INDEX_ID=I.INDEX_ID INNER JOIN
INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON I.TABLE_ID=T.TABLE_ID
WHERE T.NAME='test/t1' AND I.NAME='PRIMARY';
NAME
c5
SELECT C.REF_COL_NAME, C.FOR_COL_NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS C INNER JOIN
INFORMATION_SCHEMA.INNODB_SYS_FOREIGN F ON C.ID=F.ID
WHERE F.FOR_NAME='test/t2';
REF_COL_NAME	FOR_COL_NAME
c5	c6
DROP TABLE t2, t1;
# virtual columns case too
CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB;
ALTER TABLE t1 CHANGE COLUMN a A INT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `A` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`A`) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN
INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID
WHERE T.NAME='test/t1';
NAME
A
b
DROP TABLE t1;
# different FOREIGN KEY cases
CREATE TABLE t1 (
a INT UNIQUE KEY,
b INT UNIQUE KEY,
c INT UNIQUE KEY,
d INT UNIQUE KEY
) ENGINE=INNODB;
CREATE TABLE t2 (
aa INT,
bb INT,
cc INT,
dd INT
) ENGINE=INNODB;
INSERT INTO t1 VALUES (1, 1, 1, 1);
INSERT INTO t2 VALUES (1, 1, 1, 1);
ALTER TABLE t1 CHANGE a A INT, ALGORITHM=INPLACE;
ALTER TABLE t1 CHANGE c C INT, ALGORITHM=INPLACE;
ALTER TABLE t2 CHANGE cc CC INT, ALGORITHM=INPLACE;
ALTER TABLE t2 CHANGE dd DD INT, ALGORITHM=INPLACE;
SET foreign_key_checks=0;
ALTER TABLE t2
ADD FOREIGN KEY(aa) REFERENCES t1(a),
ADD FOREIGN KEY(bb) REFERENCES t1(b),
ADD FOREIGN KEY(cc) REFERENCES t1(c),
ADD FOREIGN KEY(dd) REFERENCES t1(d),
ALGORITHM=INPLACE;
ALTER TABLE t1 CHANGE b B INT, ALGORITHM=INPLACE;
ALTER TABLE t2 CHANGE aa AA INT, ALGORITHM=INPLACE;
# restart
ALTER TABLE t1 CHANGE d D INT, ALGORITHM=INPLACE;
ALTER TABLE t2 CHANGE bb BB INT, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `A` int(11) DEFAULT NULL,
  `B` int(11) DEFAULT NULL,
  `C` int(11) DEFAULT NULL,
  `D` int(11) DEFAULT NULL,
  UNIQUE KEY `a` (`A`),
  UNIQUE KEY `b` (`B`),
  UNIQUE KEY `c` (`C`),
  UNIQUE KEY `d` (`D`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `AA` int(11) DEFAULT NULL,
  `BB` int(11) DEFAULT NULL,
  `CC` int(11) DEFAULT NULL,
  `DD` int(11) DEFAULT NULL,
  KEY `aa` (`AA`),
  KEY `bb` (`BB`),
  KEY `CC` (`CC`),
  KEY `DD` (`DD`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`),
  CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`BB`) REFERENCES `t1` (`B`),
  CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`CC`) REFERENCES `t1` (`C`),
  CONSTRAINT `t2_ibfk_4` FOREIGN KEY (`DD`) REFERENCES `t1` (`D`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DELETE FROM t1 WHERE a=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DELETE FROM t1 WHERE A=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DELETE FROM t1 WHERE b=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DELETE FROM t1 WHERE B=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DELETE FROM t1 WHERE c=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DELETE FROM t1 WHERE C=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DELETE FROM t1 WHERE d=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DELETE FROM t1 WHERE D=1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
DROP TABLE t2, t1;
# virtual columns case too
CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB;
ALTER TABLE t1 CHANGE COLUMN a A INT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `A` int(11) DEFAULT NULL,
  `b` int(11) GENERATED ALWAYS AS (`A`) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN
INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID
WHERE T.NAME='test/t1';
NAME
A
b
DROP TABLE t1;
# and an MDEV-18041 regression related to indexes prefixes
create table `test` (
`test_old` varchar(255) NOT NULL,
`other` varchar(255) NOT NULL,
PRIMARY KEY (`test_old`,`other`),
UNIQUE KEY uk (`test_old`(100), `other`)
) ENGINE=InnoDB;
select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
name	pos
test_old	0
other	1
test_old	0
other	1
alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL;
select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
name	pos
test_new	0
other	1
test_new	0
other	1
drop table `test`;
#
# BUG 20029625 - HANDLE_FATAL_SIGNAL (SIG=11) IN
#                DICT_MEM_TABLE_COL_RENAME_LOW
#
CREATE TABLE parent(a INT, b INT, KEY(a, b)) ENGINE = InnoDB;
CREATE TABLE t1(a1 INT, a2 INT) ENGINE = InnoDB;
set foreign_key_checks=0;
ALTER TABLE t1 ADD CONSTRAINT fk_a FOREIGN KEY(a1, a2) REFERENCES parent(a, b) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE t1 CHANGE a2 a3 INT,ADD CONSTRAINT fk_1 FOREIGN KEY(a1, a3) REFERENCES parent(a, b) ON DELETE SET NULL ON UPDATE CASCADE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a1` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL,
  KEY `fk_1` (`a1`,`a3`),
  CONSTRAINT `fk_1` FOREIGN KEY (`a1`, `a3`) REFERENCES `parent` (`a`, `b`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_a` FOREIGN KEY (`a1`, `a3`) REFERENCES `parent` (`a`, `b`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE t1 CHANGE a3 a4 INT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a1` int(11) DEFAULT NULL,
  `a4` int(11) DEFAULT NULL,
  KEY `fk_1` (`a1`,`a4`),
  CONSTRAINT `fk_1` FOREIGN KEY (`a1`, `a4`) REFERENCES `parent` (`a`, `b`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_a` FOREIGN KEY (`a1`, `a4`) REFERENCES `parent` (`a`, `b`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
ALTER TABLE parent CHANGE b c INT;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a1` int(11) DEFAULT NULL,
  `a4` int(11) DEFAULT NULL,
  KEY `fk_1` (`a1`,`a4`),
  CONSTRAINT `fk_1` FOREIGN KEY (`a1`, `a4`) REFERENCES `parent` (`a`, `c`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_a` FOREIGN KEY (`a1`, `a4`) REFERENCES `parent` (`a`, `c`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1, parent;
#
#BUG#21514135 SCHEMA MISMATCH ERROR WHEN IMPORTING TABLESPACE AFTER
#DROPPING AN INDEX
#
SET NAMES utf8mb4;
CREATE DATABASE source_db;
CREATE DATABASE dest_db;
CREATE TABLE source_db.t1 (
id int(11) NOT NULL,
age int(11) DEFAULT NULL,
name varchar(20),
PRIMARY KEY (id),
KEY index1 (age)
) ENGINE=InnoDB;
ALTER TABLE source_db.t1 DROP INDEX index1, ADD INDEX index2(name, age), algorithm=inplace;
FLUSH TABLES source_db.t1 FOR EXPORT;
UNLOCK TABLES;
USE dest_db;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
ALTER TABLE dest_db.t1 DISCARD TABLESPACE;
ALTER TABLE dest_db.t1 IMPORT TABLESPACE;
CHECK TABLE dest_db.t1;
Table	Op	Msg_type	Msg_text
dest_db.t1	check	status	OK
SHOW CREATE TABLE dest_db.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT * FROM dest_db.t1;
id	age	name
DROP TABLE dest_db.t1;
ALTER TABLE source_db.t1 DROP INDEX index2, algorithm=inplace;
FLUSH TABLES source_db.t1 FOR EXPORT;
UNLOCK TABLES;
USE dest_db;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
ALTER TABLE dest_db.t1 DISCARD TABLESPACE;
ALTER TABLE dest_db.t1 IMPORT TABLESPACE;
CHECK TABLE dest_db.t1;
Table	Op	Msg_type	Msg_text
dest_db.t1	check	status	OK
SHOW CREATE TABLE dest_db.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT * FROM dest_db.t1;
id	age	name
DROP TABLE dest_db.t1;
DROP TABLE source_db.t1;
DROP DATABASE source_db;
DROP DATABASE dest_db;
#
# BUG #26334149 MYSQL CRASHES WHEN FULL TEXT INDEXES IBD FILES ARE
# ORPHANED DUE TO RENAME TABLE
#
CREATE DATABASE db1;
USE db1;
CREATE TABLE notes (
id int(11) NOT NULL AUTO_INCREMENT,
body text COLLATE utf8_unicode_ci,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
ROW_FORMAT=COMPRESSED;
ALTER TABLE notes ADD FULLTEXT INDEX index_ft_body (body(255));
DROP INDEX index_ft_body ON notes;
CREATE DATABASE db2;
RENAME TABLE db1.notes TO db2.notes;
DROP DATABASE db1;
DROP DATABASE db2;
USE test;
#
# MDEV-14038 ALTER TABLE does not exit on error with InnoDB + bad default function
#
CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
iNSERT INTO t1 VALUES (10);
ALTER TABLE t1 ADD b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0);
ERROR 22003: Out of range value for column 'b' at row 1
SELECT * FROM t1;
a
10
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
INSERT INTO t1 VALUES (10);
ALTER TABLE t1 ADD b DATE NOT NULL DEFAULT if(unix_timestamp()>1,TIMESTAMP'2001-01-01 10:20:30',0), algorithm=copy;
affected rows: 1
info: Records: 1  Duplicates: 0  Warnings: 1
Warnings:
Note	1265	Data truncated for column 'b' at row 1
SELECT * FROM t1;
a	b
10	2001-01-01
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
iNSERT INTO t1 VALUES (10);
ALTER TABLE t1 ADD b TIME NOT NULL DEFAULT if(unix_timestamp()>1,TIMESTAMP'2001-01-01 10:20:30',0);
affected rows: 0
info: Records: 0  Duplicates: 0  Warnings: 1
Warnings:
Note	1265	Data truncated for column 'b' at row 0
SELECT * FROM t1;
a	b
10	10:20:30
DROP TABLE t1;
#
# MDEV-18042 Server crashes in mysql_alter_table upon adding a non-null
# date column under NO_ZERO_DATE with ALGORITHM=INPLACE
#
SET @OLD_SQL_MODE= @@SQL_MODE;
SET @@SQL_MODE= 'NO_ZERO_DATE';
CREATE OR REPLACE TABLE t1 (i INT) ENGINE=MyISAM;
ALTER TABLE t1 ADD COLUMN d DATE NOT NULL, ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
CREATE OR REPLACE TABLE t1 (i INT) ENGINE=InnoDB;
ALTER TABLE t1 ADD d DATETIME NOT NULL CHECK (f <= 0), ALGORITHM=COPY;
ERROR 42S22: Unknown column 'f' in 'CHECK'
CREATE OR REPLACE TABLE t1 (a int) ENGINE=InnoDB;
ALTER TABLE t1 ADD COLUMN b DATETIME NOT NULL, LOCK=NONE;
# Cleanup
SET @@SQL_MODE= @OLD_SQL_MODE;
DROP TABLE t1;
#
# Bug#20977779 CANNOT IMPORT TABLES CONTAINING PREFIX INDEXES
#
CREATE TABLE t1 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32),
PRIMARY KEY (c1, c2, c3))
ENGINE=InnoDB;
ALTER TABLE t1 ADD INDEX ind1(c1(5), c2, c3);
ALTER TABLE t1 ADD INDEX ind2(c3, c1(10), c2);
ALTER TABLE t1 ADD INDEX ind3(c2, c3, c1(20));
INSERT INTO t1 VALUES ('Test Data -1', 'Test Data -2', 'Test Data -3');
# Test with 2ndary index having prefix
FLUSH TABLES test.t1 FOR EXPORT;
UNLOCK TABLES;
ALTER TABLE test.t1 DISCARD TABLESPACE;
ALTER TABLE test.t1 IMPORT TABLESPACE;
CHECK TABLE test.t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE test.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(32) NOT NULL,
  `c2` varchar(32) NOT NULL,
  `c3` varchar(32) NOT NULL,
  PRIMARY KEY (`c1`,`c2`,`c3`),
  KEY `ind1` (`c1`(5),`c2`,`c3`),
  KEY `ind2` (`c3`,`c1`(10),`c2`),
  KEY `ind3` (`c2`,`c3`,`c1`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * FROM test.t1;
c1	c2	c3
Test Data -1	Test Data -2	Test Data -3
# Test with PK & 2ndary index with prefix
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1(5), c2(10), c3(20));
FLUSH TABLES test.t1 FOR EXPORT;
UNLOCK TABLES;
ALTER TABLE test.t1 DISCARD TABLESPACE;
ALTER TABLE test.t1 IMPORT TABLESPACE;
CHECK TABLE test.t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW CREATE TABLE test.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` varchar(32) NOT NULL,
  `c2` varchar(32) NOT NULL,
  `c3` varchar(32) NOT NULL,
  PRIMARY KEY (`c1`(5),`c2`(10),`c3`(20)),
  KEY `ind1` (`c1`(5),`c2`,`c3`),
  KEY `ind2` (`c3`,`c1`(10),`c2`),
  KEY `ind3` (`c2`,`c3`,`c1`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * FROM test.t1;
c1	c2	c3
Test Data -1	Test Data -2	Test Data -3
DROP TABLE t1;
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;