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