Debian Codegraph @

mysql-test/suite/innodb/r/alter_table_update_primary_key.result

raw
1 SET GLOBAL debug = "+d,ddl_buf_add_two";
2 #
3 # Test#1 : Try and fail to rebuild the table's PK from 2 columns to 1 column with duplicates.
4 #
5 CREATE TABLE t1 (
6 id0 int,
7 id1 int,
8 PRIMARY KEY (id0, id1)
9 ) ENGINE=InnoDB;
10 INSERT INTO t1(id0, id1) VALUES
11 (1, 1),
12 (2, 2),
13 (2, 3);
14 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id0), ALGORITHM = INPLACE;
15 ERROR 23000: Duplicate entry '2' for key 't1.PRIMARY'
16 # Check that records are present.
17 SELECT * FROM t1 ORDER BY id0, id1;
18 id0 id1
19 1 1
20 2 2
21 2 3
22 DROP TABLE t1;
23 #
24 # Test#2 : Try and fail to rebuild the table's PK from 2 columns to 1 column with duplicates.
25 # Then succeed doing it on second set of two columns with unique rows.
26 #
27 CREATE TABLE t2 (
28 id0 int,
29 id1 int,
30 id2 int,
31 PRIMARY KEY (id0, id2)
32 ) ENGINE=InnoDB;
33 INSERT INTO t2(id0, id1, id2) VALUES
34 (1, 2, 3),
35 (1, 2, 4),
36 (1, 3, 5);
37 ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(id1), ALGORITHM = INPLACE;
38 ERROR 23000: Duplicate entry '2' for key 't2.PRIMARY'
39 ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2), ALGORITHM = INPLACE;
40 # Check that records are present.
41 SELECT * FROM t2 ORDER BY id0, id1, id2;
42 id0 id1 id2
43 1 2 3
44 1 2 4
45 1 3 5
46 DROP TABLE t2;
47 #
48 # Test#3 : Try and fail to rebuild the table's PK from unique 1 column to
49 # set of 2 columns with duplicate rows.
50 #
51 CREATE TABLE t3 (
52 id0 int,
53 id1 char(2),
54 id2 varchar(3),
55 id3 date,
56 id4 bool,
57 PRIMARY KEY (id0)
58 ) ENGINE=InnoDB;
59 INSERT INTO t3(id0, id1, id2, id3, id4) VALUES
60 (1, '22', '3', '2020-01-01', true),
61 (2, '33', '4', '2020-02-01', false),
62 (3, '22', '3', '2020-01-01', true);
63 ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2), ALGORITHM = INPLACE;
64 ERROR 23000: Duplicate entry '22-3' for key 't3.PRIMARY'
65 #
66 # Test#4 : Change primary key to all 5 columns. Then fail to change to
67 # last 4 ones due to duplicates.
68 #
69 ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id0, id1, id2, id3, id4), ALGORITHM = INPLACE;
70 ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2, id3, id4), ALGORITHM = INPLACE;
71 ERROR 23000: Duplicate entry '22-3-2020-01-01-1' for key 't3.PRIMARY'
72 # Check that records are present.
73 SELECT * FROM t3 ORDER BY id0, id1, id2, id3, id4;
74 id0 id1 id2 id3 id4
75 1 22 3 2020-01-01 1
76 2 33 4 2020-02-01 0
77 3 22 3 2020-01-01 1
78 DROP TABLE t3;
79 SET GLOBAL debug = "-d,ddl_buf_add_two";