path: root/mysql-test/suite/innodb/t/innodb-alter.test

--source include/have_innodb.inc
--source include/default_charset.inc

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;

-- source suite/innodb/include/innodb_dict.inc

SHOW CREATE TABLE t1;
ALTER TABLE t1 ALTER c2 DROP DEFAULT;
SHOW CREATE TABLE t1;

-- source suite/innodb/include/innodb_dict.inc

# These should be no-op.
ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1;
ALTER TABLE t1 CHANGE c1 c1 INT FIRST;

-- source suite/innodb/include/innodb_dict.inc

ALTER TABLE t1 CHANGE C2 c3 INT;

-- source suite/innodb/include/innodb_dict.inc

ALTER TABLE t1 CHANGE c3 C INT;

-- source suite/innodb/include/innodb_dict.inc

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;

-- source suite/innodb/include/innodb_dict.inc

-- error ER_BAD_FIELD_ERROR
ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT;

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;

SHOW CREATE TABLE t3;
SHOW CREATE TABLE t1c;

# The maximum column name length should be 64 characters.
--error ER_TOO_LONG_IDENT
ALTER TABLE t3 CHANGE c3
`12345678901234567890123456789012345678901234567890123456789012345` INT;
ALTER TABLE t3 CHANGE c3
`1234567890123456789012345678901234567890123456789012345678901234` INT;
SHOW CREATE TABLE t3;

# Test the length limit with non-ASCII utf-8 characters.
--error ER_TOO_LONG_IDENT
ALTER TABLE t3 CHANGE
`1234567890123456789012345678901234567890123456789012345678901234`
`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿偀` INT;
--error ER_TOO_LONG_IDENT
ALTER TABLE t3 CHANGE
`1234567890123456789012345678901234567890123456789012345678901234`
`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿ä` INT;
ALTER TABLE t3 CHANGE
`1234567890123456789012345678901234567890123456789012345678901234`
`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT;

# check that the rename is case-insensitive (note the upper-case ä at end)
ALTER TABLE t3 CHANGE
`倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä`
c3 INT;

# test with 4-byte UTF-8 (should be disallowed)
--error ER_INVALID_CHARACTER_STRING
ALTER TABLE t3 CHANGE c3 𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜 INT;
--error ER_INVALID_CHARACTER_STRING
ALTER TABLE t3 CHANGE c3 😲 INT;

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;

SHOW CREATE TABLE t2;

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;

-- source suite/innodb/include/innodb_dict.inc

--error ER_DROP_INDEX_FK
ALTER TABLE t1 DROP INDEX c2;

--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE t1 DROP INDEX c4;

--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE t1c DROP FOREIGN KEY c2;

--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2;

--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2, DROP INDEX c2;

--error ER_DROP_INDEX_FK
ALTER TABLE t1c DROP INDEX c2;

--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE t1c DROP FOREIGN KEY ẗ1C2;

SHOW CREATE TABLE t1c;

SET foreign_key_checks=0;
DROP TABLE t1p;
SET foreign_key_checks=1;

SHOW CREATE TABLE t1c;

-- source suite/innodb/include/innodb_dict.inc

CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB;

--error ER_DROP_INDEX_FK
ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3;
--error ER_DROP_INDEX_FK
ALTER TABLE t1c DROP INDEX C3;

SET foreign_key_checks=0;
ALTER TABLE t1c DROP INDEX C3;
SET foreign_key_checks=1;

SHOW CREATE TABLE t1c;
-- source suite/innodb/include/innodb_dict.inc

ALTER TABLE t1c DROP FOREIGN KEY t1C3;

SHOW CREATE TABLE t1c;
-- source suite/innodb/include/innodb_dict.inc

ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2;

SHOW CREATE TABLE t1c;

-- source suite/innodb/include/innodb_dict.inc

ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT;

-- source suite/innodb/include/innodb_dict.inc

CREATE TABLE t1o LIKE t1;

# This will implicitly add a FTS_DOC_ID column, which cannot be done online.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
ALGORITHM=INPLACE, LOCK=SHARED;

# The output should be empty, because index->id was reassigned.
-- source suite/innodb/include/innodb_dict.inc

SHOW CREATE TABLE tt;
# Non-instant ADD COLUMN would require the table to be rebuilt.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE;
# This is still non-instant ADD COLUMN, because FTS_DOC_ID is hidden.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE;

CREATE TABLE tu (
  pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT,
  FULLTEXT INDEX(t)
) ENGINE=InnoDB;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE;
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;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE;
DROP TABLE tv;

# DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR are reserved InnoDB system column names.
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=INPLACE;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o CHANGE c1 DB_TRX_ID INT;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o CHANGE c1 db_roll_ptr INT;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o ADD COLUMN DB_TRX_ID INT;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o ADD COLUMN db_roll_ptr INT;

--error ER_INNODB_FT_WRONG_DOCID_COLUMN
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT;
--error ER_INNODB_FT_WRONG_DOCID_COLUMN
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;

ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL;

--error ER_DUP_FIELDNAME
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID INT;

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;

ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, DROP INDEX ct, ALGORITHM=INPLACE;

# This creates a hidden FTS_DOC_ID column.
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN cu TEXT;

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT,
ALGORITHM=INPLACE;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED,
ALGORITHM=INPLACE;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;

# This would drop the hidden FTS_DOC_ID column and create
# a fulltext index on ct and another fulltext index on cu.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o ADD FULLTEXT INDEX(cu),
ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;

# Replace the hidden FTS_DOC_ID column with a user-visible one.
ALTER TABLE t1o ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE;
# Replace the user-visible FTS_DOC_ID column with a hidden one.
# We do not support this in-place.
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
ALTER TABLE t1o DROP COLUMN FTS_DOC_ID;

# FTS_DOC_ID is the internal row identifier for full-text search.
# It should be of type BIGINT UNSIGNED NOT NULL.
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
ALGORITHM=COPY;

--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
ALGORITHM=INPLACE;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_Doc_ID INT,
ALGORITHM=INPLACE;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE;

CREATE TABLE t1n LIKE t1o;

ALTER TABLE t1n ADD FULLTEXT INDEX(ct);
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=INPLACE;
--error ER_WRONG_COLUMN_NAME
ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=COPY;
--error ER_BAD_FIELD_ERROR
ALTER TABLE t1n CHANGE FTS_DOC_ID c11 INT, ALGORITHM=INPLACE;
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;
ALTER TABLE t1n CHANGE c2 c1 INT, CHANGE ct c2 INT, CHANGE c1 ct TEXT,
ALGORITHM=COPY;
SHOW CREATE TABLE t1n;

--error ER_KEY_COLUMN_DOES_NOT_EXIST
ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
--error ER_KEY_COLUMN_DOES_NOT_EXIST
ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY;
ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1n;
ALTER TABLE t1n DROP INDEX c4;
--error ER_DUP_FIELDNAME
ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE;
ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE;

SHOW CREATE TABLE t1n;
DROP TABLE t1n;

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct,
ALGORITHM=INPLACE;

# This will copy the table, removing the hidden FTS_DOC_ID column.
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;

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE;

# This should not show duplicates.
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';

SHOW CREATE TABLE t1o;

ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
DROP INDEX ct, ALGORITHM=INPLACE;

SHOW CREATE TABLE t1o;

DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign;

# Check the internal schemata of tt, t1o.

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';

-- source suite/innodb/include/innodb_dict.inc

# Ensure that there exists no hidden FTS_DOC_ID_INDEX on foo_id.

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;

-- source suite/innodb/include/innodb_dict.inc

DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign;

CREATE TABLE t (t TEXT, FULLTEXT(t)) ENGINE=InnoDB;
DROP INDEX t ON t;

LET $regexp=/FTS_([0-9a-f_]+)([A-Z_]+)/FTS_AUX_\2/;
--replace_regex $regexp
SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE '%FTS_%' ORDER BY 1, 2;

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';

ALTER TABLE t ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;

--replace_regex $regexp
SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE '%FTS_%' ORDER BY 1, 2;

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';

DROP TABLE t;

--disable_query_log
call mtr.add_suppression("deleting orphaned .ibd file");
--enable_query_log

--echo #
--echo # Bug #19465984 INNODB DATA DICTIONARY IS NOT UPDATED WHILE
--echo #		RENAMING THE COLUMN
--echo #
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;
SHOW CREATE TABLE t2;
ALTER TABLE t1 CHANGE COLUMN c1 C1 INT;
ALTER TABLE t2 CHANGE COLUMN c2 C2 INT;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
ALTER TABLE t2 CHANGE COLUMN C2 c6 INT;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

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';

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';

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';

DROP TABLE t2, t1;
--echo # 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;
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';
DROP TABLE t1;


--echo # 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;

--source include/restart_mysqld.inc

ALTER TABLE t1 CHANGE d D INT, ALGORITHM=INPLACE;
ALTER TABLE t2 CHANGE bb BB INT, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE a=1;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE A=1;

--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE b=1;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE B=1;

--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE c=1;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE C=1;

--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE d=1;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1 WHERE D=1;

DROP TABLE t2, t1;

--echo # 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;
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';
DROP TABLE t1;

--echo # 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');
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');
drop table `test`;


--echo #
--echo # BUG 20029625 - HANDLE_FATAL_SIGNAL (SIG=11) IN
--echo #                DICT_MEM_TABLE_COL_RENAME_LOW
--echo #
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;
CHECK TABLE t1;

ALTER TABLE t1 CHANGE a3 a4 INT;
SHOW CREATE TABLE t1;
CHECK TABLE t1;

ALTER TABLE parent CHANGE b c INT;
SHOW CREATE TABLE t1;
CHECK TABLE t1;

DROP TABLE t1, parent;

--echo #
--echo #BUG#21514135 SCHEMA MISMATCH ERROR WHEN IMPORTING TABLESPACE AFTER
--echo #DROPPING AN INDEX
--echo #

--disable_query_log
call mtr.add_suppression("\\[Warning\\] InnoDB: Unknown index id .* on page");
--enable_query_log

let $source_db = source_db;
let $dest_db = dest_db;

SET NAMES utf8mb4;

eval CREATE DATABASE $source_db;
eval CREATE DATABASE $dest_db;

eval 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;

eval ALTER TABLE $source_db.t1 DROP INDEX index1, ADD INDEX index2(name, age), algorithm=inplace;

--source suite/innodb/include/import.inc

eval ALTER TABLE $source_db.t1 DROP INDEX index2, algorithm=inplace;

--source suite/innodb/include/import.inc

eval DROP TABLE $source_db.t1;
eval DROP DATABASE $source_db;
eval DROP DATABASE $dest_db;

--echo #
--echo # BUG #26334149 MYSQL CRASHES WHEN FULL TEXT INDEXES IBD FILES ARE
--echo # ORPHANED DUE TO RENAME TABLE
--echo #
CREATE DATABASE db1; USE db1;
--disable_query_log
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
SET GLOBAL innodb_read_only_compressed=OFF;
--enable_query_log
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;
--disable_query_log
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
--enable_query_log

CREATE DATABASE db2;
RENAME TABLE db1.notes TO db2.notes;
DROP DATABASE db1;
DROP DATABASE db2;

USE test;

#
# End of 10.1 tests
#

--echo #
--echo # MDEV-14038 ALTER TABLE does not exit on error with InnoDB + bad default function
--echo #

CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
iNSERT INTO t1 VALUES (10);
--error ER_WARN_DATA_OUT_OF_RANGE
ALTER TABLE t1 ADD b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0);
SELECT * FROM t1;
DROP TABLE t1;

# DATETIME-to-DATE truncation is OK
CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
INSERT INTO t1 VALUES (10);
--enable_info
ALTER TABLE t1 ADD b DATE NOT NULL DEFAULT if(unix_timestamp()>1,TIMESTAMP'2001-01-01 10:20:30',0), algorithm=copy;
--disable_info
SELECT * FROM t1;
DROP TABLE t1;

# DATETIME-to-TIME truncation is OK
CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
iNSERT INTO t1 VALUES (10);
--enable_info
ALTER TABLE t1 ADD b TIME NOT NULL DEFAULT if(unix_timestamp()>1,TIMESTAMP'2001-01-01 10:20:30',0);
--disable_info
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # MDEV-18042 Server crashes in mysql_alter_table upon adding a non-null
--echo # date column under NO_ZERO_DATE with ALGORITHM=INPLACE
--echo #

SET @OLD_SQL_MODE= @@SQL_MODE;
SET @@SQL_MODE= 'NO_ZERO_DATE';
CREATE OR REPLACE TABLE t1 (i INT) ENGINE=MyISAM;
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t1 ADD COLUMN d DATE NOT NULL, ALGORITHM=INPLACE;
 
CREATE OR REPLACE TABLE t1 (i INT) ENGINE=InnoDB;
--error ER_BAD_FIELD_ERROR
ALTER TABLE t1 ADD d DATETIME NOT NULL CHECK (f <= 0), ALGORITHM=COPY;

CREATE OR REPLACE TABLE t1 (a int) ENGINE=InnoDB;
ALTER TABLE t1 ADD COLUMN b DATETIME NOT NULL, LOCK=NONE;

--echo # Cleanup
SET @@SQL_MODE= @OLD_SQL_MODE;
DROP TABLE t1;

#
# End of 10.2 tests
#