github.com/pingcap/tidb-lightning@v5.0.0-rc.0.20210428090220-84b649866577+incompatible/lightning/backend/tidb_test.go

// Copyright 2019 PingCAP, Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// See the License for the specific language governing permissions and
// limitations under the License.

package backend_test

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/pingcap/parser/charset"

	"github.com/DATA-DOG/go-sqlmock"
	. "github.com/pingcap/check"
	"github.com/pingcap/parser/model"
	"github.com/pingcap/parser/mysql"
	"github.com/pingcap/tidb/table"
	"github.com/pingcap/tidb/table/tables"
	"github.com/pingcap/tidb/types"

	kv "github.com/pingcap/tidb-lightning/lightning/backend"
	"github.com/pingcap/tidb-lightning/lightning/config"
	"github.com/pingcap/tidb-lightning/lightning/log"
	"github.com/pingcap/tidb-lightning/lightning/verification"
)

var _ = Suite(&mysqlSuite{})

type mysqlSuite struct {
	dbHandle *sql.DB
	mockDB   sqlmock.Sqlmock
	backend  kv.Backend
	tbl      table.Table
}

func (s *mysqlSuite) SetUpTest(c *C) {
	db, mock, err := sqlmock.New()
	c.Assert(err, IsNil)

	tys := []byte{mysql.TypeLong, mysql.TypeLong, mysql.TypeTiny, mysql.TypeInt24, mysql.TypeFloat, mysql.TypeDouble,
		mysql.TypeDouble, mysql.TypeDouble, mysql.TypeVarchar, mysql.TypeBlob, mysql.TypeBit, mysql.TypeNewDecimal, mysql.TypeEnum}
	cols := make([]*model.ColumnInfo, 0, len(tys))
	for i, ty := range tys {
		col := &model.ColumnInfo{ID: int64(i + 1), Name: model.NewCIStr(fmt.Sprintf("c%d", i)), State: model.StatePublic, Offset: i, FieldType: *types.NewFieldType(ty)}
		cols = append(cols, col)
	}
	tblInfo := &model.TableInfo{ID: 1, Columns: cols, PKIsHandle: false, State: model.StatePublic}
	tbl, err := tables.TableFromMeta(kv.NewPanickingAllocators(0), tblInfo)
	c.Assert(err, IsNil)

	s.dbHandle = db
	s.mockDB = mock
	s.backend = kv.NewTiDBBackend(db, config.ReplaceOnDup)
	s.tbl = tbl
}

func (s *mysqlSuite) TearDownTest(c *C) {
	s.backend.Close()
	c.Assert(s.mockDB.ExpectationsWereMet(), IsNil)
}

func (s *mysqlSuite) TestWriteRowsReplaceOnDup(c *C) {
	s.mockDB.
		ExpectExec("\\QREPLACE INTO `foo`.`bar`(`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`i`,`j`,`k`,`l`,`m`,`n`,`o`) VALUES(18446744073709551615,-9223372036854775808,0,NULL,7.5,5e-324,1.7976931348623157e+308,0,'甲乙丙\\r\\n\\0\\Z''\"\\\\`',x'000000abcdef',2557891634,'12.5',51)\\E").
		WillReturnResult(sqlmock.NewResult(1, 1))

	ctx := context.Background()
	logger := log.L()

	engine, err := s.backend.OpenEngine(ctx, "`foo`.`bar`", 1)
	c.Assert(err, IsNil)

	dataRows := s.backend.MakeEmptyRows()
	dataChecksum := verification.MakeKVChecksum(0, 0, 0)
	indexRows := s.backend.MakeEmptyRows()
	indexChecksum := verification.MakeKVChecksum(0, 0, 0)

	cols := s.tbl.Cols()
	perms := make([]int, 0, len(s.tbl.Cols())+1)
	for i := 0; i < len(cols); i++ {
		perms = append(perms, i)
	}
	perms = append(perms, -1)
	encoder, err := s.backend.NewEncoder(s.tbl, &kv.SessionOptions{SQLMode: 0, Timestamp: 1234567890})
	c.Assert(err, IsNil)
	row, err := encoder.Encode(logger, []types.Datum{
		types.NewUintDatum(18446744073709551615),
		types.NewIntDatum(-9223372036854775808),
		types.NewUintDatum(0),
		{},
		types.NewFloat32Datum(7.5),
		types.NewFloat64Datum(5e-324),
		types.NewFloat64Datum(1.7976931348623157e+308),
		types.NewFloat64Datum(-0.0),
		types.NewStringDatum("甲乙丙\r\n\x00\x1a'\"\\`"),
		types.NewBinaryLiteralDatum(types.NewBinaryLiteralFromUint(0xabcdef, 6)),
		types.NewMysqlBitDatum(types.NewBinaryLiteralFromUint(0x98765432, 4)),
		types.NewDecimalDatum(types.NewDecFromFloatForTest(12.5)),
		types.NewMysqlEnumDatum(types.Enum{Name: "ENUM_NAME", Value: 51}),
	}, 1, perms)
	c.Assert(err, IsNil)
	row.ClassifyAndAppend(&dataRows, &dataChecksum, &indexRows, &indexChecksum)

	err = engine.WriteRows(ctx, []string{"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}, dataRows)
	c.Assert(err, IsNil)
}

func (s *mysqlSuite) TestWriteRowsIgnoreOnDup(c *C) {
	s.mockDB.
		ExpectExec("\\QINSERT IGNORE INTO `foo`.`bar`(`a`) VALUES(1)\\E").
		WillReturnResult(sqlmock.NewResult(1, 1))

	ctx := context.Background()
	logger := log.L()

	ignoreBackend := kv.NewTiDBBackend(s.dbHandle, config.IgnoreOnDup)
	engine, err := ignoreBackend.OpenEngine(ctx, "`foo`.`bar`", 1)
	c.Assert(err, IsNil)

	dataRows := ignoreBackend.MakeEmptyRows()
	dataChecksum := verification.MakeKVChecksum(0, 0, 0)
	indexRows := ignoreBackend.MakeEmptyRows()
	indexChecksum := verification.MakeKVChecksum(0, 0, 0)

	encoder, err := ignoreBackend.NewEncoder(s.tbl, &kv.SessionOptions{})
	c.Assert(err, IsNil)
	row, err := encoder.Encode(logger, []types.Datum{
		types.NewIntDatum(1),
	}, 1, []int{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, -1})
	c.Assert(err, IsNil)
	row.ClassifyAndAppend(&dataRows, &dataChecksum, &indexRows, &indexChecksum)

	err = engine.WriteRows(ctx, []string{"a"}, dataRows)
	c.Assert(err, IsNil)

	// test encode rows with _tidb_rowid
	encoder, err = ignoreBackend.NewEncoder(s.tbl, &kv.SessionOptions{})
	c.Assert(err, IsNil)
	row, err = encoder.Encode(logger, []types.Datum{
		types.NewIntDatum(1),
		types.NewIntDatum(1), // _tidb_rowid field
	}, 1, []int{0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, 1})
	c.Assert(err, IsNil)
}

func (s *mysqlSuite) TestWriteRowsErrorOnDup(c *C) {
	s.mockDB.
		ExpectExec("\\QINSERT INTO `foo`.`bar`(`a`) VALUES(1)\\E").
		WillReturnResult(sqlmock.NewResult(1, 1))

	ctx := context.Background()
	logger := log.L()

	ignoreBackend := kv.NewTiDBBackend(s.dbHandle, config.ErrorOnDup)
	engine, err := ignoreBackend.OpenEngine(ctx, "`foo`.`bar`", 1)
	c.Assert(err, IsNil)

	dataRows := ignoreBackend.MakeEmptyRows()
	dataChecksum := verification.MakeKVChecksum(0, 0, 0)
	indexRows := ignoreBackend.MakeEmptyRows()
	indexChecksum := verification.MakeKVChecksum(0, 0, 0)

	encoder, err := ignoreBackend.NewEncoder(s.tbl, &kv.SessionOptions{})
	c.Assert(err, IsNil)
	row, err := encoder.Encode(logger, []types.Datum{
		types.NewIntDatum(1),
	}, 1, []int{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, -1})
	c.Assert(err, IsNil)

	row.ClassifyAndAppend(&dataRows, &dataChecksum, &indexRows, &indexChecksum)

	err = engine.WriteRows(ctx, []string{"a"}, dataRows)
	c.Assert(err, IsNil)
}

// TODO: temporarily disable this test before we fix strict mode
func (s *mysqlSuite) testStrictMode(c *C) {
	ft := *types.NewFieldType(mysql.TypeVarchar)
	ft.Charset = charset.CharsetUTF8MB4
	col0 := &model.ColumnInfo{ID: 1, Name: model.NewCIStr("s0"), State: model.StatePublic, Offset: 0, FieldType: ft}
	ft = *types.NewFieldType(mysql.TypeString)
	ft.Charset = charset.CharsetASCII
	col1 := &model.ColumnInfo{ID: 2, Name: model.NewCIStr("s1"), State: model.StatePublic, Offset: 1, FieldType: ft}
	tblInfo := &model.TableInfo{ID: 1, Columns: []*model.ColumnInfo{col0, col1}, PKIsHandle: false, State: model.StatePublic}
	tbl, err := tables.TableFromMeta(kv.NewPanickingAllocators(0), tblInfo)
	c.Assert(err, IsNil)

	bk := kv.NewTiDBBackend(s.dbHandle, config.ErrorOnDup)
	encoder, err := bk.NewEncoder(tbl, &kv.SessionOptions{SQLMode: mysql.ModeStrictAllTables})
	c.Assert(err, IsNil)

	logger := log.L()
	_, err = encoder.Encode(logger, []types.Datum{
		types.NewStringDatum("test"),
	}, 1, []int{0, -1, -1})
	c.Assert(err, IsNil)

	_, err = encoder.Encode(logger, []types.Datum{
		types.NewStringDatum("\xff\xff\xff\xff"),
	}, 1, []int{0, -1, -1})
	c.Assert(err, ErrorMatches, `.*incorrect utf8 value .* for column s0`)

	// oepn a new encode because column count changed.
	encoder, err = bk.NewEncoder(tbl, &kv.SessionOptions{SQLMode: mysql.ModeStrictAllTables})
	c.Assert(err, IsNil)
	_, err = encoder.Encode(logger, []types.Datum{
		types.NewStringDatum(""),
		types.NewStringDatum("非 ASCII 字符串"),
	}, 1, []int{0, 1, -1})
	c.Assert(err, ErrorMatches, ".*incorrect ascii value .* for column s1")
}

func (s *mysqlSuite) TestFetchRemoteTableModels_3_x(c *C) {
	s.mockDB.ExpectBegin()
	s.mockDB.ExpectQuery("SELECT version()").
		WillReturnRows(sqlmock.NewRows([]string{"version()"}).AddRow("5.7.25-TiDB-v3.0.18"))
	s.mockDB.ExpectQuery("\\QSELECT table_name, column_name, column_type, extra FROM information_schema.columns WHERE table_schema = ? ORDER BY table_name, ordinal_position;\\E").
		WithArgs("test").
		WillReturnRows(sqlmock.NewRows([]string{"table_name", "column_name", "column_type", "extra"}).
			AddRow("t", "id", "int(10)", "auto_increment"))
	s.mockDB.ExpectCommit()

	bk := kv.NewTiDBBackend(s.dbHandle, config.ErrorOnDup)
	tableInfos, err := bk.FetchRemoteTableModels(context.Background(), "test")
	c.Assert(err, IsNil)
	c.Assert(tableInfos, DeepEquals, []*model.TableInfo{
		{
			Name:       model.NewCIStr("t"),
			State:      model.StatePublic,
			PKIsHandle: true,
			Columns: []*model.ColumnInfo{
				{
					Name:   model.NewCIStr("id"),
					Offset: 0,
					State:  model.StatePublic,
					FieldType: types.FieldType{
						Flag: mysql.AutoIncrementFlag,
					},
				},
			},
		},
	})
}

func (s *mysqlSuite) TestFetchRemoteTableModels_4_0(c *C) {
	s.mockDB.ExpectBegin()
	s.mockDB.ExpectQuery("SELECT version()").
		WillReturnRows(sqlmock.NewRows([]string{"version()"}).AddRow("5.7.25-TiDB-v4.0.0"))
	s.mockDB.ExpectQuery("\\QSELECT table_name, column_name, column_type, extra FROM information_schema.columns WHERE table_schema = ? ORDER BY table_name, ordinal_position;\\E").
		WithArgs("test").
		WillReturnRows(sqlmock.NewRows([]string{"table_name", "column_name", "column_type", "extra"}).
			AddRow("t", "id", "bigint(20) unsigned", "auto_increment"))
	s.mockDB.ExpectQuery("SHOW TABLE `test`.`t` NEXT_ROW_ID").
		WillReturnRows(sqlmock.NewRows([]string{"DB_NAME", "TABLE_NAME", "COLUMN_NAME", "NEXT_GLOBAL_ROW_ID"}).
			AddRow("test", "t", "id", int64(1)))
	s.mockDB.ExpectCommit()

	bk := kv.NewTiDBBackend(s.dbHandle, config.ErrorOnDup)
	tableInfos, err := bk.FetchRemoteTableModels(context.Background(), "test")
	c.Assert(err, IsNil)
	c.Assert(tableInfos, DeepEquals, []*model.TableInfo{
		{
			Name:       model.NewCIStr("t"),
			State:      model.StatePublic,
			PKIsHandle: true,
			Columns: []*model.ColumnInfo{
				{
					Name:   model.NewCIStr("id"),
					Offset: 0,
					State:  model.StatePublic,
					FieldType: types.FieldType{
						Flag: mysql.AutoIncrementFlag | mysql.UnsignedFlag,
					},
				},
			},
		},
	})
}

func (s *mysqlSuite) TestFetchRemoteTableModels_4_x_auto_increment(c *C) {
	s.mockDB.ExpectBegin()
	s.mockDB.ExpectQuery("SELECT version()").
		WillReturnRows(sqlmock.NewRows([]string{"version()"}).AddRow("5.7.25-TiDB-v4.0.7"))
	s.mockDB.ExpectQuery("\\QSELECT table_name, column_name, column_type, extra FROM information_schema.columns WHERE table_schema = ? ORDER BY table_name, ordinal_position;\\E").
		WithArgs("test").
		WillReturnRows(sqlmock.NewRows([]string{"table_name", "column_name", "column_type", "extra"}).
			AddRow("t", "id", "bigint(20)", ""))
	s.mockDB.ExpectQuery("SHOW TABLE `test`.`t` NEXT_ROW_ID").
		WillReturnRows(sqlmock.NewRows([]string{"DB_NAME", "TABLE_NAME", "COLUMN_NAME", "NEXT_GLOBAL_ROW_ID", "ID_TYPE"}).
			AddRow("test", "t", "id", int64(1), "AUTO_INCREMENT"))
	s.mockDB.ExpectCommit()

	bk := kv.NewTiDBBackend(s.dbHandle, config.ErrorOnDup)
	tableInfos, err := bk.FetchRemoteTableModels(context.Background(), "test")
	c.Assert(err, IsNil)
	c.Assert(tableInfos, DeepEquals, []*model.TableInfo{
		{
			Name:       model.NewCIStr("t"),
			State:      model.StatePublic,
			PKIsHandle: true,
			Columns: []*model.ColumnInfo{
				{
					Name:   model.NewCIStr("id"),
					Offset: 0,
					State:  model.StatePublic,
					FieldType: types.FieldType{
						Flag: mysql.AutoIncrementFlag,
					},
				},
			},
		},
	})
}

func (s *mysqlSuite) TestFetchRemoteTableModels_4_x_auto_random(c *C) {
	s.mockDB.ExpectBegin()
	s.mockDB.ExpectQuery("SELECT version()").
		WillReturnRows(sqlmock.NewRows([]string{"version()"}).AddRow("5.7.25-TiDB-v4.0.7"))
	s.mockDB.ExpectQuery("\\QSELECT table_name, column_name, column_type, extra FROM information_schema.columns WHERE table_schema = ? ORDER BY table_name, ordinal_position;\\E").
		WithArgs("test").
		WillReturnRows(sqlmock.NewRows([]string{"table_name", "column_name", "column_type", "extra"}).
			AddRow("t", "id", "bigint(20)", ""))
	s.mockDB.ExpectQuery("SHOW TABLE `test`.`t` NEXT_ROW_ID").
		WillReturnRows(sqlmock.NewRows([]string{"DB_NAME", "TABLE_NAME", "COLUMN_NAME", "NEXT_GLOBAL_ROW_ID", "ID_TYPE"}).
			AddRow("test", "t", "id", int64(1), "AUTO_RANDOM"))
	s.mockDB.ExpectCommit()

	bk := kv.NewTiDBBackend(s.dbHandle, config.ErrorOnDup)
	tableInfos, err := bk.FetchRemoteTableModels(context.Background(), "test")
	c.Assert(err, IsNil)
	c.Assert(tableInfos, DeepEquals, []*model.TableInfo{
		{
			Name:           model.NewCIStr("t"),
			State:          model.StatePublic,
			PKIsHandle:     true,
			AutoRandomBits: 1,
			Columns: []*model.ColumnInfo{
				{
					Name:   model.NewCIStr("id"),
					Offset: 0,
					State:  model.StatePublic,
					FieldType: types.FieldType{
						Flag: mysql.PriKeyFlag,
					},
				},
			},
		},
	})
}