mirror of
https://github.com/MariaDB/server.git
synced 2025-07-26 21:25:02 +02:00
cffbb17480
Before MySQL 4.0.18, user-specified constraint names were ignored. Starting with MySQL 4.0.18, the specified constraint name was prepended with the schema name and '/'. Now we are transforming into a format where the constraint name is prepended with the dict_table_t::name and the impossible UTF-8 sequence 0xff. Generated constraint names will be ASCII decimal numbers. On upgrade, old FOREIGN KEY constraint names will be displayed without any schema name prefix. They will be updated to the new format on DDL operations. dict_foreign_t::sql_id(): Return the SQL constraint name without any schemaname/tablename\377 or schemaname/ prefix. row_rename_table_for_mysql(), dict_table_rename_in_cache(): Simplify the logic: Just rename constraints to the new format. dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id(). innobase_get_foreign_key_info(): Let my_error() refer to erroneous anonymous constraints as "(null)". row_delete_constraint(): Try to drop all 3 constraint name variants. Reviewed by: Thirunarayanan Balathandayuthapani Tested by: Matthias Leich
1591 lines
39 KiB
Text
1591 lines
39 KiB
Text
#
|
|
# This is a copy of innodb-alter.test except using remote tablespaces
|
|
# and showing those files.
|
|
#
|
|
ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
|
|
SET default_storage_engine=InnoDB;
|
|
SET NAMES utf8mb4;
|
|
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
|
|
t1c2 c2 c2 0
|
|
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
|
|
t1c2 c2 c2 0
|
|
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 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 DEFAULT NULL,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c2` (`c2`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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
|
|
t1c2 c2 c2 0
|
|
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
|
|
db.opt
|
|
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
|
|
t1c2 c2 c2 0
|
|
t1c3 c3 c2 0
|
|
ALTER TABLE t1 CHANGE C2 c3 INT;
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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
|
|
t1c2 c2 c3 0
|
|
t1c3 c3 c2 0
|
|
ALTER TABLE t1 CHANGE c3 C INT;
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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
|
|
t1c2 c2 C 0
|
|
t1c3 c3 c2 0
|
|
ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT;
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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
|
|
t1c2 c2 Cöŀumň_TWO 0
|
|
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
|
|
t1c2 c2 Cöŀumň_TWO 0
|
|
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
|
|
db.opt
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c2` (`c3`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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
|
|
db.opt
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c2` (`1234567890123456789012345678901234567890123456789012345678901234`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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
|
|
db.opt
|
|
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
|
|
db.opt
|
|
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 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;
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c2` (`c3`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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
|
|
db.opt
|
|
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
|
|
t1c2 c2 c3 0
|
|
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 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 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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
|
|
t1c2 c2 c3 0
|
|
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
|
|
db.opt
|
|
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 COLLATE=latin1_swedish_ci 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
|
|
t1c2 c2 c3 0
|
|
t1c3 c3 c2 0
|
|
ALTER TABLE t1c DROP FOREIGN KEY t1C3;
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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 COLLATE=latin1_swedish_ci 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
|
|
t1c2 c2 c3 0
|
|
ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2;
|
|
### files in MYSQL_DATA_DIR/test
|
|
db.opt
|
|
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 COLLATE=latin1_swedish_ci 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
|
|
db.opt
|
|
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;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
db.opt
|
|
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
|
|
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
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`pk`),
|
|
FULLTEXT KEY `ct` (`ct`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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. Try ALGORITHM=COPY
|
|
CREATE TABLE t1n LIKE t1o;
|
|
ALTER TABLE t1n ADD FULLTEXT INDEX(ct);
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`c2`),
|
|
FULLTEXT KEY `ct` (`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;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`c1`),
|
|
FULLTEXT KEY `ct` (`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;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c4` (`c4`),
|
|
FULLTEXT KEY `ct` (`ct`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
ALTER TABLE t1n DROP INDEX c4;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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 DEFAULT NULL,
|
|
PRIMARY KEY (`c1`),
|
|
KEY `c11` (`c11`),
|
|
FULLTEXT KEY `ct` (`ct`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DROP TABLE t1n;
|
|
ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
db.opt
|
|
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
|
|
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
|
|
t1c.ibd
|
|
t1p.ibd
|
|
tt.ibd
|
|
ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
|
|
CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
|
|
ALGORITHM=INPLACE;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
t1c.ibd
|
|
t1p.ibd
|
|
tt.ibd
|
|
ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
|
|
ALGORITHM=INPLACE, LOCK=NONE;
|
|
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,
|
|
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, LOCK=NONE;
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
db.opt
|
|
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
|
|
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
|
|
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 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
|
|
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.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
db.opt
|
|
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
|
|
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
|
|
tt.ibd
|
|
ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
|
|
ADD FULLTEXT INDEX(ct);
|
|
### files in MYSQL_DATA_DIR/test
|
|
FTS_AUX_INDEX_1.isl
|
|
FTS_AUX_INDEX_2.isl
|
|
FTS_AUX_INDEX_3.isl
|
|
FTS_AUX_INDEX_4.isl
|
|
FTS_AUX_INDEX_5.isl
|
|
FTS_AUX_INDEX_6.isl
|
|
FTS_AUX_BEING_DELETED.isl
|
|
FTS_AUX_BEING_DELETED_CACHE.isl
|
|
FTS_AUX_CONFIG.isl
|
|
FTS_AUX_DELETED.isl
|
|
FTS_AUX_DELETED_CACHE.isl
|
|
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
|
|
db.opt
|
|
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
|
|
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
|
|
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
|
|
db.opt
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
|