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  }