How To Work With MySQL Replication Output Using Golang

Getting Help Code Review
1

Hello Everyone,

I am MySQL database administrator and I’m testing out Golang for some administrative MySQL tasks.

Presently, I am trying to handle the output of a MySQL command called “SHOW SLAVE STATUS.”

Here is what the output looks like from the Linux / MySQL commandline

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: server.hostname.com
                  Master_User: replUser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 999999999
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 999999999
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 376515189
              Relay_Log_Space: 376515572
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
             Master_Server_Id: 340472056
                  Master_UUID: a75ae239-4444-11e6-4444-1402ec6b1540
             Master_Info_File: /mnt/server/foldername/mysql/master.info
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
1 row in set (0.00 sec)

As you can see, the output is verbose and there are many “columns” in the output.

This is the code I am using to run the “SHOW SLAVE STATUS” command.

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql",
		"$USER:$PASS@tcp($HOSTNAME:3306)/Testing")
	if err != nil {
		log.Println(err)
	}
	var str string
	err = db.QueryRow("SHOW SLAVE STATUS").Scan(&str)
	if err != nil && err != sql.ErrNoRows {
		log.Println(err)
	}
	fmt.Println(str)
	defer db.Close()
}

I want to save this output into some variable or object and pick and choose from it later.

Here’s some (not correct) pseudo code to explain what (I think) I want to do.

//Fake golang code to explain what I'm trying to do
//Get slave status
slaveStatus := getSlaveStatus()

//Check if MySQL slave is running
patternToFind := regexp.Find('Slave_IO_Running: Yes', slaveStatus)

//Evaluate if MySQL replication is running
if regexp.MatchString("Slave_IO_Running: Yes", patternToFind) {
     fmt.Println("Slave is running") 
     } else {
     fmt.Println("Slave is not running") }

I have the following questions.

1.) Is there a function or method to find out how Golang is interpreting the output of “SHOW SLAVE STATUS” data?

Is it receiving a map, a slice, a string, a blob? How can I learn how Golang sees this output?

2.) Different MySQL servers have different “columns” in the output of “SHOW SLAVE STATUS.” What is the best way to have Golang deal with a result set that never has the same amount of columns? How should I deal with ever changing number columns in the result set from a query?

====

Any advice, direction, suggestions would be appreciated. Thanks!!

2

Here are some resources that might help you understand these things better:

  1. http://go-database-sql.org/retrieving.html This is about retrieving results in general

  2. http://go-database-sql.org/varcols.html This is a technique for when you don’t know your total columns

  3. https://gist.github.com/SchumacherFM/69a167bec7dea644a20e This is an interesting bit of code that works on pullling an unknown number of columns into a map of strings.

2 Likes
3

So, I’ve brushed up on my code at the bottom of the below code block and I am missing something very, very basic (I apologize in advance for my novice’ness).

How do I print memory addresses as strings?

This code is doing exactly what I want it to with the exception of printing the values as strings…How do I get the final fmt.* command to print strings?

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	//Connect to database and check for errors
	db, err := sql.Open("mysql","username:password@tcp(127.0.0.1:3306)/Testing")
	if err != nil {
		log.Println(err)
	}
	//Execute query, check errs, defer close
	rows, err := db.Query("SELECT * FROM animals")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	//Build resultset??
	cols, err := rows.Columns() // Remember to check err afterwards
	if err != nil {
		log.Fatal(err)
	}
  
  //Build interface and load it with values
	vals := make([]interface{}, len(cols))
	for i, _ := range cols {
	vals[i] = new(sql.RawBytes)
}
  
  //for every value in 'rows', scan it into the interface
	for rows.Next() {
	err = rows.Scan(vals...)
	// Now you can check each element of vals for nil-ness,
	if err != nil {
		log.Fatal(err)
	}
	//The below Println command prints values, but
  	//it prints them as memory addresses like this => "[0xc42000c600 0xc42000c620]"
  	//↓↓↓↓How can I print the string values retrieved from MySQL???
	fmt.Println(vals) //<<<How do I prevent this guy from printing these^^^^^ memory addresses??
	}

}
4

You’ve made each element in that slice a *sql.RawBytes, and it’s the address to this that gets printed. Assuming the raw bytes in question actually represent a string you can print it by derefering and converting: fmt.Println(string(*vals[0])) for example. But there’s no guarantee that’s the case, of course. The raw bytes are just that - the bytes returned from the SQL server. A rather inconvenient type to deal with in general.

The “variable columns” article above hand waves about using “type introspection and type assertions” on the results to get the actual type, but I don’t see how that would work.

I’d suggest figuring out the types returned and using strings, ints, etc as in the first example in that article. By trial and error, if necessary. I’m sure “show slave status” can be expected to always return the same columns.

Edit: There is a rows.ColumnTypes() you can call to get the actual types. But this is still quite cumbersome.

Note that you can also access the replication status in the tables directly: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-replication-tables.html

That lets you do more controlled selects on just the columns you need, with the types you expect.

5

hey @thecartesian I am facing the same problem. If you got how to resolve this then can you please paste the solution here.

1 Like
6

Hi @Tejas_Sangani !

Totally forgot about this post.

Here’s a quick explanation of how database/sql works, and a coding solution to help you display SHOW SLAVE STATUS regardless of column type and MySQL version.

-Querying SLAVE STATUS Using Golang-

When working with SLAVE STATUS from MySQL, the DB.Query() method from database/sql is the most typical thing to use.

db.Query() returns a Rows struct.

Rows has 2 methods that are useful to you:

  • Rows.Columns() - Which provides Column names
  • Rows.Scan() - Which places []Bytes data into a variable or array

Golang is a “typed” language so you either need to express the data type for each column in SHOW SLAVE STATUS or you need to create a place-holder (like interface{}) to give the typed syntax that the Go compiler checks for.

The best way I’ve found to dynamically get the Columns and the Row data is to separately extract the Column and Row data, then format them together.

-Example Code For Querying MySQL Slave Status-

In this example, I build a Rows object. Separately extract the column data and row data, then print them formatted together.

Once formatted, you can either print the values like I did, or you can evaluate them and take some other respective action.

GITHUB LINK for better code highlighting

package main

import (
  "database/sql"
  "log"
  _ "github.com/go-sql-driver/mysql"
  "fmt"
  "strings"
)

func main() {

  //Open a connection to MySQL
  db, err := sql.Open("mysql", "username:password@tcp(hostname:mysql_port)/db_name")
  if err != nil {
    log.Fatal(err)
  }

  //No matter what happens, execute a db.Close() as the last thing we do
  defer db.Close()


  //Build sql.Rows object which contains our query data
  rows, err := db.Query("SHOW SLAVE STATUS")

  if err != nil {
    log.Fatal(err)
  }

  // sql.Rows has a function which returns all column names
  // as a slice of []string. Variable "columns" represents this
  columns, err := rows.Columns()
  if err != nil {
    log.Fatal(err)
  }

  // variable "values" is a pre-populated array of empty interfaces
  // We load an empty interface for every column 'sql.Rows' has.
  // The interfaces will allow us to call methods of any type that replaces it
  values := make([]interface{}, len(columns))

  // for every key we find while traversing array "columns"
  // set the corresponding interface in array "values" to be populated
  // with an empty sql.RawBytes type
  // sql.RawBytes is analogous to []byte
  for key, _ := range columns {
    values[key] = new(sql.RawBytes)
  }

  //Contrary to appearances, this is not a loop through every row
  // "rows.Next()"" is a recursive function that is called immediately
  // upon every row until we hit "rows.Next == false"
  // This is important because it means you must prepopulate variables or
  // arrays to the exact number of columns in the target SQL table
  // more details at: https://golang.org/pkg/database/sql/#Rows.Next
  for rows.Next() {
    //the "values..." tells Go to use every available slot to populate data
    err = rows.Scan(values...)
    if err != nil {
      log.Fatal(err)
    }
  }

  for index, columnName := range columns {
    // convert sql.RawBytes to String using "fmt.Sprintf"
    columnValue := fmt.Sprintf("%s", values[index])
    
    // Remove "&" from row values
    columnValue = strings.Replace(columnValue, "&", "", -1)
    
    // Optional: Don't display values that are NULL
    // Remove "if" to return empty NULL values
    if len(columnValue) == 0 {
      continue
    }

    // Print finished product
    fmt.Printf("%s: %s\n", columnName, columnValue)
  }
}

Please let me know if this code works for anyone!

1 Like