Debian Codegraph @

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

raw
1 ################################################################################
2 # This test checks correctness of error handling during setting incorrect
3 # primary key using alter table. Setting new PK with duplicated rows should
4 # fail and report an error, but should not cause early exit.
5 ################################################################################
6
7 --source include/have_debug.inc
8
9 SET GLOBAL debug = "+d,ddl_buf_add_two";
10
11 --echo #
12 --echo # Test#1 : Try and fail to rebuild the table's PK from 2 columns to 1 column with duplicates.
13 --echo #
14
15 CREATE TABLE t1 (
16 id0 int,
17 id1 int,
18 PRIMARY KEY (id0, id1)
19 ) ENGINE=InnoDB;
20
21 INSERT INTO t1(id0, id1) VALUES
22 (1, 1),
23 (2, 2),
24 (2, 3);
25
26 --error ER_DUP_ENTRY
27 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id0), ALGORITHM = INPLACE;
28
29 --echo # Check that records are present.
30 SELECT * FROM t1 ORDER BY id0, id1;
31
32 DROP TABLE t1;
33
34 --echo #
35 --echo # Test#2 : Try and fail to rebuild the table's PK from 2 columns to 1 column with duplicates.
36 --echo # Then succeed doing it on second set of two columns with unique rows.
37 --echo #
38
39 CREATE TABLE t2 (
40 id0 int,
41 id1 int,
42 id2 int,
43 PRIMARY KEY (id0, id2)
44 ) ENGINE=InnoDB;
45
46 INSERT INTO t2(id0, id1, id2) VALUES
47 (1, 2, 3),
48 (1, 2, 4),
49 (1, 3, 5);
50
51 --error ER_DUP_ENTRY
52 ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(id1), ALGORITHM = INPLACE;
53
54 ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2), ALGORITHM = INPLACE;
55
56 --echo # Check that records are present.
57 SELECT * FROM t2 ORDER BY id0, id1, id2;
58
59 DROP TABLE t2;
60
61 --echo #
62 --echo # Test#3 : Try and fail to rebuild the table's PK from unique 1 column to
63 --echo # set of 2 columns with duplicate rows.
64 --echo #
65
66 CREATE TABLE t3 (
67 id0 int,
68 id1 char(2),
69 id2 varchar(3),
70 id3 date,
71 id4 bool,
72 PRIMARY KEY (id0)
73 ) ENGINE=InnoDB;
74
75 INSERT INTO t3(id0, id1, id2, id3, id4) VALUES
76 (1, '22', '3', '2020-01-01', true),
77 (2, '33', '4', '2020-02-01', false),
78 (3, '22', '3', '2020-01-01', true);
79
80 --error ER_DUP_ENTRY
81 ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2), ALGORITHM = INPLACE;
82
83 --echo #
84 --echo # Test#4 : Change primary key to all 5 columns. Then fail to change to
85 --echo # last 4 ones due to duplicates.
86 --echo #
87
88 ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id0, id1, id2, id3, id4), ALGORITHM = INPLACE;
89
90 --error ER_DUP_ENTRY
91 ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2, id3, id4), ALGORITHM = INPLACE;
92
93 --echo # Check that records are present.
94 SELECT * FROM t3 ORDER BY id0, id1, id2, id3, id4;
95
96 DROP TABLE t3;
97
98 SET GLOBAL debug = "-d,ddl_buf_add_two";