839 lines
32 KiB
Plaintext
839 lines
32 KiB
Plaintext
SET NAMES utf8;
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
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_TABLES
|
|
WHERE NAME LIKE 'test/t%';
|
|
CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_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_FOREIGN i
|
|
WHERE FOR_NAME LIKE 'test/t%';
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c2 1
|
|
c3 c2 1
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c2 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c2
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c2 1
|
|
c3 c2 1
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_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,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c2` (`c2`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c2 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c2
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c2 1
|
|
c3 c2 1
|
|
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_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c2 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c2
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c2 1
|
|
c3 c2 1
|
|
ALTER TABLE t1 CHANGE C2 c3 INT;
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c3 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c3
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c3 1
|
|
c3 c2 1
|
|
ALTER TABLE t1 CHANGE c3 C INT;
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
C 1 6 1027 4
|
|
c1 0 6 1283 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 C
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 C 1
|
|
c3 c2 1
|
|
ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT;
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 Cöŀumň_TWO 1
|
|
c3 c2 1
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
Cöŀumň_TWO 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 Cöŀumň_TWO
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 Cöŀumň_TWO 1
|
|
c3 c2 1
|
|
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_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=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_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,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c2` (`1234567890123456789012345678901234567890123456789012345678901234`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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 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;
|
|
SELECT st.NAME, i.NAME
|
|
FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
RENAME TABLE t2 TO t1;
|
|
SELECT st.NAME, i.NAME
|
|
FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_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_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c3 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c3
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c3 1
|
|
c3 c2 1
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c3 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c3
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c3 1
|
|
c3 c2 1
|
|
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
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c3 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c3
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c3 1
|
|
c3 c2 1
|
|
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`),
|
|
KEY `c3` (`c3`),
|
|
CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c3 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c3
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.ID;
|
|
FOR_COL_NAME REF_COL_NAME POS
|
|
c2 c3 1
|
|
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`),
|
|
KEY `c3` (`c3`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c3 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
c2 0 c3
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.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_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c1 0 6 1283 4
|
|
c2 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
PRIMARY 0 c1
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.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
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
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;
|
|
Warnings:
|
|
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
|
|
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT,
|
|
ALGORITHM=INPLACE;
|
|
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
|
|
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), 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(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED,
|
|
ALGORITHM=INPLACE;
|
|
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
|
|
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), 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(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;
|
|
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;
|
|
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 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 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 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 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 1831 Duplicate index 'ct_2' defined on the table 'test.t1n'. This is deprecated and will be disallowed in a future release.
|
|
ALTER TABLE t1n 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 t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=COPY;
|
|
ERROR HY000: Column 'Fts_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
|
|
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,
|
|
`cu` text,
|
|
PRIMARY KEY (`c2`),
|
|
FULLTEXT KEY `ct` (`c1`),
|
|
FULLTEXT KEY `ct_2` (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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,
|
|
`cu` text,
|
|
PRIMARY KEY (`c1`),
|
|
FULLTEXT KEY `ct` (`ct`),
|
|
FULLTEXT KEY `ct_2` (`ct`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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,
|
|
`cu` text,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c4` (`c4`),
|
|
FULLTEXT KEY `ct` (`ct`),
|
|
FULLTEXT KEY `ct_2` (`ct`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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,
|
|
`cu` text,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c11` (`c11`),
|
|
FULLTEXT KEY `ct` (`ct`),
|
|
FULLTEXT KEY `ct_2` (`ct`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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 INPLACE. 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_columns sc
|
|
INNER JOIN information_schema.innodb_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,
|
|
`cu` text,
|
|
PRIMARY KEY (`FTS_DOC_ID`),
|
|
FULLTEXT KEY `ct` (`ct`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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,
|
|
`cu` text,
|
|
PRIMARY KEY (`foo_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign;
|
|
CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE NAME='test/t1o';
|
|
CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
|
|
CREATE TABLE sys_foreign SELECT i.*
|
|
FROM INFORMATION_SCHEMA.INNODB_FOREIGN i WHERE FOR_NAME='test/t1o';
|
|
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
|
|
FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c2 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
cu 3 5 16711932 10
|
|
foo_id 0 6 1800 8
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
PRIMARY 0 foo_id
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.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);
|
|
ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
|
|
ALGORITHM=INPLACE;
|
|
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_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_COLUMNS i
|
|
INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
|
|
NAME POS MTYPE PRTYPE LEN
|
|
c2 1 6 1027 4
|
|
ct 2 5 16711932 10
|
|
cu 3 5 16711932 10
|
|
FTS_DOC_ID 0 6 1800 8
|
|
SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
|
|
INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
|
|
NAME POS NAME
|
|
ct 0 ct
|
|
FTS_DOC_ID_INDEX 0 FTS_DOC_ID
|
|
PRIMARY 0 FTS_DOC_ID
|
|
SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
|
|
INNER JOIN sys_foreign sf ON i.ID = sf.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_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_columns sc
|
|
INNER JOIN information_schema.innodb_tables st
|
|
ON sc.TABLE_ID=st.TABLE_ID
|
|
WHERE st.NAME='test/t' ORDER BY sc.pos, sc.NAME;
|
|
pos NAME
|
|
0 t
|
|
ALTER TABLE t ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
|
|
SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
|
|
prefix name
|
|
ALTER TABLE t ADD FULLTEXT INDEX(t);
|
|
Warnings:
|
|
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
|
|
SELECT sc.pos, sc.NAME FROM information_schema.innodb_columns sc
|
|
INNER JOIN information_schema.innodb_tables st
|
|
ON sc.TABLE_ID=st.TABLE_ID
|
|
WHERE st.NAME='test/t' ORDER BY sc.pos, sc.NAME;
|
|
pos NAME
|
|
0 t
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
ALTER TABLE t1 CHANGE COLUMN c1 C1 INT;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`C1` int(11) NOT NULL,
|
|
PRIMARY KEY (`C1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c5` int(11) NOT NULL,
|
|
PRIMARY KEY (`c5`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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` (`c5`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t2, t1;
|
|
#
|
|
#BUG#21514135 SCHEMA MISMATCH ERROR WHEN IMPORTING TABLESPACE AFTER
|
|
#DROPPING AN INDEX
|
|
#
|
|
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;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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_0900_ai_ci;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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_0900_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_0900_ai_ci;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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_0900_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#22005726 ASSERT COL->ORD_PART IN ROW_BUILD_INDEX_ENTRY_LOW AT
|
|
# ROW0ROW.CC:299
|
|
#
|
|
USE test;
|
|
CREATE TABLE t1 (col1 INT, col2 TEXT) ENGINE = InnoDB;
|
|
INSERT INTO t1 (col1, col2) VALUES (2 , 'A');
|
|
ALTER TABLE t1 ADD KEY idx (col2(10));
|
|
INSERT INTO t1 (col1, col2) VALUES (2 , 'A');
|
|
ALTER TABLE t1 DROP KEY idx, ADD UNIQUE KEY uidx (col1);
|
|
ERROR 23000: Duplicate entry '2' for key 'uidx'
|
|
DELETE FROM t1 WHERE col1 = 2;
|
|
DROP TABLE t1;
|