| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
# |
| 7 |
# 1) Iterate over storage engines and |
| 8 |
# |
| 9 |
# |
| 10 |
# a) Create a table with default value columns and generated |
| 11 |
# columns on MASTER. |
| 12 |
# |
| 13 |
CREATE TABLE t ( |
| 14 |
id INT PRIMARY KEY NOT NULL, |
| 15 |
name VARCHAR(20) NOT NULL, |
| 16 |
nick VARCHAR(20) NOT NULL DEFAULT "", |
| 17 |
state INT DEFAULT 0, |
| 18 |
description varchar(64) GENERATED ALWAYS AS (CONCAT("User#", id, " (", name, ") has state ", state)) STORED, |
| 19 |
welcome varchar(64) GENERATED ALWAYS AS (CONCAT("Welcome ", name, ", your nick is ", nick)) |
| 20 |
) ENGINE= InnoDB; |
| 21 |
# |
| 22 |
# b) Sync SLAVE and ensure that table was replicated. |
| 23 |
# |
| 24 |
SHOW CREATE TABLE t; |
| 25 |
Table Create Table |
| 26 |
t CREATE TABLE `t` ( |
| 27 |
`id` int NOT NULL, |
| 28 |
`name` varchar(20) NOT NULL, |
| 29 |
`nick` varchar(20) NOT NULL DEFAULT '', |
| 30 |
`state` int DEFAULT '0', |
| 31 |
`description` varchar(64) GENERATED ALWAYS AS (concat(_utf8mb4'User#',`id`,_utf8mb4' (',`name`,_utf8mb4') has state ',`state`)) STORED, |
| 32 |
`welcome` varchar(64) GENERATED ALWAYS AS (concat(_utf8mb4'Welcome ',`name`,_utf8mb4', your nick is ',`nick`)) VIRTUAL, |
| 33 |
PRIMARY KEY (`id`) |
| 34 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 35 |
# |
| 36 |
# c) Insert records on MASTER, with no values for default |
| 37 |
# value columns. |
| 38 |
# |
| 39 |
INSERT INTO t (id, name) VALUES (1, "Scott Summers"), (2, "Ororo Munroe"), (3, "Remy LeBeau"), (4, "James Howlett"), (5, "Jean Grey"); |
| 40 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 41 |
CONCAT(welcome, ". Your ", description, ".") |
| 42 |
Welcome Scott Summers, your nick is . Your User#1 (Scott Summers) has state 0. |
| 43 |
Welcome Ororo Munroe, your nick is . Your User#2 (Ororo Munroe) has state 0. |
| 44 |
Welcome Remy LeBeau, your nick is . Your User#3 (Remy LeBeau) has state 0. |
| 45 |
Welcome James Howlett, your nick is . Your User#4 (James Howlett) has state 0. |
| 46 |
Welcome Jean Grey, your nick is . Your User#5 (Jean Grey) has state 0. |
| 47 |
# |
| 48 |
# d) Sync SLAVE and ensure that records were replicated. |
| 49 |
# |
| 50 |
include/diff_tables.inc [master:t, slave:t] |
| 51 |
# |
| 52 |
# e) Update default values, for inserted records, on MASTER. |
| 53 |
# |
| 54 |
UPDATE t SET nick= "cycl0ps" WHERE id = 1; |
| 55 |
UPDATE t SET nick= "st0rm" WHERE id = 2; |
| 56 |
UPDATE t SET nick= "gamb1t" WHERE id = 3; |
| 57 |
UPDATE t SET nick= "l0gan" WHERE id = 4; |
| 58 |
UPDATE t SET nick= "f3nix" WHERE id = 5; |
| 59 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 60 |
CONCAT(welcome, ". Your ", description, ".") |
| 61 |
Welcome Scott Summers, your nick is cycl0ps. Your User#1 (Scott Summers) has state 0. |
| 62 |
Welcome Ororo Munroe, your nick is st0rm. Your User#2 (Ororo Munroe) has state 0. |
| 63 |
Welcome Remy LeBeau, your nick is gamb1t. Your User#3 (Remy LeBeau) has state 0. |
| 64 |
Welcome James Howlett, your nick is l0gan. Your User#4 (James Howlett) has state 0. |
| 65 |
Welcome Jean Grey, your nick is f3nix. Your User#5 (Jean Grey) has state 0. |
| 66 |
# |
| 67 |
# f) Sync SLAVE and ensure that records were replicated. |
| 68 |
# |
| 69 |
include/diff_tables.inc [master:t, slave:t] |
| 70 |
# |
| 71 |
# g) Insert *more* records on MASTER, with values for default |
| 72 |
# value columns. |
| 73 |
# |
| 74 |
INSERT INTO t (id, name, nick, state) VALUES (6, "Anna Marie", "r0gu3", 1), (7, "Alex Summers", "hav0k", 2), (8, "Warren Worthington", "ang3l", 1), (9, "Lorna Dane", "p0lar1s", 1); |
| 75 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 76 |
CONCAT(welcome, ". Your ", description, ".") |
| 77 |
Welcome Scott Summers, your nick is cycl0ps. Your User#1 (Scott Summers) has state 0. |
| 78 |
Welcome Ororo Munroe, your nick is st0rm. Your User#2 (Ororo Munroe) has state 0. |
| 79 |
Welcome Remy LeBeau, your nick is gamb1t. Your User#3 (Remy LeBeau) has state 0. |
| 80 |
Welcome James Howlett, your nick is l0gan. Your User#4 (James Howlett) has state 0. |
| 81 |
Welcome Jean Grey, your nick is f3nix. Your User#5 (Jean Grey) has state 0. |
| 82 |
Welcome Anna Marie, your nick is r0gu3. Your User#6 (Anna Marie) has state 1. |
| 83 |
Welcome Alex Summers, your nick is hav0k. Your User#7 (Alex Summers) has state 2. |
| 84 |
Welcome Warren Worthington, your nick is ang3l. Your User#8 (Warren Worthington) has state 1. |
| 85 |
Welcome Lorna Dane, your nick is p0lar1s. Your User#9 (Lorna Dane) has state 1. |
| 86 |
# |
| 87 |
# h) Sync SLAVE and ensure if records were replicated. |
| 88 |
# |
| 89 |
include/diff_tables.inc [master:t, slave:t] |
| 90 |
# |
| 91 |
# i) Drop table |
| 92 |
# |
| 93 |
DROP TABLE t; |
| 94 |
# |
| 95 |
# a) Create a table with default value columns and generated |
| 96 |
# columns on MASTER. |
| 97 |
# |
| 98 |
CREATE TABLE t ( |
| 99 |
id INT PRIMARY KEY NOT NULL, |
| 100 |
name VARCHAR(20) NOT NULL, |
| 101 |
nick VARCHAR(20) NOT NULL DEFAULT "", |
| 102 |
state INT DEFAULT 0, |
| 103 |
description varchar(64) GENERATED ALWAYS AS (CONCAT("User#", id, " (", name, ") has state ", state)) STORED, |
| 104 |
welcome varchar(64) GENERATED ALWAYS AS (CONCAT("Welcome ", name, ", your nick is ", nick)) |
| 105 |
) ENGINE= MyISAM; |
| 106 |
# |
| 107 |
# b) Sync SLAVE and ensure that table was replicated. |
| 108 |
# |
| 109 |
SHOW CREATE TABLE t; |
| 110 |
Table Create Table |
| 111 |
t CREATE TABLE `t` ( |
| 112 |
`id` int NOT NULL, |
| 113 |
`name` varchar(20) NOT NULL, |
| 114 |
`nick` varchar(20) NOT NULL DEFAULT '', |
| 115 |
`state` int DEFAULT '0', |
| 116 |
`description` varchar(64) GENERATED ALWAYS AS (concat(_utf8mb4'User#',`id`,_utf8mb4' (',`name`,_utf8mb4') has state ',`state`)) STORED, |
| 117 |
`welcome` varchar(64) GENERATED ALWAYS AS (concat(_utf8mb4'Welcome ',`name`,_utf8mb4', your nick is ',`nick`)) VIRTUAL, |
| 118 |
PRIMARY KEY (`id`) |
| 119 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 120 |
# |
| 121 |
# c) Insert records on MASTER, with no values for default |
| 122 |
# value columns. |
| 123 |
# |
| 124 |
INSERT INTO t (id, name) VALUES (1, "Scott Summers"), (2, "Ororo Munroe"), (3, "Remy LeBeau"), (4, "James Howlett"), (5, "Jean Grey"); |
| 125 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 126 |
CONCAT(welcome, ". Your ", description, ".") |
| 127 |
Welcome Scott Summers, your nick is . Your User#1 (Scott Summers) has state 0. |
| 128 |
Welcome Ororo Munroe, your nick is . Your User#2 (Ororo Munroe) has state 0. |
| 129 |
Welcome Remy LeBeau, your nick is . Your User#3 (Remy LeBeau) has state 0. |
| 130 |
Welcome James Howlett, your nick is . Your User#4 (James Howlett) has state 0. |
| 131 |
Welcome Jean Grey, your nick is . Your User#5 (Jean Grey) has state 0. |
| 132 |
# |
| 133 |
# d) Sync SLAVE and ensure that records were replicated. |
| 134 |
# |
| 135 |
include/diff_tables.inc [master:t, slave:t] |
| 136 |
# |
| 137 |
# e) Update default values, for inserted records, on MASTER. |
| 138 |
# |
| 139 |
UPDATE t SET nick= "cycl0ps" WHERE id = 1; |
| 140 |
UPDATE t SET nick= "st0rm" WHERE id = 2; |
| 141 |
UPDATE t SET nick= "gamb1t" WHERE id = 3; |
| 142 |
UPDATE t SET nick= "l0gan" WHERE id = 4; |
| 143 |
UPDATE t SET nick= "f3nix" WHERE id = 5; |
| 144 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 145 |
CONCAT(welcome, ". Your ", description, ".") |
| 146 |
Welcome Scott Summers, your nick is cycl0ps. Your User#1 (Scott Summers) has state 0. |
| 147 |
Welcome Ororo Munroe, your nick is st0rm. Your User#2 (Ororo Munroe) has state 0. |
| 148 |
Welcome Remy LeBeau, your nick is gamb1t. Your User#3 (Remy LeBeau) has state 0. |
| 149 |
Welcome James Howlett, your nick is l0gan. Your User#4 (James Howlett) has state 0. |
| 150 |
Welcome Jean Grey, your nick is f3nix. Your User#5 (Jean Grey) has state 0. |
| 151 |
# |
| 152 |
# f) Sync SLAVE and ensure that records were replicated. |
| 153 |
# |
| 154 |
include/diff_tables.inc [master:t, slave:t] |
| 155 |
# |
| 156 |
# g) Insert *more* records on MASTER, with values for default |
| 157 |
# value columns. |
| 158 |
# |
| 159 |
INSERT INTO t (id, name, nick, state) VALUES (6, "Anna Marie", "r0gu3", 1), (7, "Alex Summers", "hav0k", 2), (8, "Warren Worthington", "ang3l", 1), (9, "Lorna Dane", "p0lar1s", 1); |
| 160 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 161 |
CONCAT(welcome, ". Your ", description, ".") |
| 162 |
Welcome Scott Summers, your nick is cycl0ps. Your User#1 (Scott Summers) has state 0. |
| 163 |
Welcome Ororo Munroe, your nick is st0rm. Your User#2 (Ororo Munroe) has state 0. |
| 164 |
Welcome Remy LeBeau, your nick is gamb1t. Your User#3 (Remy LeBeau) has state 0. |
| 165 |
Welcome James Howlett, your nick is l0gan. Your User#4 (James Howlett) has state 0. |
| 166 |
Welcome Jean Grey, your nick is f3nix. Your User#5 (Jean Grey) has state 0. |
| 167 |
Welcome Anna Marie, your nick is r0gu3. Your User#6 (Anna Marie) has state 1. |
| 168 |
Welcome Alex Summers, your nick is hav0k. Your User#7 (Alex Summers) has state 2. |
| 169 |
Welcome Warren Worthington, your nick is ang3l. Your User#8 (Warren Worthington) has state 1. |
| 170 |
Welcome Lorna Dane, your nick is p0lar1s. Your User#9 (Lorna Dane) has state 1. |
| 171 |
# |
| 172 |
# h) Sync SLAVE and ensure if records were replicated. |
| 173 |
# |
| 174 |
include/diff_tables.inc [master:t, slave:t] |
| 175 |
# |
| 176 |
# i) Drop table |
| 177 |
# |
| 178 |
DROP TABLE t; |
| 179 |
# |
| 180 |
# a) Create a table with default value columns and generated |
| 181 |
# columns on MASTER. |
| 182 |
# |
| 183 |
CREATE TABLE t ( |
| 184 |
id INT PRIMARY KEY NOT NULL, |
| 185 |
name VARCHAR(20) NOT NULL, |
| 186 |
nick VARCHAR(20) NOT NULL DEFAULT "", |
| 187 |
state INT DEFAULT 0, |
| 188 |
description varchar(64) GENERATED ALWAYS AS (CONCAT("User#", id, " (", name, ") has state ", state)) STORED, |
| 189 |
welcome varchar(64) GENERATED ALWAYS AS (CONCAT("Welcome ", name, ", your nick is ", nick)) |
| 190 |
) ENGINE= NDB; |
| 191 |
# |
| 192 |
# b) Sync SLAVE and ensure that table was replicated. |
| 193 |
# |
| 194 |
SHOW CREATE TABLE t; |
| 195 |
Table Create Table |
| 196 |
t CREATE TABLE `t` ( |
| 197 |
`id` int NOT NULL, |
| 198 |
`name` varchar(20) NOT NULL, |
| 199 |
`nick` varchar(20) NOT NULL DEFAULT '', |
| 200 |
`state` int DEFAULT '0', |
| 201 |
`description` varchar(64) GENERATED ALWAYS AS (concat(_utf8mb4'User#',`id`,_utf8mb4' (',`name`,_utf8mb4') has state ',`state`)) STORED, |
| 202 |
`welcome` varchar(64) GENERATED ALWAYS AS (concat(_utf8mb4'Welcome ',`name`,_utf8mb4', your nick is ',`nick`)) VIRTUAL, |
| 203 |
PRIMARY KEY (`id`) |
| 204 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 205 |
# |
| 206 |
# c) Insert records on MASTER, with no values for default |
| 207 |
# value columns. |
| 208 |
# |
| 209 |
INSERT INTO t (id, name) VALUES (1, "Scott Summers"), (2, "Ororo Munroe"), (3, "Remy LeBeau"), (4, "James Howlett"), (5, "Jean Grey"); |
| 210 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 211 |
CONCAT(welcome, ". Your ", description, ".") |
| 212 |
Welcome Scott Summers, your nick is . Your User#1 (Scott Summers) has state 0. |
| 213 |
Welcome Ororo Munroe, your nick is . Your User#2 (Ororo Munroe) has state 0. |
| 214 |
Welcome Remy LeBeau, your nick is . Your User#3 (Remy LeBeau) has state 0. |
| 215 |
Welcome James Howlett, your nick is . Your User#4 (James Howlett) has state 0. |
| 216 |
Welcome Jean Grey, your nick is . Your User#5 (Jean Grey) has state 0. |
| 217 |
# |
| 218 |
# d) Sync SLAVE and ensure that records were replicated. |
| 219 |
# |
| 220 |
include/diff_tables.inc [master:t, slave:t] |
| 221 |
# |
| 222 |
# e) Update default values, for inserted records, on MASTER. |
| 223 |
# |
| 224 |
UPDATE t SET nick= "cycl0ps" WHERE id = 1; |
| 225 |
UPDATE t SET nick= "st0rm" WHERE id = 2; |
| 226 |
UPDATE t SET nick= "gamb1t" WHERE id = 3; |
| 227 |
UPDATE t SET nick= "l0gan" WHERE id = 4; |
| 228 |
UPDATE t SET nick= "f3nix" WHERE id = 5; |
| 229 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 230 |
CONCAT(welcome, ". Your ", description, ".") |
| 231 |
Welcome Scott Summers, your nick is cycl0ps. Your User#1 (Scott Summers) has state 0. |
| 232 |
Welcome Ororo Munroe, your nick is st0rm. Your User#2 (Ororo Munroe) has state 0. |
| 233 |
Welcome Remy LeBeau, your nick is gamb1t. Your User#3 (Remy LeBeau) has state 0. |
| 234 |
Welcome James Howlett, your nick is l0gan. Your User#4 (James Howlett) has state 0. |
| 235 |
Welcome Jean Grey, your nick is f3nix. Your User#5 (Jean Grey) has state 0. |
| 236 |
# |
| 237 |
# f) Sync SLAVE and ensure that records were replicated. |
| 238 |
# |
| 239 |
include/diff_tables.inc [master:t, slave:t] |
| 240 |
# |
| 241 |
# g) Insert *more* records on MASTER, with values for default |
| 242 |
# value columns. |
| 243 |
# |
| 244 |
INSERT INTO t (id, name, nick, state) VALUES (6, "Anna Marie", "r0gu3", 1), (7, "Alex Summers", "hav0k", 2), (8, "Warren Worthington", "ang3l", 1), (9, "Lorna Dane", "p0lar1s", 1); |
| 245 |
SELECT CONCAT(welcome, ". Your ", description, ".") FROM t ORDER BY id; |
| 246 |
CONCAT(welcome, ". Your ", description, ".") |
| 247 |
Welcome Scott Summers, your nick is cycl0ps. Your User#1 (Scott Summers) has state 0. |
| 248 |
Welcome Ororo Munroe, your nick is st0rm. Your User#2 (Ororo Munroe) has state 0. |
| 249 |
Welcome Remy LeBeau, your nick is gamb1t. Your User#3 (Remy LeBeau) has state 0. |
| 250 |
Welcome James Howlett, your nick is l0gan. Your User#4 (James Howlett) has state 0. |
| 251 |
Welcome Jean Grey, your nick is f3nix. Your User#5 (Jean Grey) has state 0. |
| 252 |
Welcome Anna Marie, your nick is r0gu3. Your User#6 (Anna Marie) has state 1. |
| 253 |
Welcome Alex Summers, your nick is hav0k. Your User#7 (Alex Summers) has state 2. |
| 254 |
Welcome Warren Worthington, your nick is ang3l. Your User#8 (Warren Worthington) has state 1. |
| 255 |
Welcome Lorna Dane, your nick is p0lar1s. Your User#9 (Lorna Dane) has state 1. |
| 256 |
# |
| 257 |
# h) Sync SLAVE and ensure if records were replicated. |
| 258 |
# |
| 259 |
include/diff_tables.inc [master:t, slave:t] |
| 260 |
# |
| 261 |
# i) Drop table |
| 262 |
# |
| 263 |
DROP TABLE t; |
| 264 |
include/rpl_end.inc |