Beispiel #1
0
// build a new shard DB after more the limit
// or no any one shard DB.
func buildNewShardDB(grp *host.Group) (*MysqlShardDB, error) {
	mut.Lock()
	defer mut.Unlock()

	shardDBName := "shard" + strconv.Itoa(ShardDBCnt+1)

	// to check this new shard db has been exists.
	isExists := IsExistsShardDB(shardDBName)
	if isExists != nil {
		return isExists, nil
	}

	newShardDBId := redis.BuildPrimaryKey(shardDBName, true)

	shardDB := &MysqlShardDB{
		Id:          newShardDBId,
		Name:        shardDBName,
		TableTotal:  uint64(0),
		SizeTotal:   uint64(0),
		HostGroupId: grp.Id,
		Created:     time.Now().Unix(),
		HostGroup:   grp,
	}

	// create the database to host.
	//
	master := host.GetBetterHost(grp.Master, "master")
	db, err := (&master).ConnToDB("mysql")
	if err != nil {
		return nil, err
	}

	stmt, err := db.Prepare(fmt.Sprintf("CREATE DATABASE `%s` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci", shardDBName))
	if err != nil {
		return nil, err
	}

	_, err = stmt.Exec()
	if err != nil {
		return nil, err
	}
	stmt.Close()
	db.Close()

	// to write the new shard db to redis
	//
	err = redis.WriteDB(newShardDBId, redis.EncodeData(shardDB), "MysqlShardDB")
	if err != nil {
		return nil, err
	}

	// notice Mysql Project object to add a new shard db memery.
	NewShardDBCh <- shardDB

	return shardDB, nil
}
Beispiel #2
0
// get the better slave host db connection by shard tables.
//
func (tbl *MysqlTable) GetSlaveShardDBConn(shardTabOrderId int) (*sql.DB, error) {

	if len(tbl.Shards) == 0 {
		return nil, errors.New("No any one shard table")
	}

	shardDB := tbl.Shards[shardTabOrderId].ShardDB

	h := host.GetBetterHost(shardDB.HostGroup.Slave, "slave")

	if h == nil {
		return nil, errors.New("no found out any one valid host")
	}

	db, err := h.ConnToDB(shardDB.Name)
	if err != nil {
		return nil, err
	}

	return db, err
}
Beispiel #3
0
func (d *DDL) alterOnAllShard(alterType string) (interface{}, *schema.MysqlTable, error) {
	// to found out the alter table's host.
	//var err error
	var shardTables []*schema.MysqlShardTable
	var curTable *schema.MysqlTable
	for i := 0; i < len(schema.Tables); i++ {
		if schema.Tables[i].Name == d.TableName {
			curTable = schema.Tables[i]
			shardTables = curTable.Shards
			break
		}
	}
	if shardTables == nil {
		return nil, nil, errors.New("no found any one this table " + d.TableName)
	}

	// when alter table name, then need change MysqlProxyTable log
	resultCh := make(chan *alterResult, runtime.NumCPU())

	shardTBLen := len(shardTables)

	// to do the sql at the same time.
	for i := 0; i < shardTBLen; i++ {
		go func(i int) {
			stb := shardTables[i]

			conn, err := host.GetBetterHost(stb.ShardDB.HostGroup.Master, "master").ConnToDB(stb.ShardDB.Name)
			if err != nil && i == 0 {
				conn.Close()
				resultCh <- &alterResult{Res: nil, Err: err}
				return
			}

			trueSql := ""
			if alterType == "renameddl" {
				oldSTbName := stb.Name
				stb.Name = strings.Replace(stb.Name, curTable.Name, d.NewName, -1)
				trueSql = "alter table " + oldSTbName + " rename " + stb.Name
				stb.UpdateToRedisDB()
			} else if alterType == "dropddl" {
				oldSTbName := stb.Name
				stb.Name = strings.Replace(stb.Name, curTable.Name, d.NewName, -1)
				trueSql = "drop table " + oldSTbName
			} else {
				trueSql = strings.Replace(d.Sql, d.TableName, stb.Name, -1)
			}
			stmt, err := conn.Prepare(trueSql)
			if err != nil {
				conn.Close()
				resultCh <- &alterResult{Res: nil, Err: err}
				return
			}

			res, err := stmt.Exec()
			if err != nil {
				conn.Close()
				resultCh <- &alterResult{Res: nil, Err: err}
				return
			}

			stmt.Close()
			conn.Close()

			if i == 0 {
				resultCh <- &alterResult{Res: res, Err: nil}
				return
			}
		}(i)
	}

	res := <-resultCh

	if res.Err != nil {
		return nil, nil, res.Err
	}

	result, err := res.Res.RowsAffected()

	return result, curTable, err
}
Beispiel #4
0
// restore the schema ddl.
func (tbl *MysqlTable) BuildNewShardTable() (*MysqlShardTable, error) {
	curSharded := len(tbl.Shards)
	tTableName := tbl.Name + "_shard" + strconv.Itoa(curSharded+1)

	newTabId := redis.BuildPrimaryKey(tTableName, true)

	shardTable := &MysqlShardTable{
		Id:        newTabId,
		Name:      tTableName,
		RowTotal:  uint64(0),
		ShardDBId: "",
		Created:   time.Now().Unix(),
		ShardDB:   nil,
	}

	// get bettet group.
	group := host.GetBetterMasterGroup()

	shardDB, err := tbl.GetMasterShardDBByGroup(group)
	if err != nil {
		return nil, err
	}

	shardTable.ShardDBId = shardDB.Id
	shardTable.ShardDB = shardDB

	betterHost := host.GetBetterHost(group.Master, "master")

	ddlSql, err := tbl.GetSchemaDDLByDb()
	if err != nil {
		return nil, err
	}

	ddlSql = strings.Replace(ddlSql, tbl.Shards[0].Name, tTableName, -1)
	db, err := betterHost.ConnToDB(shardDB.Name)
	if err != nil {
		return nil, err
	}

	stmt, err := db.Prepare(ddlSql)
	if err != nil {
		return nil, err
	}

	_, err = stmt.Exec()
	if err != nil {
		return nil, err
	}

	stmt.Close()

	// write the new shard table to redis
	err = redis.WriteDB(newTabId, redis.EncodeData(shardTable), "MysqlShardTable")
	if err != nil {
		return nil, err
	}

	tbl.Shards = append(tbl.Shards, shardTable)
	tbl.ShardIds = append(tbl.ShardIds, newTabId)

	err = redis.UpdateDB(tbl.Id, redis.EncodeData(tbl), "MysqlTable")
	if err != nil {
		return nil, err
	}

	return shardTable, nil
}
Beispiel #5
0
func (d *DML) doOnAllShard() (interface{}, *schema.MysqlTable, error) {
	// to found out the alter table's host.
	//var err error
	var shardTables []*schema.MysqlShardTable
	var curTable *schema.MysqlTable
	for i := 0; i < len(schema.Tables); i++ {
		if schema.Tables[i].Name == d.TableName {
			curTable = schema.Tables[i]
			shardTables = curTable.Shards
			break
		}
	}
	if shardTables == nil {
		return nil, nil, errors.New("no found any one this table " + d.TableName)
	}

	// when alter table name, then need change MysqlProxyTable log

	resultCh := make(chan *alterResult, runtime.NumCPU())

	shardTBLen := len(shardTables)

	// the the sql param pointer
	params := getInterfaceParams(d.Sql[1:])

	// to do the sql at the same time.
	for i := 0; i < shardTBLen; i++ {
		go func(i int) {
			stb := shardTables[i]

			conn, err := host.GetBetterHost(stb.ShardDB.HostGroup.Master, "master").ConnToDB(stb.ShardDB.Name)
			if err != nil {
				conn.Close()
				resultCh <- &alterResult{Res: nil, Err: err}
				return
			}

			trueSql := strings.Replace(d.Sql[0], d.TableName, stb.Name, -1)
			stmt, err := conn.Prepare(trueSql)
			if err != nil {
				conn.Close()
				resultCh <- &alterResult{Res: nil, Err: err}
				return
			}

			res, err := stmt.Exec(params...)
			if err != nil {
				conn.Close()
				resultCh <- &alterResult{Res: nil, Err: err}
				return
			}

			stmt.Close()
			conn.Close()

			resultCh <- &alterResult{Res: res, Err: nil}
		}(i)
	}

	result := int64(0)
	for i := 0; i < shardTBLen; i++ {
		res := <-resultCh
		if res.Err != nil {
			return nil, nil, res.Err
		}

		rowAffected, err := res.Res.RowsAffected()
		if err != nil {
			return nil, nil, err
		}

		result += rowAffected
	}

	return result, curTable, nil
}
Beispiel #6
0
// to come true ExecCore interface
func (d *InsertDML) Do() (interface{}, error) {
	d.Mut.Lock()
	defer d.Mut.Unlock()

	// get the relation true table.
	var err error
	var shardTables []*schema.MysqlShardTable
	var curTable *schema.MysqlTable
	for i := 0; i < len(schema.Tables); i++ {
		if schema.Tables[i].Name == d.TableName {
			curTable = schema.Tables[i]
			shardTables = curTable.Shards
			break
		}
	}
	if shardTables == nil {
		return nil, errors.New("no found any one this table " + d.TableName)
	}

	// get the last true table.
	tableLen := len(shardTables)
	var table *schema.MysqlShardTable

	for i := 0; i < tableLen; i++ {
		if shardTables[i].RowTotal < uint64(5000000) {
			table = shardTables[i]
			break
		}
	}

	if table == nil {
		// to build new shard table
		table, err = curTable.BuildNewShardTable()
		if err != nil {
			return nil, err
		}
	}

	// parse the sql, to find the auto increment key,
	// then change its' value to table global id.
	sql, params, err := curTable.ParseMergeInsertGlobalId(d.Sql, table)
	if err != nil {
		return nil, err
	}

	master := host.GetBetterHost(table.ShardDB.HostGroup.Master, "master")
	conn, err := master.ConnToDB(table.ShardDB.Name)
	if err != nil {
		conn.Close()
		return nil, err
	}

	stmt, err := conn.Prepare(sql)
	if err != nil {
		conn.Close()
		return nil, err
	}

	res, err := stmt.Exec(params...)
	if err != nil {
		conn.Close()
		return nil, err
	}

	stmt.Close()
	conn.Close()

	curTable.RowTotal++
	curTable.UpdateToRedisDB()

	return res.LastInsertId()
}
Beispiel #7
0
// to come true ExecCore interface
func (d *SelectDML) Do() (interface{}, error) {
	d.Mut.Lock()
	defer d.Mut.Unlock()

	var err error
	var shardTables []*schema.MysqlShardTable
	var curTable *schema.MysqlTable

	for i := 0; i < len(schema.Tables); i++ {
		if schema.Tables[i].Name == d.TableName {
			curTable = schema.Tables[i]
			shardTables = curTable.Shards
			break
		}
	}
	if shardTables == nil {
		return nil, errors.New("no found any one this table " + d.TableName)
	}

	// Select expressions
	selects, err := analyzeSelectExprs(d.PDML.SelectExprs, curTable)
	limit := 0

	if d.PDML.Limit != nil {
		// offset, err  = strconv.Atoi(sqlparser.String(d.PDML.Limit.Offset))
		// if err != nil { return nil, err }

		limit, err = strconv.Atoi(sqlparser.String(d.PDML.Limit.Rowcount))
		if err != nil {
			return nil, err
		}
	}

	if err != nil {
		return nil, err
	}
	if selects == nil {
		return nil, errors.New("no any found select column")
	}

	// where
	// conditions  := analyzeWhere(d.PDML.Where)
	params := getInterfaceParams(d.Sql[1:])

	resultCh := make(chan *selectResult, runtime.NumCPU())
	shardTBLen := len(shardTables)
	maxTotal := 5000

	// to select the data from all shard tabl at the same time.
	for i := 0; i < shardTBLen; i++ {
		go func(i int) {
			stb := shardTables[i]
			conn, err := host.GetBetterHost(stb.ShardDB.HostGroup.Slave, "slave").ConnToDB(stb.ShardDB.Name)
			if err != nil {
				conn.Close()
				resultCh <- &selectResult{Res: nil, Err: err}
			}

			trueSql := strings.Replace(d.Sql[0], d.TableName, stb.Name, -1)
			if limit == 0 {
				trueSql += " limit " + strconv.Itoa(maxTotal)
			}

			stmt, err := conn.Prepare(trueSql)
			if err != nil {
				conn.Close()
				resultCh <- &selectResult{Res: nil, Err: err}
			}
			rows, err := stmt.Query(params...)
			if err != nil {
				conn.Close()
				resultCh <- &selectResult{Res: nil, Err: err}
			}

			stmt.Close()
			conn.Close()

			dataCols, err := rows.Columns()
			if err != nil {
				resultCh <- &selectResult{Res: nil, Err: err}
			}

			dataLen := len(dataCols)
			rowData := make([]interface{}, dataLen)
			valuePtrs := make([]interface{}, dataLen)
			backData := []map[string]interface{}{}
			curFound := 0

			for rows.Next() {
				if curFound >= maxTotal {
					break
				}

				oneData := map[string]interface{}{}
				for j, _ := range dataCols {
					valuePtrs[j] = &rowData[j]
				}

				err = rows.Scan(valuePtrs...)
				if err != nil {
					resultCh <- &selectResult{Res: nil, Err: err}
					return
				}

				for j, col := range dataCols {
					var v interface{}
					val := rowData[j]
					b, ok := val.([]byte)
					if ok {
						v = string(b)
					} else {
						v = val
					}
					oneData[col] = v
				}

				backData = append(backData, oneData)
				curFound++
			}
			err = rows.Err()
			rows.Close()

			if err != nil {
				resultCh <- &selectResult{Res: nil, Err: err}
			}
			resultCh <- &selectResult{Res: backData, Err: nil}
		}(i)
	}

	result := make([][]map[string]interface{}, shardTBLen)

	// get all shard data.
	for i := 0; i < shardTBLen; i++ {
		res := <-resultCh
		if res.Err != nil {
			return nil, res.Err
		}

		result[i] = res.Res
	}

	// to merge all data from all shard.
	mergedResult := []map[string]interface{}{}

	if d.hasDescInOrder() { // for desc order data in shard tables
		for i := (shardTBLen - 1); i >= 0; i-- {
			if i == (shardTBLen - 1) {
				mergedResult = result[i]
				continue
			}

			for j := 0; j < len(result[i]); j++ {
				mergedResult = append(mergedResult, result[i][j])
				if limit != 0 && limit <= len(mergedResult) {
					goto LAST
				}
			}
		}
	} else { // for asc order data in shard tables
		for i := 0; i < shardTBLen; i++ {
			if i == 0 {
				mergedResult = result[i]
				continue
			}

			for j := 0; j < len(result[i]); j++ {
				mergedResult = append(mergedResult, result[i][j])
				if limit != 0 && limit <= len(mergedResult) {
					goto LAST
				}
			}
		}
	}

LAST:
	return mergedResult, nil
}