| 1 |
************************************ |
| 2 |
*Blob table handing with pk changes* |
| 3 |
************************************ |
| 4 |
No changes to Blobs |
| 5 |
create table test.t1 (a varchar(20) not null, |
| 6 |
b varchar(20) not null, |
| 7 |
c varchar(20) not null, |
| 8 |
d text(3000), |
| 9 |
primary key(a,b)) engine=ndb; |
| 10 |
insert into test.t1 values |
| 11 |
("1a","1b","1c",repeat("1d", 1000)), |
| 12 |
("2a","2b","2c",repeat("2d", 1000)), |
| 13 |
("3a","3b","3c",repeat("3d", 1000)), |
| 14 |
("4a","4b","4c",repeat("4d", 1000)), |
| 15 |
("5a","5b","5c",repeat("5d", 1000)), |
| 16 |
("6a","6b","6c",repeat("6d", 1000)); |
| 17 |
create table test.t2 (a varchar(20) not null, |
| 18 |
b varchar(20) not null, |
| 19 |
c text not null, |
| 20 |
d text not null, |
| 21 |
primary key (a,b)) engine=ndb; |
| 22 |
insert into test.t2 values |
| 23 |
("Ein", "Ein", repeat("Ein", 1000), repeat("Ein", 1000)), |
| 24 |
("Zwei", "Zwei", repeat("Zwei", 1000), repeat("Zwei", 1000)), |
| 25 |
("Drei", "Drei", repeat("Drei", 1000), repeat("Drei", 1000)), |
| 26 |
("Vier", "Vier", repeat("Vier", 1000), repeat("Vier", 1000)), |
| 27 |
("Funf", "Funf", repeat("Funf", 1000), repeat("Funf", 1000)), |
| 28 |
("Sechs", "Sechs", repeat("Sechs", 1000), repeat("Sechs", 1000)), |
| 29 |
("Sieben", "Sieben", repeat("Sieben", 1000), repeat("Sieben", 1000)); |
| 30 |
Stall backup completion |
| 31 |
Run backup |
| 32 |
Starting backup |
| 33 |
Backup started |
| 34 |
Make some changes which will be logged... |
| 35 |
insert into test.t1 values |
| 36 |
("7a","7b","7c",repeat("7d",1000)), |
| 37 |
("8a","8b","8c",repeat("8d",1000)), |
| 38 |
("9a","9b","9c",repeat("9d",1000)); |
| 39 |
update test.t1 set c=c, d=concat(d, "-modified"); |
| 40 |
delete from test.t1 where a in ("5a","8a"); |
| 41 |
insert into test.t2 values |
| 42 |
("Acht", "Acht", repeat("Acht", 10), repeat("Acht", 2000)), |
| 43 |
("Neun", "Neun", repeat("Neun", 200), repeat("Neun", 5)); |
| 44 |
update test.t2 set b=concat(b, "-jah") where a like "%ei"; |
| 45 |
delete from test.t2 where a like "S%"; |
| 46 |
Allow backup to complete... |
| 47 |
Waiting for backup to complete |
| 48 |
Backup completed |
| 49 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 50 |
a b c length(d) md5(d) right(d,20) |
| 51 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 52 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 53 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 54 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 55 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 56 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 57 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 58 |
select a,b,length(c), md5(c), right(c, 20), length(d), md5(d), right(d,20) from test.t2 order by a; |
| 59 |
a b length(c) md5(c) right(c, 20) length(d) md5(d) right(d,20) |
| 60 |
Acht Acht 40 4e5fab56696355c99c3bcffd5fe39d5e AchtAchtAchtAchtAcht 8000 50bd6e30f3801a968d8e4044751a98ad AchtAchtAchtAchtAcht |
| 61 |
Drei Drei-jah 4000 0b5298c37fbb5cfc7137b89f9711974b DreiDreiDreiDreiDrei 4000 0b5298c37fbb5cfc7137b89f9711974b DreiDreiDreiDreiDrei |
| 62 |
Ein Ein 3000 109abc9b5499d3de3cce205cd4e25da9 inEinEinEinEinEinEin 3000 109abc9b5499d3de3cce205cd4e25da9 inEinEinEinEinEinEin |
| 63 |
Funf Funf 4000 32d2ad309dccb89aab90f178934c3933 FunfFunfFunfFunfFunf 4000 32d2ad309dccb89aab90f178934c3933 FunfFunfFunfFunfFunf |
| 64 |
Neun Neun 800 9712e7c5fc2470f11185907bcde8293d NeunNeunNeunNeunNeun 20 c6e516d71a611ae0941d9581e8522b6d NeunNeunNeunNeunNeun |
| 65 |
Vier Vier 4000 e4c3ae579b61aa4630e10e220c0620e4 VierVierVierVierVier 4000 e4c3ae579b61aa4630e10e220c0620e4 VierVierVierVierVier |
| 66 |
Zwei Zwei-jah 4000 4531420243d24d5002c58f578e8ec114 ZweiZweiZweiZweiZwei 4000 4531420243d24d5002c58f578e8ec114 ZweiZweiZweiZweiZwei |
| 67 |
drop table test.t1; |
| 68 |
truncate table test.t2; |
| 69 |
******************************************** |
| 70 |
* Tests with column change from pk <-> !pk * |
| 71 |
******************************************** |
| 72 |
Extend pk (a,b) -> (a,b,c) |
| 73 |
************************** |
| 74 |
create table test.t1 (a varchar(20) not null, |
| 75 |
b varchar(20) not null, |
| 76 |
c varchar(20) not null, |
| 77 |
d text(3000), |
| 78 |
primary key(a,b,c)) engine=ndb; |
| 79 |
Restore will require use of a staging table due to the blobs |
| 80 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 81 |
a b c length(d) md5(d) right(d,20) |
| 82 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 83 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 84 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 85 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 86 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 87 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 88 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 89 |
drop table test.t1; |
| 90 |
truncate table test.t2; |
| 91 |
Extend pk (a,b) -> (a,b,c) with pk type promotion |
| 92 |
************************************************* |
| 93 |
create table test.t1 (a varchar(40) not null, |
| 94 |
b varchar(20) not null, |
| 95 |
c varchar(20) not null, |
| 96 |
d text(3000), |
| 97 |
primary key(a,b,c)) engine=ndb; |
| 98 |
Restore will require use of a staging table due to the blobs |
| 99 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 100 |
a b c length(d) md5(d) right(d,20) |
| 101 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 102 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 103 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 104 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 105 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 106 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 107 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 108 |
drop table test.t1; |
| 109 |
truncate table test.t2; |
| 110 |
Extend pk (a,b) -> (a,b,c) with pk type demotion |
| 111 |
************************************************ |
| 112 |
create table test.t1 (a varchar(15) not null, |
| 113 |
b varchar(20) not null, |
| 114 |
c varchar(20) not null, |
| 115 |
d text(3000), |
| 116 |
primary key(a,b,c)) engine=ndb; |
| 117 |
Restore will require use of a staging table due to the blobs |
| 118 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 119 |
a b c length(d) md5(d) right(d,20) |
| 120 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 121 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 122 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 123 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 124 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 125 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 126 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 127 |
drop table test.t1; |
| 128 |
truncate table test.t2; |
| 129 |
Extend pk (a,b) -> (a,b,c) with pk and blob type changes |
| 130 |
******************************************************** |
| 131 |
create table test.t1 (a varchar(40) not null, |
| 132 |
b varchar(20) not null, |
| 133 |
c varchar(20) not null, |
| 134 |
d varchar(2500), |
| 135 |
primary key(a,b,c)) engine=ndb; |
| 136 |
Restore will require use of a staging table due to the blobs |
| 137 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 138 |
a b c length(d) md5(d) right(d,20) |
| 139 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 140 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 141 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 142 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 143 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 144 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 145 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 146 |
drop table test.t1; |
| 147 |
truncate table test.t2; |
| 148 |
Contract pk (a,b) -> (b) |
| 149 |
************************** |
| 150 |
create table test.t1 (a varchar(20) not null, |
| 151 |
b varchar(20) not null, |
| 152 |
c varchar(20) not null, |
| 153 |
d text(3000), |
| 154 |
primary key(b)) engine=ndb; |
| 155 |
Run restore |
| 156 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 157 |
a b c length(d) md5(d) right(d,20) |
| 158 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 159 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 160 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 161 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 162 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 163 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 164 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 165 |
drop table test.t1; |
| 166 |
truncate table test.t2; |
| 167 |
Contract pk (a,b) -> (b), dropping a |
| 168 |
************************************ |
| 169 |
create table test.t1 (b varchar(20) not null, |
| 170 |
c varchar(20) not null, |
| 171 |
d text(3000), |
| 172 |
primary key(b)) engine=ndb; |
| 173 |
Run restore |
| 174 |
select b,c,length(d), md5(d), right(d,20) from test.t1 order by b; |
| 175 |
b c length(d) md5(d) right(d,20) |
| 176 |
1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 177 |
2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 178 |
3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 179 |
4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 180 |
6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 181 |
7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 182 |
9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 183 |
drop table test.t1; |
| 184 |
truncate table test.t2; |
| 185 |
Contract pk (a,b) -> (b) |
| 186 |
Also add type changes |
| 187 |
************************** |
| 188 |
create table test.t1 (a varchar(200) not null, |
| 189 |
b varchar(15) not null, |
| 190 |
c varchar(200) not null, |
| 191 |
d text(3000), |
| 192 |
primary key(b)) engine=ndb; |
| 193 |
Run restore |
| 194 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 195 |
a b c length(d) md5(d) right(d,20) |
| 196 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 197 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 198 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 199 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 200 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 201 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 202 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 203 |
drop table test.t1; |
| 204 |
truncate table test.t2; |
| 205 |
Contract pk (a,b) -> (b) |
| 206 |
Also blob changes requiring staging |
| 207 |
************************** |
| 208 |
create table test.t1 (a varchar(200) not null, |
| 209 |
b varchar(15) not null, |
| 210 |
c varchar(200) not null, |
| 211 |
d varchar(2500), |
| 212 |
primary key(b)) engine=ndb; |
| 213 |
Run restore |
| 214 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 215 |
a b c length(d) md5(d) right(d,20) |
| 216 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 217 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 218 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 219 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 220 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 221 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 222 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 223 |
drop table test.t1; |
| 224 |
truncate table test.t2; |
| 225 |
Contract pk (a,b) -> (b) |
| 226 |
Also blob changes requiring staging |
| 227 |
Also drop column |
| 228 |
************************** |
| 229 |
create table test.t1 (b varchar(15) not null, |
| 230 |
c varchar(200) not null, |
| 231 |
d varchar(2500), |
| 232 |
primary key(b)) engine=ndb; |
| 233 |
Run restore |
| 234 |
select b,c,length(d), md5(d), right(d,20) from test.t1 order by b; |
| 235 |
b c length(d) md5(d) right(d,20) |
| 236 |
1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 237 |
2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 238 |
3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 239 |
4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 240 |
6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 241 |
7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 242 |
9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 243 |
drop table test.t1; |
| 244 |
truncate table test.t2; |
| 245 |
Test with multiple table requiring changes |
| 246 |
------------------------------------------ |
| 247 |
drop table test.t2; |
| 248 |
Contract pk (a,b) -> (b) |
| 249 |
Also blob changes requiring staging |
| 250 |
************************** |
| 251 |
create table test.t1 (a varchar(200) not null, |
| 252 |
b varchar(15) not null, |
| 253 |
c varchar(200) not null, |
| 254 |
d varchar(2500), |
| 255 |
primary key(b)) engine=ndb; |
| 256 |
Contract pk (a,b) -> (a) |
| 257 |
Also blob changes requiring staging |
| 258 |
************************** |
| 259 |
create table test.t2 (a varchar(20) not null, |
| 260 |
b varchar(20) not null, |
| 261 |
c varchar(4000) not null, |
| 262 |
d text(2000) not null, |
| 263 |
primary key (a)) engine=ndb; |
| 264 |
Run restore |
| 265 |
select a,b,c,length(d), md5(d), right(d,20) from test.t1 order by a; |
| 266 |
a b c length(d) md5(d) right(d,20) |
| 267 |
1a 1b 1c 2009 68d455348c0d3bb17f4615a5691e632a d1d1d1d1d1d-modified |
| 268 |
2a 2b 2c 2009 ff0d862d596a331af498dafbe633c9a7 d2d2d2d2d2d-modified |
| 269 |
3a 3b 3c 2009 79abe7b2f32f098a49866b27026af10c d3d3d3d3d3d-modified |
| 270 |
4a 4b 4c 2009 ccdb41368e16028cb059182a2fd1ca97 d4d4d4d4d4d-modified |
| 271 |
6a 6b 6c 2009 0f6ca49fc7b53e16d20428904af2d7ff d6d6d6d6d6d-modified |
| 272 |
7a 7b 7c 2009 fe3251c0a77e687fae5163c4c37d8394 d7d7d7d7d7d-modified |
| 273 |
9a 9b 9c 2009 0e83ae0b9cc46e9ca293663e528d0129 d9d9d9d9d9d-modified |
| 274 |
select a,b,length(c), md5(c), right(c, 20), length(d), md5(d), right(d,20) from test.t2 order by a; |
| 275 |
a b length(c) md5(c) right(c, 20) length(d) md5(d) right(d,20) |
| 276 |
Acht Acht 40 4e5fab56696355c99c3bcffd5fe39d5e AchtAchtAchtAchtAcht 8000 50bd6e30f3801a968d8e4044751a98ad AchtAchtAchtAchtAcht |
| 277 |
Drei Drei-jah 4000 0b5298c37fbb5cfc7137b89f9711974b DreiDreiDreiDreiDrei 4000 0b5298c37fbb5cfc7137b89f9711974b DreiDreiDreiDreiDrei |
| 278 |
Ein Ein 3000 109abc9b5499d3de3cce205cd4e25da9 inEinEinEinEinEinEin 3000 109abc9b5499d3de3cce205cd4e25da9 inEinEinEinEinEinEin |
| 279 |
Funf Funf 4000 32d2ad309dccb89aab90f178934c3933 FunfFunfFunfFunfFunf 4000 32d2ad309dccb89aab90f178934c3933 FunfFunfFunfFunfFunf |
| 280 |
Neun Neun 800 9712e7c5fc2470f11185907bcde8293d NeunNeunNeunNeunNeun 20 c6e516d71a611ae0941d9581e8522b6d NeunNeunNeunNeunNeun |
| 281 |
Vier Vier 4000 e4c3ae579b61aa4630e10e220c0620e4 VierVierVierVierVier 4000 e4c3ae579b61aa4630e10e220c0620e4 VierVierVierVierVier |
| 282 |
Zwei Zwei-jah 4000 4531420243d24d5002c58f578e8ec114 ZweiZweiZweiZweiZwei 4000 4531420243d24d5002c58f578e8ec114 ZweiZweiZweiZweiZwei |
| 283 |
drop table test.t1; |
| 284 |
drop table test.t2; |