vitess.io/vitess@v0.16.2/go/vt/vttablet/tabletmanager/vreplication/vreplicator_test.go (about) 1 /* 2 Copyright 2019 The Vitess Authors. 3 4 Licensed under the Apache License, Version 2.0 (the "License"); 5 you may not use this file except in compliance with the License. 6 You may obtain a copy of the License at 7 8 http://www.apache.org/licenses/LICENSE-2.0 9 10 Unless required by applicable law or agreed to in writing, software 11 distributed under the License is distributed on an "AS IS" BASIS, 12 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 See the License for the specific language governing permissions and 14 limitations under the License. 15 */ 16 17 package vreplication 18 19 import ( 20 "context" 21 "encoding/json" 22 "fmt" 23 "reflect" 24 "regexp" 25 "strings" 26 "sync" 27 "testing" 28 "time" 29 "unicode" 30 31 "github.com/buger/jsonparser" 32 "github.com/stretchr/testify/assert" 33 "github.com/stretchr/testify/require" 34 35 "vitess.io/vitess/go/vt/binlog/binlogplayer" 36 "vitess.io/vitess/go/vt/dbconfigs" 37 "vitess.io/vitess/go/vt/mysqlctl" 38 binlogdatapb "vitess.io/vitess/go/vt/proto/binlogdata" 39 tabletmanagerdatapb "vitess.io/vitess/go/vt/proto/tabletmanagerdata" 40 "vitess.io/vitess/go/vt/schemadiff" 41 ) 42 43 func TestRecalculatePKColsInfoByColumnNames(t *testing.T) { 44 tt := []struct { 45 name string 46 colNames []string 47 colInfos []*ColumnInfo 48 expectPKColInfos []*ColumnInfo 49 }{ 50 { 51 name: "trivial, single column", 52 colNames: []string{"c1"}, 53 colInfos: []*ColumnInfo{{Name: "c1", IsPK: true}}, 54 expectPKColInfos: []*ColumnInfo{{Name: "c1", IsPK: true}}, 55 }, 56 { 57 name: "trivial, multiple columns", 58 colNames: []string{"c1"}, 59 colInfos: []*ColumnInfo{{Name: "c1", IsPK: true}, {Name: "c2", IsPK: false}, {Name: "c3", IsPK: false}}, 60 expectPKColInfos: []*ColumnInfo{{Name: "c1", IsPK: true}, {Name: "c2", IsPK: false}, {Name: "c3", IsPK: false}}, 61 }, 62 { 63 name: "last column, multiple columns", 64 colNames: []string{"c3"}, 65 colInfos: []*ColumnInfo{{Name: "c1", IsPK: false}, {Name: "c2", IsPK: false}, {Name: "c3", IsPK: true}}, 66 expectPKColInfos: []*ColumnInfo{{Name: "c3", IsPK: true}, {Name: "c1", IsPK: false}, {Name: "c2", IsPK: false}}, 67 }, 68 { 69 name: "change of key, single column", 70 colNames: []string{"c2"}, 71 colInfos: []*ColumnInfo{{Name: "c1", IsPK: false}, {Name: "c2", IsPK: false}, {Name: "c3", IsPK: true}}, 72 expectPKColInfos: []*ColumnInfo{{Name: "c2", IsPK: true}, {Name: "c1", IsPK: false}, {Name: "c3", IsPK: false}}, 73 }, 74 { 75 name: "change of key, multiple columns", 76 colNames: []string{"c2", "c3"}, 77 colInfos: []*ColumnInfo{{Name: "c1", IsPK: false}, {Name: "c2", IsPK: false}, {Name: "c3", IsPK: true}}, 78 expectPKColInfos: []*ColumnInfo{{Name: "c2", IsPK: true}, {Name: "c3", IsPK: true}, {Name: "c1", IsPK: false}}, 79 }, 80 } 81 82 for _, tc := range tt { 83 t.Run(tc.name, func(t *testing.T) { 84 pkColInfos := recalculatePKColsInfoByColumnNames(tc.colNames, tc.colInfos) 85 assert.Equal(t, tc.expectPKColInfos, pkColInfos) 86 }) 87 } 88 } 89 90 func TestPrimaryKeyEquivalentColumns(t *testing.T) { 91 ctx := context.Background() 92 tests := []struct { 93 name string 94 table string 95 ddl string 96 want []string 97 wantErr bool 98 }{ 99 { 100 name: "WITHPK", 101 table: "withpk_t", 102 ddl: `CREATE TABLE withpk_t (pkid INT NOT NULL AUTO_INCREMENT, col1 VARCHAR(25), 103 PRIMARY KEY (pkid))`, 104 want: []string{"pkid"}, 105 }, 106 { 107 name: "0PKE", 108 table: "zeropke_t", 109 ddl: `CREATE TABLE zeropke_t (id INT NULL, col1 VARCHAR(25), UNIQUE KEY (id))`, 110 want: []string{}, 111 }, 112 { 113 name: "1PKE", 114 table: "onepke_t", 115 ddl: `CREATE TABLE onepke_t (id INT NOT NULL, col1 VARCHAR(25), UNIQUE KEY (id))`, 116 want: []string{"id"}, 117 }, 118 { 119 name: "3MULTICOL1PKE", 120 table: "onemcpke_t", 121 ddl: `CREATE TABLE onemcpke_t (col1 VARCHAR(25) NOT NULL, col2 VARCHAR(25) NOT NULL, 122 col3 VARCHAR(25) NOT NULL, col4 VARCHAR(25), UNIQUE KEY c4_c2_c1 (col4, col2, col1), 123 UNIQUE KEY c1_c2 (col1, col2), UNIQUE KEY c1_c2_c4 (col1, col2, col4), 124 KEY nc1_nc2 (col1, col2))`, 125 want: []string{"col1", "col2"}, 126 }, 127 { 128 name: "3MULTICOL2PKE", 129 table: "twomcpke_t", 130 ddl: `CREATE TABLE twomcpke_t (col1 VARCHAR(25) NOT NULL, col2 VARCHAR(25) NOT NULL, 131 col3 VARCHAR(25) NOT NULL, col4 VARCHAR(25), UNIQUE KEY (col4), UNIQUE KEY c4_c2_c1 (col4, col2, col1), 132 UNIQUE KEY c1_c2_c3 (col1, col2, col3), UNIQUE KEY c1_c2 (col1, col2))`, 133 want: []string{"col1", "col2"}, 134 }, 135 { 136 name: "1INTPKE1CHARPKE", 137 table: "oneintpke1charpke_t", 138 ddl: `CREATE TABLE oneintpke1charpke_t (col1 VARCHAR(25) NOT NULL, col2 VARCHAR(25) NOT NULL, 139 col3 VARCHAR(25) NOT NULL, id1 INT NOT NULL, id2 INT NOT NULL, 140 UNIQUE KEY c1_c2 (col1, col2), UNIQUE KEY id1_id2 (id1, id2))`, 141 want: []string{"id1", "id2"}, 142 }, 143 { 144 name: "INTINTVSVCHAR", 145 table: "twointvsvcharpke_t", 146 ddl: `CREATE TABLE twointvsvcharpke_t (col1 VARCHAR(25) NOT NULL, id1 INT NOT NULL, id2 INT NOT NULL, 147 UNIQUE KEY c1 (col1), UNIQUE KEY id1_id2 (id1, id2))`, 148 want: []string{"id1", "id2"}, 149 }, 150 { 151 name: "TINYINTVSBIGINT", 152 table: "tinyintvsbigint_t", 153 ddl: `CREATE TABLE tinyintvsbigint_t (tid1 TINYINT NOT NULL, id1 INT NOT NULL, 154 UNIQUE KEY tid1 (tid1), UNIQUE KEY id1 (id1))`, 155 want: []string{"tid1"}, 156 }, 157 { 158 name: "VCHARINTVSINT2VARCHAR", 159 table: "vcharintvsinttwovchar_t", 160 ddl: `CREATE TABLE vcharintvsinttwovchar_t (id1 INT NOT NULL, col1 VARCHAR(25) NOT NULL, col2 VARCHAR(25) NOT NULL, 161 UNIQUE KEY col1_id1 (col1, id1), UNIQUE KEY id1_col1_col2 (id1, col1, col2))`, 162 want: []string{"col1", "id1"}, 163 }, 164 { 165 name: "VCHARVSINT3", 166 table: "vcharvsintthree_t", 167 ddl: `CREATE TABLE vcharvsintthree_t (id1 INT NOT NULL, id2 INT NOT NULL, id3 INT NOT NULL, col1 VARCHAR(50) NOT NULL, 168 UNIQUE KEY col1 (col1), UNIQUE KEY id1_id2_id3 (id1, id2, id3))`, 169 want: []string{"id1", "id2", "id3"}, 170 }, 171 } 172 for _, tt := range tests { 173 t.Run(tt.name, func(t *testing.T) { 174 require.NoError(t, env.Mysqld.ExecuteSuperQuery(ctx, tt.ddl)) 175 got, err := env.Mysqld.GetPrimaryKeyEquivalentColumns(ctx, env.Dbcfgs.DBName, tt.table) 176 if (err != nil) != tt.wantErr { 177 t.Errorf("Mysqld.GetPrimaryKeyEquivalentColumns() error = %v, wantErr %v", err, tt.wantErr) 178 return 179 } 180 if !reflect.DeepEqual(got, tt.want) { 181 t.Errorf("Mysqld.GetPrimaryKeyEquivalentColumns() = %v, want %v", got, tt.want) 182 } 183 }) 184 } 185 } 186 187 // TestDeferSecondaryKeys confirms the behavior of the 188 // --defer-secondary-keys MoveTables/Migrate, and Reshard 189 // workflow/command flag. 190 // 1. We drop the secondary keys 191 // 2. We store the secondary key definitions for step 3 192 // 3. We add the secondary keys back after the rows are copied 193 func TestDeferSecondaryKeys(t *testing.T) { 194 ctx := context.Background() 195 tablet := addTablet(100) 196 defer deleteTablet(tablet) 197 filter := &binlogdatapb.Filter{ 198 Rules: []*binlogdatapb.Rule{{ 199 Match: "t1", 200 }}, 201 } 202 bls := &binlogdatapb.BinlogSource{ 203 Keyspace: env.KeyspaceName, 204 Shard: env.ShardName, 205 Filter: filter, 206 } 207 id := uint32(1) 208 vsclient := newTabletConnector(tablet) 209 stats := binlogplayer.NewStats() 210 dbClient := playerEngine.dbClientFactoryFiltered() 211 err := dbClient.Connect() 212 require.NoError(t, err) 213 defer dbClient.Close() 214 dbName := dbClient.DBName() 215 // Ensure there's a dummy vreplication workflow record 216 _, err = dbClient.ExecuteFetch(fmt.Sprintf("insert into _vt.vreplication (id, workflow, source, pos, max_tps, max_replication_lag, time_updated, transaction_timestamp, state, db_name) values (%d, 'test', '', '', 99999, 99999, 0, 0, 'Running', '%s') on duplicate key update workflow='test', source='', pos='', max_tps=99999, max_replication_lag=99999, time_updated=0, transaction_timestamp=0, state='Running', db_name='%s'", 217 id, dbName, dbName), 1) 218 require.NoError(t, err) 219 defer func() { 220 _, err = dbClient.ExecuteFetch(fmt.Sprintf("delete from _vt.vreplication where id = %d", id), 1) 221 require.NoError(t, err) 222 }() 223 vr := newVReplicator(id, bls, vsclient, stats, dbClient, env.Mysqld, playerEngine) 224 getActionsSQLf := "select action from _vt.post_copy_action where table_name='%s'" 225 getCurrentDDL := func(tableName string) string { 226 req := &tabletmanagerdatapb.GetSchemaRequest{Tables: []string{tableName}} 227 sd, err := env.Mysqld.GetSchema(ctx, dbName, req) 228 require.NoError(t, err) 229 require.Equal(t, 1, len(sd.TableDefinitions)) 230 return removeVersionDifferences(sd.TableDefinitions[0].Schema) 231 } 232 _, err = dbClient.ExecuteFetch("use "+dbName, 1) 233 require.NoError(t, err) 234 diffHints := &schemadiff.DiffHints{ 235 StrictIndexOrdering: false, 236 } 237 238 tests := []struct { 239 name string 240 tableName string 241 initialDDL string 242 strippedDDL string 243 intermediateDDL string 244 actionDDL string 245 WorkflowType int32 246 wantStashErr string 247 wantExecErr string 248 expectFinalSchemaDiff bool 249 postStashHook func() error 250 }{ 251 { 252 name: "0SK", 253 tableName: "t1", 254 initialDDL: "create table t1 (id int not null, primary key (id))", 255 strippedDDL: "create table t1 (id int not null, primary key (id))", 256 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 257 }, 258 { 259 name: "1SK:Materialize", 260 tableName: "t1", 261 initialDDL: "create table t1 (id int not null, c1 int default null, primary key (id), key c1 (c1))", 262 strippedDDL: "create table t1 (id int not null, c1 int default null, primary key (id), key c1 (c1))", 263 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_Materialize), 264 wantStashErr: "deferring secondary key creation is not supported for Materialize workflows", 265 }, 266 { 267 name: "1SK:OnlineDDL", 268 tableName: "t1", 269 initialDDL: "create table t1 (id int not null, c1 int default null, primary key (id), key c1 (c1))", 270 strippedDDL: "create table t1 (id int not null, c1 int default null, primary key (id), key c1 (c1))", 271 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_OnlineDDL), 272 wantStashErr: "deferring secondary key creation is not supported for OnlineDDL workflows", 273 }, 274 { 275 name: "1SK", 276 tableName: "t1", 277 initialDDL: "create table t1 (id int not null, c1 int default null, primary key (id), key c1 (c1))", 278 strippedDDL: "create table t1 (id int not null, c1 int default null, primary key (id))", 279 actionDDL: "alter table %s.t1 add key c1 (c1)", 280 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_Reshard), 281 }, 282 { 283 name: "2SK", 284 tableName: "t1", 285 initialDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id), key c1 (c1), key c2 (c2))", 286 strippedDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id))", 287 actionDDL: "alter table %s.t1 add key c1 (c1), add key c2 (c2)", 288 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 289 }, 290 { 291 name: "2tSK", 292 tableName: "t1", 293 initialDDL: "create table t1 (id int not null, c1 varchar(10) default null, c2 varchar(10) default null, primary key (id), key c1_c2 (c1,c2), key c2 (c2))", 294 strippedDDL: "create table t1 (id int not null, c1 varchar(10) default null, c2 varchar(10) default null, primary key (id))", 295 actionDDL: "alter table %s.t1 add key c1_c2 (c1, c2), add key c2 (c2)", 296 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 297 }, 298 { 299 name: "2FPK2SK", 300 tableName: "t1", 301 initialDDL: "create table t1 (id int not null, c1 varchar(10) not null, c2 varchar(10) default null, primary key (id,c1), key c1_c2 (c1,c2), key c2 (c2))", 302 strippedDDL: "create table t1 (id int not null, c1 varchar(10) not null, c2 varchar(10) default null, primary key (id,c1))", 303 actionDDL: "alter table %s.t1 add key c1_c2 (c1, c2), add key c2 (c2)", 304 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 305 }, 306 { 307 name: "3FPK1SK", 308 tableName: "t1", 309 initialDDL: "create table t1 (id int not null, c1 varchar(10) not null, c2 varchar(10) not null, primary key (id,c1,c2), key c2 (c2))", 310 strippedDDL: "create table t1 (id int not null, c1 varchar(10) not null, c2 varchar(10) not null, primary key (id,c1,c2))", 311 actionDDL: "alter table %s.t1 add key c2 (c2)", 312 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_Reshard), 313 }, 314 { 315 name: "3FPK1SK_ShardMerge", 316 tableName: "t1", 317 initialDDL: "create table t1 (id int not null, c1 varchar(10) not null, c2 varchar(10) not null, primary key (id,c1,c2), key c2 (c2))", 318 strippedDDL: "create table t1 (id int not null, c1 varchar(10) not null, c2 varchar(10) not null, primary key (id,c1,c2))", 319 actionDDL: "alter table %s.t1 add key c2 (c2)", 320 postStashHook: func() error { 321 myid := id + 1000 322 // Insert second vreplication record to simulate a second controller/vreplicator 323 _, err = dbClient.ExecuteFetch(fmt.Sprintf("insert into _vt.vreplication (id, workflow, source, pos, max_tps, max_replication_lag, time_updated, transaction_timestamp, state, db_name) values (%d, 'test', '', '', 99999, 99999, 0, 0, 'Running', '%s')", 324 myid, dbName), 1) 325 if err != nil { 326 return err 327 } 328 myvr := newVReplicator(myid, bls, vsclient, stats, dbClient, env.Mysqld, playerEngine) 329 myvr.WorkflowType = int32(binlogdatapb.VReplicationWorkflowType_Reshard) 330 // Insert second post copy action record to simulate a shard merge where you 331 // have N controllers/replicators running for the same table on the tablet. 332 // This forces a second row, which would otherwise not get created beacause 333 // when this is called there's no secondary keys to stash anymore. 334 addlAction, err := json.Marshal(PostCopyAction{ 335 Type: PostCopyActionSQL, 336 Task: fmt.Sprintf("alter table %s.t1 add key c2 (c2)", dbName), 337 }) 338 if err != nil { 339 return err 340 } 341 _, err = dbClient.ExecuteFetch(fmt.Sprintf("insert into _vt.post_copy_action (vrepl_id, table_name, action) values (%d, 't1', '%s')", 342 myid, string(addlAction)), 1) 343 if err != nil { 344 return err 345 } 346 err = myvr.execPostCopyActions(ctx, "t1") 347 if err != nil { 348 return err 349 } 350 return nil 351 }, 352 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_Reshard), 353 }, 354 { 355 name: "0FPK2tSK", 356 tableName: "t1", 357 initialDDL: "create table t1 (id int not null, c1 varchar(10) default null, c2 varchar(10) default null, key c1_c2 (c1,c2), key c2 (c2))", 358 strippedDDL: "create table t1 (id int not null, c1 varchar(10) default null, c2 varchar(10) default null)", 359 actionDDL: "alter table %s.t1 add key c1_c2 (c1, c2), add key c2 (c2)", 360 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 361 }, 362 { 363 name: "2SKRetryNoErr", 364 tableName: "t1", 365 initialDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id), key c1 (c1), key c2 (c2))", 366 strippedDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id))", 367 intermediateDDL: "alter table %s.t1 add key c1 (c1), add key c2 (c2)", 368 actionDDL: "alter table %s.t1 add key c1 (c1), add key c2 (c2)", 369 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 370 }, 371 { 372 name: "2SKRetryNoErr2", 373 tableName: "t1", 374 initialDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id), key c1 (c1), key c2 (c2))", 375 strippedDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id))", 376 intermediateDDL: "alter table %s.t1 add key c2 (c2), add key c1 (c1)", 377 actionDDL: "alter table %s.t1 add key c1 (c1), add key c2 (c2)", 378 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 379 }, 380 { 381 name: "SKSuperSetNoErr", // a superset of the original keys is allowed 382 tableName: "t1", 383 initialDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id), key c1 (c1), key c2 (c2))", 384 strippedDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id))", 385 intermediateDDL: "alter table %s.t1 add unique key c1_c2 (c1,c2), add key c2 (c2), add key c1 (c1)", 386 actionDDL: "alter table %s.t1 add key c1 (c1), add key c2 (c2)", 387 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 388 expectFinalSchemaDiff: true, 389 }, 390 { 391 name: "2SKRetryErr", 392 tableName: "t1", 393 initialDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id), key c1 (c1), key c2 (c2))", 394 strippedDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id))", 395 intermediateDDL: "alter table %s.t1 add key c2 (c2)", 396 actionDDL: "alter table %s.t1 add key c1 (c1), add key c2 (c2)", 397 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 398 wantExecErr: "Duplicate key name 'c2' (errno 1061) (sqlstate 42000)", 399 }, 400 { 401 name: "2SKRetryErr2", 402 tableName: "t1", 403 initialDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id), key c1 (c1), key c2 (c2))", 404 strippedDDL: "create table t1 (id int not null, c1 int default null, c2 int default null, primary key (id))", 405 intermediateDDL: "alter table %s.t1 add key c1 (c1)", 406 actionDDL: "alter table %s.t1 add key c1 (c1), add key c2 (c2)", 407 WorkflowType: int32(binlogdatapb.VReplicationWorkflowType_MoveTables), 408 wantExecErr: "Duplicate key name 'c1' (errno 1061) (sqlstate 42000)", 409 }, 410 } 411 412 for _, tcase := range tests { 413 t.Run(tcase.name, func(t *testing.T) { 414 // Deferred secondary indexes are only supported for 415 // MoveTables and Reshard workflows. 416 vr.WorkflowType = tcase.WorkflowType 417 418 // Create the table. 419 _, err := dbClient.ExecuteFetch(tcase.initialDDL, 1) 420 require.NoError(t, err) 421 defer func() { 422 _, err = dbClient.ExecuteFetch(fmt.Sprintf("drop table %s.%s", dbName, tcase.tableName), 1) 423 require.NoError(t, err) 424 _, err = dbClient.ExecuteFetch("delete from _vt.post_copy_action", 1) 425 require.NoError(t, err) 426 }() 427 428 confirmNoSecondaryKeys := func() { 429 // Confirm that the table now has no secondary keys. 430 tcase.strippedDDL = removeVersionDifferences(tcase.strippedDDL) 431 currentDDL := getCurrentDDL(tcase.tableName) 432 require.True(t, strings.EqualFold(stripCruft(tcase.strippedDDL), stripCruft(currentDDL)), 433 "Expected: %s\n Got: %s", forError(tcase.strippedDDL), forError(currentDDL)) 434 } 435 436 // If the table has any secondary keys, drop them and 437 // store an ALTER TABLE statement to re-add them after 438 // the table is copied. 439 err = vr.stashSecondaryKeys(ctx, tcase.tableName) 440 if tcase.wantStashErr != "" { 441 require.EqualError(t, err, tcase.wantStashErr) 442 } else { 443 require.NoError(t, err) 444 } 445 confirmNoSecondaryKeys() 446 447 if tcase.postStashHook != nil { 448 err = tcase.postStashHook() 449 require.NoError(t, err) 450 451 // We should still NOT have any secondary keys because there's still 452 // a running controller/vreplicator in the copy phase. 453 confirmNoSecondaryKeys() 454 } 455 456 // If we expect post-copy SQL actions, then ensure 457 // that the stored DDL matches what we expect. 458 if tcase.actionDDL != "" { 459 res, err := dbClient.ExecuteFetch(fmt.Sprintf(getActionsSQLf, tcase.tableName), 1) 460 require.Equal(t, 1, len(res.Rows)) 461 require.NoError(t, err) 462 val, err := res.Rows[0][0].ToBytes() 463 require.NoError(t, err) 464 alter, err := jsonparser.GetString(val, "task") 465 require.NoError(t, err) 466 require.True(t, strings.EqualFold(stripCruft(fmt.Sprintf(tcase.actionDDL, dbName)), stripCruft(alter)), 467 "Expected: %s\n Got: %s", forError(fmt.Sprintf(tcase.actionDDL, dbName)), forError(alter)) 468 } 469 470 if tcase.intermediateDDL != "" { 471 _, err := dbClient.ExecuteFetch(fmt.Sprintf(tcase.intermediateDDL, dbName), 1) 472 require.NoError(t, err) 473 } 474 475 err = vr.execPostCopyActions(ctx, tcase.tableName) 476 expectedPostCopyActionRecs := 0 477 if tcase.wantExecErr != "" { 478 require.Contains(t, err.Error(), tcase.wantExecErr) 479 expectedPostCopyActionRecs = 1 480 } else { 481 require.NoError(t, err) 482 // Confirm that the final DDL logically matches the initial DDL. 483 // We do not require that the index definitions are in the same 484 // order in the table schema. 485 if !tcase.expectFinalSchemaDiff { 486 currentDDL := getCurrentDDL(tcase.tableName) 487 sdiff, err := schemadiff.DiffCreateTablesQueries(currentDDL, tcase.initialDDL, diffHints) 488 require.NoError(t, err) 489 require.Nil(t, sdiff, "Expected no schema difference but got: %s", sdiff.CanonicalStatementString()) 490 } 491 } 492 493 // Confirm that the post copy action record(s) are deleted when there's 494 // no exec error or conversely that it still exists when there was 495 // one. 496 res, err := dbClient.ExecuteFetch(fmt.Sprintf(getActionsSQLf, tcase.tableName), expectedPostCopyActionRecs) 497 require.NoError(t, err) 498 require.Equal(t, expectedPostCopyActionRecs, len(res.Rows), 499 "Expected %d post copy action records, got %d", expectedPostCopyActionRecs, len(res.Rows)) 500 }) 501 } 502 } 503 504 // TestCancelledDeferSecondaryKeys tests that the ALTER 505 // TABLE statement used to re-add secondary keys (when 506 // the --defer-secondary-keys flag was used), after 507 // copying all rows, is properly killed when the context 508 // is cancelled -- e.g. due to the VReplication engine 509 // closing for a tablet transition during a PRS. 510 func TestCancelledDeferSecondaryKeys(t *testing.T) { 511 // Skip the test for MariaDB as it does not have 512 // performance_schema enabled by default. 513 version, err := mysqlctl.GetVersionString() 514 require.NoError(t, err) 515 flavor, _, err := mysqlctl.ParseVersionString(version) 516 require.NoError(t, err) 517 if flavor == mysqlctl.FlavorMariaDB { 518 t.Skipf("Skipping test as it's not supported with %s", flavor) 519 } 520 521 ctx, cancel := context.WithCancel(context.Background()) 522 defer cancel() 523 tablet := addTablet(100) 524 defer deleteTablet(tablet) 525 filter := &binlogdatapb.Filter{ 526 Rules: []*binlogdatapb.Rule{{ 527 Match: "t1", 528 }}, 529 } 530 bls := &binlogdatapb.BinlogSource{ 531 Keyspace: env.KeyspaceName, 532 Shard: env.ShardName, 533 Filter: filter, 534 } 535 // The test env uses the same factory for both dba and 536 // filtered connections. 537 dbconfigs.GlobalDBConfigs.Filtered.User = "vt_dba" 538 id := uint32(1) 539 vsclient := newTabletConnector(tablet) 540 stats := binlogplayer.NewStats() 541 dbaconn := playerEngine.dbClientFactoryDba() 542 err = dbaconn.Connect() 543 require.NoError(t, err) 544 defer dbaconn.Close() 545 dbClient := playerEngine.dbClientFactoryFiltered() 546 err = dbClient.Connect() 547 require.NoError(t, err) 548 defer dbClient.Close() 549 dbName := dbClient.DBName() 550 // Ensure there's a dummy vreplication workflow record 551 _, err = dbClient.ExecuteFetch(fmt.Sprintf("insert into _vt.vreplication (id, workflow, source, pos, max_tps, max_replication_lag, time_updated, transaction_timestamp, state, db_name) values (%d, 'test', '', '', 99999, 99999, 0, 0, 'Running', '%s') on duplicate key update workflow='test', source='', pos='', max_tps=99999, max_replication_lag=99999, time_updated=0, transaction_timestamp=0, state='Running', db_name='%s'", 552 id, dbName, dbName), 1) 553 require.NoError(t, err) 554 defer func() { 555 _, err = dbClient.ExecuteFetch(fmt.Sprintf("delete from _vt.vreplication where id = %d", id), 1) 556 require.NoError(t, err) 557 }() 558 vr := newVReplicator(id, bls, vsclient, stats, dbClient, env.Mysqld, playerEngine) 559 vr.WorkflowType = int32(binlogdatapb.VReplicationWorkflowType_MoveTables) 560 getCurrentDDL := func(tableName string) string { 561 req := &tabletmanagerdatapb.GetSchemaRequest{Tables: []string{tableName}} 562 sd, err := env.Mysqld.GetSchema(context.Background(), dbName, req) 563 require.NoError(t, err) 564 require.Equal(t, 1, len(sd.TableDefinitions)) 565 return removeVersionDifferences(sd.TableDefinitions[0].Schema) 566 } 567 getActionsSQLf := "select action from _vt.post_copy_action where vrepl_id=%d and table_name='%s'" 568 569 tableName := "t1" 570 ddl := fmt.Sprintf("create table %s.t1 (id int not null, c1 int default null, c2 int default null, primary key(id), key c1 (c1), key c2 (c2))", dbName) 571 withoutPKs := "create table t1 (id int not null, c1 int default null, c2 int default null, primary key(id))" 572 alter := fmt.Sprintf("alter table %s.t1 add key c1 (c1), add key c2 (c2)", dbName) 573 574 // Create the table. 575 _, err = dbClient.ExecuteFetch(ddl, 1) 576 require.NoError(t, err) 577 578 // Setup the ALTER work. 579 err = vr.stashSecondaryKeys(ctx, tableName) 580 require.NoError(t, err) 581 582 // Lock the table to block execution of the ALTER so 583 // that we can be sure that it runs and we can KILL it. 584 _, err = dbaconn.ExecuteFetch(fmt.Sprintf("lock table %s.%s write", dbName, tableName), 1) 585 require.NoError(t, err) 586 587 // The ALTER should block on the table lock. 588 wg := sync.WaitGroup{} 589 wg.Add(1) 590 go func() { 591 defer wg.Done() 592 err := vr.execPostCopyActions(ctx, tableName) 593 assert.True(t, strings.EqualFold(err.Error(), fmt.Sprintf("EOF (errno 2013) (sqlstate HY000) during query: %s", alter))) 594 }() 595 596 // Confirm that the expected ALTER query is being attempted. 597 query := fmt.Sprintf("select count(*) from performance_schema.events_statements_current where sql_text = '%s'", alter) 598 waitForQueryResult(t, dbaconn, query, "1") 599 600 // Cancel the context while the ALTER is running/blocked 601 // and wait for it to be KILLed off. 602 playerEngine.cancel() 603 wg.Wait() 604 605 _, err = dbaconn.ExecuteFetch("unlock tables", 1) 606 assert.NoError(t, err) 607 608 // Confirm that the ALTER to re-add the secondary keys 609 // did not succeed. 610 currentDDL := getCurrentDDL(tableName) 611 assert.True(t, strings.EqualFold(stripCruft(withoutPKs), stripCruft(currentDDL)), 612 "Expected: %s\n Got: %s", forError(withoutPKs), forError(currentDDL)) 613 614 // Confirm that we successfully attempted to kill it. 615 query = "select count(*) from performance_schema.events_statements_history where digest_text = 'KILL ?' and errors = 0" 616 res, err := dbaconn.ExecuteFetch(query, 1) 617 assert.NoError(t, err) 618 assert.Equal(t, 1, len(res.Rows)) 619 // TODO: figure out why the KILL never shows up... 620 //require.Equal(t, "1", res.Rows[0][0].ToString()) 621 622 // Confirm that the post copy action record still exists 623 // so it will later be retried. 624 res, err = dbClient.ExecuteFetch(fmt.Sprintf(getActionsSQLf, id, tableName), 1) 625 require.NoError(t, err) 626 require.Equal(t, 1, len(res.Rows)) 627 } 628 629 // stripCruft removes all whitespace unicode chars and backticks. 630 func stripCruft(in string) string { 631 out := strings.Builder{} 632 for _, r := range in { 633 if unicode.IsSpace(r) || r == '`' { 634 continue 635 } 636 out.WriteRune(r) 637 } 638 return out.String() 639 } 640 641 // forError returns a string for humans to easily compare in 642 // in error messages. 643 func forError(in string) string { 644 mid := strings.ToLower(in) 645 // condense multiple spaces into one. 646 mid = regexp.MustCompile(`\s+`).ReplaceAllString(mid, " ") 647 sr := strings.NewReplacer( 648 "\t", "", 649 "\n", "", 650 "\r", "", 651 "`", "", 652 "( ", "(", 653 " )", ")", 654 ) 655 return sr.Replace(mid) 656 } 657 658 // removeVersionDifferences removes portions of a CREATE TABLE statement 659 // that differ between versions: 660 // - 8.0 no longer includes display widths for integer or year types 661 // - MySQL and MariaDB versions differ in what table options they display 662 func removeVersionDifferences(in string) string { 663 out := in 664 var re *regexp.Regexp 665 for _, baseType := range []string{"int", "year"} { 666 re = regexp.MustCompile(fmt.Sprintf(`(?i)%s\(([0-9]*)?\)`, baseType)) 667 out = re.ReplaceAllString(out, baseType) 668 } 669 re = regexp.MustCompile(`(?i)engine[\s]*=[\s]*innodb.*$`) 670 out = re.ReplaceAllString(out, "") 671 return out 672 } 673 674 func waitForQueryResult(t *testing.T, dbc binlogplayer.DBClient, query, val string) { 675 tmr := time.NewTimer(1 * time.Second) 676 defer tmr.Stop() 677 for { 678 res, err := dbc.ExecuteFetch(query, 1) 679 assert.NoError(t, err) 680 assert.Equal(t, 1, len(res.Rows)) 681 if res.Rows[0][0].ToString() == val { 682 return 683 } 684 select { 685 case <-tmr.C: 686 t.Fatalf("query %s did not return expected value of %s", query, val) 687 default: 688 time.Sleep(50 * time.Millisecond) 689 } 690 } 691 }