// AgentSeedDetails reads details from backend table
func AgentSeedDetails(seedId int64) ([]SeedOperation, error) {
whereCondition := `
where
agent_seed_id = ?
`
return readSeeds(whereCondition, sqlutils.Args(seedId), "")
}
// ReadRecentlyActiveClusterRecovery reads recently completed entries for a given cluster
func ReadRecentlyActiveClusterRecovery(clusterName string) ([]TopologyRecovery, error) {
whereClause := `
where
end_recovery > now() - interval 5 minute
and cluster_name=?`
return readRecoveries(whereClause, ``, sqlutils.Args(clusterName))
}
// acknowledgeRecoveries sets acknowledged* details and clears the in_active_period flags from a set of entries
func acknowledgeRecoveries(owner string, comment string, markEndRecovery bool, whereClause string, args []interface{}) (countAcknowledgedEntries int64, err error) {
additionalSet := ``
if markEndRecovery {
additionalSet = `
end_recovery=IFNULL(end_recovery, NOW()),
`
}
query := fmt.Sprintf(`
update topology_recovery set
in_active_period = 0,
end_active_period_unixtime = IF(end_active_period_unixtime = 0, UNIX_TIMESTAMP(), end_active_period_unixtime),
%s
acknowledged = 1,
acknowledged_at = NOW(),
acknowledged_by = ?,
acknowledge_comment = ?
where
acknowledged = 0
and
%s
`, additionalSet, whereClause)
args = append(sqlutils.Args(owner, comment), args...)
sqlResult, err := db.ExecOrchestrator(query, args...)
if err != nil {
return 0, log.Errore(err)
}
rows, err := sqlResult.RowsAffected()
return rows, log.Errore(err)
}
func ReadAvailableNodes(onlyHttpNodes bool) ([]string, error) {
res := []string{}
extraInfo := ""
if onlyHttpNodes {
extraInfo = string(OrchestratorExecutionHttpMode)
}
query := `
select
concat(hostname, ';', token, ';', app_version) as node
from
node_health
where
last_seen_active > now() - interval ? second
and ? in (extra_info, '')
order by
hostname
`
err := db.QueryOrchestrator(query, sqlutils.Args(registrationPollSeconds*2, extraInfo), func(m sqlutils.RowMap) error {
res = append(res, m.GetString("node"))
return nil
})
if err != nil {
log.Errore(err)
}
return res, err
}
// SeedOperationState reads states for a given seed operation
func ReadSeedStates(seedId int64) ([]SeedOperationState, error) {
res := []SeedOperationState{}
query := `
select
agent_seed_state_id,
agent_seed_id,
state_timestamp,
state_action,
error_message
from
agent_seed_state
where
agent_seed_id = ?
order by
agent_seed_state_id desc
`
err := db.QueryOrchestrator(query, sqlutils.Args(seedId), func(m sqlutils.RowMap) error {
seedState := SeedOperationState{}
seedState.SeedStateId = m.GetInt64("agent_seed_state_id")
seedState.SeedId = m.GetInt64("agent_seed_id")
seedState.StateTimestamp = m.GetString("state_timestamp")
seedState.Action = m.GetString("state_action")
seedState.ErrorMessage = m.GetString("error_message")
res = append(res, seedState)
return nil
})
if err != nil {
log.Errore(err)
}
return res, err
}
// AcknowledgeInstanceRecoveries marks active recoveries for given instane as acknowledged.
// This also implied clearing their active period, which in turn enables further recoveries on those topologies
func AcknowledgeInstanceRecoveries(instanceKey *inst.InstanceKey, owner string, comment string) (countAcknowledgedEntries int64, err error) {
whereClause := `
hostname = ?
and port = ?
`
return acknowledgeRecoveries(owner, comment, false, whereClause, sqlutils.Args(instanceKey.Hostname, instanceKey.Port))
}
// 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 := `
select
hostname,
port,
token,
last_submitted,
mysql_port
from
host_agent
where
hostname = ?
`
err := db.QueryOrchestrator(query, sqlutils.Args(hostname), 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 err != nil {
return agent, "", err
}
if token == "" {
return agent, "", log.Errorf("Cannot get agent/token: %s", hostname)
}
return agent, token, nil
}
func ReadClusterPoolInstances(clusterName string) (*PoolInstancesMap, error) {
var poolInstancesMap = make(PoolInstancesMap)
query := `
select
database_instance_pool.*
from
database_instance
join database_instance_pool using (hostname, port)
where
database_instance.cluster_name = ?
`
err := db.QueryOrchestrator(query, sqlutils.Args(clusterName), func(m sqlutils.RowMap) error {
pool := m.GetString("pool")
hostname := m.GetString("hostname")
port := m.GetInt("port")
if _, ok := poolInstancesMap[pool]; !ok {
poolInstancesMap[pool] = [](*InstanceKey){}
}
poolInstancesMap[pool] = append(poolInstancesMap[pool], &InstanceKey{Hostname: hostname, Port: port})
return nil
})
if err != nil {
return nil, err
}
return &poolInstancesMap, nil
}
// ReadMaintenanceInstanceKey will return the instanceKey for active maintenance by maintenanceToken
func ReadMaintenanceInstanceKey(maintenanceToken int64) (*InstanceKey, error) {
var res *InstanceKey
query := `
select
hostname, port
from
database_instance_maintenance
where
database_instance_maintenance_id = ?
`
err := db.QueryOrchestrator(query, sqlutils.Args(maintenanceToken), func(m sqlutils.RowMap) error {
instanceKey, merr := NewInstanceKeyFromStrings(m.GetString("hostname"), m.GetString("port"))
if merr != nil {
return merr
}
res = instanceKey
return nil
})
if err != nil {
log.Errore(err)
}
return res, err
}
// ReadActiveRecoveries reads active recovery entry/audit entires from topology_recovery
func ReadActiveRecoveries() ([]TopologyRecovery, error) {
return readRecoveries(`
where
in_active_period=1
and end_recovery is null`,
``, sqlutils.Args())
}
// ReadInActivePeriodClusterRecovery reads recoveries (possibly complete!) that are in active period.
// (may be used to block further recoveries on this cluster)
func ReadInActivePeriodClusterRecovery(clusterName string) ([]TopologyRecovery, error) {
whereClause := `
where
in_active_period=1
and cluster_name=?`
return readRecoveries(whereClause, ``, sqlutils.Args(clusterName))
}
// ReadLongRunningProcesses returns the list of current known long running processes of all instances
func ReadLongRunningProcesses(filter string) ([]Process, error) {
longRunningProcesses := []Process{}
if filter != "" {
filter = "%" + filter + "%"
} else {
filter = "%"
}
query := `
select
hostname,
port,
process_id,
process_started_at,
process_user,
process_host,
process_db,
process_command,
process_time_seconds,
process_state,
process_info
from
database_instance_long_running_queries
where
hostname like ?
or process_user like ?
or process_host like ?
or process_db like ?
or process_command like ?
or process_state like ?
or process_info like ?
order by
process_time_seconds desc
`
args := sqlutils.Args(filter, filter, filter, filter, filter, filter, filter)
err := db.QueryOrchestrator(query, args, func(m sqlutils.RowMap) error {
process := Process{}
process.InstanceHostname = m.GetString("hostname")
process.InstancePort = m.GetInt("port")
process.Id = m.GetInt64("process_id")
process.User = m.GetString("process_user")
process.Host = m.GetString("process_host")
process.Db = m.GetString("process_db")
process.Command = m.GetString("process_command")
process.Time = m.GetInt64("process_time_seconds")
process.State = m.GetString("process_state")
process.Info = m.GetString("process_info")
process.StartedAt = m.GetString("process_started_at")
longRunningProcesses = append(longRunningProcesses, process)
return nil
})
if err != nil {
log.Errore(err)
}
return longRunningProcesses, err
}
// ReadInActivePeriodSuccessorInstanceRecovery reads completed recoveries for a given instance, where said instance
// was promoted as result, still in active period (may be used to block further recoveries should this instance die)
func ReadInActivePeriodSuccessorInstanceRecovery(instanceKey *inst.InstanceKey) ([]TopologyRecovery, error) {
whereClause := `
where
in_active_period=1
and
successor_hostname=? and successor_port=?`
return readRecoveries(whereClause, ``, sqlutils.Args(instanceKey.Hostname, instanceKey.Port))
}
// ReadRecentlyActiveInstanceRecovery reads recently completed entries for a given instance
func ReadRecentlyActiveInstanceRecovery(instanceKey *inst.InstanceKey) ([]TopologyRecovery, error) {
whereClause := `
where
end_recovery > now() - interval 5 minute
and
successor_hostname=? and successor_port=?`
return readRecoveries(whereClause, ``, sqlutils.Args(instanceKey.Hostname, instanceKey.Port))
}
// GetHostAttributesByMatch
func GetHostAttributesByAttribute(attributeName string, valueMatch string) ([]HostAttributes, error) {
if valueMatch == "" {
valueMatch = ".?"
}
whereClause := ` where attribute_name = ? and attribute_value rlike ?`
return getHostAttributesByClause(whereClause, sqlutils.Args(attributeName, valueMatch))
}
// AcknowledgeInstanceRecoveries marks active recoveries for given instane as acknowledged.
// This also implied clearing their active period, which in turn enables further recoveries on those topologies
func AcknowledgeInstanceRecoveries(instanceKey *inst.InstanceKey, owner string, comment string) (countAcknowledgedEntries int64, err error) {
whereClause := `
hostname = ?
and port = ?
`
args := sqlutils.Args(instanceKey.Hostname, instanceKey.Port)
clearAcknowledgedFailureDetections(whereClause, args)
return acknowledgeRecoveries(owner, comment, false, whereClause, args)
}
func GetEquivalentMasterCoordinates(instanceCoordinates *InstanceBinlogCoordinates) (result [](*InstanceBinlogCoordinates), err error) {
query := `
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 = ?
and master2_port = ?
and master2_binary_log_file = ?
and master2_binary_log_pos = ?
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 = ?
and master1_port = ?
and master1_binary_log_file = ?
and master1_binary_log_pos = ?
`
args := sqlutils.Args(
instanceCoordinates.Key.Hostname,
instanceCoordinates.Key.Port,
instanceCoordinates.Coordinates.LogFile,
instanceCoordinates.Coordinates.LogPos,
instanceCoordinates.Key.Hostname,
instanceCoordinates.Key.Port,
instanceCoordinates.Coordinates.LogFile,
instanceCoordinates.Coordinates.LogPos,
)
err = db.QueryOrchestrator(query, args, 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
})
if err != nil {
return nil, err
}
return result, nil
}
// ReadActiveSeedsForHost reads active seeds where host participates either as source or target
func ReadActiveSeedsForHost(hostname string) ([]SeedOperation, error) {
whereCondition := `
where
is_complete = 0
and (
target_hostname = ?
or source_hostname = ?
)
`
return readSeeds(whereCondition, sqlutils.Args(hostname, hostname), "")
}
// ReadRecentCompletedSeedsForHost reads active seeds where host participates either as source or target
func ReadRecentCompletedSeedsForHost(hostname string) ([]SeedOperation, error) {
whereCondition := `
where
is_complete = 1
and (
target_hostname = ?
or source_hostname = ?
)
`
return readSeeds(whereCondition, sqlutils.Args(hostname, hostname), "limit 10")
}
func ReadPendingAsyncRequests(limit int) (res [](*AsyncRequest), err error) {
limitClause := ``
args := sqlutils.Args()
if limit > 0 {
limitClause = `limit ?`
args = append(args, limit)
}
query := fmt.Sprintf(`
select
request_id,
command,
hostname,
port,
destination_hostname,
destination_port,
pattern,
gtid_hint,
story
from
async_request
where
begin_timestamp IS NULL
order by
request_id asc
%s
`, limitClause)
err = db.QueryOrchestrator(query, args, func(m sqlutils.RowMap) error {
asyncRequest := NewEmptyAsyncRequest()
asyncRequest.Id = m.GetInt64("request_id")
asyncRequest.Command = m.GetString("command")
asyncRequest.OperatedInstanceKey = &inst.InstanceKey{}
asyncRequest.OperatedInstanceKey.Hostname = m.GetString("hostname")
asyncRequest.OperatedInstanceKey.Port = m.GetInt("port")
if m.GetString("destination_hostname") != "" {
asyncRequest.DestinationKey = &inst.InstanceKey{}
asyncRequest.DestinationKey.Hostname = m.GetString("destination_hostname")
asyncRequest.DestinationKey.Port = m.GetInt("destination_port")
}
asyncRequest.Pattern = m.GetString("pattern")
asyncRequest.GTIDHint = inst.OperationGTIDHint(m.GetString("gtid_hint"))
asyncRequest.Story = m.GetString("story")
res = append(res, asyncRequest)
return nil
})
if err != nil {
log.Errore(err)
}
return res, err
}
// GetHostAttribute expects to return a single attribute for a given hostname/attribute-name combination
// or error on empty result
func GetHostAttribute(hostname string, attributeName string) (string, error) {
whereClause := `where hostname=? and attribute_name=?`
attributes, err := getHostAttributesByClause(whereClause, sqlutils.Args(hostname, attributeName))
if err != nil {
return "", err
}
if len(attributeName) == 0 {
return "", log.Errorf("No attribute found for %+v, %+v", hostname, attributeName)
}
return attributes[0].AttributeValue, nil
}
// ReadAliasByClusterName returns the cluster alias for the given cluster name,
// or the cluster name itself if not explicit alias found
func ReadAliasByClusterName(clusterName string) (alias string, err error) {
alias = clusterName // default return value
query := `
select
alias
from
cluster_alias
where
cluster_name = ?
`
err = db.QueryOrchestrator(query, sqlutils.Args(clusterName), func(m sqlutils.RowMap) error {
alias = m.GetString("alias")
return nil
})
return clusterName, err
}
// ReadRecentAudit returns a list of audit entries order chronologically descending, using page number.
func ReadRecentAudit(instanceKey *InstanceKey, page int) ([]Audit, error) {
res := []Audit{}
args := sqlutils.Args()
whereCondition := ``
if instanceKey != nil {
whereCondition = `where hostname=? and port=?`
args = append(args, instanceKey.Hostname, instanceKey.Port)
}
query := fmt.Sprintf(`
select
audit_id,
audit_timestamp,
audit_type,
hostname,
port,
message
from
audit
%s
order by
audit_timestamp desc
limit ?
offset ?
`, whereCondition)
args = append(args, config.Config.AuditPageSize, page*config.Config.AuditPageSize)
err := db.QueryOrchestrator(query, args, func(m sqlutils.RowMap) error {
audit := Audit{}
audit.AuditId = m.GetInt64("audit_id")
audit.AuditTimestamp = m.GetString("audit_timestamp")
audit.AuditType = m.GetString("audit_type")
audit.AuditInstanceKey.Hostname = m.GetString("hostname")
audit.AuditInstanceKey.Port = m.GetInt("port")
audit.Message = m.GetString("message")
res = append(res, audit)
return nil
})
if err != nil {
log.Errore(err)
}
return res, err
}
// ReadCRecoveries reads latest recovery entries from topology_recovery
func ReadRecentRecoveries(clusterName string, unacknowledgedOnly bool, page int) ([]TopologyRecovery, error) {
whereConditions := []string{}
whereClause := ""
args := sqlutils.Args()
if unacknowledgedOnly {
whereConditions = append(whereConditions, `acknowledged=0`)
}
if clusterName != "" {
whereConditions = append(whereConditions, `cluster_name=?`)
args = append(args, clusterName)
}
if len(whereConditions) > 0 {
whereClause = fmt.Sprintf("where %s", strings.Join(whereConditions, " and "))
}
limit := `
limit ?
offset ?`
args = append(args, config.Config.AuditPageSize, page*config.Config.AuditPageSize)
return readRecoveries(whereClause, limit, args)
}
func TokenBelongsToHealthyHttpService(token string) (result bool, err error) {
extraInfo := string(OrchestratorExecutionHttpMode)
query := `
select
token
from
node_health
where
and token = ?
and extra_info = ?
`
err = db.QueryOrchestrator(query, sqlutils.Args(token, extraInfo), func(m sqlutils.RowMap) error {
// Row exists? We're happy
result = true
return nil
})
return result, log.Errore(err)
}
// TokenIsValid checks to see whether a given token exists and is not outdated.
func TokenIsValid(publicToken string, secretToken string) (result bool, err error) {
query := `
select
count(*) as valid_token
from
access_token
where
public_token=?
and secret_token=?
and (
generated_at >= now() - interval ? minute
or is_reentrant = 1
)
`
err = db.QueryOrchestrator(query, sqlutils.Args(publicToken, secretToken, config.Config.AccessTokenExpiryMinutes), func(m sqlutils.RowMap) error {
result = m.GetInt("valid_token") > 0
return nil
})
return result, log.Errore(err)
}
// ReadOutdatedAgentsHosts returns agents that need to be updated
func ReadOutdatedAgentsHosts() ([]string, error) {
res := []string{}
query := `
select
hostname
from
host_agent
where
IFNULL(last_checked < now() - interval ? minute, true)
`
err := db.QueryOrchestrator(query, sqlutils.Args(config.Config.AgentPollMinutes), func(m sqlutils.RowMap) error {
hostname := m.GetString("hostname")
res = append(res, hostname)
return nil
})
if err != nil {
log.Errore(err)
}
return res, err
}
// ReadBlockedRecoveries reads blocked recovery entries, potentially filtered by cluster name (empty to unfilter)
func ReadBlockedRecoveries(clusterName string) ([]BlockedTopologyRecovery, error) {
res := []BlockedTopologyRecovery{}
whereClause := ""
args := sqlutils.Args()
if clusterName != "" {
whereClause = `where cluster_name = ?`
args = append(args, clusterName)
}
query := fmt.Sprintf(`
select
hostname,
port,
cluster_name,
analysis,
last_blocked_timestamp,
blocking_recovery_id
from
blocked_topology_recovery
%s
order by
last_blocked_timestamp desc
`, whereClause)
err := db.QueryOrchestrator(query, args, func(m sqlutils.RowMap) error {
blockedTopologyRecovery := BlockedTopologyRecovery{}
blockedTopologyRecovery.FailedInstanceKey.Hostname = m.GetString("hostname")
blockedTopologyRecovery.FailedInstanceKey.Port = m.GetInt("port")
blockedTopologyRecovery.ClusterName = m.GetString("cluster_name")
blockedTopologyRecovery.Analysis = inst.AnalysisCode(m.GetString("analysis"))
blockedTopologyRecovery.LastBlockedTimestamp = m.GetString("last_blocked_timestamp")
blockedTopologyRecovery.BlockingRecoveryId = m.GetInt64("blocking_recovery_id")
res = append(res, blockedTopologyRecovery)
return nil
})
if err != nil {
log.Errore(err)
}
return res, err
}
// ReadClusterPoolInstances reads cluster-pool-instance associationsfor given cluster and pool
func ReadClusterPoolInstances(clusterName string, pool string) (result [](*ClusterPoolInstance), err error) {
args := sqlutils.Args()
whereClause := ``
if clusterName != "" {
whereClause = `
where
database_instance.cluster_name = ?
and ? in ('', pool)
`
args = append(args, clusterName, pool)
}
query := fmt.Sprintf(`
select
cluster_name,
ifnull(alias, cluster_name) as alias,
database_instance_pool.*
from
database_instance
join database_instance_pool using (hostname, port)
left join cluster_alias using (cluster_name)
%s
`, whereClause)
err = db.QueryOrchestrator(query, args, func(m sqlutils.RowMap) error {
clusterPoolInstance := ClusterPoolInstance{
ClusterName: m.GetString("cluster_name"),
ClusterAlias: m.GetString("alias"),
Pool: m.GetString("pool"),
Hostname: m.GetString("hostname"),
Port: m.GetInt("port"),
}
result = append(result, &clusterPoolInstance)
return nil
})
if err != nil {
return nil, err
}
return result, nil
}
// AcquireAccessToken attempts to acquire a hopefully free token; returning in such case
// the secretToken as proof of ownership.
func AcquireAccessToken(publicToken string) (secretToken string, err error) {
secretToken = ""
sqlResult, err := db.ExecOrchestrator(`
update access_token
set
is_acquired=1,
acquired_at=now()
where
public_token=?
and (
(
is_acquired=0
and generated_at > now() - interval ? second
)
or is_reentrant=1
)
`,
publicToken, config.Config.AccessTokenUseExpirySeconds,
)
if err != nil {
return secretToken, log.Errore(err)
}
rows, err := sqlResult.RowsAffected()
if err != nil {
return secretToken, log.Errore(err)
}
if rows == 0 {
return secretToken, log.Errorf("Cannot acquire token %s", publicToken)
}
// Seems like we made it!
query := `
select secret_token from access_token where public_token=?
`
err = db.QueryOrchestrator(query, sqlutils.Args(publicToken), func(m sqlutils.RowMap) error {
secretToken = m.GetString("secret_token")
return nil
})
return secretToken, log.Errore(err)
}