Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 # This is the test for bug #47621, column rename operation should
3 # not result in column definition inconsistency between MySQL and
4 # InnoDB
5
6
7 CREATE TABLE bug47621 (salesperson INT) ENGINE=InnoDB;
8
9 # Change the column name
10 ALTER 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
14 create index orgs on bug47621(sales_acct_id);
15
16 # Change the column name back with the index defined on it.
17 ALTER TABLE bug47621 CHANGE sales_acct_id salesperson INT;
18
19 drop table bug47621;
20
21 CREATE TABLE bug47621_sale (
22 salesperson INT,
23 PRIMARY KEY(salesperson)) engine = innodb;
24
25 CREATE 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
32 insert into bug47621_sale values(9);
33
34 insert 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
47 ALTER TABLE bug47621_shirt CHANGE id new_id INT;
48
49 # Referencing table dropped, the rename operation on related columns
50 # could proceed
51 drop table bug47621_shirt;
52
53 ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT;
54
55 ALTER TABLE bug47621_sale ADD INDEX idx (sales_acct_id);
56
57 drop table bug47621_sale;