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