mysql data cannot be queried using go gorm

Question

I use gorm to count qps of mysql, but the query data is always empty. The program did not report any errors, and I could have used similar code to get my own test table data.

My code

type mysqlQueires struct {
    Queries sql.NullString `gorm:"column:queries"`
    Uptime  sql.NullString `gorm:"column:uptime"`
}

type attendees struct {
    Id            sql.NullString `gorm:"column:id"`
    Created_at    sql.NullString `gorm:"column:created_at"`
    Updated_at    sql.NullString `gorm:"column:updated_at"`
    Deleted_at    sql.NullString `gorm:"column:deleted_at"`
    Attendee_user sql.NullString `gorm:"column:attendee_user"`
    Meeting_id    sql.NullString `gorm:"column:meeting_id"`
}


func (n *Node) GetDB() *gorm.DB {
    return n.db.Session(&gorm.Session{})
}

func (n *Node) GetQps() (int64, error) {
    sys := []mysqlQueires{}

    rawQueries := "show global status where variable_name in ('queries','uptime')"
    result := n.GetDB().Raw(rawQueries).Scan(&sys)
    if result.Error != nil {
        xlog.Error("query variable_name error", "get qps", false, "err=", result.Error)
        return -1, result.Error
    }
    if result.RowsAffected == 0 {
        xlog.Error("query variable_name result 0", "get qps", false)
        return -1, fmt.Errorf("query variable_name result 0")
    }

    //
    fmt.Println(sys)
    //result:
    //[{{ false} { false}} {{ false} { false}}]

    var variable sql.NullString
    n.GetDB().Raw("SHOW GLOBAL STATUS LIKE 'Questions'").Scan(&variable)
    fmt.Println(variable)
    //result:
    //sql: expected 2 destination arguments in Scan, not 1
    //{ false}


    var ttt []attendees
    n.GetDB().Raw("select * from attendees").Scan(&ttt)
    fmt.Println(ttt)
    //result:
    //[{{1 true} { false} { false} { false} { false} {1111 true}}]

    return 0, nil
}

I used dsn is "root:rootPwd@tcp(localhost:3306)/test_example". The same command can be used to retrieve data using cmd and DBeaver. May I ask if there is a problem with my mysql configuration or the go code needs to be corrected?

Answer 1

It seems your mysqlQueries struct properties are not suitable for the output of the show global status command.

Mysql documentation shows result as follows;

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
...
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
...
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

According to that info, you should change the mysqlQueries struct as follows;

type mysqlQueries struct {
    VariableName string `gorm:"column:variable_name"`
    Value  int `gorm:"column:value"`
}

And while sending that raw show global status query,

    var records []mysqlQueries
    n.GetDB().Raw("SHOW GLOBAL STATUS").Scan(&records)
    fmt.Println(records)

It should map the actual values inside that slice.