1# This is the test for bug #47621, column rename operation should 2# not result in column definition inconsistency between MySQL and 3# InnoDB 4 5--source include/have_innodb.inc 6 7CREATE TABLE bug47621 (salesperson INT) ENGINE=InnoDB; 8 9# Change the column name 10ALTER TABLE bug47621 CHANGE salesperson sales_acct_id INT; 11 12# If there is inconsistency of column name definition 13# in MySQL or InnoDB, following create index would fail 14create index orgs on bug47621(sales_acct_id); 15 16# Change the column name back with the index defined on it. 17ALTER TABLE bug47621 CHANGE sales_acct_id salesperson INT; 18 19drop table bug47621; 20 21CREATE TABLE bug47621_sale ( 22 salesperson INT, 23 PRIMARY KEY(salesperson)) engine = innodb; 24 25CREATE TABLE bug47621_shirt( 26 id SMALLINT, 27 owner INT, 28 FOREIGN KEY(owner) 29 references bug47621_sale(salesperson) ON DELETE RESTRICT) 30 engine = innodb; 31 32insert into bug47621_sale values(9); 33 34insert into bug47621_shirt values(1, 9); 35 36# Any rename operation on columns involved in a reference constraint will 37# fail, as it will be rejected by InnoDB row_rename_table_for_mysql(). 38# In above example, any rename on column "salesperson" for table 39# "bug47621_sale", or on column "owner" for table "bug47621_shirt will 40# be blocked. We do not put such rename in the test since InnoDB error 41# message will be printed in the error log, and result in test failure. 42# 43# ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; 44 45# Any rename on columns not involved in the foreign key constraint 46# could still proceed 47ALTER TABLE bug47621_shirt CHANGE id new_id INT; 48 49# Referencing table dropped, the rename operation on related columns 50# could proceed 51drop table bug47621_shirt; 52 53ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; 54 55ALTER TABLE bug47621_sale ADD INDEX idx (sales_acct_id); 56 57drop table bug47621_sale; 58