Debian Codegraph @

mysql-test/suite/innodb/t/innodb_bug47621.test

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