Debian Codegraph @

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

raw
1 #########
2 # SETUP #
3 #########
4 # Stop the running the server
5 # Copy and unzip the datadir.
6 # Restart the server against the unzipped datadir
7 # restart: --datadir=DATADIR --innodb-page-size=4k
8 ##############################################
9 # Test instant ADD/DROP COLUMN for REDUNDANT format
10 ##############################################
11 # ------------------------------------------------------------
12 # Start server with old datadir with
13 # table t1_redundant having
14 # id, c1, c2 normal columns
15 # c3 INSTANT ADD column with default.
16 # ------------------------------------------------------------
17 SHOW CREATE TABLE t1_redundant;
18 Table Create Table
19 t1_redundant CREATE TABLE `t1_redundant` (
20 `id` int NOT NULL,
21 `c1` varchar(4000) DEFAULT NULL,
22 `c2` varchar(4000) DEFAULT NULL,
23 `c3` varchar(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd',
24 PRIMARY KEY (`id`)
25 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
26 # ------------------------------------------------------------
27 # Read rows from upgraded table t1_redundant
28 # ------------------------------------------------------------
29 # Metadata from INFORMATION_SCHEMA.TABLES
30 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
31 test/t1_redundant 7 3 0
32 # Metadata from INFORMATION_SCHEMA.COLUMNS
33 NAME POS MTYPE PRTYPE HAS_DEFAULT
34 id 0 6 1283 0
35 c1 1 12 16715791 0
36 c2 2 12 16715791 0
37 c3 3 12 16716047 1
38 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_redundant ORDER BY id;
39 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
40 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
41 # ------------------------------------------------------------
42 # Run an UPDATE and ROLLBACK.
43 # ------------------------------------------------------------
44 START TRANSACTION;
45 UPDATE t1_redundant SET c1 = repeat('x', 200) WHERE id = 1;
46 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_redundant ORDER BY id;
47 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
48 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
49 ROLLBACK;
50 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_redundant ORDER BY id;
51 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
52 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
53 ############################################
54 # Test instant ADD/DROP COLUMN for DYNAMIC format
55 ############################################
56 # ------------------------------------------------------------
57 # Start server with old datadir with
58 # table t1_dynamic having
59 # id, c1, c2 normal columns
60 # c3 INSTANT ADD column with default.
61 # ------------------------------------------------------------
62 SHOW CREATE TABLE t1_dynamic;
63 Table Create Table
64 t1_dynamic CREATE TABLE `t1_dynamic` (
65 `id` int NOT NULL,
66 `c1` varchar(4000) DEFAULT NULL,
67 `c2` varchar(4000) DEFAULT NULL,
68 `c3` varchar(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd',
69 PRIMARY KEY (`id`)
70 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
71 # ------------------------------------------------------------
72 # Read rows from upgraded table t1_dynamic
73 # ------------------------------------------------------------
74 # Metadata from INFORMATION_SCHEMA.TABLES
75 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
76 test/t1_dynamic 7 3 0
77 # Metadata from INFORMATION_SCHEMA.COLUMNS
78 NAME POS MTYPE PRTYPE HAS_DEFAULT
79 id 0 6 1283 0
80 c1 1 12 16715791 0
81 c2 2 12 16715791 0
82 c3 3 12 16716047 1
83 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_dynamic ORDER BY id;
84 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
85 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
86 # ------------------------------------------------------------
87 # Run an UPDATE and ROLLBACK.
88 # ------------------------------------------------------------
89 START TRANSACTION;
90 UPDATE t1_dynamic SET c1 = repeat('x', 200) WHERE id = 1;
91 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_dynamic ORDER BY id;
92 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
93 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
94 ROLLBACK;
95 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_dynamic ORDER BY id;
96 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
97 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
98 ############################################
99 # Test instant ADD/DROP COLUMN for COMPACT format
100 ############################################
101 # ------------------------------------------------------------
102 # Start server with old datadir with
103 # table t1_compact having
104 # id, c1, c2 normal columns
105 # c3 INSTANT ADD column with default.
106 # ------------------------------------------------------------
107 SHOW CREATE TABLE t1_compact;
108 Table Create Table
109 t1_compact CREATE TABLE `t1_compact` (
110 `id` int NOT NULL,
111 `c1` varchar(4000) DEFAULT NULL,
112 `c2` varchar(4000) DEFAULT NULL,
113 `c3` varchar(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd',
114 PRIMARY KEY (`id`)
115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
116 # ------------------------------------------------------------
117 # Read rows from upgraded table t1_compact
118 # ------------------------------------------------------------
119 # Metadata from INFORMATION_SCHEMA.TABLES
120 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
121 test/t1_compact 7 3 0
122 # Metadata from INFORMATION_SCHEMA.COLUMNS
123 NAME POS MTYPE PRTYPE HAS_DEFAULT
124 id 0 6 1283 0
125 c1 1 12 16715791 0
126 c2 2 12 16715791 0
127 c3 3 12 16716047 1
128 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_compact ORDER BY id;
129 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
130 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
131 # ------------------------------------------------------------
132 # Run an UPDATE and ROLLBACK.
133 # ------------------------------------------------------------
134 START TRANSACTION;
135 UPDATE t1_compact SET c1 = repeat('x', 200) WHERE id = 1;
136 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_compact ORDER BY id;
137 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
138 1 xxxxxxxxxx bbbbbbbbbb dddddddddd
139 ROLLBACK;
140 SELECT id, LEFT(c1 , 10), LEFT(c2, 10), LEFT(c3, 10) FROM t1_compact ORDER BY id;
141 id LEFT(c1 , 10) LEFT(c2, 10) LEFT(c3, 10)
142 1 aaaaaaaaaa bbbbbbbbbb dddddddddd
143 ###########
144 # CLEANUP #
145 ###########
146 # Shutdown server
147 # Remove copied files
148 # Restarting server to restore server state
149 # restart