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 }