| 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 |
set @@global.ndb_schema_dist_lock_wait_timeout = 1; |
| 7 |
create table t1 ( a int ) engine = ndb; |
| 8 |
begin; |
| 9 |
insert into t1 values(1); |
| 10 |
alter table t1 rename t2; |
| 11 |
Warnings: |
| 12 |
Warning 1296 Node <nodeid> 'Distribution of RENAME TABLE 't1' failed' |
| 13 |
commit; |
| 14 |
drop table t2; |
| 15 |
set global ndb_dbg_check_shares=1; |
| 16 |
set global ndb_dbg_check_shares=1; |
| 17 |
create table t1 ( a int primary key) engine = ndb; |
| 18 |
begin; |
| 19 |
insert into t1 values(1); |
| 20 |
alter table t1 algorithm=inplace, add column b int column_format dynamic; |
| 21 |
Warnings: |
| 22 |
Warning 1296 Node <nodeid> 'Distribution of ALTER TABLE 't1' failed' |
| 23 |
commit; |
| 24 |
drop table t1; |
| 25 |
set global ndb_dbg_check_shares=1; |
| 26 |
set global ndb_dbg_check_shares=1; |
| 27 |
create table t1 ( a int primary key) engine = ndb; |
| 28 |
begin; |
| 29 |
insert into t1 values(1); |
| 30 |
alter table t1 algorithm=copy, add column b int column_format dynamic; |
| 31 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 32 |
commit; |
| 33 |
drop table t1; |
| 34 |
set global ndb_dbg_check_shares=1; |
| 35 |
set global ndb_dbg_check_shares=1; |
| 36 |
create table t1 ( a int primary key, b int) engine = ndb; |
| 37 |
begin; |
| 38 |
insert into t1 values(1, 1); |
| 39 |
create index ind_b on t1(b) algorithm=inplace; |
| 40 |
Warnings: |
| 41 |
Warning 1296 Node <nodeid> 'Distribution of ALTER TABLE 't1' failed' |
| 42 |
commit; |
| 43 |
drop table t1; |
| 44 |
set global ndb_dbg_check_shares=1; |
| 45 |
set global ndb_dbg_check_shares=1; |
| 46 |
create table t1 ( a int primary key, b int, index ind_b (b)) engine = ndb; |
| 47 |
begin; |
| 48 |
insert into t1 values(1, 1); |
| 49 |
drop index ind_b on t1; |
| 50 |
Warnings: |
| 51 |
Warning 1296 Node <nodeid> 'Distribution of ALTER TABLE 't1' failed' |
| 52 |
commit; |
| 53 |
drop table t1; |
| 54 |
set global ndb_dbg_check_shares=1; |
| 55 |
set global ndb_dbg_check_shares=1; |
| 56 |
create database testdb; |
| 57 |
create table testdb.t1 (a int) engine = ndb; |
| 58 |
begin; |
| 59 |
insert into testdb.t1 values(1); |
| 60 |
alter database testdb charset = latin1; |
| 61 |
Warnings: |
| 62 |
Warning 1296 Node <nodeid> 'Distribution of ALTER DATABASE 'testdb' failed' |
| 63 |
commit; |
| 64 |
drop database testdb; |
| 65 |
set global ndb_dbg_check_shares=1; |
| 66 |
set global ndb_dbg_check_shares=1; |
| 67 |
Remote MDL lock claim failure |
| 68 |
Server1 Create table |
| 69 |
create table t1 (a int primary key, b int) engine=ndb; |
| 70 |
insert into t1 values (1,1); |
| 71 |
Add column c on server1 |
| 72 |
|
| 73 |
Server2 Get + hold MDL on table t1, no row locks |
| 74 |
begin; |
| 75 |
select * from t1; |
| 76 |
a b |
| 77 |
1 1 |
| 78 |
|
| 79 |
Server1 Send Alter add column, will stall due to remote MDL lock |
| 80 |
alter table t1 add column c int; # Online alter; |
| 81 |
Server2 Issue normal queries on table, causing table access, rollback etc |
| 82 |
|
| 83 |
Server1 Allow ALTER to complete successfully |
| 84 |
|
| 85 |
Server 1 Show table schema at origin |
| 86 |
select * from t1; |
| 87 |
a b c |
| 88 |
1 1 NULL |
| 89 |
show create table t1; |
| 90 |
Table Create Table |
| 91 |
t1 CREATE TABLE `t1` ( |
| 92 |
`a` int NOT NULL, |
| 93 |
`b` int DEFAULT NULL, |
| 94 |
`c` int DEFAULT NULL, |
| 95 |
PRIMARY KEY (`a`) |
| 96 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 97 |
desc t1; |
| 98 |
Field Type Null Key Default Extra |
| 99 |
a int NO PRI NULL |
| 100 |
b int YES NULL |
| 101 |
c int YES NULL |
| 102 |
select column_name from information_schema.columns where table_name='t1'; |
| 103 |
COLUMN_NAME |
| 104 |
a |
| 105 |
b |
| 106 |
c |
| 107 |
|
| 108 |
Server2 Show schema at participant |
| 109 |
select * from t1; |
| 110 |
a b c |
| 111 |
1 1 NULL |
| 112 |
desc t1; |
| 113 |
Field Type Null Key Default Extra |
| 114 |
a int NO PRI NULL |
| 115 |
b int YES NULL |
| 116 |
c int YES NULL |
| 117 |
select column_name from information_schema.columns where table_name='t1'; |
| 118 |
COLUMN_NAME |
| 119 |
a |
| 120 |
b |
| 121 |
c |
| 122 |
show create table t1; |
| 123 |
Table Create Table |
| 124 |
t1 CREATE TABLE `t1` ( |
| 125 |
`a` int NOT NULL, |
| 126 |
`b` int DEFAULT NULL, |
| 127 |
`c` int DEFAULT NULL, |
| 128 |
PRIMARY KEY (`a`) |
| 129 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 130 |
Add index c on server 1 |
| 131 |
Server2 Take + hold MDL lock again |
| 132 |
begin; |
| 133 |
select * from test.t1; |
| 134 |
a b c |
| 135 |
1 1 NULL |
| 136 |
Server1 Issue another online DDL, will stall due to remote MDL lock |
| 137 |
alter table t1 add index(c);; |
| 138 |
Server2 Issue normal queries on table, causing table access, rollback etc |
| 139 |
|
| 140 |
Server1 Allow ALTER to complete successfully |
| 141 |
|
| 142 |
Server 1 Show table schema at origin |
| 143 |
select * from t1; |
| 144 |
a b c |
| 145 |
1 1 NULL |
| 146 |
show create table t1; |
| 147 |
Table Create Table |
| 148 |
t1 CREATE TABLE `t1` ( |
| 149 |
`a` int NOT NULL, |
| 150 |
`b` int DEFAULT NULL, |
| 151 |
`c` int DEFAULT NULL, |
| 152 |
PRIMARY KEY (`a`), |
| 153 |
KEY `c` (`c`) |
| 154 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 155 |
desc t1; |
| 156 |
Field Type Null Key Default Extra |
| 157 |
a int NO PRI NULL |
| 158 |
b int YES NULL |
| 159 |
c int YES MUL NULL |
| 160 |
select column_name from information_schema.columns where table_name='t1'; |
| 161 |
COLUMN_NAME |
| 162 |
a |
| 163 |
b |
| 164 |
c |
| 165 |
|
| 166 |
Server2 Show schema at participant |
| 167 |
select * from t1; |
| 168 |
a b c |
| 169 |
1 1 NULL |
| 170 |
desc t1; |
| 171 |
Field Type Null Key Default Extra |
| 172 |
a int NO PRI NULL |
| 173 |
b int YES NULL |
| 174 |
c int YES MUL NULL |
| 175 |
select column_name from information_schema.columns where table_name='t1'; |
| 176 |
COLUMN_NAME |
| 177 |
a |
| 178 |
b |
| 179 |
c |
| 180 |
show create table t1; |
| 181 |
Table Create Table |
| 182 |
t1 CREATE TABLE `t1` ( |
| 183 |
`a` int NOT NULL, |
| 184 |
`b` int DEFAULT NULL, |
| 185 |
`c` int DEFAULT NULL, |
| 186 |
PRIMARY KEY (`a`), |
| 187 |
KEY `c` (`c`) |
| 188 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 189 |
Now further changes in the opposite direction |
| 190 |
Server2 -> Server1 |
| 191 |
Server1 Take and hold MDL lock |
| 192 |
begin; |
| 193 |
select * from test.t1; |
| 194 |
a b c |
| 195 |
1 1 NULL |
| 196 |
Server2 Send Alter table add column, will stall due to remote MDL lock |
| 197 |
alter table t1 add column d int;; |
| 198 |
Server1 Issue normal queries on table, causing table access, rollback etc |
| 199 |
|
| 200 |
Server2 Allow ALTER to complete successfully |
| 201 |
Warnings: |
| 202 |
Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN |
| 203 |
|
| 204 |
Server 2 Show table schema at origin |
| 205 |
select * from t1; |
| 206 |
a b c d |
| 207 |
1 1 NULL NULL |
| 208 |
show create table t1; |
| 209 |
Table Create Table |
| 210 |
t1 CREATE TABLE `t1` ( |
| 211 |
`a` int NOT NULL, |
| 212 |
`b` int DEFAULT NULL, |
| 213 |
`c` int DEFAULT NULL, |
| 214 |
`d` int DEFAULT NULL, |
| 215 |
PRIMARY KEY (`a`), |
| 216 |
KEY `c` (`c`) |
| 217 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 218 |
desc t1; |
| 219 |
Field Type Null Key Default Extra |
| 220 |
a int NO PRI NULL |
| 221 |
b int YES NULL |
| 222 |
c int YES MUL NULL |
| 223 |
d int YES NULL |
| 224 |
select column_name from information_schema.columns where table_name='t1'; |
| 225 |
COLUMN_NAME |
| 226 |
a |
| 227 |
b |
| 228 |
c |
| 229 |
d |
| 230 |
|
| 231 |
Server1 Show schema at participant |
| 232 |
select * from t1; |
| 233 |
a b c d |
| 234 |
1 1 NULL NULL |
| 235 |
desc t1; |
| 236 |
Field Type Null Key Default Extra |
| 237 |
a int NO PRI NULL |
| 238 |
b int YES NULL |
| 239 |
c int YES MUL NULL |
| 240 |
d int YES NULL |
| 241 |
select column_name from information_schema.columns where table_name='t1'; |
| 242 |
COLUMN_NAME |
| 243 |
a |
| 244 |
b |
| 245 |
c |
| 246 |
d |
| 247 |
show create table t1; |
| 248 |
Table Create Table |
| 249 |
t1 CREATE TABLE `t1` ( |
| 250 |
`a` int NOT NULL, |
| 251 |
`b` int DEFAULT NULL, |
| 252 |
`c` int DEFAULT NULL, |
| 253 |
`d` int DEFAULT NULL, |
| 254 |
PRIMARY KEY (`a`), |
| 255 |
KEY `c` (`c`) |
| 256 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 257 |
Add index d on server 2 |
| 258 |
Server1 Take and hold MDL lock again |
| 259 |
begin; |
| 260 |
select * from test.t1; |
| 261 |
a b c d |
| 262 |
1 1 NULL NULL |
| 263 |
Server2 Issue another online DDL, will stall due to remote MDL lock |
| 264 |
alter table t1 add index(d);; |
| 265 |
Server1 Issue normal queries on table, causing table access, rollback etc |
| 266 |
|
| 267 |
Server2 Allow ALTER to complete successfully |
| 268 |
|
| 269 |
Server 2 Show table schema |
| 270 |
select * from t1; |
| 271 |
a b c d |
| 272 |
1 1 NULL NULL |
| 273 |
show create table t1; |
| 274 |
Table Create Table |
| 275 |
t1 CREATE TABLE `t1` ( |
| 276 |
`a` int NOT NULL, |
| 277 |
`b` int DEFAULT NULL, |
| 278 |
`c` int DEFAULT NULL, |
| 279 |
`d` int DEFAULT NULL, |
| 280 |
PRIMARY KEY (`a`), |
| 281 |
KEY `c` (`c`), |
| 282 |
KEY `d` (`d`) |
| 283 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 284 |
desc t1; |
| 285 |
Field Type Null Key Default Extra |
| 286 |
a int NO PRI NULL |
| 287 |
b int YES NULL |
| 288 |
c int YES MUL NULL |
| 289 |
d int YES MUL NULL |
| 290 |
select column_name from information_schema.columns where table_name='t1'; |
| 291 |
COLUMN_NAME |
| 292 |
a |
| 293 |
b |
| 294 |
c |
| 295 |
d |
| 296 |
|
| 297 |
Server1 Show schema at participant |
| 298 |
select * from t1; |
| 299 |
a b c d |
| 300 |
1 1 NULL NULL |
| 301 |
desc t1; |
| 302 |
Field Type Null Key Default Extra |
| 303 |
a int NO PRI NULL |
| 304 |
b int YES NULL |
| 305 |
c int YES MUL NULL |
| 306 |
d int YES MUL NULL |
| 307 |
select column_name from information_schema.columns where table_name='t1'; |
| 308 |
COLUMN_NAME |
| 309 |
a |
| 310 |
b |
| 311 |
c |
| 312 |
d |
| 313 |
show create table t1; |
| 314 |
Table Create Table |
| 315 |
t1 CREATE TABLE `t1` ( |
| 316 |
`a` int NOT NULL, |
| 317 |
`b` int DEFAULT NULL, |
| 318 |
`c` int DEFAULT NULL, |
| 319 |
`d` int DEFAULT NULL, |
| 320 |
PRIMARY KEY (`a`), |
| 321 |
KEY `c` (`c`), |
| 322 |
KEY `d` (`d`) |
| 323 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 324 |
drop table t1; |
| 325 |
include/rpl_end.inc |