vitess.io/vitess@v0.16.2/go/mysql/endtoend/schema_change_test.go (about)

     1  /*
     2  Copyright 2021 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 endtoend
    18  
    19  import (
    20  	"context"
    21  	"fmt"
    22  	"strings"
    23  	"testing"
    24  
    25  	"vitess.io/vitess/go/vt/sqlparser"
    26  
    27  	"github.com/stretchr/testify/require"
    28  
    29  	"vitess.io/vitess/go/mysql"
    30  )
    31  
    32  var ctx = context.Background()
    33  
    34  const (
    35  	createUserTable = `create table vttest.product (id bigint(20) primary key, name char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, created bigint(20))`
    36  	dropTestTable   = `drop table if exists product`
    37  )
    38  
    39  func TestChangeSchemaIsNoticed(t *testing.T) {
    40  	conn, err := mysql.Connect(ctx, &connParams)
    41  	require.NoError(t, err)
    42  	defer conn.Close()
    43  
    44  	tests := []struct {
    45  		name    string
    46  		changeQ string
    47  	}{{
    48  		name:    "add column",
    49  		changeQ: "alter table vttest.product add column phone VARCHAR(15)",
    50  	}, {
    51  		name:    "rename column",
    52  		changeQ: "alter table vttest.product change name firstname char(10)",
    53  	}, {
    54  		name:    "change column type",
    55  		changeQ: "alter table vttest.product change name name char(100)",
    56  	}, {
    57  		name:    "remove column",
    58  		changeQ: "alter table vttest.product drop column name",
    59  	}, {
    60  		name:    "remove last column",
    61  		changeQ: "alter table vttest.product drop column created",
    62  	}, {
    63  		name:    "remove table",
    64  		changeQ: "drop table product",
    65  	}, {
    66  		name:    "create table",
    67  		changeQ: `create table vttest.new_table (id bigint(20) primary key)`,
    68  	}, {
    69  		name:    "change character set",
    70  		changeQ: "alter table vttest.product change name name char(10) CHARACTER SET utf8mb4",
    71  	}, {
    72  		name:    "change collation",
    73  		changeQ: "alter table vttest.product change name name char(10) COLLATE utf8_unicode_520_ci",
    74  	}, {
    75  		name:    "drop PK",
    76  		changeQ: "alter table vttest.product drop primary key",
    77  	}, {
    78  		name:    "change PK",
    79  		changeQ: "alter table vttest.product drop primary key, add primary key (name)",
    80  	}, {
    81  		name:    "two tables changes",
    82  		changeQ: "create table vttest.new_table2 (id bigint(20) primary key);alter table vttest.product drop column name",
    83  	}}
    84  
    85  	for _, test := range tests {
    86  		t.Run(test.name, func(t *testing.T) {
    87  			// reset schemacopy
    88  			_, err := conn.ExecuteFetch(mysql.ClearSchemaCopy, 1000, true)
    89  			require.NoError(t, err)
    90  			_, err = conn.ExecuteFetch(dropTestTable, 1000, true)
    91  			require.NoError(t, err)
    92  			_, err = conn.ExecuteFetch(createUserTable, 1000, true)
    93  			require.NoError(t, err)
    94  			rs, err := conn.ExecuteFetch(mysql.InsertIntoSchemaCopy, 1000, true)
    95  			require.NoError(t, err)
    96  			require.NotZero(t, rs.RowsAffected)
    97  
    98  			// make sure no changes are detected
    99  			rs, err = conn.ExecuteFetch(mysql.DetectSchemaChange, 1000, true)
   100  			require.NoError(t, err)
   101  			require.Empty(t, rs.Rows)
   102  
   103  			for _, q := range strings.Split(test.changeQ, ";") {
   104  				// make the schema change
   105  				_, err = conn.ExecuteFetch(q, 1000, true)
   106  				require.NoError(t, err)
   107  			}
   108  
   109  			// make sure the change is detected
   110  			rs, err = conn.ExecuteFetch(mysql.DetectSchemaChange, 1000, true)
   111  			require.NoError(t, err)
   112  			require.NotEmpty(t, rs.Rows)
   113  
   114  			var tables []string
   115  			for _, row := range rs.Rows {
   116  				apa := sqlparser.NewStrLiteral(row[0].ToString())
   117  				tables = append(tables, "table_name = "+sqlparser.String(apa))
   118  			}
   119  			tableNamePredicates := strings.Join(tables, " OR ")
   120  			del := fmt.Sprintf("%s AND %s", mysql.ClearSchemaCopy, tableNamePredicates)
   121  			upd := fmt.Sprintf("%s AND %s", mysql.InsertIntoSchemaCopy, tableNamePredicates)
   122  
   123  			_, err = conn.ExecuteFetch(del, 1000, true)
   124  			require.NoError(t, err)
   125  			_, err = conn.ExecuteFetch(upd, 1000, true)
   126  			require.NoError(t, err)
   127  
   128  			// make sure the change is detected
   129  			rs, err = conn.ExecuteFetch(mysql.DetectSchemaChange, 1000, true)
   130  			require.NoError(t, err)
   131  			require.Empty(t, rs.Rows)
   132  		})
   133  	}
   134  }