vitess.io/vitess@v0.16.2/go/test/endtoend/mysqlserver/mysql_server_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  R442
    16  */
    17  
    18  package mysqlserver
    19  
    20  import (
    21  	"context"
    22  	"fmt"
    23  	"io"
    24  	"net/http"
    25  	"os"
    26  	"strconv"
    27  	"strings"
    28  	"testing"
    29  
    30  	"github.com/icrowley/fake"
    31  	"github.com/stretchr/testify/assert"
    32  	"github.com/stretchr/testify/require"
    33  
    34  	"vitess.io/vitess/go/mysql"
    35  	"vitess.io/vitess/go/test/endtoend/cluster"
    36  
    37  	"database/sql"
    38  
    39  	_ "github.com/go-sql-driver/mysql"
    40  )
    41  
    42  // TestMultiStmt checks that multiStatements=True and multiStatements=False work properly.
    43  func TestMultiStatement(t *testing.T) {
    44  	defer cluster.PanicHandler(t)
    45  	ctx := context.Background()
    46  
    47  	// connect database with multiStatements=True
    48  	db := connectDB(t, vtParams, "multiStatements=True", "timeout=90s", "collation=utf8mb4_unicode_ci")
    49  
    50  	rows, err := db.QueryContext(ctx, "SELECT 1; SELECT 2; SELECT 3")
    51  	require.Nilf(t, err, "multiple statements should be executed without error, got %v", err)
    52  	var count int
    53  	for rows.Next() || (rows.NextResultSet() && rows.Next()) {
    54  		var i int
    55  		rows.Scan(&i)
    56  		count++
    57  		assert.Equalf(t, count, i, "result of query %v query should be %v, got %v", count, count, i)
    58  	}
    59  	assert.Equalf(t, 3, count, "this query should affect 3 row, got %v", count)
    60  	db.Close()
    61  
    62  	// connect database with multiStatements=False
    63  	db = connectDB(t, vtParams, "multiStatements=False", "timeout=90s", "collation=utf8mb4_unicode_ci")
    64  
    65  	_, err = db.QueryContext(ctx, "SELECT 1; SELECT 2; SELECT 3")
    66  	require.NotNilf(t, err, "error expected, got nil error")
    67  	assert.Containsf(t, err.Error(), "syntax error", "expected syntax error, got %v", err)
    68  }
    69  
    70  // TestLargeComment add large comment in insert stmt and validate the insert process.
    71  func TestLargeComment(t *testing.T) {
    72  	defer cluster.PanicHandler(t)
    73  	ctx := context.Background()
    74  
    75  	conn, err := mysql.Connect(ctx, &vtParams)
    76  	require.Nilf(t, err, "unable to connect mysql: %v", err)
    77  	defer conn.Close()
    78  
    79  	// insert data with large comment
    80  	_, err = conn.ExecuteFetch("insert into vt_insert_test (id, msg, keyspace_id, data) values(1, 'large blob', 123, 'LLL') /* "+fake.CharactersN(4*1024*1024)+" */", 1, false)
    81  	require.Nilf(t, err, "insertion error: %v", err)
    82  
    83  	qr, err := conn.ExecuteFetch("select * from vt_insert_test where id = 1", 1, false)
    84  	require.Nilf(t, err, "select error: %v", err)
    85  	assert.Equal(t, 1, len(qr.Rows))
    86  	assert.Equal(t, "BLOB(\"LLL\")", qr.Rows[0][3].String())
    87  }
    88  
    89  // TestInsertLargerThenGrpcLimit insert blob larger then grpc limit and verify the error.
    90  func TestInsertLargerThenGrpcLimit(t *testing.T) {
    91  	defer cluster.PanicHandler(t)
    92  
    93  	ctx := context.Background()
    94  
    95  	conn, err := mysql.Connect(ctx, &vtParams)
    96  	require.Nilf(t, err, "unable to connect mysql: %v", err)
    97  	defer conn.Close()
    98  
    99  	grpcLimit := os.Getenv("grpc_max_message_size")
   100  	limit, err := strconv.Atoi(grpcLimit)
   101  	require.Nilf(t, err, "int parsing error: %v", err)
   102  
   103  	// insert data with large blob
   104  	_, err = conn.ExecuteFetch("insert into vt_insert_test (id, msg, keyspace_id, data) values(2, 'huge blob', 123, '"+fake.CharactersN(limit+1)+"')", 1, false)
   105  	require.NotNil(t, err, "error expected on insert")
   106  	assert.Contains(t, err.Error(), "trying to send message larger than max")
   107  }
   108  
   109  // TestTimeout executes sleep(5) with query_timeout of 1 second, and verifies the error.
   110  func TestTimeout(t *testing.T) {
   111  	defer cluster.PanicHandler(t)
   112  	ctx := context.Background()
   113  
   114  	conn, err := mysql.Connect(ctx, &vtParams)
   115  	require.Nilf(t, err, "unable to connect mysql: %v", err)
   116  	defer conn.Close()
   117  
   118  	_, err = conn.ExecuteFetch("SELECT SLEEP(5);", 1, false)
   119  	require.NotNilf(t, err, "quiry timeout error expected")
   120  	mysqlErr, ok := err.(*mysql.SQLError)
   121  	require.Truef(t, ok, "invalid error type")
   122  	assert.Equal(t, 1317, mysqlErr.Number(), err)
   123  }
   124  
   125  // TestInvalidField tries to fetch invalid column and verifies the error.
   126  func TestInvalidField(t *testing.T) {
   127  	defer cluster.PanicHandler(t)
   128  	ctx := context.Background()
   129  
   130  	conn, err := mysql.Connect(ctx, &vtParams)
   131  	require.Nilf(t, err, "unable to connect mysql: %v", err)
   132  	defer conn.Close()
   133  
   134  	_, err = conn.ExecuteFetch("SELECT invalid_field from vt_insert_test;", 1, false)
   135  	require.NotNil(t, err, "invalid field error expected")
   136  	mysqlErr, ok := err.(*mysql.SQLError)
   137  	require.Truef(t, ok, "invalid error type")
   138  	assert.Equal(t, 1054, mysqlErr.Number(), err)
   139  }
   140  
   141  // TestWarnings validates the behaviour of SHOW WARNINGS.
   142  func TestWarnings(t *testing.T) {
   143  	defer cluster.PanicHandler(t)
   144  	ctx := context.Background()
   145  
   146  	conn, err := mysql.Connect(ctx, &vtParams)
   147  	require.NoError(t, err)
   148  	defer conn.Close()
   149  
   150  	// using CALL will produce a warning saying this only works in unsharded
   151  	qr, err := conn.ExecuteFetch("CALL testing()", 1, false)
   152  	require.NoError(t, err)
   153  	assert.Empty(t, qr.Rows, "number of rows")
   154  
   155  	qr, err = conn.ExecuteFetch("SHOW WARNINGS;", 1, false)
   156  	require.NoError(t, err, "SHOW WARNINGS")
   157  	assert.EqualValues(t, 1, len(qr.Rows), "number of rows")
   158  	assert.Contains(t, qr.Rows[0][0].String(), "VARCHAR(\"Warning\")", qr.Rows)
   159  	assert.Contains(t, qr.Rows[0][1].String(), "UINT16(1235)", qr.Rows)
   160  	assert.Contains(t, qr.Rows[0][2].String(), "'CALL' not supported in sharded mode", qr.Rows)
   161  
   162  	// validate with 0 warnings
   163  	_, err = conn.ExecuteFetch("SELECT 1 from vt_insert_test limit 1", 1, false)
   164  	require.NoError(t, err)
   165  
   166  	qr, err = conn.ExecuteFetch("SHOW WARNINGS;", 1, false)
   167  	require.NoError(t, err)
   168  	assert.Empty(t, qr.Rows)
   169  
   170  	// verify that show warnings are empty if another statement is run before calling it
   171  	qr, err = conn.ExecuteFetch("CALL testing()", 1, false)
   172  	require.NoError(t, err)
   173  	assert.Empty(t, qr.Rows, "number of rows")
   174  	_, err = conn.ExecuteFetch("SELECT 1 from vt_insert_test limit 1", 1, false)
   175  	require.NoError(t, err)
   176  
   177  	qr, err = conn.ExecuteFetch("SHOW WARNINGS;", 1, false)
   178  	require.NoError(t, err)
   179  	assert.Empty(t, qr.Rows)
   180  }
   181  
   182  // TestSelectWithUnauthorizedUser verifies that an unauthorized user
   183  // is not able to read from the table.
   184  func TestSelectWithUnauthorizedUser(t *testing.T) {
   185  	defer cluster.PanicHandler(t)
   186  	ctx := context.Background()
   187  
   188  	tmpVtParam := vtParams
   189  	tmpVtParam.Uname = "testuser2"
   190  	tmpVtParam.Pass = "testpassword2"
   191  
   192  	conn, err := mysql.Connect(ctx, &tmpVtParam)
   193  	require.Nilf(t, err, "unable to connect to mysql: %v", err)
   194  	defer conn.Close()
   195  
   196  	_, err = conn.ExecuteFetch("SELECT * from vt_insert_test limit 1", 1, false)
   197  	require.NotNilf(t, err, "error expected, got nil")
   198  	assert.Contains(t, err.Error(), "Select command denied to user")
   199  	assert.Contains(t, err.Error(), "for table 'vt_insert_test' (ACL check error)")
   200  }
   201  
   202  // TestPartitionedTable validates that partitioned tables are recognized by schema engine
   203  func TestPartitionedTable(t *testing.T) {
   204  	defer cluster.PanicHandler(t)
   205  
   206  	tablet := clusterInstance.Keyspaces[0].Shards[0].PrimaryTablet()
   207  
   208  	// Partitioned table already created, check if vttablet knows about it
   209  	url := fmt.Sprintf("http://localhost:%d/schemaz", tablet.HTTPPort)
   210  	resp, err := http.Get(url)
   211  	require.NoError(t, err)
   212  
   213  	body, err := io.ReadAll(resp.Body)
   214  	require.NoError(t, err)
   215  	defer resp.Body.Close()
   216  
   217  	assert.Contains(t, string(body), "vt_partition_test")
   218  }
   219  
   220  func connectDB(t *testing.T, vtParams mysql.ConnParams, params ...string) *sql.DB {
   221  	connectionStr := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?%s", vtParams.Uname, vtParams.Pass, vtParams.Host, vtParams.Port, keyspaceName, strings.Join(params, "&"))
   222  	db, err := sql.Open("mysql", connectionStr)
   223  	require.Nil(t, err)
   224  	return db
   225  }
   226  
   227  // createConfig create file in to Tmp dir in vtdataroot and write the given data.
   228  func createConfig(name, data string) error {
   229  	// creating new file
   230  	f, err := os.Create(clusterInstance.TmpDirectory + name)
   231  	if err != nil {
   232  		return err
   233  	}
   234  
   235  	if data == "" {
   236  		return nil
   237  	}
   238  
   239  	// write the given data
   240  	_, err = fmt.Fprint(f, data)
   241  	return err
   242  }