vitess.io/vitess@v0.16.2/go/test/endtoend/vtgate/schema/schema_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 schema
    18  
    19  import (
    20  	"encoding/json"
    21  	"flag"
    22  	"fmt"
    23  	"os"
    24  	"path"
    25  	"reflect"
    26  	"strings"
    27  	"testing"
    28  	"time"
    29  
    30  	"github.com/stretchr/testify/assert"
    31  	"github.com/stretchr/testify/require"
    32  
    33  	"vitess.io/vitess/go/test/endtoend/cluster"
    34  )
    35  
    36  var (
    37  	clusterInstance       *cluster.LocalProcessCluster
    38  	hostname              = "localhost"
    39  	keyspaceName          = "ks"
    40  	cell                  = "zone1"
    41  	schemaChangeDirectory = ""
    42  	totalTableCount       = 4
    43  	createTable           = `
    44  		CREATE TABLE %s (
    45  		id BIGINT(20) not NULL,
    46  		msg varchar(64),
    47  		PRIMARY KEY (id)
    48  		) ENGINE=InnoDB;`
    49  	alterTable = `
    50  		ALTER TABLE %s
    51  		ADD COLUMN new_id bigint(20) NOT NULL AUTO_INCREMENT FIRST,
    52  		DROP PRIMARY KEY,
    53  		ADD PRIMARY KEY (new_id),
    54  		ADD INDEX idx_column(%s)`
    55  )
    56  
    57  func TestMain(m *testing.M) {
    58  	defer cluster.PanicHandler(nil)
    59  	flag.Parse()
    60  
    61  	exitcode, err := func() (int, error) {
    62  		clusterInstance = cluster.NewCluster(cell, hostname)
    63  		schemaChangeDirectory = path.Join("/tmp", fmt.Sprintf("schema_change_dir_%d", clusterInstance.GetAndReserveTabletUID()))
    64  		defer os.RemoveAll(schemaChangeDirectory)
    65  		defer clusterInstance.Teardown()
    66  
    67  		if _, err := os.Stat(schemaChangeDirectory); os.IsNotExist(err) {
    68  			_ = os.Mkdir(schemaChangeDirectory, 0700)
    69  		}
    70  
    71  		clusterInstance.VtctldExtraArgs = []string{
    72  			"--schema_change_dir", schemaChangeDirectory,
    73  			"--schema_change_controller", "local",
    74  			"--schema_change_check_interval", "1"}
    75  
    76  		if err := clusterInstance.StartTopo(); err != nil {
    77  			return 1, err
    78  		}
    79  
    80  		// Start keyspace
    81  		keyspace := &cluster.Keyspace{
    82  			Name: keyspaceName,
    83  		}
    84  
    85  		if err := clusterInstance.StartUnshardedKeyspace(*keyspace, 2, true); err != nil {
    86  			return 1, err
    87  		}
    88  		if err := clusterInstance.StartKeyspace(*keyspace, []string{"1"}, 1, false); err != nil {
    89  			return 1, err
    90  		}
    91  		return m.Run(), nil
    92  	}()
    93  	if err != nil {
    94  		fmt.Printf("%v\n", err)
    95  		os.Exit(1)
    96  	} else {
    97  		os.Exit(exitcode)
    98  	}
    99  
   100  }
   101  
   102  func TestSchemaChange(t *testing.T) {
   103  	defer cluster.PanicHandler(t)
   104  	testWithInitialSchema(t)
   105  	testWithAlterSchema(t)
   106  	testWithAlterDatabase(t)
   107  	testWithDropCreateSchema(t)
   108  	testSchemaChangePreflightErrorPartially(t)
   109  	testDropNonExistentTables(t)
   110  	testCreateInvalidView(t)
   111  	testCopySchemaShards(t, clusterInstance.Keyspaces[0].Shards[0].Vttablets[0].VttabletProcess.TabletPath, 2)
   112  	testCopySchemaShards(t, fmt.Sprintf("%s/0", keyspaceName), 3)
   113  	testCopySchemaShardWithDifferentDB(t, 4)
   114  	testWithAutoSchemaFromChangeDir(t)
   115  }
   116  
   117  func testWithInitialSchema(t *testing.T) {
   118  	// Create 4 tables
   119  	var sqlQuery = "" // nolint
   120  	for i := 0; i < totalTableCount; i++ {
   121  		sqlQuery = fmt.Sprintf(createTable, fmt.Sprintf("vt_select_test_%02d", i))
   122  		err := clusterInstance.VtctlclientProcess.ApplySchema(keyspaceName, sqlQuery)
   123  		require.Nil(t, err)
   124  
   125  	}
   126  
   127  	// Check if 4 tables are created
   128  	checkTables(t, totalTableCount)
   129  	checkTables(t, totalTableCount)
   130  
   131  	// Also match the vschema for those tablets
   132  	matchSchema(t, clusterInstance.Keyspaces[0].Shards[0].Vttablets[0].VttabletProcess.TabletPath, clusterInstance.Keyspaces[0].Shards[1].Vttablets[0].VttabletProcess.TabletPath)
   133  }
   134  
   135  // testWithAlterSchema if we alter schema and then apply, the resultant schema should match across shards
   136  func testWithAlterSchema(t *testing.T) {
   137  	sqlQuery := fmt.Sprintf(alterTable, fmt.Sprintf("vt_select_test_%02d", 3), "msg")
   138  	err := clusterInstance.VtctlclientProcess.ApplySchema(keyspaceName, sqlQuery)
   139  	require.Nil(t, err)
   140  	matchSchema(t, clusterInstance.Keyspaces[0].Shards[0].Vttablets[0].VttabletProcess.TabletPath, clusterInstance.Keyspaces[0].Shards[1].Vttablets[0].VttabletProcess.TabletPath)
   141  }
   142  
   143  // testWithAlterDatabase tests that ALTER DATABASE is accepted by the validator.
   144  func testWithAlterDatabase(t *testing.T) {
   145  	sql := "create database alter_database_test; alter database alter_database_test default character set = utf8mb4; drop database alter_database_test"
   146  	err := clusterInstance.VtctlclientProcess.ApplySchema(keyspaceName, sql)
   147  	assert.Nil(t, err)
   148  }
   149  
   150  // testWithDropCreateSchema , we should be able to drop and create same schema
   151  // Tests that a DROP and CREATE table will pass PreflightSchema check.
   152  //
   153  // PreflightSchema checks each SQL statement separately. When doing so, it must
   154  // consider previous statements within the same ApplySchema command. For
   155  // example, a CREATE after DROP must not fail: When CREATE is checked, DROP
   156  // must have been executed first.
   157  // See: https://github.com/vitessio/vitess/issues/1731#issuecomment-222914389
   158  func testWithDropCreateSchema(t *testing.T) {
   159  	dropCreateTable := fmt.Sprintf("DROP TABLE vt_select_test_%02d ;", 2) + fmt.Sprintf(createTable, fmt.Sprintf("vt_select_test_%02d", 2))
   160  	err := clusterInstance.VtctlclientProcess.ApplySchema(keyspaceName, dropCreateTable)
   161  	require.Nil(t, err)
   162  	checkTables(t, totalTableCount)
   163  }
   164  
   165  // testWithAutoSchemaFromChangeDir on putting sql file to schema change directory, it should apply that sql to all shards
   166  func testWithAutoSchemaFromChangeDir(t *testing.T) {
   167  	_ = os.Mkdir(path.Join(schemaChangeDirectory, keyspaceName), 0700)
   168  	_ = os.Mkdir(path.Join(schemaChangeDirectory, keyspaceName, "input"), 0700)
   169  	sqlFile := path.Join(schemaChangeDirectory, keyspaceName, "input/create_test_table_x.sql")
   170  	err := os.WriteFile(sqlFile, []byte("create table test_table_x (id int)"), 0644)
   171  	require.Nil(t, err)
   172  	timeout := time.Now().Add(10 * time.Second)
   173  	matchFoundAfterAutoSchemaApply := false
   174  	for time.Now().Before(timeout) {
   175  		if _, err := os.Stat(sqlFile); os.IsNotExist(err) {
   176  			matchFoundAfterAutoSchemaApply = true
   177  			checkTables(t, totalTableCount+1)
   178  			matchSchema(t, clusterInstance.Keyspaces[0].Shards[0].Vttablets[0].VttabletProcess.TabletPath, clusterInstance.Keyspaces[0].Shards[1].Vttablets[0].VttabletProcess.TabletPath)
   179  		}
   180  	}
   181  	if !matchFoundAfterAutoSchemaApply {
   182  		assert.Fail(t, "Auto schema is not consumed")
   183  	}
   184  	defer os.RemoveAll(path.Join(schemaChangeDirectory, keyspaceName))
   185  }
   186  
   187  // matchSchema schema for supplied tablets should match
   188  func matchSchema(t *testing.T, firstTablet string, secondTablet string) {
   189  	firstShardSchema, err := clusterInstance.VtctlclientProcess.ExecuteCommandWithOutput("GetSchema", firstTablet)
   190  	require.Nil(t, err)
   191  
   192  	secondShardSchema, err := clusterInstance.VtctlclientProcess.ExecuteCommandWithOutput("GetSchema", secondTablet)
   193  	require.Nil(t, err)
   194  
   195  	assert.Equal(t, firstShardSchema, secondShardSchema)
   196  }
   197  
   198  // testSchemaChangePreflightErrorPartially applying same schema + new schema should throw error for existing one
   199  // Tests that some SQL statements fail properly during PreflightSchema.
   200  func testSchemaChangePreflightErrorPartially(t *testing.T) {
   201  	createNewTable := fmt.Sprintf(createTable, fmt.Sprintf("vt_select_test_%02d", 5)) + fmt.Sprintf(createTable, fmt.Sprintf("vt_select_test_%02d", 2))
   202  	output, err := clusterInstance.VtctlclientProcess.ExecuteCommandWithOutput("ApplySchema", "--", "--sql", createNewTable, keyspaceName)
   203  	require.Error(t, err)
   204  	assert.True(t, strings.Contains(output, "already exists"))
   205  
   206  	checkTables(t, totalTableCount)
   207  }
   208  
   209  // testDropNonExistentTables applying same schema + new schema should throw error for existing one and also add the new schema
   210  // If a table does not exist, DROP TABLE should error during preflight
   211  // because the statement does not change the schema as there is
   212  // nothing to drop.
   213  // In case of DROP TABLE IF EXISTS though, it should not error as this
   214  // is the MySQL behavior the user expects.
   215  func testDropNonExistentTables(t *testing.T) {
   216  	dropNonExistentTable := "DROP TABLE nonexistent_table;"
   217  	output, err := clusterInstance.VtctlclientProcess.ExecuteCommandWithOutput("ApplySchema", "--", "--sql", dropNonExistentTable, keyspaceName)
   218  	require.Error(t, err)
   219  	assert.True(t, strings.Contains(output, "Unknown table"))
   220  
   221  	dropIfExists := "DROP TABLE IF EXISTS nonexistent_table;"
   222  	err = clusterInstance.VtctlclientProcess.ApplySchema(keyspaceName, dropIfExists)
   223  	require.Nil(t, err)
   224  
   225  	checkTables(t, totalTableCount)
   226  }
   227  
   228  // testCreateInvalidView attempts to create a view that depends on non-existent table. We expect an error
   229  // we test with different 'direct' strategy options
   230  func testCreateInvalidView(t *testing.T) {
   231  	for _, ddlStrategy := range []string{"direct", "direct -allow-zero-in-date"} {
   232  		createInvalidView := "CREATE OR REPLACE VIEW invalid_view AS SELECT * FROM nonexistent_table;"
   233  		output, err := clusterInstance.VtctlclientProcess.ExecuteCommandWithOutput("ApplySchema", "--", "--skip_preflight", "--ddl_strategy", ddlStrategy, "--sql", createInvalidView, keyspaceName)
   234  		require.Error(t, err)
   235  		assert.Contains(t, output, "doesn't exist (errno 1146)")
   236  	}
   237  }
   238  
   239  // checkTables checks the number of tables in the first two shards.
   240  func checkTables(t *testing.T, count int) {
   241  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[0].Vttablets[0], count)
   242  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[1].Vttablets[0], count)
   243  }
   244  
   245  // checkTablesCount checks the number of tables in the given tablet
   246  func checkTablesCount(t *testing.T, tablet *cluster.Vttablet, count int) {
   247  	queryResult, err := tablet.VttabletProcess.QueryTablet("show tables;", keyspaceName, true)
   248  	require.Nil(t, err)
   249  	assert.Equal(t, len(queryResult.Rows), count)
   250  }
   251  
   252  // testCopySchemaShards tests that schema from source is correctly applied to destination
   253  func testCopySchemaShards(t *testing.T, source string, shard int) {
   254  	addNewShard(t, shard)
   255  	// InitShardPrimary creates the db, but there shouldn't be any tables yet.
   256  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[0], 0)
   257  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[1], 0)
   258  	// Run the command twice to make sure it's idempotent.
   259  	for i := 0; i < 2; i++ {
   260  		err := clusterInstance.VtctlclientProcess.ExecuteCommand("CopySchemaShard", source, fmt.Sprintf("%s/%d", keyspaceName, shard))
   261  		require.Nil(t, err)
   262  	}
   263  	// shard2 primary should look the same as the replica we copied from
   264  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[0], totalTableCount)
   265  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[1], totalTableCount)
   266  
   267  	matchSchema(t, clusterInstance.Keyspaces[0].Shards[0].Vttablets[0].VttabletProcess.TabletPath, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[0].VttabletProcess.TabletPath)
   268  }
   269  
   270  // testCopySchemaShardWithDifferentDB if we apply different schema to new shard, it should throw error
   271  func testCopySchemaShardWithDifferentDB(t *testing.T, shard int) {
   272  	addNewShard(t, shard)
   273  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[0], 0)
   274  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[1], 0)
   275  	source := fmt.Sprintf("%s/0", keyspaceName)
   276  
   277  	tabletAlias := clusterInstance.Keyspaces[0].Shards[shard].Vttablets[0].VttabletProcess.TabletPath
   278  	schema, err := clusterInstance.VtctlclientProcess.ExecuteCommandWithOutput("GetSchema", tabletAlias)
   279  	require.Nil(t, err)
   280  
   281  	resultMap := make(map[string]any)
   282  	err = json.Unmarshal([]byte(schema), &resultMap)
   283  	require.Nil(t, err)
   284  	dbSchema := reflect.ValueOf(resultMap["database_schema"])
   285  	assert.True(t, strings.Contains(dbSchema.String(), "utf8"))
   286  
   287  	// Change the db charset on the destination shard from utf8 to latin1.
   288  	// This will make CopySchemaShard fail during its final diff.
   289  	// (The different charset won't be corrected on the destination shard
   290  	//  because we use "CREATE DATABASE IF NOT EXISTS" and this doesn't fail if
   291  	//  there are differences in the options e.g. the character set.)
   292  	err = clusterInstance.VtctlclientProcess.ExecuteCommand("ExecuteFetchAsDba", "--", "--json", tabletAlias, "ALTER DATABASE vt_ks CHARACTER SET latin1")
   293  	require.Nil(t, err)
   294  
   295  	output, err := clusterInstance.VtctlclientProcess.ExecuteCommandWithOutput("CopySchemaShard", source, fmt.Sprintf("%s/%d", keyspaceName, shard))
   296  	require.Error(t, err)
   297  	assert.True(t, strings.Contains(output, "schemas are different"))
   298  
   299  	// shard2 primary should have the same number of tables. Only the db
   300  	// character set is different.
   301  	checkTablesCount(t, clusterInstance.Keyspaces[0].Shards[shard].Vttablets[0], totalTableCount)
   302  }
   303  
   304  // addNewShard adds a new shard dynamically
   305  func addNewShard(t *testing.T, shard int) {
   306  	keyspace := &cluster.Keyspace{
   307  		Name: keyspaceName,
   308  	}
   309  	err := clusterInstance.StartKeyspace(*keyspace, []string{fmt.Sprintf("%d", shard)}, 1, false)
   310  	require.Nil(t, err)
   311  }