Debian Codegraph @

mysql-test/suite/innodb/include/instant_ddl_upgrade_part.inc

raw
1 ##########################################################################
2 # Test script to test UPGRADE from older version prtitioned table (not)
3 # having INSTANT ADD columns.
4 #
5 # $row_format is to be set to the row_format on which test is to be run.
6 ##########################################################################
7
8 --echo # ------------------------------------------------------------
9 --echo # Start server with old datadir with table t1_$row_format having
10 --echo # c1, c2, normal columns
11 --echo # c3 INSTANT ADD column with default def_c3.
12 --echo # c4 INSTANT ADD column with default def_c4.
13 --echo # with 4 partitions (p0, p1, p2, p3)
14 --echo # ------------------------------------------------------------
15
16 eval SHOW CREATE TABLE t1_$row_format;
17
18 --echo # ------------------------------------------------------------
19 --echo # Read rows from upgraded table t1_$row_format
20 --echo # ------------------------------------------------------------
21 --let $table_name=t1_$row_format
22 --source suite/innodb/include/print_instant_part_metadata.inc
23 eval SELECT * FROM t1_$row_format ORDER BY c1;
24
25 --echo # ------------------------------------------------------------
26 --echo # REORGANIZE PARTITION (p0 into p0_a p0_b)
27 --echo # ------------------------------------------------------------
28 eval SELECT * FROM t1_$row_format PARTITION (p0);
29
30 eval ALTER TABLE t1_$row_format ALGORITHM=INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100));
31
32 eval SELECT * FROM t1_$row_format PARTITION (p0_a);
33 eval SELECT * FROM t1_$row_format PARTITION (p0_b);
34
35 eval SELECT * FROM t1_$row_format ORDER BY c1;
36
37 --echo # ------------------------------------------------------------
38 --echo # Insert a row in upgraded table t1_$row_format
39 --echo # ------------------------------------------------------------
40 eval INSERT INTO t1_$row_format values (60, "r7c2", "r7c3", "r7c4");
41 eval SELECT * FROM t1_$row_format ORDER BY c1;
42
43 --echo # ------------------------------------------------------------
44 --echo # UPDATE : IN PLACE for row with no version
45 --echo # ------------------------------------------------------------
46 eval UPDATE t1_$row_format SET c2="temp" where c1=10;
47 eval SELECT * FROM t1_$row_format ORDER BY c1;
48
49 --echo # ------------------------------------------------------------
50 --echo # UPDATE : IN PLACE for row with V1
51 --echo # ------------------------------------------------------------
52 eval UPDATE t1_$row_format SET c2="temp" where c1=40;
53 eval SELECT * FROM t1_$row_format ORDER BY c1;
54
55 --echo # ------------------------------------------------------------
56 --echo # UPDATE : NOT IN PLACE for row with no version
57 --echo # ------------------------------------------------------------
58 eval UPDATE t1_$row_format SET c3="r1c3" where c1=10;
59 eval SELECT * FROM t1_$row_format ORDER BY c1;
60
61 --echo # ------------------------------------------------------------
62 --echo # UPDATE : NOT IN PLACE for row with V1
63 --echo # ------------------------------------------------------------
64 eval UPDATE t1_$row_format SET c4="r2c4" where c1=40;
65 eval SELECT * FROM t1_$row_format ORDER BY c1;
66
67 --echo # ------------------------------------------------------------
68 --echo # INSTANT DROP c2
69 --echo # ------------------------------------------------------------
70 eval ALTER TABLE t1_$row_format DROP COLUMN c2, ALGORITHM=INSTANT;
71 --let $table_name=t1_$row_format
72 --source suite/innodb/include/print_instant_part_metadata.inc
73 eval SELECT * FROM t1_$row_format ORDER BY c1;
74 eval INSERT INTO t1_$row_format values (50, "r8c3", "r8c4");
75 eval SELECT * FROM t1_$row_format ORDER BY c1;
76
77 --echo # ------------------------------------------------------------
78 --echo # INSTANT ADD c5 at the end
79 --echo # ------------------------------------------------------------
80 eval ALTER TABLE t1_$row_format ADD COLUMN c5 char(10) default "c5_def", ALGORITHM=INSTANT;
81 --let $table_name=t1_$row_format
82 --source suite/innodb/include/print_instant_part_metadata.inc
83 eval SELECT * FROM t1_$row_format ORDER BY c1;
84 eval INSERT INTO t1_$row_format values (150, "r9c3", "r9c4", "r9c5");
85 eval SELECT * FROM t1_$row_format ORDER BY c1;
86
87 --echo # ------------------------------------------------------------
88 --echo # INSTANT ADD c6 somewhere in between
89 --echo # ------------------------------------------------------------
90 eval ALTER TABLE t1_$row_format ADD COLUMN c6 char(10) default "c6_def" after c1, ALGORITHM=INSTANT;
91 --let $table_name=t1_$row_format
92 --source suite/innodb/include/print_instant_part_metadata.inc
93 eval SELECT * FROM t1_$row_format ORDER BY c1;
94 eval INSERT INTO t1_$row_format values (250, "r0c6", "r0c3", "r0c4", "r0c5");
95 eval SELECT * FROM t1_$row_format ORDER BY c1;
96
97 eval DROP TABLE t1_$row_format;
98
99 --echo # ------------------------------------------------------------
100 --echo # TRUNCATE Partition test for Upgraded table
101 --echo # ------------------------------------------------------------
102 --echo # table t2_$row_format having
103 --echo # c1, c2, c3, c4 normal columns
104 --echo # c5 INSTANT ADD column with default NULL.
105 --echo # with 4 partitions (p0, p1, p2, p3)
106 --echo #
107 --echo # Partition p0, p1, p2 are truncated.
108 --echo # ------------------------------------------------------------
109 eval SHOW CREATE TABLE t2_$row_format;
110
111 eval SELECT * FROM t2_$row_format ORDER BY c1;
112 --let $table_name=t2_$row_format
113 --source suite/innodb/include/print_instant_part_metadata.inc
114
115 eval INSERT INTO t2_$row_format values (101, 102, 103, "104", 105);
116 eval SELECT * FROM t2_$row_format ORDER BY c1;
117
118 eval Alter table t2_$row_format add column c6 INT DEFAULT 666 FIRST, algorithm=INSTANT;
119 eval SELECT * FROM t2_$row_format ORDER BY c1;
120 --source suite/innodb/include/print_instant_part_metadata.inc
121
122 eval INSERT INTO t2_$row_format values (206, 201, 202, 203, "204", 205);
123 eval SELECT * FROM t2_$row_format ORDER BY c1;
124
125 eval ALTER TABLE t2_$row_format DROP COLUMN c2, algorithm=INSTANT;
126 eval SELECT * FROM t2_$row_format ORDER BY c1;
127
128 eval ALTER TABLE t2_$row_format TRUNCATE partition p3;
129 # Row in partition p3 should have been removed but remaining 2 rows should be there.
130 eval SELECT * FROM t2_$row_format ORDER BY c1;
131 --source suite/innodb/include/print_instant_part_metadata.inc
132
133 eval DROP TABLE t2_$row_format;