Golang sqlutils.QueryRowsMap函数代码示例

// ElectedNode returns the details of the elected node, as well as answering the question "is this process the elected one"?
func ElectedNode() (hostname string, token string, isElected bool, err error) {
	query := `
		select 
			hostname,
			token
		from 
			active_node
		where
			anchor = 1
		`
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		hostname = m.GetString("hostname")
		token = m.GetString("token")
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	isElected = (hostname == ThisHostname && token == ProcessToken.Hash)
	return hostname, token, isElected, err
}

// readMissingHostnamesToResolve gets those (unresolved, e.g. VIP) hostnames that *should* be present in
// the hostname_resolve table, but aren't.
func readMissingKeysToResolve() (result InstanceKeyMap, err error) {
	query := `
   		select 
   				hostname_unresolve.unresolved_hostname,
   				database_instance.port
   			from 
   				database_instance 
   				join hostname_unresolve on (database_instance.hostname = hostname_unresolve.hostname) 
   				left join hostname_resolve on (database_instance.hostname = hostname_resolve.resolved_hostname) 
   			where 
   				hostname_resolve.hostname is null
	   		`
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		instanceKey := InstanceKey{Hostname: m.GetString("unresolved_hostname"), Port: m.GetInt("port")}
		result.AddKey(instanceKey)
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	return result, err
}

func GetEquivalentMasterCoordinates(instanceCoordinates *InstanceBinlogCoordinates) (result [](*InstanceBinlogCoordinates), err error) {
	query := fmt.Sprintf(`
		select 
				master1_hostname as hostname,
				master1_port as port,
				master1_binary_log_file as binlog_file,
				master1_binary_log_pos as binlog_pos
			from 
				master_position_equivalence
			where
				master2_hostname = '%s'
				and master2_port = '%d'
				and master2_binary_log_file = '%s'
				and master2_binary_log_pos = '%d'
		union
		select 
				master2_hostname as hostname,
				master2_port as port,
				master2_binary_log_file as binlog_file,
				master2_binary_log_pos as binlog_pos
			from 
				master_position_equivalence
			where
				master1_hostname = '%s'
				and master1_port = '%d'
				and master1_binary_log_file = '%s'
				and master1_binary_log_pos = '%d'
		`,
		instanceCoordinates.Key.Hostname,
		instanceCoordinates.Key.Port,
		instanceCoordinates.Coordinates.LogFile,
		instanceCoordinates.Coordinates.LogPos,
		instanceCoordinates.Key.Hostname,
		instanceCoordinates.Key.Port,
		instanceCoordinates.Coordinates.LogFile,
		instanceCoordinates.Coordinates.LogPos)

	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		equivalentCoordinates := InstanceBinlogCoordinates{}
		equivalentCoordinates.Key.Hostname = m.GetString("hostname")
		equivalentCoordinates.Key.Port = m.GetInt("port")
		equivalentCoordinates.Coordinates.LogFile = m.GetString("binlog_file")
		equivalentCoordinates.Coordinates.LogPos = m.GetInt64("binlog_pos")

		result = append(result, &equivalentCoordinates)
		return nil
	})
Cleanup:

	if err != nil {
		return nil, err
	}

	return result, nil
}

// ReadClusterDomainName reads the domain name associated with a cluster, if any
func ReadClusterDomainName(clusterName string) (string, error) {
	domainName := ""
	query := fmt.Sprintf(`
		select 
			domain_name
		from 
			cluster_domain_name
		where
			cluster_name = '%s'
		`, clusterName)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		domainName = m.GetString("domain_name")
		return nil
	})
Cleanup:

	if err != nil {
		return "", err
	}
	if domainName == "" {
		err = fmt.Errorf("No domain name found for cluster %s", clusterName)
	}
	return domainName, err

}

// ReadClusterAliases reads the entrie cluster name aliases mapping
func ReadClusterByAlias(alias string) (string, error) {
	clusterName := ""
	query := fmt.Sprintf(`
		select 
			cluster_name
		from 
			cluster_alias
		where
			alias = '%s'
		`, alias)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		clusterName = m.GetString("cluster_name")
		return nil
	})
Cleanup:

	if err != nil {
		return "", err
	}
	if clusterName == "" {
		err = fmt.Errorf("No cluster found for alias %s", alias)
	}
	return clusterName, err

}

func readAvailableNodes() ([]string, error) {
	res := []string{}
	query := fmt.Sprintf(`
		select 
			concat(hostname, ';', token) as node
		from 
			node_health
		where
			last_seen_active > now() - interval 5 minute
		order by
			hostname
		`)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		res = append(res, m.GetString("node"))
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	return res, err
}

// ReadResolvedHostname returns the resolved hostname given a hostname, or empty if not exists
func ReadResolvedHostname(hostname string) (string, error) {
	var resolvedHostname string = ""

	query := fmt.Sprintf(`
		select 
			resolved_hostname
		from 
			hostname_resolve
		where
			hostname = '%s'
		`, hostname)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		resolvedHostname = m.GetString("resolved_hostname")
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	return resolvedHostname, err
}

// readAgentBasicInfo returns the basic data for an agent directly from backend table (no agent access)
func readAgentBasicInfo(hostname string) (Agent, string, error) {
	agent := Agent{}
	token := ""
	query := fmt.Sprintf(`
		select 
			hostname,
			port,
			token,
			last_submitted,
			mysql_port
		from 
			host_agent
		where
			hostname = '%s'
		`, hostname)
	db, err := db.OpenOrchestrator()
	if err != nil {
		return agent, "", err
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		agent.Hostname = m.GetString("hostname")
		agent.Port = m.GetInt("port")
		agent.LastSubmitted = m.GetString("last_submitted")
		agent.MySQLPort = m.GetInt64("mysql_port")
		token = m.GetString("token")

		return nil
	})

	if token == "" {
		return agent, "", log.Errorf("Cannot get agent/token: %s", hostname)
	}
	return agent, token, nil
}

// ReadClusterAliases reads the entrie cluster name aliases mapping
func ReadClusterAliases() error {
	updatedMap := make(map[string]string)
	query := fmt.Sprintf(`
		select 
			cluster_name,
			alias
		from 
			cluster_alias
		`)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		updatedMap[m.GetString("cluster_name")] = m.GetString("alias")
		return err
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	clusterAliasMapMutex.Lock()
	defer clusterAliasMapMutex.Unlock()
	clusterAliasMap = updatedMap
	return err

}

func readAllHostnameResolves() ([]HostnameResolve, error) {
	res := []HostnameResolve{}
	query := fmt.Sprintf(`
		select 
			hostname, 
			resolved_hostname  
		from 
			hostname_resolve
		`)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		hostnameResolve := HostnameResolve{hostname: m.GetString("hostname"), resolvedHostname: m.GetString("resolved_hostname")}

		res = append(res, hostnameResolve)
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	return res, err
}

// ReadOutdatedAgentsHosts returns agents that need to be updated
func ReadOutdatedAgentsHosts() ([]string, error) {
	res := []string{}
	query := fmt.Sprintf(`
		select 
			hostname 
		from 
			host_agent 
		where
			IFNULL(last_checked < now() - interval %d minute, true)
			`,
		config.Config.AgentPollMinutes)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		hostname := m.GetString("hostname")
		res = append(res, hostname)
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	return res, err
}

// ElectedNode returns the hostname of the elected node
func ElectedNode() (string, string, bool, error) {
	hostname := ""
	token := ""
	isElected := false
	query := fmt.Sprintf(`
		select 
			ifnull(max(hostname), '') as hostname,
			ifnull(max(token), '') as token,
			(ifnull(max(hostname), '') = '%s') and (ifnull(max(token), '') = '%s') as is_elected
		from 
			active_node
		where
			anchor = 1
		`,
		ThisHostname, ProcessToken.Hash)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		hostname = m.GetString("hostname")
		token = m.GetString("token")
		isElected = m.GetBool("is_elected")
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	return hostname, token, isElected, err
}

// Read (as much as possible of) a chunk of binary log events starting the given startingCoordinates
func readBinlogEventsChunk(instanceKey *InstanceKey, startingCoordinates BinlogCoordinates) ([]BinlogEvent, error) {
	events := []BinlogEvent{}
	db, err := db.OpenTopology(instanceKey.Hostname, instanceKey.Port)
	if err != nil {
		return events, err
	}
	commandToken := math.TernaryString(startingCoordinates.Type == BinaryLog, "binlog", "relaylog")
	if startingCoordinates.LogFile == "" {
		return events, log.Errorf("readBinlogEventsChunk: empty binlog file name for %+v.", *instanceKey)
	}
	query := fmt.Sprintf("show %s events in '%s' FROM %d LIMIT %d", commandToken, startingCoordinates.LogFile, startingCoordinates.LogPos, config.Config.BinlogEventsChunkSize)
	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		binlogEvent := BinlogEvent{}
		binlogEvent.Coordinates.LogFile = m.GetString("Log_name")
		binlogEvent.Coordinates.LogPos = m.GetInt64("Pos")
		binlogEvent.Coordinates.Type = startingCoordinates.Type
		binlogEvent.NextEventPos = m.GetInt64("End_log_pos")
		binlogEvent.EventType = m.GetString("Event_type")
		binlogEvent.Info = m.GetString("Info")

		events = append(events, binlogEvent)
		return nil
	})
	return events, err
}

// IsElected checks whether this node is the elected active node
func IsElected() (bool, error) {
	isElected := false
	query := fmt.Sprintf(`
		select 
			count(*) as is_elected
		from 
			active_node
		where
			anchor = 1
			and hostname = '%s'
			and token = '%s'
		`,
		ThisHostname, ProcessToken.Hash)
	db, err := db.OpenOrchestrator()
	if err != nil {
		goto Cleanup
	}

	err = sqlutils.QueryRowsMap(db, query, func(m sqlutils.RowMap) error {
		isElected = m.GetBool("is_elected")
		return nil
	})
Cleanup:

	if err != nil {
		log.Errore(err)
	}
	return isElected, err
}

func main() {
	flag.BoolVar(&verbose, "verbose", false, "Verbose logging")
	flag.Parse()

	dsn := os.Getenv("MYSQL_DSN") // better name ?
	if dsn == "" {
		fmt.Println("Using default dsn", defaultDSN)
		dsn = defaultDSN
	} else {
		fmt.Println("Using dsn defined in environment variable MYSQL_DSN")
	}
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	// really connect
	if err = db.Ping(); err != nil {
		log.Fatal("Ping failure:", err)
	}
	fmt.Println("Connected to database")

	fmt.Println("show variables like 'maxscale%'")
	rows, err := db.Query("show variables like 'maxscale%'")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	for rows.Next() {
		var variable, value string
		if err := rows.Scan(&variable, &value); err != nil {
			log.Fatal(err)
		}
		namedCheckForNulls(variable, value)
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}

	// show slave status
	fmt.Println("show slave status:")
	err = sqlutils.QueryRowsMap(db, "show slave status", func(m sqlutils.RowMap) error {
		namedCheckForNulls("Slave_IO_Running", m.GetString("Slave_IO_Running"))
		namedCheckForNulls("Slave_SQL_Running", m.GetString("Slave_SQL_Running"))
		namedCheckForNulls("Master_Log_File", m.GetString("Master_Log_File"))
		namedCheckForNulls("Relay_Master_Log_File", m.GetString("Relay_Master_Log_File"))
		namedCheckForNulls("Relay_Log_File", m.GetString("Relay_Log_File"))
		namedCheckForNulls("Executed_Gtid_Set", m.GetString("Executed_Gtid_Set"))
		namedCheckForNulls("UsingMariaDBGTID", m.GetString("Using_Gtid"))
		namedCheckForNulls("Master_Host", m.GetString("Master_Host"))

		return nil
	})

	fmt.Println("other commands:")
	namedSingleRowQuery(db, "VERSION()", "SELECT VERSION()")
	namedSingleRowQuery(db, "@@hostname", "SELECT @@hostname")
	namedSingleRowQuery(db, "@@report_host", "SELECT @@report_host")
}